INSERT大概是所有 SQL 语句里长得最老实的一条看起来就是“往表里塞数据”很多同学写了两三年 SQL用到 90% 的场景也就一句话INSERT INTO 表名 VALUES (...)。可真到了线上环境你会发现这条“简单”语句背后全是坑主键冲突、字符集报错、批量插入慢到怀疑人生、自增 ID 莫名其妙疯狂跳号、并发一上来就锁等待超时。这篇文章把我这几年在 MySQL 插入数据这件事上踩过的坑和总结出来的经验完整过一遍从最基础的语法拆到类型转换、约束处理再到ON DUPLICATE KEY UPDATE、批量插入优化和报错排查。适合刚接触 MySQL 的初学者也适合写了两三年代码但没认真抠过细节的开发者对照着查能帮你少走很多弯路。1. 先看最基础的 INSERT 语法两种核心写法怎么选1.1 完整语法与两种基础写法MySQL 的 INSERT 语句标准语法可以简单拆成这么几块INSERT [IGNORE] INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);这是最常见也最推荐的一种写法显式列出目标列再给对应的值。除了这种还有两种变体也经常遇到-- 省略列名按表结构的列顺序给值 INSERT INTO 表名 VALUES (值1, 值2, ...); -- 使用 SET 方式更像 UPDATE 的写法 INSERT INTO 表名 SET 列名1 值1, 列名2 值2;SET写法其实在很多 ORM 框架底层会用到比如某些早期版本的框架在生成插入语句时就会转成这种形式。它的好处是列名和值一一对应写起来直观缺点是同一张表插入多条数据时要写多条语句没法合并成一条多值插入性能上不如VALUES列表方式。多值插入可以写成这样INSERT INTO 表名 (列名1, 列名2) VALUES (值1, 值2), (值3, 值4), (值5, 值6);这种一次性插多行的写法也是后面讲批量插入性能优化的基础先把语法记住。这里补充一个容易看花眼的细节官方文档里VALUES也可以写作VALUE两者等价。VALUE (1, 2)这种写法在某些自动生成的脚本里能看到是合法的不用觉得奇怪。1.2 为什么业务代码里建议显式写清列名很多初学者图省事喜欢写不带列名的INSERT INTO 表名 VALUES (...)。这种写法的前提是值的数量、顺序必须和表结构完全一致包括自增主键、默认值字段、时间字段一个都不能少顺序也不能乱。问题是表结构是会变的。我见过一次线上事故起因就是某个开发同学图省事写了不带列名的 INSERT后来 DBA 给表加了一个业务字段这条 SQL 直接在线上报错“Column count doesnt match value count at row 1”接口瞬间 500。排查的时候发现代码里几十处插入语句全是这种写法一次性全炸。显式写出列名至少有三个好处表结构增加字段时老的 INSERT 不受影响新字段有默认值就能正常插入。列的物理顺序随便调整SQL 都不会错。可读性强别人看 SQL 一眼就知道每个值对应的含义排查问题省很多时间。所以我个人的习惯是只要是写进业务代码或脚本里的 INSERT一律显式列名这一条从来没有例外。2. 类型转换、约束与自增主键插入前的隐藏逻辑2.1 严格模式才是你想要的模式MySQL 在插入数据时会对值做隐式类型转换。最经典的例子往INT类型的列里插入字符串abc非严格模式下 MySQL 不会报错而是把字符串转成0存进去插入12abc会转成12。这种自动容错看起来“方便”实际是在往数据表里埋雷。等到你按数字查数据时发现一堆脏数据根本不知道是哪条语句写入的。MySQL 5.7 开始默认开启了严格模式核心参数是STRICT_TRANS_TALLS。在严格模式下插入abc到整数列会直接报错Incorrect integer value: abc for column id at row 1。这样反而好问题在写入那一刻就暴露出来了。查看当前严格模式的命令SELECT sql_mode;比较常见的sql_mode配置会包含这些值ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION。其中NO_ZERO_DATE和NO_ZERO_IN_DATE跟插入关系很大它们禁止插入0000-00-00这类零日期值。如果你是从老系统迁移数据可能会遇到原本存在的零日期在插入新库时全部报错的情况这个后面讲时间字段时会再提到。2.2 自增主键与 last_insert_id() 的正确用法自增主键是 InnoDB 表最常见的做法。插入一条数据后要拿到这条数据生成的主键 ID标准做法是调用LAST_INSERT_ID()INSERT INTO 用户表 (姓名) VALUES (张三); SELECT LAST_INSERT_ID();两个容易被忽略的细节第一LAST_INSERT_ID()是会话级别的不是全局的也不是表级别的。A 连接插入后拿到 IDB 连接再调用拿到的是自己会话的值互不影响。如果在一个连接里发起了大量别的 SQL 之后再调用拿到的可能就不是你想的那个 ID 了所以要在插入语句之后立刻调用。第二如果用的是一条多值 INSERT 插入了多行LAST_INSERT_ID()只返回批量插入中产生的第一个 ID不是最后一个。比如一次插入了 5 行自增 ID 从 100 到 104调用返回的是 100。如果你需要拿到一整批的 ID方案有这么几种插入后用业务唯一键回查这批记录。插入前在应用层生成 ID比如雪花算法然后把 ID 显式插入。逐条插入逐条获取但性能较差不推荐大批量场景用。另外注意如果你在 INSERT 时显式指定了自增列的值比如INSERT INTO 表 (id, name) VALUES (200, a)那么LAST_INSERT_ID()返回的仍然是上一条自动生成的值不会变成 200除非你再插入一条真正由 MySQL 自增生成的记录。2.3 约束在插入时怎么发力INSERT 不光是往表里写数据它还要过一遍表上所有约束任何一环过不去整条语句就报错。最常见的几类NOT NULL约束字段没有默认值且没传值报Field xxx doesnt have a default value。DEFAULT约束字段有默认值不传时自动用默认值。UNIQUE唯一约束插入重复值报Duplicate entry xxx for key yyy。CHECK约束MySQL 8.0.16 之后 CHECK 约束才真正生效插入不符合条件的值会报错。FOREIGN KEY外键约束插入子表记录时外键列的值在主表不存在报Cannot add or update a child row: a foreign key constraint fails。理解约束的检查时机很重要。一条 INSERT 语句要插入多行时如果第 3 行违反了唯一约束整条语句会报错而在默认的自动提交模式下已经插入的前两行不会自动回滚表里会留下部分数据。如果你希望“要么全成功要么全失败”必须手动开启事务把多条 INSERT 包在BEGIN和COMMIT之间出错就ROLLBACK。3. 进阶写法怎么选INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO3.1 INSERT IGNORE一次性导入去重很方便但要看清它会吞掉什么语法就是在 INSERT 后面加一个IGNOREINSERT IGNORE INTO 用户表 (id, 邮箱) VALUES (1, testexample.com);IGNORE的本意是插入时遇到错误就跳过这条不报错只给一个 warning。最常见的用途是做数据初始化或外部数据导入时遇到重复唯一键自动跳过保证后面数据继续插入。但这里有个容易被忽略的坑IGNORE吞掉的不只是唯一键冲突还包括NOT NULL约束、Data too long、无效日期等很多错误。比如某个字段是NOT NULL你插入NULL普通 INSERT 会报错INSERT IGNORE会静默跳过然后字段被写成默认值或空字符串。数据缺失了程序也不知道后面查数据时才发现少了一堆记录。我的建议是INSERT IGNORE只用在两种场景一是外部导入时明确知道要按唯一键去重二是初始化字典表这类基础数据。写进业务代码前一定要想清楚如果这条插入因为数据质量被吞掉后续逻辑会不会出问题。3.2 ON DUPLICATE KEY UPDATE存在就更新但别忽视自增消耗这个语法是最常用的“存在即更新不存在即插入”方案俗称 upsertINSERT INTO 计数器表 (id, 计数) VALUES (1, 1) ON DUPLICATE KEY UPDATE 计数 计数 1;在 MySQL 8.0.20 之前的版本里想在更新时引用本次准备插入的值要借助VALUES()函数INSERT INTO 计数器表 (id, 计数) VALUES (1, 1) ON DUPLICATE KEY UPDATE 计数 VALUES(计数);8.0.20 开始官方把VALUES()这种写法标记为废弃推荐使用行别名方式INSERT INTO 计数器表 (id, 计数) VALUES (1, 1) AS new ON DUPLICATE KEY UPDATE 计数 new.计数;用ON DUPLICATE KEY UPDATE时影响行数有三种含义插入成功返回 1更新成功返回 2数据没任何变化返回 0。如果你的应用依赖影响行数来判断是插入还是更新这个细节要特别小心。还有个大坑是自增 ID 的消耗。ON DUPLICATE KEY UPDATE触发更新时MySQL 在尝试插入的那一刻就已经从自增计数器里取走了一个 ID即使最终没有真正插入新记录ID 也会被“浪费”。长期大量执行 upsert 的表自增 ID 会涨得飞快甚至出现跳号几百上千的情况。如果业务对主键连续性有要求这种方案就不太合适了。3.3 REPLACE INTO先删再插代价比你想的大REPLACE INTO的语法和 INSERT 很像REPLACE INTO 用户表 (id, 邮箱) VALUES (1, newexample.com);遇到主键或唯一键冲突时REPLACE不是更新而是先把旧记录 DELETE 掉再插入一条新记录。听上去“无非是更新”实际代价很大删除再插入意味着每次都要分配一个新的自增 ID自增消耗比ON DUPLICATE KEY UPDATE更严重。如果旧行被其他表的外键引用DELETE 可能会触发外键失败或级联删除。只想要更新某一个字段时其他字段也必须全量给出来否则会被重置为默认值。如果表上有触发器REPLACE会触发 DELETE 和 INSERT 两类触发器行为比预期复杂很多。所以我在工作里几乎不用REPLACE INTO只有一种场景会考虑明确要重建整行数据且能接受主键 ID 变化和触发器副作用。绝大多数“存在则更新”的需求ON DUPLICATE KEY UPDATE都更可控。4. 大批量插入时性能为什么差以及怎么改4.1 多行 VALUES 比单条插入快在哪插入性能最直观的优化办法把多条单行 INSERT 合并成一条多行 INSERT。-- 不推荐循环 1000 次 INSERT INTO 表 (a, b) VALUES (1, 2); INSERT INTO 表 (a, b) VALUES (3, 4); -- 推荐一条语句插多行 INSERT INTO 表 (a, b) VALUES (1, 2), (3, 4);快的原因有几点SQL 解析只做一次网络往返从 N 次变成 1 次InnoDB 写 binlog 和 redo log 的 IO 次数也大幅减少。尤其是网络开销以前每次 INSERT 都有一次客户端和 MySQL 服务器之间的往返合并后直接省掉了绝大部分 RTT。所以在代码里写循环插入等于把最容易省掉的成本全扛在自己身上。需要注意单条 INSERT 语句也不是越大越好。MySQL 有max_allowed_packet参数限制单次包大小默认值常见 64MB 或 128MB。如果一次插入的数据量太大会直接报错Packet too large。建议单条多行 INSERT 控制在几百到一两千行之间既能享受合并带来的性能收益又不会让单条语句太大太笨重。4.2 JDBC 批量插入与 rewriteBatchedStatements 参数Java 后端最常见的插入场景是 MyBatis / JDBC 批量写入。很多人写代码时用的是foreach拼接多行 VALUES或者是 JDBC 的addBatch()/executeBatch()。如果你的应用用的是 JDBC 批量接口一定要确认连接串里有没有加上这么个参数jdbc:mysql://host:port/db?rewriteBatchedStatementstrue没有这个参数MySQL 驱动会把executeBatch()里的每一条 SQL 都单独发给服务器执行所谓的“批量插入”实际上还是逐条插入性能提升很有限。加上rewriteBatchedStatementstrue之后驱动会自动把一批 INSERT 重写成一条多行 VALUES 语句再发给服务器这时才是真正的批量插入。我遇到过很多次“一样用 batch为什么性能天差地别”的排查最后 90% 都是这个参数没开。还有一点批量插入最好确保每批次的数据量是可控的不要一次性addBatch几百万条还在继续往内存里塞建议每 1000 到 2000 条就executeBatch一次并清空 batch避免内存撑爆和单条语句过大。4.3 几个影响插入性能的数据库侧参数除了 SQL 写法数据库侧有几个参数会直接影响插入速度排查性能问题时可以按顺序看innodb_flush_log_at_trx_commit默认值 1表示每次事务提交都要把 redo log flush 到磁盘数据安全最好但最慢。如果业务能接受断电或崩溃时丢失最近一小段数据可以临时调到 2会快很多。生产环境调整这个参数必须经过充分评估别把数据安全当儿戏。innodb_autoinc_lock_mode自增锁模式。默认在 MySQL 5.7 里是 1连续模式会导致批量插入时其他插入操作被阻塞改成 2交错模式后并发插入性能更好但要求 binlog 格式必须是ROW否则主从复制数据会不一致。max_allowed_packet前面提过控制单次最大包大小大批量插入时如果报Packet too large就调它。这些参数的名字我建议你记下来因为很多 MySQL 性能排查资料都会提到面试时也经常被问。具体调参之前先看清楚自己用的 MySQL 版本不同版本默认行为和参数取值不完全一样。5. 插入报错的常见场景与排查实录5.1 先把最常见的报错整理成一张表我根据平时排查线上问题的经验把 INSERT 最常见的报错整理成一张速查表按错误码分类遇到问题直接对照着看很方便。错误码常见报错信息含义解决思路1062Duplicate entry xxx for key yyy唯一键或主键冲突检查是否重复提交或改用 upsert 写法1366Incorrect string value: \x...字符集不支持当前字符字符串字符集改utf8mb4检查连接字符集1364Field xxx doesnt have a default value非空字段没有值给字段补值或为字段设置 DEFAULT1406Data too long for column xxx数据超过列长度限制检查字段长度定义或截断超长内容1136Column count doesnt match value count列数量与值数量不一致检查 SQL 中列名和值的数量1054Unknown column xxx in field list指定的列不存在核对表结构确认列名拼写1146Table xxx doesnt exist表不存在检查库名表名是否存在大小写是否有问题1452Cannot add or update a child row外键约束失败检查父表是否有对应记录1205Lock wait timeout exceeded锁等待超时查事务、锁持有情况缩短事务时间1213Deadlock found死锁检查事务加锁顺序必要时增加重试机制这张表只覆盖高频场景实际工作中还会遇到一些低频但更隐蔽的问题下面挑几个详细展开。5.2 字符集引发的插入失败emoji 插不进去这是非常经典的坑。往表里插入一个 emoji 或者生僻字数据库直接报错INSERT INTO 用户表 (昵称) VALUES (张三); -- ERROR 1366 (HY000): Incorrect string value: \xF0\x9F\x98\x80 for column 昵称 at row 1原因在于 MySQL 里的utf8其实最多只能存 3 个字节而 emoji 是 4 字节编码必须用utf8mb4才能存下。很多老项目建表时写的是utf8字符集本身就不支持 emoji。排查步骤一般是先看表的字符集SHOW CREATE TABLE 用户表;如果字段是varchar(20) CHARACTER SET utf8就要修改字段字符集ALTER TABLE 用户表 MODIFY 昵称 VARCHAR(20) CHARACTER SET utf8mb4;还要确保 MySQL 连接的字符集也是utf8mb4否则改了表也白搭。连接后执行SET NAMES utf8mb4;生产环境更彻底的做法建库时就统一用utf8mb4连接串上也加上characterEncodingutf8mb4Java 侧写法是characterEncodingutf8配合服务端utf8mb4也能正常工作具体看驱动版本。如果只在某个表字段上用utf8mb4其他表和索引还是utf8两表关联时会出现字符集不一致导致索引失效的问题这个也要注意。5.3 时间字段的三个隐藏坑时间字段插入报错没有那么频繁但一旦出问题排查起来很绕。第一个坑零日期。0000-00-00这种值在包含NO_ZERO_DATE的严格模式下插入会直接报错。老系统导出数据时经常有这种值导入新库前要么先清洗成合法日期要么临时调整sql_mode。第二个坑TIMESTAMP的范围。TIMESTAMP类型取值范围是 1970 年到 2038 年如果你插入一个2040-01-01会直接报错或变成0000-00-00取决于模式。如果业务需要存更早或更晚的日期用DATETIME它能存从 1000 年到 9999 年。第三个坑时区。TIMESTAMP在存储时会按照当前会话时区转换成 UTC读取时再转回来。如果应用连接串上的时区和数据库服务器的时区不一致插入的时间会比预期差几个小时。排查时先看会话时区SELECT global.time_zone, session.time_zone;遇到时区问题建议应用层统一用同一个时区写入一般是东八区数据库侧也明确设置time_zone避免“明明插入的是一个时间点查出来却是另一个时间”这种诡异现象。5.4 并发插入时的锁等待与死锁并发插入同一张表尤其是同一个唯一键附近很容易触发锁等待超时和死锁。最典型的场景是多个事务同时准备插入同一条唯一键第一个没提交第二个插入相同键时在等待它的锁超出时间就报Lock wait timeout exceeded; try restarting transaction。遇到这种情况先看当前有哪些事务没有提交SELECT * FROM information_schema.innodb_trx\G;重点看trx_started字段找出运行时间特别长的事务。很多锁问题都是因为应用开了事务插了一条数据却迟迟不 COMMIT把锁一直攥着其他线程全部卡死。死锁的解决思路通常是缩短事务范围插入后及时提交。多个事务插入多行时尽量按相同顺序操作减少互相等待。应用层处理死锁的常规做法是捕获 1213 错误后做有限次数的重试。还有一个容易被忽略的并发问题大量并发执行INSERT ... ON DUPLICATE KEY UPDATE会在重复键上产生锁竞争严重时也会引发死锁。如果业务并发量很高建议从架构上考虑比如把计数类的更新独立到消息队列中串行处理而不是所有线程同时怼一张表。6. 写在最后几次实战排查后的个人体会干了这么多年我越来越觉得“插入数据”这件事看着简单真正写稳一点都不容易。每次线上出问题最后定位到的原因往往都是那些平时不太注意的参数和细节。比如有一次排查接口偶发超时查到最后是一条 INSERT 语句在等另一个未提交事务的锁而那个事务是前一个开发同学调试时手动开启忘了提交的这种问题靠排错表都很难一次定位必须熟悉事务和锁的底层机制。我自己现在写插入相关代码时有几个固定习惯写完 SQL 先执行SHOW CREATE TABLE核对字段和字符集批量插入强制分批能用显式列名就绝不用省略写法涉及“存在则更新”时先想清楚自增 ID 的消耗会不会影响业务。这些习惯不能帮你写出更花哨的 SQL但能让你少在半夜被线上告警叫醒。最后再分享一个小技巧如果你发现某条 INSERT 慢得异常别只盯着 SQL 本身先看表的数据量和索引情况。插入操作需要维护所有二级索引索引越多插入越慢。表里有大量在线业务还需要频繁写入时索引设计一定要谨慎这也是从源头减少插入性能问题的最好方式。