前阵子有个小组的后台接口频繁报警查了半天发现是日志表的数据写入方式太原始——一条一条循环INSERT压测一上来直接拖垮了数据库连接池。那一刻我又想起刚学SQL时的自己以为“添加数据”就是写一句INSERT INTO ... VALUES就完事了。实际上等你在真实项目里用SQL加数据要考虑的东西远比一行INSERT多得多字段约束、批量写入、冲突策略、事务边界、字符集……这篇就把“SQL如何添加数据”这件事从头到尾拆开讲适合刚接触数据库的同学也适合平时写业务SQL但没系统梳理过插入逻辑的人。1. 添加数据前先把这几件事想清楚很多人拿到一张表急着往上塞数据结果要么报错、要么写入后数据是脏的。我在实际开发里养成的习惯是动手写INSERT之前先花三十秒做三件事——看表结构、看约束、看默认值。这个习惯帮我省掉了至少一半的返工。1.1 先看表结构字段名、约束、默认值以最常见的用户表为例建表语句长这样CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, age INT NULL DEFAULT 18, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );这个表结构里已经埋了好几个会影响INSERT的规则id是自增主键写入时通常不传数据库自己生成username有NOT NULL UNIQUE说明不能为空而且不能重复age虽然允许为空但有默认值18不传的时候自动落18created_at默认当前时间通常也不需要手动传入。实操建议写SQL前先用DESC users;或SHOW CREATE TABLE users;确认一遍字段定义。不同客户端的显示方式略有差别但核心信息都一样——字段名、是否允许NULL、有无默认值、是否主键。尤其是团队共用的表字段含义经常靠口头沟通很容易传错看一遍结构比问十个人都靠谱。1.2 NULL、NOT NULL与DEFAULT这三个关键词如何影响写入一次写入失败最常见的根因就是对这三个词的理解不到位。如果字段被定义为NOT NULL你INSERT时不给它值并且它又没有默认值数据库直接抛错如果字段允许NULL你不给值它最终落进表里的就是NULL要注意NULL不是空字符串它表示“未知”如果字段有DEFAULT你INSERT时不写该字段数据库自动填默认值。看下面这条INSERTINSERT INTO users (username, email) VALUES (neo, neoexample.com);因为age有默认值18created_at有默认当前时间id自增所以这条语句完全可以执行。最终落库的效果等同于INSERT INTO users (username, email, age, created_at) VALUES (neo, neoexample.com, 18, NOW());有些新手喜欢把每一个字段都写进VALUES但又不确定默认值是什么干脆填个NULL进去。这会把age的默认值机制完全绕过落库的就是真正的NULL后续做统计或展示时还要额外处理属于自己给自己挖坑。原则就是能依赖默认值的字段就别显式传默认值本身就是表的约定。2. 单行插入INSERT INTO的完整姿势与常见误区先看最基本的单行插入。语法本身不复杂但写法细节会影响可读性和后续维护成本。2.1 INSERT INTO的标准写法和字段省略写法标准写法是指定字段列表后再给值INSERT INTO users (username, email, age) VALUES (alice, aliceexample.com, 25);这种写法的好处是字段和值一一对应哪怕表结构发生了字段增删只要你的列表里还是这三个字段SQL就不会因为其他新字段而失败。我在重构老系统时特别喜欢这种写法——它把“我要写入哪几列”表达得非常明确。省略字段列表的写法是这样的INSERT INTO users VALUES (bob, bobexample.com, 30, 2024-01-01 10:00:00);看着省事实际上风险很高必须按照建表时的字段顺序填值一个都不能漏自增主键也要你手动给值一旦给错或漏掉要么报错要么制造脏数据表结构只要新增一个字段这条SQL立刻失效。我的建议非常直接生产环境一律写完整字段列表省略字段列表的写法只适合自己本地临时玩一下不要带进业务代码。2.2 自增主键该不该写在VALUES里自增主键AUTO_INCREMENT在大多数情况下都不需要手动指定。你不写它数据库会取当前最大值加一。但有一种场景需要显式写入你要把某张历史表的数据迁移到新表并且希望保留原来的ID。比如从users_old迁移到users_new就这么写INSERT INTO users_new (id, username, email, age) SELECT id, username, email, age FROM users_old;这里显式带上id就是为了保持新旧数据的主键一一对应。需要注意的是显式插入过自增ID之后MySQL的自增计数器会更新到当前最大值普通插入会自动跳开这些已占用的ID值不用担心冲突。2.3 日期、字符串、布尔值的正确写法字符串和日期一定要用单引号包裹。数字可以不加但我个人习惯统一加引号让SQL更直观减少出错概率。布尔值在不同数据库里写法差别比较大数据库布尔字段定义INSERT写法MySQLTINYINT(1) 或 BOOLVALUES (..., 1, ...)PostgreSQLBOOLEANVALUES (..., TRUE, ...)SQL ServerBITVALUES (..., 1, ...)OracleNUMBER(1)VALUES (..., 1, ...)日期字段的格式也是个经典坑。MySQL默认支持YYYY-MM-DD HH:MM:SS但很多接口传过来的是时间戳此时要么在SQL里用FROM_UNIXTIME()转换要么在应用层处理好再拼进SQLINSERT INTO orders (order_no, created_at) VALUES (A10001, FROM_UNIXTIME(1735689600));时间字段宁可统一交给数据库处理也不要靠应用层拼接字符串否则十有八九会出现时区错乱和格式混乱。3. 批量插入与INSERT SELECT把“加数据”变成“移数据”很多性能问题不是因为数据量多大而是因为插入的方式太蠢。一次插入一万条记录和一万次每次插一条性能差一个数量级不止。这一节讲两种用得最多的批量写入方式。3.1 VALUES列表的多行批量用一条SQL插入多行这是最直观的批量写入方式INSERT INTO users (username, email, age) VALUES (u1, u1example.com, 20), (u2, u2example.com, 21), (u3, u3example.com, 22);一次写几百行甚至几千行通常没问题。真实项目中比较稳妥的单条SQL批次大小是500到2000行具体看你所在的数据库版本和参数配置。批量插入相比逐条插入主要省了三块开销SQL解析编译只需要一次网络往返从N次降为1次多个聚合操作在事务日志里可以被更紧凑地记录。注意点单条SQL太大也会有问题。我见过有人把十万条数据拼成一条INSERT结果数据库直接报max_allowed_packet超限。与其赌边界不如在代码里固定每500条一批分批执行稳得多。3.2 INSERT INTO ... SELECT从已有表复制数据比手工罗列VALUES更高级的是从查询结果直接插入。比如你需要给所有年龄大于25的用户创建一份“活动备份”INSERT INTO users_backup (username, email, age) SELECT username, email, age FROM users WHERE age 25;这种方式特别适合做数据归档、表拆分、临时数据准备。它把“加数据”变成了“移数据”——源表有多少行符合条件目标表就添加多少行。需要注意两件事查询结果的列数、类型必须和目标表的字段列表匹配如果源表数据量很大先不要指望一条SQL瞬间完成分批配合过滤条件执行更可控。3.3 临时表的另类插入CREATE TABLE AS SELECT除了INSERT INTO ... SELECT还有一类场景是“边建表边灌数据”。比如你想快速构建一个报表用的宽表不想手动定义每个字段CREATE TABLE user_order_stats AS SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY user_id;这在PostgreSQL和MySQL里都很常用。它和你先建表再INSERT的最终效果类似但省去了手动写字段定义的步骤。代价是字段类型完全由查询结果推导精度和默认值未必符合你的预期。如果要靠这张表做长期跑批我更建议老老实实先建好正式表结构再用INSERT SELECT填充数据控制力更强。4. 重复写入怎么办主键冲突的三种处理策略日常开发里最让新手头疼的插入场景就是数据可能重复。比如上游接口推送了一条订单你INSERT时发现主键或唯一键已经存在程序当场炸掉。4.1 直接报错与应对思路先理解为什么会报错。以users表为例id是主键username有唯一约束。当你插入一个已存在的username时数据库会拒绝写入ERROR 1062 (23000): Duplicate entry alice for key users.username如果你不在意这行是否重复只想跳过可以用INSERT IGNOREMySQLINSERT IGNORE INTO users (username, email, age) VALUES (alice, aliceexample.com, 25);这个语句的语义是有冲突就放弃本次插入不报错。它尤其适合日志采集、去重导入这类“丢一条也无所谓”的场景。4.2 MySQL的ON DUPLICATE KEY UPDATE与REPLACE INTO如果需要冲突时更新而不是跳过就轮到ON DUPLICATE KEY UPDATE上场INSERT INTO users (username, email, age) VALUES (alice, aliceexample.com, 26) ON DUPLICATE KEY UPDATE email VALUES(email), age VALUES(age);这个语句的意思是如果能正常插入就直接插入如果遇到主键或唯一键冲突就执行后面的UPDATE逻辑。在业务里这种“存在就更新不存在就插入”的能力通常被称为UPSERT是订单状态同步、用户画像更新等场景的常见需求。顺便提一下REPLACE INTOREPLACE INTO users (id, username, email, age) VALUES (1, alice, newaliceexample.com, 26);它和ON DUPLICATE KEY UPDATE看起来都解决了重复写入问题但行为完全不同REPLACE是先删除旧记录再插入新记录。这会导致自增ID变化如果有外键引用还可能引发级联删除酿成大事故。我基本不推荐在业务表上使用REPLACE除非你非常清楚删除行为的连锁影响。4.3 PostgreSQL的ON CONFLICT与SQL Server的MERGE不同数据库对UPSERT的实现方式不一样。PostgreSQL的写法是ON CONFLICT语义更清晰INSERT INTO users (username, email, age) VALUES (alice, aliceexample.com, 27) ON CONFLICT (username) DO UPDATE SET email EXCLUDED.email, age EXCLUDED.age;这里的EXCLUDED表示“本次想插入但被挡下来的行”相当于MySQL里VALUES()的作用。如果你只想让它在冲突时什么都不做可以写DO NOTHING。SQL Server则倾向于用MERGE写起来会啰嗦一些MERGE users AS target USING (SELECT alice AS username, aliceexample.com AS email, 28 AS age) AS source ON target.username source.username WHEN MATCHED THEN UPDATE SET email source.email, age source.age WHEN NOT MATCHED THEN INSERT (username, email, age) VALUES (source.username, source.email, source.age);我给个横向对比表方便大家记数据库冲突处理语句适用场景MySQLON DUPLICATE KEY UPDATE按主键或唯一键更新MySQLINSERT IGNORE / REPLACE INTO跳过重复 / 删除后重插PostgreSQLON CONFLICT DO UPDATE / DO NOTHING按指定唯一约束处理SQL ServerMERGE复杂同步场景SQLiteON CONFLICT / UPSERT轻量级场景读到这里你应该明白一个核心思路添加数据不是只管“塞进去”还得管“塞不进去怎么办”。UPSERT不是花哨技巧而是真实业务里每天都要用的基本能力。5. 插入性能、事务与真实项目中的经验SQL写得好不好除了语法正确还要看性能表现和事务边界。这一节聊几个和INSERT关系密切的实际问题。5.1 一次插入一万行与一次插入一万次先说结论在绝大多数数据库里一次批量插入一万行远远快于一万次单行插入。原因可以拆成三点每次INSERT都要经历解析、优化、执行、日志写入一万次重复这个全套流程CPU和IO开销成倍增加网络交互次数差三个数量级局域网内还能忍跨机房调用时这种差异会被无限放大大批量插入时数据库可以更高效地组织磁盘写入减少随机IO。但“批量”也不是越大越好。单条SQL太大可能撑爆网络缓冲区也可能长时间占用表锁拖慢其他查询。我的经验值是常规导入场景每批500到1000行高并发业务入口每批100行左右具体要看行宽和字段数量行宽大的表要适当缩小批次。5.2 事务提交的时机与回滚如果你在一次操作里要写多张表或者写入多行数据且要求“要么全成功、要么全失败”就必须显式使用事务。MySQL的InnoDB、PostgreSQL、SQL Server都支持事务基本用法是START TRANSACTION; INSERT INTO orders (order_no, user_id, amount) VALUES (A10002, 1, 99.00); INSERT INTO order_items (order_no, product_id, qty) VALUES (A10002, 101, 2); UPDATE users SET total_orders total_orders 1 WHERE id 1; COMMIT;如果在COMMIT之前发生任何一步失败可以执行ROLLBACK;把所有写入全部撤销。这里有个容易犯的错误很多人分不清“写了但没提交”和“没写”。在事务未提交之前这条数据只有你自己当前会话能看到其他连接是看不到的。曾有同事在测试环境发现“数据偶尔看不到”排查半天原来是他代码里开了事务忘提交。解决方法很简单——养成写操作后立刻COMMIT的习惯除非你明确知道要跨多个操作保持原子性。5.3 插入数据慢的常见原因索引、字符集、日志遇到INSERT变慢按下面几个方向排查基本都能找到原因索引过多每插入一行所有二级索引都要同步更新。表上挂了七八个索引写入速度会肉眼可见地下降。对导入场景可以考虑先删掉非必要索引导入完再重建字符集与排序规则表使用utf8mb4但连接字符集是latin1一类的错配不但可能乱码还会导致隐式转换增加处理成本事务日志刷盘频率每次COMMIT都触发日志刷盘。如果业务允许小概率丢失最近数据可以把innodb_flush_log_at_trx_commit调整为2MySQL写入性能会明显改善但这是有取舍的调优默认不要动唯一键查重如果INSERT的目标表带唯一索引每次写入都要先查一遍是否冲突。数据量上千万后这个名字查重本身就会成为瓶颈。一句话总结插入慢不一定是数据库机器的锅更多时候是表设计和连接配置的问题先把这些检查一遍再考虑上机器配置。6. 我踩过的坑补充几个实际问题的排查记录最后分享几个我在实际项目里碰到的INSERT相关怪问题每一个都对应一个具体的经验点。6.1 中文乱码不是SQL的错是连接字符集的错有次同事导入一批中文用户名SQL在客户端里看完全正常写入后用SELECT查出来全是问号。查到最后根因是连接数据库的字符集没设置对和INSERT语句本身没关系。MySQL可以在连接后立即指定字符集SET NAMES utf8mb4;表、连接、客户端三者的字符集要一致。实践经验是任何需要写入中文、表情符号的系统建表时统一用utf8mb4连接层也显式设置为utf8mb4能避免绝大多数乱码问题。6.2 自增ID莫名其妙跳号可能是写入失败占用了ID有人刚用INSERT IGNORE导数据发现自增ID出现大量空洞1、2、4、6、8……开始以为是数据被删了检查之后发现是INSERT IGNORE在遇到冲突时也消耗了自增ID。这个现象在MySQL里是正常的自增ID的生成发生在插入尝试阶段而不是最终成功之后。失败或跳过并不回收已分配的ID。如果业务上有“ID必须严格连续”的需求就别指望数据库的自增功能需要自己维护一套编号分配逻辑。6.3 数据导入失败的权限问题还有个容易被忽略的报错不是语法问题而是权限不足ERROR 1142 (42000): INSERT command denied to user app_userlocalhost如果某条INSERT在生产环境执行不了先别急着改SQL用SHOW GRANTS FOR app_userlocalhost;查一下账号权限。有时候业务账号只有SELECT权限你要找DBA开通对应表的INSERT权限而不是绕过去在线上用root乱试。写在最后的个人建议我自己的习惯是一个系统的数据写入逻辑从第一天就分三层单条写入走业务接口批量导入走专门的导入脚本冲突处理统一封装成UPSERT函数。表结构变更时先跑一遍“试插入”看清楚约束再动真格。SQL里的添加数据看似基础但凡是线上事故多半就藏在“没想清楚默认值”“没处理重复”“没控制批量大小”这几个细节里。把这些基础坐实了后面写复杂查询和分析脚本都会顺手很多。