INSERT 这语句是很多人职业生涯里写下的第一条 SQL。学校里教INSERT INTO ... VALUES (...)背个语法就完事可实际到了线上你会碰到死锁、主键冲突、批量插入慢到报警、字符集报错、id 跳号一堆问题每一件都够你排查半天。因为 MySQL 的 INSERT 表面是“往表里加一行”背后却牵扯解析器、存储引擎、索引更新、锁、事务日志、主从复制一整条链路。这篇就把 INSERT 从头到尾拆一遍结合我日常踩过的坑和调优记录把语法、原理、常见报错、性能优化一次说清楚适合刚入门的新手也适合写 CRUD 写了两三年还没深究过的开发同学。1. INSERT 的基本形态与执行逻辑1.1 最常用的几种 INSERT 写法先过一遍最基础的。标准写法是这样INSERT INTO user (id, name, age, created_at) VALUES (1, 张三, 25, NOW());这里面有个很多人没注意的点列名到底写不写如果你不写列名直接INSERT INTO user VALUES (...)那必须按表结构里的字段顺序把值一个一个补齐缺一个就报错。我见过不少老系统为了省事不写列名结果后来表结构加了字段插入直接崩。所以我的习惯是永远显式列出要插入的列哪怕只写一行代码也别省这个信息。理由很简单列名一旦明确代码的可读性和容错性都上去了以后别人接手、或者表加字段都不会因为位置错位出问题。还有一种是单行写成 SET 形式INSERT INTO user SET id 1, name 张三, age 25;这种写法有点类似 UPDATE 的语法适合插入的列比较少、且一眼能看明白的场景。不过需要注意INSERT ... SET在 MySQL 官方文档里属于扩展语法在某些自动化迁移工具里可能不被兼容。我个人更推荐标准写法但也不排斥这种团队成员统一风格就行。1.2 这条 SQL 在 MySQL 内部到底经历了什么很多人以为 INSERT 就是把数据丢进表里其实它走的路径挺长。一条 INSERT 进入 MySQL 之后大致要过这几关第一关是连接层负责权限校验和连接管理。正常情况下你至少要有对这张表的 INSERT 权限否则在这里就被拦下来了。第二关是解析器MySQL 会把 SQL 文本拆成语法树检查关键字、表名、列名、值数量是否匹配。这阶段出问题会直接抛ERROR 1064 You have an error in your SQL syntax最常见的场景就是漏逗号、括号不匹配、字符串少引号。第三关是优化器。对 INSERT 来说优化器做的主要工作是确定插入哪个表、检查有哪些索引需要更新、提前计算分区路由等。注意这里说的“优化”不是你想的那种复杂执行计划生成—— INSERT 的执行路径相对固定不会有 JOIN 那种多表关联的局面所以优化器在 INSERT 上能折腾的空间比较小但它会做一些关键判断比如是否走批量插入的优化路径、唯一性冲突的检查顺序。第四关是存储引擎层。以 InnoDB 为例这里会做缓冲池判断——目标行对应的数据页是否在内存里如果在直接在内存页里写入如果不在要把页面从磁盘捞进缓冲池再写。这还没完写完数据页之后还会随后台线程异步刷回磁盘但你需要先保证事务提交时日志已经落盘。第五关是 InnoDB 的日志系统。这里我要多说一句因为很多人对“InnoDB 怎么保证断电不丢数据”有误解。InnoDB 对 INSERT 不是直接写磁盘上的表文件而是先写redo log然后等事务提交时把 redo log 刷到磁盘。一旦 redo log 落盘哪怕数据页还在内存里没刷出去MySQL 挂了之后重启也能通过 redo log 恢复。这个机制叫 WALWrite-Ahead Logging是 InnoDB 可靠性的基石。所以你可以这么理解一条 INSERT 真正的性能瓶颈不在 SQL 本身的语法而在磁盘日志刷盘的频率。1.3 NULL、默认值与取舍逻辑插入时最容易出问题的是 NULL 和默认值。MySQL 的默认行为是你插入某列时如果显式给了 NULL那就存 NULL即使这列定义了DEFAULT 0NULL 也会覆盖默认值。想要数据库在你不提供时填 0你得在 INSERT 语句里完全省略这列而不是传 NULL。-- 假设 age 列是 DEFAULT 0 INSERT INTO user (name, age) VALUES (李四, NULL); -- 存进去的是 NULL INSERT INTO user (name) VALUES (李四); -- 存进去的是 0这个差异在业务上很坑。比如统计平均值、年龄比较的时候NULL 和 0 会产生完全不同的结果。我经历过一次线上故障就是 API 层把前端的空字符串转成了 NULL 再插入结果 NULL 直接覆盖了业务默认值导致后续一堆IS NULL判断的逻辑全部失效。所以建议在应用层的 DTO 或实体里就明确字段语义是“不传用默认值”还是“允许显式存 NULL”这两者不能混。另外MySQL 8.0 里时间字段如果你什么都不传默认值是CURRENT_TIMESTAMP但旧表如果是从 5.6/5.7 迁移上来的有可能默认值是0000-00-00 00:00:00这种情况这在新版严格模式下会直接报错。查一下SHOW CREATE TABLE看清楚 DDL再决定是否 SQL_MODE 里去掉NO_ZERO_DATE。这一块最容易在数据迁移后冒出来别问我怎么知道的。2. 插入数据的进阶姿势2.1 多行批量插入的性能逻辑一次性写多行是 INSERT 最常见的优化手段之一INSERT INTO user (name, age) VALUES (张三, 20), (李四, 21), (王五, 22);这个做法的提速原理在于它把 N 条 INSERT 的 SQL 解析、网络传输、日志写入合并成了更少次数。但是注意这里有一个关于 redo log 和 binlog 的细节多行插入并不会减少真正写入磁盘的数据量数据总归要落盘但它能减少日志刷盘时“同步等待”的次数通过分摊把单条插入的固定开销摊薄。实际经验是单条 INSERT 逐行插入 10 万条数据可能要几分钟但把它们合并成每批 1000 行、每批用多行值的方式插入通常能提速 5 到 10 倍。这里有个经验值单批 500 到 2000 行比较合适。太少起不到效果太多会导致单条 SQL 超过max_allowed_packet默认 64MB一般不会撞但遇到大的 TEXT/BLOB 就会也容易让主从延迟飙高。2.2 INSERT IGNORE、ON DUPLICATE KEY UPDATE 与 REPLACE 的取舍这三个都是针对“插入时唯一键冲突”的解决思路但机制差异很大。INSERT IGNORE遇到主键或唯一索引冲突时直接忽略这条数据不报错也不更新。它适合“只入不存在的新数据”的场景比如同步历史数据时跳过已存在的记录INSERT IGNORE INTO user (id, name) VALUES (1, 张三);INSERT ... ON DUPLICATE KEY UPDATE遇到冲突时转为 UPDATE这才是真正的“有则更新无则插入”也就是常说 UPSERTINSERT INTO user (id, name, age) VALUES (1, 张三, 30) ON DUPLICATE KEY UPDATE age VALUES(age);这里有个 MySQL 8.0.20 的坑VALUES()函数在ON DUPLICATE KEY UPDATE里被标记为废弃未来的版本可能会彻底移除官方推荐改用别名语法INSERT INTO user (id, name, age) VALUES (1, 张三, 30) AS new ON DUPLICATE KEY UPDATE age new.age;实际项目里能少写一个坑就先少踩一个新写的代码建议直接用别名写法。另外ON DUPLICATE KEY UPDATE触发更新逻辑时如果更新的值和原值完全相同MySQL 返回的受影响行数是 0如果值有变化返回 1如果是新插入返回 1。有些人用受影响行数判断“有没有插入”这个会踩坑正确做法是看返回的“2”更新还是“1”插入不同驱动表现略有差异需要以实际返回为准。REPLACE INTO则是暴力的先删后插。遇到唯一键冲突先 DELETE 旧行再 INSERT 新行。副作用很明显如果表上有外键引用REPLACE 可能因为 DELETE 触发外键限制而失败旧行被删、新行插入生成一个新的主键自增 id即使业务上主键没变化这个 id 也会重新分配删除和插入在事务里并不是原子的中间会有一小段时间旧行不存在对实时读会有影响如果一个表有多个唯一索引REPLACE 删除的可能是另一条你没预期的行这个坑特别隐蔽。结论很简单能不用 REPLACE 就别用。大多数场景用ON DUPLICATE KEY UPDATE替代语义更安全、更可控。2.3 INSERT ... SELECT 与数据迁移陷阱INSERT ... SELECT可以从一张表查出数据直接插入另一张表批量导数据时非常好用INSERT INTO user_new (id, name, age) SELECT id, name, age FROM user_old WHERE age 30;但在这个语句上MySQL 有一个真实存在的“坑”如果 binlog 格式是 STATEMENTMySQL 为了确保主从一致会给INSERT ... SELECT涉及的源表和目标表都加锁源表加的是共享锁也就是在整个执行期间其他人不能往源表和目标表里写数据。这在线上大表上跑的时候意味着业务写入会被卡住。排查手段是退化到 ROW 格式或者改成“分批 SELECT 出来再逐批 INSERT”避免一条 SQL 长时间锁表。还有一点INSERT ... SELECT要明确两条路线的数据一致性。默认 REPEATABLE READ 隔离级别下SELECT 看到的是一个快照吗不完全。INSERT ... SELECT内部的 SELECT 在某些参数配置下受 binlog 格式影响会和普通 SELECT 的可见性不同。所以稳妥的做法是先单独 SELECT 确认数量再执行插入同时用事务把整个操作包起来。2.4 INSERT 加锁的一个冷门方向插入意向锁根据热搜词里大量出现的“mysql锁的分类”“锁表”可见锁的问题是大家非常关心的。但这里我不去把锁的分类像字典一样铺开只聚焦到 INSERT 真正会碰到的锁。InnoDB 在插入一条记录时会在索引项之间申请一种叫插入意向锁Insert Intention Lock的间隙锁。它本质是一个“排队机制”如果两个事务要往同一个间隙里插数据它们彼此之间其实不会冲突但必须等待前面事务在间隙上持有的锁释放。这听起来有点绕举个实际例子事务 A 对主键范围 (10, 20) 的间隙加了间隙锁事务 B 想往里面插入 id15此时 B 就需要等待 A 提交或回滚。如果 A 只是插入 id15 并提交B 再插 16 就不会互相阻塞。死锁的高发场景往往是“两个事务互相持有对方需要的间隙锁”。最典型的是A 先插入一条记录未提交B 去更新/删除 A 插入的记录或者两个事务交替在同一间隙里插入多条数据。避开的方法核心就一条同一批业务操作里对多个表的插入顺序保持全局一致就像转门一样大家朝一个方向进别中途绕道。3. INSERT 性能、自增主键与事务细节3.1 自增主键的分配逻辑为什么 id 会跳号InnoDB 的自增主键分配在 MySQL 8.0 里的实现方式已经和 5.7 不一样了。5.7 及更早版本AUTO-INC 锁是表级锁插入时会把整张表的自增计数器锁住等插入完成才释放所以并发插入性能很差。8.0 默认用的是innodb_autoinc_lock_mode2交错模式不再持有表级锁自增值的分配变成在内存里直接递增性能上去了但代价是已经分配出去的自增值如果事务回滚了不会归还。这就是为什么你会发现表里最大 id 是 100但下一次插入直接跳到 103中间空了 3 个号。所以面试里常说“自增主键为什么会有空洞”根源就在这里。对于业务而言自增 ID 不连续是完全正常的不要去依赖 ID 的连续性做业务判断。如果必须保证连续号段就不能用自增主键得自己在应用层设计号段分配但这又是一个新的并发控制问题多数业务没必要这么干。还有一个高频问题LAST_INSERT_ID()在多行插入时返回的是哪一条的 id答案是第一条生成的 id。如果你用多行 VALUES 插入 100 条LAST_INSERT_ID()返回第一条分配的 A之后你可以在应用层按A到A99推算后续行 id但前提是表里没有其他并发插入干扰。所以在高并发下用这个函数推算连续 ID 是不可靠的这也是为什么很多系统干脆不依赖数据库自增主键作为业务编号。3.2 大量插入时的索引维护代价INSERT 不只是往表的“末尾”加数据如果表上有二级索引每一行都要同步更新所有索引。二级索引越多插入越慢这是索引维护的物理成本绕不开。而且二级索引和数据页并不一定在同一个位置插入一条记录可能涉及多个随机页的读改写。这直接影响批量插入策略导数据之前先把非必要的二级索引删掉导完再重建。比如在一个 200 万行的表里有一个二级索引和没有二级索引插入速度可能相差 3 倍以上。因为每插一行都要去更新那棵 B 树。另外一个隐藏点如果二级索引是随机值比如 UUID插入会造成大量的随机页访问和页分裂性能会比自增连续的表差很大。这也是为什么业内强烈不建议用 UUID 做主键。但如果是 UUID 作为二级索引同样会有类似的问题只是影响面小一些。3.3 在代码里批量插入的正确姿势我见过很多新手写循环插入代码for (User user : userList) { jdbcTemplate.update(INSERT INTO user ..., ...); }一次 10 万条这种写法能跑十分钟还要承受网络往返和日志刷盘。正确做法是在代码层面做分批比如每 500 条拼成一个多行 INSERT然后用事务包住批次。这里有个安全边界值得备注单条 SQL 太长会导致 binlog 变大、主从延迟、max_allowed_packet超限。所以最好是1000 行一批commit 一次观察主从延迟再进行下一批。如果延迟明显就把批次调小到 200 或 500。事务大小也有讲究一个事务插 10 万行不是不能做但事务太大回滚段压力大一旦中途出错回滚要等很久才能释放锁资源。所以批量插入时把事务拆成多个中等事务通常是更稳妥的选择。3.4 可以临时调整的插入相关参数如果你是 DBA 或者有权限执行SET SESSION ...在导数据时可以临时调整下面几个参数插入速度能提升得很明显innodb_flush_log_at_trx_commit默认是 1表示每个事务提交都要刷盘最安全但最慢。导数据时临时设成 2表示写到操作系统缓存即可不强制落盘速度会快很多但要注意异常断电会丢最近 1 秒的已提交记录。sync_binlog默认 1改成 0 或 N 可以降低磁盘 I/O 压力但同样会降低安全性。innodb_autoinc_lock_mode如果你还在用 5.7插入频繁时确认它是 2如果有些逻辑依赖自增值连续的用 1性能损失但连续性好。unique_checks 0关闭唯一性检查可以加速导入但导入后要自己保证数据唯一否则会出现脏数据。foreign_key_checks 0关闭外键检查用在重新导入顺序敏感的数据时避免子表先插导致父表不存在的报错。这几个参数建议只在明确的一次性导数据场景里调整生产环境的常规业务写入保持默认值尤其是innodb_flush_log_at_trx_commit1那是保命的。3.5 INSERT 与事务的隔离、回滚协作INSERT 其实天然站在事务体系里。事务的四大特性INSERT 全占原子性靠 undo log回滚日志实现持久性靠 redo log 实现隔离性靠锁和 MVCC 实现一致性靠层层约束和日志协调实现。所以我在看 INSERT 问题的时候从来不是只看一条 SQL而是看它所在的事务边界。打个比方线上某个 UPDATE 的大事务阻塞了后面的 INSERT你查 INSERT 本身执行计划再优化也没用因为瓶颈在别的语句持有了锁。这就是为什么排查 INSERT 阻塞问题时performance_schema的锁等待、INFORMATION_SCHEMA.INNODB_TRX、sys.innodb_lock_waits这些视图比 EXPLAIN 更有用。SELECT * FROM sys.innodb_lock_waits\G这条查询能直接告诉你当前等待的锁是被哪个事务持有这个事务执行了什么 SQL。拿到事务 ID再去看它是什么时候开始的、持有了多久基本就能定位是长事务没提交导致下游插入全部卡死。4. 常见问题与排查技巧实录4.1 报错 1062Duplicate entry 主键/唯一键冲突这是 INSERT 最经典的报错。排查思路不是单纯看数据重不重复而是分清冲突键。如果冲突的是主键那大概率是应用生成主键的算法有 bug比如雪花 ID 回拨导致生成重复 id如果冲突的是唯一索引就要看业务上到底允不允许同一字段重复。如果是并发场景两个请求同时插入相同唯一键的数据建议用ON DUPLICATE KEY UPDATE而不是在应用层先 SELECT 再判断插入因为后者中间有时间窗口高并发下照样冲突。真正的处理方案是让数据库来约束应用去捕获。4.2 报错 1366Incorrect string value 字符集与中文乱码插入中文时报Incorrect string value: \xE5\xBC\xA0...基本是字符集不一致。核心检查链路是连接层字符集、数据库/表字符集、字段字符集三者必须一致链。连接层设置执行SET NAMES utf8mb4;表和字段字符集在 DDL 里指定不要依赖默认值CREATE TABLE user ( name VARCHAR(50) CHARACTER SET utf8mb4 ) DEFAULT CHARSETutf8mb4;为什么用 utf8mb4 而不是 utf8因为 MySQL 的 utf8 实际上是 utf8mb3只有 3 个字节存不了 emoji 和部分生僻汉字。新库一律用 utf8mb4没跑。还有一个很隐蔽的坑utf8mb4_0900_ai_ci是 MySQL 8.0 默认排序规则但如果你把 5.7 的库导入 8.0旧表可能还是utf8mb4_general_ci。排序规则不影响存储但影响比较和索引。如果查询时因为排序规则混用导致字符比较出错一般会在 JOIN 或 WHERE 环节暴露插入阶段还好。4.3 报错 1406 与 1264数据过长、数值溢出1406Data too long for column意思是字符串超出字段长度。要注意VARCHAR(50) 里的 50 默认指字符数不是字节数所以中文英文都按一个字符算。但如果是旧库用了 GBK/BIG5那是按字节算这时 VARCHAR(50) 只能存 25 个汉字。所以就这一条就够坑很久的人。1264Out of range value是数值超出 INT/BIGINT/DECIMAL 的范围。比如 TINYINT 范围 -128 到 127插入 200 就报这个。排查时先看表结构拿字段类型再去对业务数据。这两个错误还有一个共同源头SQL_MODE 的严格模式。如果 SQL_MODE 不包含STRICT_TRANS_TABLES部分非法值只会被截断或转成最接近的合法值然后给个 warning不报错。这在表面上看是“哪里都没错”实际数据可能已经变形了。尤其要注意 5.7 和 8.0 默认都开了严格模式但有些云数据库没有默认开启导致行为不一致。4.4 插入速度突然变慢的排查思路插入慢优先排查锁等待不要一上来就调参数。按这个顺序查看SHOW ENGINE INNODB STATUS\G里的 LATEST DETECTED DEADLOCK 和 TRANSACTIONS 段落查sys.innodb_lock_waits看当前有没有阻塞查performance_schema.table_io_waits_summary_by_table看表的 I/O 延迟查磁盘iostat确认是不是磁盘延迟满了最后再看执行计划和日志刷盘参数。有个用户提过的场景很典型备份或者大批量查询把 buffer pool 占满了插入时发现要读的页都不在内存全部变成物理读SQL 执行时间从 0.5ms 飙到 50ms。这种情况 INSERT 本身没问题是缓冲池命中率下降导致的。解决方向是控制后台任务并发度或者给插入路径预留足够内存。4.5 主键自增 id 被大量消耗前面提到过8.0 的 AUTO-INC 锁模式是交错分配就算插入失败已经分配的自增值也会继续消耗。比如一张表的自增主键从 1 到 10 万实际只成功插入了 5 万行另外 5 万是失败或回滚时“浪费”掉的。这个问题在业务上通常不用管但如果你的 id 被当作对外暴露的订单号或者消息编号就必须警惕。遇到这种需求建议把 ID 字段改成业务号比如关联日期、随机数、雪花 ID数据库主键只做内部标识。4.6 一个容易被忽略的边界INSERT 触发器表上的触发器也会影响 INSERT 行为。如果某个 INSERT 突然变慢了排查完索引和锁之后还要看一眼SHOW TRIGGERS。触发器里的 UPDATE、DELETE 语句非常容易拖慢 INSERT因为它们常常带着额外的事务逻辑。曾经碰到一个数据同步的案例主表每次 INSERT 后通过触发器去 UPDATE 一张汇总表结果汇总表的行级锁竞争把整个插入链路拖垮。后来把实时触发器改成定时批量汇总插入时间立刻降回毫秒级。5. 一个高效的 INSERT 功能扩展建议最后既然聊到这里了顺便提一个不常用但很有用的扩展MySQL 8.0 的VALUES语句是可以和INSERT组合来构造临时表的。比如INSERT INTO temp_report (uid, cnt) VALUES (1, 100), (2, 200), (3, 300);这种小批量的“计算底表”写法在写统计报表临时验证逻辑时非常好用免去反复SELECT UNION ALL的冗长语句直接靠 INSERT 把测试数据灌入跑完查询后要么回滚要么删表很方便。我个人还有一个习惯几乎所有的 INSERT 脚本都会包在一个显式事务里哪怕只插一条。这样做的原因是万一发生主键冲突或者数据异常回滚可以直接把已经插入的多条数据全部撤销不用一个个 DELETE 清理。脚本跑完再 COMMIT出问题就 ROLLBACK这个操作在本地跑数据修复时能省下很多“手滑时间”。踩过几次坑之后我对 INSERT 的态度是这样的越基础的语句越值得把它的底层机制吃透。很多人写了几十年 INSERT遇到报错还是靠搜本质上是因为没有理解它在索引、锁、日志、事务这套体系里的位置。真把这些想透了你写 SQL 的时候大概能预判哪条语句会慢哪里会堵哪里会产生额外副作用而不是等线上出故障才后悔。有机会再单独聊聊 UPDATE 和 DELETE那几个的坑不比 INSERT 少。