SQL添加数据全解析：从INSERT到批量导入的避坑指南

做开发这些年，我写过最多的SQL不是复杂到绕头的查询，反而是 INSERT。SQL中添加数据这个操作，表面看就是一条 INSERT INTO 语句，实际上一头扎进去，字符集、主键冲突、批量导入、事务边界，哪个都能让你线上躺枪。今天这篇文章我不想只贴语法，而是把添加数据这条链路从头到尾拆一遍：从最基础的 INSERT 写法，到冲突处理、大批量导入，再到我踩过的坑。适合刚接触 SQL 的新手，也适合想补全细节、避免线上事故的开发者。

先说一个结论放在前面：添加数据不是把 SQL 写出来就完事。你得知道语句是怎么执行的、哪些操作会让索引和锁出问题、批量插入到底该不该开事务。下面按照“基本操作、进阶策略、批量导入、问题排查、场景选择”的顺序讲，每一部分都能直接照着用。

1. 添加数据的基本功：INSERT 语句的完整形态

1.1 最基础的 INSERT 语句到底该怎么写

很多刚入行的同学写 INSERT 是这样的：

INSERT INTO user (name, age, created_at) VALUES ('张三', 28, NOW());

这当然没问题，但我想先说说为什么推荐你总是把列名写出来。有些人为了省事，写成INSERT INTO user VALUES (...),这要求 values 里的顺序和表结构完全一致。表结构平时看起来挺稳，可一旦某天 DBA 在中间加了一个字段，这条 SQL 就会直接报“列数量不匹配”。更麻烦的是，如果不写列名，别人看这条 SQL 根本不知道每个值代表什么含义。排查问题的时候还得先去翻一遍表结构，效率很低。

所以我个人的习惯是：所有 INSERT 一律显式列出列名，顺序和业务语义对齐，不跟物理表结构强绑定。这样即使表加了字段，只要你不往这条 SQL 里加值，它依然能稳定运行。列名顺序完全由你控制，阅读代码的人一眼就能看出“这一行是插入了姓名、年龄和创建时间”，不需要猜。

多表插入的场景也值得提一句。比如在前端表单提交里，你要同时写主记录和子记录，正确做法是在同一个事务里先插入主表拿到主键，再插入子表。顺序不能反，否则子表的外键没有来源。有些 ORM 会自动帮你做，但如果你直接写 SQL，就要自己保证事务边界和获取主键的时机，别把两条独立 INSERT 硬拼成一个字符串，那是给自己埋坑。

1.2 多行插入不是让你拼接 SQL 字符串

插入多条数据时，最高效的 SQL 形态是单条语句里跟多组 values：

INSERT INTO order_item (order_id, sku_id, num, price) VALUES (1001, 'SKU001', 2, 19.90), (1001, 'SKU002', 1, 29.90), (1002, 'SKU001', 1, 19.90);

这样一种多行插入，比在应用层循环执行单条 INSERT 少了很多网络往返，性能能提升一个量级。但这里要泼一盆冷水：不是一次塞得越多越好，很多新手喜欢用一个循环拼出几千上万行的 SQL，结果要么超过数据库的包大小限制，要么把主从延迟瞬间拉高。

我实际操作时一般控制单条 SQL 在 100 到 500 行之间。这个数不是拍脑袋定的，主要考虑到几个限制：一是数据库对单条 SQL 的大小有限制，比如常见开源数据库默认包大小是几 MB 到几十 MB，一次塞太多行容易顶到上限；二是单条 SQL 太长，出错的定位成本会变大，日志里随便截断一下都看不出是哪一行的问题；三是事务提交粒度，100 到 500 行一批，内存、锁、undo 日志都相对可控。

再强调一个所有人都该注意的点：不要在应用层通过字符串拼接来构造 SQL，极容易被注入攻击。多条插入你完全可以用参数化查询，把值作为参数传进去，而不是把变量拼到 SQL 文本里。拼 SQL 这件事，在测试环境看着没什么，一旦上了生产就是事故预备役。

1.3 隐式列、DEFAULT 与 NULL 的纠葛

看这条 SQL：

INSERT INTO user (name, age) VALUES ('李四', 30);

你会发现我没写 id，也没写 created_at。这种情况数据库会用列定义里的默认值：id 如果是自增主键就自动生成，created_at 如果有DEFAULT CURRENT_TIMESTAMP就自动填当前时间。很多人以为“不写某一列就等于插入 NULL”，这个认知在严格模式下会翻车。实际上，省略列时数据库优先使用列的默认值；显式指定 NULL 时，才会真的写入 NULL。如果列是NOT NULL又没有默认值，显式 NULL 会直接报错。

这里有个常见的坑来自 ORM 层。比如 Java 里某个对象字段没赋值，底层框架可能把 null 传到 SQL 里，结果插入时命中非空约束，数据库报Column cannot be null。你检查 SQL 时发现“我明明没写这一列”，其实框架帮你写了 NULL。解决方式不是跟框架较劲，而是搞清楚字段该有的默认值，或者在上游数据校验时就把空值转成默认值。

还有个容易被忽略的点是 MySQL 的隐式默认值。在非严格模式下，如果某列没有默认值又不是自增，插入时省略它，数据库会根据类型生成隐式默认值：数值类型是 0，字符串类型是空字符串，时间类型可能是 0000-00-00。这种数据进入表里基本就是脏数据，后面做统计时各种幺蛾子。所以我一直建议大家尽量开启严格模式，让数据库用报错的方式把问题暴露出来，而不是悄悄写一个 0 进去。

2. 插入操作的第二层：从查询结果灌数据与冲突更新

2.1 INSERT INTO ... SELECT 复制数据

添加数据不只是从应用层传值，还有一种非常实用的形态：把一张表里查询出来的结果直接插入到另一张表。最典型的使用场景是订单归档和数据迁移。

INSERT INTO order_archive (id, user_id, amount, created_at) SELECT id, user_id, amount, created_at FROM `order` WHERE created_at < '2023-01-01';

这条语句的目标是把满足条件的订单复制到归档表，然后你就可以在源表里安全地删除或者继续保留热数据。需要注意的是，INSERT 的列顺序必须和 SELECT 的列顺序一一对应，两个地方都很容易被忽略。SELECT 结果的类型最好和目标列兼容，比如源表是 DECIMAL，目标表是 INT，数据库有时候会做隐式转换，有时候直接报错，不要依赖隐式转换来解决问题。

大批量 SELECT 后再 INSERT，要考虑源表的负载。数据库在执行这种语句时，会在源表上扫描数据，期间可能持有行锁或间隙锁。我的经验是：不要一次性把几千万行归档完，最好按主键范围或者时间范围分片，每片用一条 INSERT INTO ... SELECT，配合事务分批提交。比如每次处理id BETWEEN 100000 AND 200000这段，跑完再处理下一段。这样即使中途失败，也能从失败的位置重跑，源表的压力也小很多。

跨库或跨服务器迁移时，这种写法就力不从心了。常规做法是先导出文件，再用导入工具，这部分我会在下面讲到。但单库内部的复制、归档、临时表建设，INSERT INTO ... SELECT 是正确的首选。

2.2 唯一键冲突的三种处理方式

插入数据时最经典的意外，就是主键或唯一键已经存在。比如你导入一批用户数据，里面有一部分是之前已经存在的，直接 INSERT 会报Duplicate entry。处理冲突的方式有好几种，但它们的语义差别很大，不能随手乱用。

第一种是INSERT IGNORE：

INSERT IGNORE INTO user (id, name, age) VALUES (1, '张三', 28);

它的行为是：如果唯一键冲突，就安静地跳过这一行，不报错也不更新。适合你只想把新数据塞进去、旧数据完全不想动的场景。但我要提醒一句，INSERT IGNORE并不只忽略唯一键冲突，它还会忽略其他错误，比如数据格式错误、非空约束错误。这就意味着它可能掩盖真正的脏数据问题，所以线上导入时不要盲目用它当万能盾牌。

第二种是REPLACE INTO：

REPLACE INTO user (id, name, age) VALUES (1, '王五', 25);

这是 MySQL 的全家桶式处理：遇到唯一键冲突时，先把旧行删除，再插入新行。看起来达到了“覆盖更新”的效果，但副作用不少。删除旧行会导致自增主键消耗一个值，比如你原本 id=1，REPLACE 之后新的行可能是 id=2，因为旧行被删了，AUTO_INCREMENT 已经往前走了一步。如果表上有外键，特别是外键带了 ON DELETE CASCADE，REPLACE 可能把关联子表的数据一并删光，这个后果非常严重。所以 REPLACE 只适合你非常确定“这一行被彻底替换也没关系”的场景，一般我都不太推荐。

第三种是INSERT ... ON DUPLICATE KEY UPDATE，这是线上最实用的一种：

INSERT INTO user (id, name, age) VALUES (1, '赵六', 26) ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

冲突的时候只在原行上做更新，不会删除旧行，id 保持稳定。注意 MySQL 8.0.20 之后，推荐写成下面的别名方式，VALUES()在 ON DUPLICATE KEY UPDATE 里已经被标记为废弃用法：

INSERT INTO user (id, name, age) VALUES (1, '赵六', 26) AS new ON DUPLICATE KEY UPDATE name = new.name, age = new.age;

如果你的数据库是 PostgreSQL，对应的标准能力是ON CONFLICT，比如：

INSERT INTO user (id, name, age) VALUES (1, '赵六', 26) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

所以选择哪种方案，先搞清楚你现在用的是哪个数据库，再谈具体语法。业务上“有则更新，无则插入”这种 UPSERT 需求，优先考虑乐观并发控制和唯一键约束，不要在数据库层面做太复杂的循环判断。

2.3 有更新就有风险：UPSERT 的 WHERE 条件不能丢

上面讲到 ODKU 会在冲突时执行 UPDATE 部分，这其实就把“插入”和“更新”绑在一起了。如果你在 UPDATE 部分写了不该写的赋值，或者你的唯一键范围没考虑周全，可能把本不该动的数据改掉。举个例子：

INSERT INTO user (id, name, age) VALUES (1, '赵六', 26) ON DUPLICATE KEY UPDATE age = 26;

如果业务上冲突的并不是 id，而是一个业务唯一键，比如手机号 unique，那么这个 UPDATE 会把所有手机号相同的老用户年龄都改成 26。如果这份数据来源本身不可靠，就会造成大面积的数据被覆盖。

所以在写 UPSERT 之前，一定要先确认哪个字段是业务真正的主键，哪个字段是不可重复的业务键。结合索引设计，把冲突条件和更新范围锁死在最小粒度，别把整行所有字段都无脑更新。很多线上的诡异数据变动，都是因为 UPSERT 的更新范围写得比业务预期宽了一个维度。

3. 大批量添加数据的实操方法：LOAD DATA、分批提交与加速细节

3.1 文件导入：LOAD DATA INFILE 是怎么省时间的

如果你的数据量到了几十万、上百万行，用 INSERT 一条条插已经不现实了。把数据整理成 CSV 或固定分隔符文本，用数据库提供的导入工具，效率能快一个数量级。在常见开源数据库里，MySQL 对应的是LOAD DATA INFILE：

LOAD DATA INFILE '/tmp/user.csv' INTO TABLE user CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (id, name, age, created_at);

这里每个关键字都有实际意义：CHARACTER SET 告诉导入文件是什么字符集，防止中文乱码；FIELDS TERMINATED BY 指定列分隔符；ENCLOSED BY 处理字段值里包含分隔符的情况，比如 CSV 里某个字符串里带逗号，必须用引号包起来；LINES TERMINATED BY 是行结束符，Windows 文件可能是\r\n。

需要注意权限问题：服务器端读取文件需要 FILE 权限，而且文件路径通常是数据库服务器上的路径，别把路径看错成应用服务器的路径。如果文件在客户端机器上，可以使用LOAD DATA LOCAL INFILE，但客户端会先把文件传到服务器，大文件传输也会带来额外时间。PostgreSQL 里对应的命令是COPY ... FROM，SQLite 里则是.import，思路都一样：文件在外，导入在库内，数据不经过应用层逐条转发。

3.2 分批提交：为什么不能一条条插入

有人可能会问，数据库不是自带事务吗？每条 INSERT 不是也有自动提交吗？问题就出在自动提交上。如果每一条 INSERT 都单独提交，数据库就要为每一条做一次事务、写一次日志、刷一次磁盘，主从复制时还要把每条操作都同步过去。数据量一大，延迟和日志量都是灾难。

我自己的做法是显式分批提交。用伪代码说明就是：

batch = [] for row in rows: batch.append(row) if len(batch) >= 500: insert_many(batch) batch.clear()

在批量导入程序中，控制每 500 到 1000 行提交一次。为什么不是越大越好？因为一个事务持有锁的时间太长，如果操作的表还有并发访问，就会造成锁等待。而且单个事务的回滚段会膨胀，万一中途出错要回滚，等待时间会非常长。我有一个很直观的体验：同样的 100 万行数据，逐条提交可能要跑半个多小时，按 500 行一批跑，几分钟就能完成；但如果一次性把所有数据放到一个事务里，最后可能因为内存或日志问题直接 OOM 或把磁盘写满。

所以批量导入的实践原则是：事务粒度要适中，既不能小到一行一事务，也不能大到一把梭。结合总数据量，先估算一批要占多少内存，再决定批次大小。

3.3 索引、约束和数据库参数对写入的影响

数据插入是要维护索引的。表上的二级索引越多，插一行要更新的索引页就越多，写入速度自然越慢。很多同学导入数据时发现“为什么比预期慢这么多”，跑到SHOW INDEX一看，表上有七八个索引。常规优化手段是导入前先禁用或删除非唯一索引，导入完成后再重建索引。唯一索引不要随便关，那是数据正确性的底线。

在 MySQL 里，有几个参数对批量导入影响很大：unique_checks=0会跳过唯一键检查，foreign_key_checks=0会跳过外键检查，innodb_flush_log_at_trx_commit=2可以减少每事务刷盘次数。但要注意，这些是拿数据安全换性能的手段。innodb_flush_log_at_trx_commit=2意味着数据库崩溃时可能丢失最近一秒的已提交事务，绝对不能用在核心在线业务上，只适合做可容忍少量丢失的离线导入。

如果做跨数据库导入，下面是几个常见数据库的对照：

这里提醒一句：不同版本、不同引擎的行为会有差异，导入前先确认版本，再查对应文档，别在测试环境验证完就假设生产环境一模一样。

4. 添加数据时最常见的坑和排查技巧

4.1 字符集乱码：一看见 1366 就头大

我见过最多的插入报错，就是1366 Incorrect string value。出现这个报错，十有八九是中文字符或者 emoji 写不进去。典型原因是列或表的字符集是 utf8，而连接字符集是 latin1，或者数据里带了四字节的 emoji。注意，MySQL 的 utf8 最大只能存三个字节，emoji 需要四个字节，必须用 utf8mb4。

排查方向很固定：先查库、表、列三级的字符集，再查连接字符集。命令行里可以执行SET NAMES utf8mb4来指定本次连接的字符集。如果你的应用是通过驱动连库，检查连接参数里的 characterEncoding，确保为 UTF-8。很多情况下，乱码问题不是 SQL 本身写错，而是连接建立的字符集跟表定义不匹配，数据传到数据库的时候已经被错误转码了。

解决办法是一套组合拳：库表列统一用 utf8mb4，连接字符集统一用 utf8mb4，应用端在写库之前确保字符串本身是 UTF-8 编码。这样基本能把乱码问题消灭在发生之前。如果已经产生了乱码数据，那就要做数据修复，这就比防止问题麻烦得多了。

4.2 自增主键跳号：看起来连续，实际上有很多空洞

有些业务会把自增主键当“单据号”来用，要求数据编号连续。这其实是个误区。数据库的自增主键设计目标是唯一标识一行，不是生成一个连续不中断的业务流水号。事务回滚了，自增值可能已经消耗掉，比如你插入一条数据后回滚，下一条数据的 id 可能就从 2 开始，而不是继续用 1。REPLACE INTO 删除旧行后，也会导致自增值变大。所以，如果业务要求编号完全连续，正确做法是单独设计一个发号器，或者用序列，而不是依赖 AUTO_INCREMENT。

这里还有一个和主从相关的问题：某些数据库在自增步长设计不当时，主库和从库生成的自增值可能不同。比如双主架构中一台库的步长为 2、起始为奇数，另一台库的步长为 2、起始为偶数，这是正常设计，但如果你误以为所有库都是从 1 连续递增，看到从库自增值是奇数就会慌。要理解底层的全局唯一性设计，不要用“连续”来校验。

4.3 NOT NULL、默认值和严格模式的爱恨情仇

插入时最常见的逻辑错误是把空字符串传成了 NULL，或者反过来。如果列定义为NOT NULL DEFAULT 'default_value'，省略列会用默认值，显式传 NULL 就会报错。很多框架在处理前端传来的空字符串时，会把它转成 NULL，导致插入失败。

还有个问题是时间字段。一张表里有created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP，你插入时可以不传，它会自动补当前时间。但如果别的系统导数据时往这个字段写了 ‘0000-00-00 00:00:00’，在严格模式下可能会直接报错，因为日期不合法。数据仓库和业务库之间经常出现这种格式差异，清洗数据时最好先把这类值统一处理掉。

我现在倾向于把数据库的严格模式打开，让错误尽早暴露。虽然有些老系统依赖“插入非严格字段自动填 0”的行为，但那样产生的数据，后续跑统计的时候会给你制造更多的麻烦。宁可在入口报错，也不要在出口算错。

4.4 常见错误速查表

排查这些错误时，我第一件事永远是看完整报错，而不是只看错误码。很多同学一看到 1062 就急着删数据，其实下一步应该是查全表里到底哪一行冲突，搞清楚这条数据是不是可以覆盖或跳过。加锁超时 1205 这类问题，更是要结合当前数据库的活跃事务来判断，盲目调大超时时间只是把问题往后推。

5. 不同场景下我选择哪种添加数据方式

5.1 在线业务写入：短、稳、可重试

所谓在线业务写入，就是用户在前台操作，每一次请求背后对应一次或几次插入。这种场景的核心要求是快、不阻塞其他会话。我习惯把一次请求中的写操作控制在很小的粒度，事务不要跨越多条慢查询，更不要在事务里做远程调用。用户点击注册，回来查重、插用户表、插角色表，整个过程在几十毫秒内完成，才叫合格。

在线写入必须考虑重试。网络抖动、数据库连接断开、主从切换都可能让一次插入失败。但重试不是无脑再发一遍，而要看上一次请求是否真的写成功了。如果应用在发送 INSERT 之前没有拿到事务结果就超时，重试可能造成重复插入。解决办法是业务上引入唯一请求号作为唯一键，或者在应用层做幂等控制。这一点在分布式环境下尤其重要，不然一次“看起来失败但实际成功”的写入会让你对数据对不对心里没底。

尽量不要在在线业务高峰期执行大批量导入。很多公司喜欢把数据订正任务放到白天跑，结果一到业务高峰就把线上库的锁和 IO 打满，用户侧全是超时。离线作业就应该放在低峰期，并且做好限流和分片，让系统能感知到它在跑而不是觉得有人在拖垮数据库。

5.2 离线批量导入：先备份，再分片，最后校验

离线导入的场景通常是：从别的系统拿到一批数据文件，要刷进业务表。我前面讲到的 LOAD DATA、分批事务、关闭检查，主要是这类场景。但还有两个步骤容易被漏掉：导入前的备份和导入后的校验。

备份的重要性不用多说，但很多人做数据导入时觉得“原表数据还在，出问题也能重新导”，就忽略了备份。如果你用的 UPSERT 覆盖了原表，原数据被更新后可就找不回来了。所以我自己的流程是：先对目标表和源文件做行数、唯一键、关键字段的校验，确认两边口径一致；然后对要更新或覆盖的表做备份；接着分片执行导入；最后再跑一遍校验，对比目标行数、关键汇总字段和异常记录数。

分片策略其实很简单，最常见的是按照主键区间分片。每一片一个独立事务，失败后只需要从失败区间开始重跑，不用推倒重来。这样导入脚本就是可重入的，而不是一把梭一次性的。这个习惯让我在多次数据修复中避免了重复劳动。

5.3 数据进数据库之前，先想想要不要进

最后聊点架构层面的。有些数据量非常大、实时性要求又不高的场景，直接 INSERT 到业务库并不是最好的选择。比如埋点日志、用户行为明细，这类数据更适合先落文件或消息队列，再由消费程序批量写入数据仓库，而不是让在线业务库扛住每秒几万条写入。

把数据库当消息队列用，是我最反对的做法之一。业务表的数据模型要服务查询和事务，不适合做流式缓冲。大批量数据进库前先经过数据清洗、去重、格式校验，会节省很多后面查问题的成本。数据格式在源头定死，比如时间统一用 UTC 还是北京时间、缺失值用 NULL 还是空字符串，到了写入阶段就少一半烦恼。

这里可以给一个简单的选型对照：

表格只是个参考，具体情况要结合团队基础设施。比如你已经有了成熟的异步任务平台，那批量导入完全可以拆成多个任务并发跑。如果没有，控制在单机上分批跑，也是稳妥的起步方式。

我个人这些年最大的体会是：SQL 中添加数据这个动作，本质上是跟数据库的一次交易。你给它一份数据，它给你一个承诺，但这个承诺是否可靠，取决于你写语句时有没有把字符集、约束、锁、事务边界都考虑清楚。别小看一条 INSERT，线上事故从来没嫌弃过哪条语句“太简单”。最值得长期坚持的习惯，就是在执行任何插入操作之前，先问自己三个问题：这条数据如果已经存在怎么办？如果格式有问题怎么办？如果数据量很大怎么办？把这三个问题想清楚，再动手敲 SQL，基本上能避开大多数写库时才会暴露的坑。