1. 插入数据为何值得单独写一篇长文先抛一个可能让不少人意外的事实我接手过的绝大多数线上事故根源之一都出在“看起来最没技术含量”的INSERT语句上。要么是开发环境跑得好好的SQL拿到生产库一执行就锁表要么是同步任务半夜失败一看日志是主键冲突要么是某个报表接口突然变慢DBA查了半天发现是有人在循环里逐条INSERT。“SQL插入数据”这件事表面上就一个INSERT INTO语法简单到实习生看五分钟文档就能写。但真正到生产环境你会碰到字符集、隐式转换、约束顺序、事务隔离级别、批量提交策略、主键生成方式、跨库类型映射这一连串问题。每一个坑都能让你的程序“看起来正常”却在某个凌晨突然暴雷。这篇文章我按实际项目里处理插入数据的完整链路来写从单条INSERT的正确姿势到跨数据库迁移时的类型映射再到Java/JDBC场景下的批量插入与安全问题最后聊慢插入的排查思路。内容偏向实战适合正在写业务代码、又经常要和数据库打交道的后端开发也适合刚接触SQL、想绕过那些“没人提醒你但只要踩一次就够疼”的坑的新手。我尽量不写教科书式的话所有内容都是我实际用过、踩过、又爬起来过的经验。部分步骤会标注“按常见实践补充”意思是具体参数和工具版本你按自己的环境来但思路是通用的。2. INSERT的十八般武艺不止是INSERT INTO那么简单2.1 单行插入表设计直接决定你的SQL写法先说最基础的单行插入。很多人以为写INSERT就是把值往表里怼其实你写出来的语句长什么样早在建表那一刻就决定了。INSERT INTO users (user_name, email, age, created_at) VALUES (张三, zhangsanexample.com, 28, NOW());这条语句本身没什么好讲的但有几个细节值得注意第一字段列表和值列表必须一一对应。我见过不少同事图省事省略字段名直接写VALUES这个习惯在大表上特别危险。只要表结构一变加了字段、调了顺序你的INSERT就会悄悄出错而且往往不是在执行时报错而是数据进错了列——比如把手机号写进了邮箱字段。这种错误排查起来极其痛苦。第二有默认值的字段可以不在INSERT里出现但依赖这个特性之前你得先搞清楚默认值是谁在维护。如果是数据库层面的DEFAULT那你省掉字段没问题如果是应用层代码在插入前算好再传那千万别省。另外NULL和默认值是两回事你显式传NULL数据库不会拿默认值给你顶替这会让很多“为什么我插进去是空的”的排查绕一大圈。第三和时间相关的插入要格外小心时区。很多系统部署在云上数据库服务器和业务服务器可能不在一个时区。你在代码里用new Date()拿到的本地时间和数据库的NOW()可能相差好几个小时。我处理过一个数据统计对不上账的案例最后发现就是插入时间普遍比真实时间慢了8小时——因为这个项目里有几处代码用本地时间字符串拼SQL有几处用数据库时间函数两边差了整整一个时区。单行INSERT还有一个容易被忽略的点它可能不是“单行”操作。如果表上有触发器Trigger或者外键约束那么一条INSERT会连带触发一系列操作。在性能测试环境看起来毫秒级的插入到生产环境可能因为外键检查、索引维护、触发器逻辑而变得很慢。后面讲慢插入排查时我会再展开。2.2 多行批量插入一条语句还是循环单插业务里真正更多的场景是一次插入多条数据比如批量导入Excel、批量同步接口数据。这时候有两种写法-- 方式一一条语句多个VALUES INSERT INTO orders (order_no, user_id, amount, status) VALUES (A001, 1001, 99.00, 0), (A002, 1002, 199.00, 0), (A003, 1003, 299.00, 0); -- 方式二循环逐条插入 -- 各种语言的伪代码for each item: INSERT INTO ...绝大多数情况下方式一明显优于方式二。原因有几个减少了客户端和数据库之间的网络往返。逐条插入一万条数据就是一万次网络交互合并成一条只有一次。在InnoDB这类事务型引擎里一条多VALUES的INSERT默认在一个事务内减少了事务提交的fsync次数。代码层面更简洁也更容易统一处理异常。但方式一也不是无脑用。生产环境里我遇到过超大批量插入把事务日志撑爆的情况。比如一次插入50万行一个事务完成日志文件暴涨甚至影响同库其他业务的提交速度。这种场景更适合分片批量插入每次500到1000条提交一次既利用批量优势又控制单事务大小。还有一个和数据库版本相关的点MySQL的max_allowed_packet限制的是单条SQL的总大小。如果你把一万条数据拼成一条SQL很容易超过这个上限。这个参数默认值因版本而异但4MB、16MB、64MB都有可能。真遇到该错误要么调大这个参数要么分片插入。分片是更稳妥的方案——数据库参数不是给你随便动的。2.3 插入时常见的约束冲突处理插入数据最经典的报错就是主键冲突或唯一键冲突。处理方式通常有三种第一种先查再插。插入前先SELECT一次判断是否存在不存在才INSERT。这种方式的缺点是并发下容易出问题两个请求同时查都没查到然后同时插入照样冲突。而且多一次查询就多一倍数据库压力数据量大了之后性能很差。第二种利用数据库的原生语法。不同数据库提供了不同的“冲突时怎么办”的能力MySQLINSERT ... ON DUPLICATE KEY UPDATEPostgreSQLINSERT ... ON CONFLICT (id) DO UPDATE SET ...SQL ServerMERGE或INSERT ... WHERE NOT EXISTSSQLiteINSERT OR REPLACE或INSERT OR IGNORE-- MySQL 示例存在则更新部分字段不存在则插入 INSERT INTO user_points (user_id, points) VALUES (1001, 50) ON DUPLICATE KEY UPDATE points points 50; -- PostgreSQL 示例冲突时什么都不做静默跳过 INSERT INTO user_points (user_id, points) VALUES (1001, 50) ON CONFLICT (user_id) DO NOTHING;这两种写法非常实用尤其是做数据同步、累加积分、记录埋点这类场景。用原生语法的好处是“要么成功要么明确告诉你冲突”不会出现先查再插的并发漏洞。第三种捕获异常程序里处理。捕获唯一键冲突的异常不同语言的数据库驱动会映射成不同异常类型然后走更新逻辑或忽略逻辑。这种方式的缺点是不够优雅异常处理消耗比正常判断高频繁冲突时性能反而更差。提示ON DUPLICATE KEY UPDATE有个坑——它影响的记录数有时候是1有时候是2。MySQL官方文档解释过插入新记录是1发生更新是2如果更新前后值一样可能是0。这个返回值在实际业务里用于判断“是新增还是更新”时一定要先实测你自己的数据库版本别直接拿数字判断。3. 从Oracle往PostgreSQL迁数据那些让你怀疑人生的类型映射热搜词里有“将oracle查询出来的数据插入到pgsql”这个场景我太熟悉了。这几年国产化替代和数据中台建设Oracle迁PG、迁国产库的项目遍地都是而插入数据环节往往是迁移过程中第一个“翻车现场”。3.1 类型映射表先对照清楚再动手Oracle和PostgreSQL虽然都是关系型数据库但数据类型不是一一对应的。我列一份常用的映射对照都是实际项目验证过的Oracle类型PostgreSQL类型注意事项VARCHAR2(n)VARCHAR(n) / TEXTVARCHAR2(4000以下可对应VARCHAR超过4000建议改TEXTNUMBER(p,s)NUMERIC(p,s) / DECIMAL(p,s)不带精度时建议DECIMAL避免整型溢出INTEGERINTEGER / BIGINT看实际取值范围Oracle的INTEGER是NUMBER(38)别名别直接照搬DATETIMESTAMPOracle的DATE带时分秒PG的DATE只到天容易丢精度TIMESTAMPTIMESTAMP / TIMESTAMPTZ有时区需求就选TIMESTAMPTZCLOBTEXT大字段平替BLOBBYTEAOracle的BLOB到PG用BYTEACHAR(n)CHAR(n)注意Oracle的CHAR会自动补空格PG不会比对时容易出问题ROWID无对应概念迁移代码里用到ROWID的地方需要重写逻辑这张表看起来简单真正迁移时踩坑最多的是DATE和NUMBER这两列。3.2 Oracle的DATE为什么迁到PG就变了味在Oracle里DATE类型是包含时分秒的很多老系统的“创建时间”字段都用的DATE。但PostgreSQL里DATE精确到天TIMESTAMP才带时分秒。直接照搬建表语句等你插入数据并查询出来时会发现所有的时间都变成了当天零点——因为PG把你传进来的字符串比如“2024-05-18 14:30:00”按DATE处理时直接截断到了日期。解决办法是建表阶段就注意Oracle源表的DATE字段在PG这边映射成TIMESTAMP。但如果迁移已经做完了表也已经建好了那只能用ALTER TABLE改列类型耗时耗力还可能锁表。插入阶段还有一个隐蔽问题时间字符串的格式差异。从Oracle导出再拼成INSERT语句时可能导出的日期格式是“18-MAY-24”这种Oracle默认格式直接拼进PG的SQL里会报语法错误或者插入错误的值。正确做法是导出时用TO_CHAR统一格式SELECT TO_CHAR(created_date, YYYY-MM-DD HH24:MI:SS) FROM source_table;然后在PG那边用TO_TIMESTAMP(2024-05-18 14:30:00, YYYY-MM-DD HH24:MI:SS)转回去。省掉这个格式化步骤迁移程序八成会在前几百条数据就挂掉。3.3 NUMBER和空字符串两个最容易翻车的细节Oracle的NUMBER是一个“万能数值类型”不带精度时能存非常大的数。PG这边建议映射成NUMERIC或DECIMAL而不是INTEGER/ BIGINT。血的教训我一个同事迁移用户表时把年龄字段映射成了INTEGER结果源数据里有一条年龄为空时程序用NULL插入没问题但某天源系统里出现了一个超出INTEGER范围的数值同步任务瞬间暴毙排查了半小时才定位到是类型溢出。再就是空字符串和NULL的差异。Oracle里和NULL在不少语境下是等价的你用空字符串插入一个VARCHAR2字段查出来是NULL。但PostgreSQL严格区分空字符串就是空字符串NULL就是NULL。这会导致一个现象Oracle迁移过来的数据里所有“应该为空”的字段到了PG里变成了, 或者反过来。业务代码里如果写if (value null)判断结果永远对不上。处理办法是在迁移的SQL里显式转换-- Oracle导出时把空串转成NULL SELECT NULLIF(column_name, ) FROM source_table;或者在PG插入时判断INSERT INTO target_table (name) VALUES (CASE WHEN 某个导出值 THEN NULL ELSE 某个导出值 END);这些听起来繁琐但迁移数据质量的好坏往往就体现在这种细节上。3.4 大批量跨库插入的推荐姿势真正做Oracle到PG的全量迁移时很少有人是一条条INSERT硬插的。常见姿势有这么几种我按推荐程度排序通过ETL工具如Kettle、DataX、Nifi配置好源库和目标库连接字段映射配好工具自动帮你做类型转换。适合一次性全量迁移。Oracle导出为文件PG批量COPY从Oracle导出CSV或定长文本然后在PG里用COPY FROM快速导入。COPY是PG里最快的数据导入方式比逐条INSERT快一个数量级。程序内循环读取分批INSERT适合增量同步或数据做了清洗转换的场景用JDBC批量接口分批提交。我实际做过的项目里全量用DataX增量用程序定时任务两者都跑得挺稳。如果你只是临时导一次数据COPY方案是最省事的# 假设源数据已经导出到 data.csv COPY target_table (col1, col2, col3) FROM /path/to/data.csv WITH (FORMAT csv, DELIMITER ,, NULL NULL);注意COPY命令需要在数据库服务器本地文件系统上有访问权限或者用\copy在psql客户端执行后者走客户端文件路径相对更灵活。如果文件在远程机器上可以先用psql的\copy或者程序方式。4. JDBC插入用户数据从入门到写不出“能用”的代码热搜词里好几条跟JDBC相关“第1关jdbc插入用户数据”“jdbc插入用户数据”。这应该是学校作业或者入门教程里面常见的标题。但我想说的是JDBC插入这件事从“能跑”到“能上生产”中间差着十万八千里。4.1 最基础的PreparedStatement入门教程大概率会教你这样写String sql INSERT INTO users (user_name, email, age) VALUES (?, ?, ?); try (PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, userName); ps.setString(2, email); ps.setInt(3, age); ps.executeUpdate(); }这个写法本身没错PreparedStatement用参数占位符避免了字符串拼接带来的SQL注入风险也算Java里推荐的标准姿势。但很多人第一次接触JDBC时容易犯的错是想当然地以为Statement就够了直接拿字符串拼接SQL// 反面教材千万不要这么写 String sql INSERT INTO users (user_name) VALUES ( userName ); Statement stmt conn.createStatement(); stmt.executeUpdate(sql);这种写法一旦userName里带个单引号SQL语法就错了再进一步如果userName内容被精心构造就是经典的SQL注入。热搜词里“sql注入”“sql注入万能密码绕过”这些相关词的出现不是偶然现实中因为拼接SQL被拖库的案例实在太多。记住一条铁律任何外部输入永远不要用字符串拼接进SQL。哪怕你的项目没有安全团队盯着这个习惯就是保护自己的底线。4.2 连接管理不要每次插入都new连接入门教程里通常会在main方法里写死一个Connection但不告诉你连接是怎么来的。到了真实项目里你不可能每次插入都DriverManager.getConnection一次——那会消耗大量的时间在建立TCP连接、握手认证上数据库也受不了。实际项目中连接怎么管理主流方案是连接池。最常见的两个库是HikariCP和Druid。配置大概长这样# HikariCP 核心配置 jdbcUrljdbc:mysql://localhost:3306/app_db usernameapp_user passwordyour_password maximumPoolSize20 minimumIdle5 connectionTimeout30000 idleTimeout600000连接池的好处不只是减少了连接创建开销更重要的是限制了数据库的最大连接数。没有连接池的代码在高并发下很容易把数据库的连接数打满然后整个系统雪崩——这是我在一个抢购活动项目里亲身体验过的数据库连接池配置是50活动一开抢几十个服务实例一起抢连接数据库直接被拖死。4.3 批量插入addBatch和rewriteBatchedStatementsJDBC批量插入的标准姿势是String sql INSERT INTO orders (order_no, user_id, amount) VALUES (?, ?, ?); try (PreparedStatement ps conn.prepareStatement(sql)) { for (Order order : orderList) { ps.setString(1, order.getOrderNo()); ps.setLong(2, order.getUserId()); ps.setBigDecimal(3, order.getAmount()); ps.addBatch(); // 每500条批量提交一次避免一次攒太多 if (orderList.size() % 500 0) { ps.executeBatch(); } } ps.executeBatch(); // 提交剩余 }这里面有一个MySQL用户特别容易踩的坑MySQL的JDBC驱动默认并没有真正执行多行插入。驱动默认会把你的PreparedStatement逐条发给服务器所谓的batch只是客户端缓冲区。性能提升很有限。要让MySQL的JDBC真正走批量插入需要连接串上追加一个参数jdbc:mysql://localhost:3306/app_db?rewriteBatchedStatementstrue加上这个参数之后MySQL驱动才会把连续的addBatch语句重写为INSERT INTO ... VALUES (...), (...), (...)这种多值语句性能提升可能是数量级的。我第一次从几百条每秒提升到上千甚至上万条每秒就是加了这个参数。PostgreSQL的JDBC驱动默认就会批量发送不需要额外参数。SQL Server的JDBC驱动也可以通过useBulkCopyForBatchInsert等配置实现更高效的批量写入但那是另一个话题。4.4 事务边界批量的另一半灵魂批量插入往往伴随着事务控制两者是硬币的两面。全部成功一起提交任何一条失败全部回滚——这是最理想的语义。但批量插入的时间越长事务持有的锁就越久。在InnoDB里INSERT会加行级锁而长时间未提交的事务还会导致其他事务等待甚至是死锁的温床。所以实际项目中我通常把批量控制在事务里但控制批次大小比如每500条一批每批一个事务。对于“中途失败要不要回滚全部”的问题完全取决于业务。导账单时一条错了全部回滚然后程序终止方便人工修复后重新导但如果是消息消费的场景一条消息处理失败影响的是这条消息对应的数据不应该把所有消息都回滚。这个决策要在设计阶段就想清楚别写到一半再纠结。5. 插入数据与SQL注入你以为你懂了其实并没有5.1 注入的本质是什么SQL注入在新闻里已经见怪不怪了但很多人理解的并不透彻。注入的本质是你的程序把用户输入的数据当成了SQL指令来执行。举个例子-- 假设用户输入的用户名是admin -- SELECT * FROM users WHERE user_name admin -- AND password xxx这里输入的内容里包含SQL语法片段导致原本的查询条件被篡改。如果你拼接的是INSERT语句场景更危险-- 假设某个功能是用户输入备注拼到SQL里 INSERT INTO comments (content) VALUES (用户输入的内容) -- 如果用户输入); DROP TABLE comments; -- -- 最终SQL就会变成 INSERT INTO comments (content) VALUES (); DROP TABLE comments; --)网上流传的“万能密码绕过”也好各种注入payload也好本质都归结为一点你不该让数据变成指令。5.2 参数化为什么能防注入PreparedStatement用?占位符的时候驱动会把你传入的字符串当成“数据”而非“SQL片段”。数据库端处理时参数和SQL文本是分开传递的用户的输入永远不会被解释成SQL语法。所以参数化能够防注入不是靠你对输入做了过滤而是从架构上把数据和指令分离了。理解了这一点你就会明白为什么“写几个关键词替换的过滤函数”并不能真正防注入——比如把单引号替换成两个单引号、把--删掉这种基于黑名单的思路永远会被绕过。黑名单不可能覆盖所有攻击变体而参数化是白名单式的彻底分离。5.3 ORM框架和MyBatis的坑现代开发里手写JDBC已经不多见更多是MyBatis、Hibernate、JPA这类框架。但框架不会自动帮你防注入——你得知道它的规则。MyBatis里#{value}是预编译参数安全${value}是字符串直接拼接注入风险。很多新人以为只要用了MyBatis就安全了其实ORDER BY ${sortColumn}这种动态排序、LIKE %${keyword}%这种模糊查询都是经典注入点。!-- 安全写法 -- select idqueryUsers resultTypeUser SELECT * FROM users WHERE user_name #{name} /select !-- 危险写法排序字段如果来自前端千万不要这样 -- select idqueryUsers resultTypeUser SELECT * FROM users ORDER BY ${sortField} ${sortOrder} /select排序字段这种场景我通常的做法是白名单校验前端传一个字段名代码里映射到固定白名单里的实际列名不进SQL模板。前端传name就对应user_name列前端传createTime就对应created_at列。这样既保留了动态排序的灵活性又不给注入留空间。5.4 插入场景的附加安全面插入数据的SQL注入防范里还有个容易被忽略的角度数据内容本身可能被其他系统消费。你插入了一条带HTML标签或者脚本的内容如果后续有页面直接渲染这些数据就变成了存储型XSS攻击。这就是为什么后端插入数据时往往还要做一些内容校验和清洗而不是原样存库。我处理过一个论坛系统用户发帖内容里嵌了脚本虽然SQL层面是参数化安全的但前端渲染时没做转义导致每个打开帖子的用户都“被”执行了一段脚本一度让整个站点口碑崩掉。从那以后我养成了习惯插入的数据要带着“将来会被谁以什么方式使用”的视角来审查。6. 慢SQL优化插入慢的时候到底在慢什么热搜词里有“慢sql优化”“并行sql优化”而插入场景里的慢SQL往往比查询慢更让人头疼。因为查询慢你还能用EXPLAIN看执行计划插入慢的时候很多人的第一反应是“数据库是不是出问题了”然后就没了方向。6.1 插入慢的排查路径一条INSERT执行很慢可能的原因有很多我建议按下面的顺序排查第一步看执行计划确认是不是索引太多。每条INSERT都要维护表上的所有索引。一张表如果有5个索引每次插入等于写了6份数据。索引不是越多越好这句话在插入频繁的表上尤其适用。索引太多导致插入慢的典型场景是一个日志表上建了十几个索引结果写入QPS上不去一些索引明明是低频查询才用的。第二步看是否有锁等待。用数据库的锁监控工具查一下是不是有别的会话锁了表或行。最常见的场景是夜间批量任务在更新同一张表白天业务的插入在旁边排队等着。这种问题本质上是业务设计冲突不是SQL本身的问题。MySQL里可以用SHOW ENGINE INNODB STATUS看最近死锁和锁等待PG里可以查pg_locks视图。第三步看刷盘相关参数。每秒插入很多条数据但整体吞吐不高通常要检查事务提交的fsync频率。MySQL里innodb_flush_log_at_trx_commit1是最安全的但每次提交都要刷盘SSD上也会明显影响吞吐改成2能提升不少性能但异常断电可能丢最近1秒的数据。这个参数怎么选取决于业务对数据丢失的容忍度金融业务老老实实用1日志类业务可以考虑2。第四步看触发器、外键、级联操作。前面提到过INSERT不一定是单表操作。表上有触发器时每条插入都会在校验阶段执行触发逻辑慢的触发器可以把原本毫秒级的插入拖到秒级。6.2 为什么批量插入后反而更慢一个我反复见过的现象开发同学把逐条INSERT改成批量INSERT之后数据库负载反而飙升了。原因通常是批量插入的重写之后单条SQL包含几千个VALUES执行时需要在服务器端做更多的排序、唯一性检查。尤其当目标表有唯一索引时INSERT本身要检查一大堆值是否冲突。数据量过大时一次事务持有锁的时间太长甚至把其他会话的业务查询拖死。解决方案就是前面说的分片。我比较常用的经验值是MySQL单批500到1000条PG单批1000到2000条。这个数不是玄学是事务日志大小、网络包大小、服务器解析开销之间的平衡点。具体数值建议在自己的环境里压测压测方法很简单分别用100、500、1000、2000、5000做同一份数据的插入测试画个耗时曲线拐点基本就是最佳批次大小。6.3 并行插入适当并发别盲目并发POSTGRES的热搜词里有“并行sql优化”MySQL里对应的就是多线程插入。并行插入确实能提升吞吐但有两个前提数据库硬件资源还有富余。CPU没跑满、磁盘IO没打满的时候并发才有意义。如果数据库已经是瓶颈加再多线程只会让情况更糟。并发度要可控。我建议从4到8个并发线程开始试观察数据库的CPU和IO指标。别一上来就是几十个线程尤其是同一个事务里开并行很多数据库压根不吃这一套。还有一点容易被忽略并发插入相同表时锁冲突可能抵消并行收益。尤其是有自增主键的表并发插入时主键索引的最右端是一个热点线程越多锁竞争越激烈。反而是一两个线程的时候InnoDB可以走AUTO-INC的批量分配优化插入效率更高。这就是为什么有时候并发从4调到8性能没涨还跌了。7. 插入之后的事数据质量与去重策略热搜词里出现了“sql去除空值”“sql语句去重”“清洗---sql语句去重”。这些词和插入数据放一起看其实是同一个故事的下半场数据插进去了但插进去的数据是不是干净、可用的7.1 去除空值和NULL别把脏数据留给下游插入时最常见的脏数据就是空字符串、只有空格的字符串和NULL的混淆。在MySQL里默认不是NULL查询条件WHERE email IS NULL根本查不到email 的记录。很多报表数据“对不上”查到最后都是这种空值混乱问题。插入前的清洗动作我一般放在代码里不放在SQL里。Java代码里如果拿到的是null存库时就存null如果是空字符串要么转null要么干脆不插入这个字段。PostgreSQL的NULLIF也很方便前面提过。关键是一套代码里最好只有一个规则别一会儿存null一会儿存空串。7.2 插入时的去重设计从源头避免重复去重听起来是查询的事情但真正有效的去重是在插入阶段就拦截掉的。你后面写再多的DISTINCT、GROUP BY都只是弥补前面的偷懒。源头去重有几种层次数据库唯一索引最根本的保障。如果业务上用户手机号不应该重复那就建唯一索引。程序写得再烂数据库也会兜底挡住重复。业务前置校验插入前查询一次是否已存在。适合非强一致场景比如防止用户短时间内重复提交表单。幂等设计给数据加一个业务唯一键比如订单号、请求流水号插入时带这个字段配合唯一索引天然幂等。APP点击多次提交、消息队列重复投递都靠这个防重。我见过最典型的案例是支付回调处理第三方支付平台为了确保消息送达会多次回调同一个订单。如果系统里没有幂等设计每次回调都插入一条处理记录账目就乱了。正确做法是订单处理表里加一个callback_id字段建唯一索引回调来了先尝试插入冲突就UPDATE状态这样无论重复回调多少次数据都只有一条。7.3 插入后的一致性验证数据插入完成后尤其是迁移、批量导入这类场景不要直接宣布“完成”要做验证。我通常用这几条SQL组合-- 对比源表和目标表的行数 SELECT COUNT(*) FROM source_table; SELECT COUNT(*) FROM target_table; -- 抽查关键字段不一致的记录 SELECT * FROM ( SELECT id, SUM(CASE WHEN a.name b.name THEN 0 ELSE 1 END) AS diff_count FROM source_export a LEFT JOIN target_table b ON a.id b.id GROUP BY id ) t WHERE diff_count 0;行数一致不代表内容一致还要抽样对比。尤其是跨数据库迁移时DATE截断、空串变NULL、数字精度丢失都有可能在你没注意的地方悄悄发生。8. 从“插入”到“数据生命周期”我的一些体会和扩展思路写到这里想把视角稍微拉高一点。插入数据是整个数据生命周期的最前端但它不是终点。数据插对了、插快了、插安全了后面的查询、分析、清洗、归档才能站得住脚。我个人在实际操作中最大的体会是插入这一环的质量往往取决于你在建表时考虑了多少未来的事。索引建多了插入慢索引建少了查询慢。默认值、字符集、时间时区这些当时花半小时定下来的东西未来能帮你省下好几个通宵。所以每次设计表我习惯问自己几个问题这个表是读多还是写多哪些字段可能有唯一性需求将来会有哪种类型的查询把这些想清楚再动手建表比写一万个聪明的INSERT技巧都重要。最后再分享一个我在多个项目里验证过的小技巧给所有的批量插入任务加上一个“可重跑”的设计。即插入逻辑设计成无论跑几次最终的数据结果都一致。这在实现上通常就是“唯一索引 冲突处理”但它带来的收益非常大——数据同步任务半夜失败了你不用从头排查那些已经插进去的数据直接重新跑一遍就行脏数据被发现了你可以放心地把整张表清掉重导。能做到这一点的系统可以说在数据质量这件事上就赢了一大半。数据无小事哪怕是一条最简单的INSERT背后也藏着事务、并发、安全、一致性这些大命题。希望这篇文章能帮你少踩几个我踩过的坑。 从INSERT到JDBC批量插入：数据库写入避坑与优化全指南
1. 插入数据为何值得单独写一篇长文先抛一个可能让不少人意外的事实我接手过的绝大多数线上事故根源之一都出在“看起来最没技术含量”的INSERT语句上。要么是开发环境跑得好好的SQL拿到生产库一执行就锁表要么是同步任务半夜失败一看日志是主键冲突要么是某个报表接口突然变慢DBA查了半天发现是有人在循环里逐条INSERT。“SQL插入数据”这件事表面上就一个INSERT INTO语法简单到实习生看五分钟文档就能写。但真正到生产环境你会碰到字符集、隐式转换、约束顺序、事务隔离级别、批量提交策略、主键生成方式、跨库类型映射这一连串问题。每一个坑都能让你的程序“看起来正常”却在某个凌晨突然暴雷。这篇文章我按实际项目里处理插入数据的完整链路来写从单条INSERT的正确姿势到跨数据库迁移时的类型映射再到Java/JDBC场景下的批量插入与安全问题最后聊慢插入的排查思路。内容偏向实战适合正在写业务代码、又经常要和数据库打交道的后端开发也适合刚接触SQL、想绕过那些“没人提醒你但只要踩一次就够疼”的坑的新手。我尽量不写教科书式的话所有内容都是我实际用过、踩过、又爬起来过的经验。部分步骤会标注“按常见实践补充”意思是具体参数和工具版本你按自己的环境来但思路是通用的。2. INSERT的十八般武艺不止是INSERT INTO那么简单2.1 单行插入表设计直接决定你的SQL写法先说最基础的单行插入。很多人以为写INSERT就是把值往表里怼其实你写出来的语句长什么样早在建表那一刻就决定了。INSERT INTO users (user_name, email, age, created_at) VALUES (张三, zhangsanexample.com, 28, NOW());这条语句本身没什么好讲的但有几个细节值得注意第一字段列表和值列表必须一一对应。我见过不少同事图省事省略字段名直接写VALUES这个习惯在大表上特别危险。只要表结构一变加了字段、调了顺序你的INSERT就会悄悄出错而且往往不是在执行时报错而是数据进错了列——比如把手机号写进了邮箱字段。这种错误排查起来极其痛苦。第二有默认值的字段可以不在INSERT里出现但依赖这个特性之前你得先搞清楚默认值是谁在维护。如果是数据库层面的DEFAULT那你省掉字段没问题如果是应用层代码在插入前算好再传那千万别省。另外NULL和默认值是两回事你显式传NULL数据库不会拿默认值给你顶替这会让很多“为什么我插进去是空的”的排查绕一大圈。第三和时间相关的插入要格外小心时区。很多系统部署在云上数据库服务器和业务服务器可能不在一个时区。你在代码里用new Date()拿到的本地时间和数据库的NOW()可能相差好几个小时。我处理过一个数据统计对不上账的案例最后发现就是插入时间普遍比真实时间慢了8小时——因为这个项目里有几处代码用本地时间字符串拼SQL有几处用数据库时间函数两边差了整整一个时区。单行INSERT还有一个容易被忽略的点它可能不是“单行”操作。如果表上有触发器Trigger或者外键约束那么一条INSERT会连带触发一系列操作。在性能测试环境看起来毫秒级的插入到生产环境可能因为外键检查、索引维护、触发器逻辑而变得很慢。后面讲慢插入排查时我会再展开。2.2 多行批量插入一条语句还是循环单插业务里真正更多的场景是一次插入多条数据比如批量导入Excel、批量同步接口数据。这时候有两种写法-- 方式一一条语句多个VALUES INSERT INTO orders (order_no, user_id, amount, status) VALUES (A001, 1001, 99.00, 0), (A002, 1002, 199.00, 0), (A003, 1003, 299.00, 0); -- 方式二循环逐条插入 -- 各种语言的伪代码for each item: INSERT INTO ...绝大多数情况下方式一明显优于方式二。原因有几个减少了客户端和数据库之间的网络往返。逐条插入一万条数据就是一万次网络交互合并成一条只有一次。在InnoDB这类事务型引擎里一条多VALUES的INSERT默认在一个事务内减少了事务提交的fsync次数。代码层面更简洁也更容易统一处理异常。但方式一也不是无脑用。生产环境里我遇到过超大批量插入把事务日志撑爆的情况。比如一次插入50万行一个事务完成日志文件暴涨甚至影响同库其他业务的提交速度。这种场景更适合分片批量插入每次500到1000条提交一次既利用批量优势又控制单事务大小。还有一个和数据库版本相关的点MySQL的max_allowed_packet限制的是单条SQL的总大小。如果你把一万条数据拼成一条SQL很容易超过这个上限。这个参数默认值因版本而异但4MB、16MB、64MB都有可能。真遇到该错误要么调大这个参数要么分片插入。分片是更稳妥的方案——数据库参数不是给你随便动的。2.3 插入时常见的约束冲突处理插入数据最经典的报错就是主键冲突或唯一键冲突。处理方式通常有三种第一种先查再插。插入前先SELECT一次判断是否存在不存在才INSERT。这种方式的缺点是并发下容易出问题两个请求同时查都没查到然后同时插入照样冲突。而且多一次查询就多一倍数据库压力数据量大了之后性能很差。第二种利用数据库的原生语法。不同数据库提供了不同的“冲突时怎么办”的能力MySQLINSERT ... ON DUPLICATE KEY UPDATEPostgreSQLINSERT ... ON CONFLICT (id) DO UPDATE SET ...SQL ServerMERGE或INSERT ... WHERE NOT EXISTSSQLiteINSERT OR REPLACE或INSERT OR IGNORE-- MySQL 示例存在则更新部分字段不存在则插入 INSERT INTO user_points (user_id, points) VALUES (1001, 50) ON DUPLICATE KEY UPDATE points points 50; -- PostgreSQL 示例冲突时什么都不做静默跳过 INSERT INTO user_points (user_id, points) VALUES (1001, 50) ON CONFLICT (user_id) DO NOTHING;这两种写法非常实用尤其是做数据同步、累加积分、记录埋点这类场景。用原生语法的好处是“要么成功要么明确告诉你冲突”不会出现先查再插的并发漏洞。第三种捕获异常程序里处理。捕获唯一键冲突的异常不同语言的数据库驱动会映射成不同异常类型然后走更新逻辑或忽略逻辑。这种方式的缺点是不够优雅异常处理消耗比正常判断高频繁冲突时性能反而更差。提示ON DUPLICATE KEY UPDATE有个坑——它影响的记录数有时候是1有时候是2。MySQL官方文档解释过插入新记录是1发生更新是2如果更新前后值一样可能是0。这个返回值在实际业务里用于判断“是新增还是更新”时一定要先实测你自己的数据库版本别直接拿数字判断。3. 从Oracle往PostgreSQL迁数据那些让你怀疑人生的类型映射热搜词里有“将oracle查询出来的数据插入到pgsql”这个场景我太熟悉了。这几年国产化替代和数据中台建设Oracle迁PG、迁国产库的项目遍地都是而插入数据环节往往是迁移过程中第一个“翻车现场”。3.1 类型映射表先对照清楚再动手Oracle和PostgreSQL虽然都是关系型数据库但数据类型不是一一对应的。我列一份常用的映射对照都是实际项目验证过的Oracle类型PostgreSQL类型注意事项VARCHAR2(n)VARCHAR(n) / TEXTVARCHAR2(4000以下可对应VARCHAR超过4000建议改TEXTNUMBER(p,s)NUMERIC(p,s) / DECIMAL(p,s)不带精度时建议DECIMAL避免整型溢出INTEGERINTEGER / BIGINT看实际取值范围Oracle的INTEGER是NUMBER(38)别名别直接照搬DATETIMESTAMPOracle的DATE带时分秒PG的DATE只到天容易丢精度TIMESTAMPTIMESTAMP / TIMESTAMPTZ有时区需求就选TIMESTAMPTZCLOBTEXT大字段平替BLOBBYTEAOracle的BLOB到PG用BYTEACHAR(n)CHAR(n)注意Oracle的CHAR会自动补空格PG不会比对时容易出问题ROWID无对应概念迁移代码里用到ROWID的地方需要重写逻辑这张表看起来简单真正迁移时踩坑最多的是DATE和NUMBER这两列。3.2 Oracle的DATE为什么迁到PG就变了味在Oracle里DATE类型是包含时分秒的很多老系统的“创建时间”字段都用的DATE。但PostgreSQL里DATE精确到天TIMESTAMP才带时分秒。直接照搬建表语句等你插入数据并查询出来时会发现所有的时间都变成了当天零点——因为PG把你传进来的字符串比如“2024-05-18 14:30:00”按DATE处理时直接截断到了日期。解决办法是建表阶段就注意Oracle源表的DATE字段在PG这边映射成TIMESTAMP。但如果迁移已经做完了表也已经建好了那只能用ALTER TABLE改列类型耗时耗力还可能锁表。插入阶段还有一个隐蔽问题时间字符串的格式差异。从Oracle导出再拼成INSERT语句时可能导出的日期格式是“18-MAY-24”这种Oracle默认格式直接拼进PG的SQL里会报语法错误或者插入错误的值。正确做法是导出时用TO_CHAR统一格式SELECT TO_CHAR(created_date, YYYY-MM-DD HH24:MI:SS) FROM source_table;然后在PG那边用TO_TIMESTAMP(2024-05-18 14:30:00, YYYY-MM-DD HH24:MI:SS)转回去。省掉这个格式化步骤迁移程序八成会在前几百条数据就挂掉。3.3 NUMBER和空字符串两个最容易翻车的细节Oracle的NUMBER是一个“万能数值类型”不带精度时能存非常大的数。PG这边建议映射成NUMERIC或DECIMAL而不是INTEGER/ BIGINT。血的教训我一个同事迁移用户表时把年龄字段映射成了INTEGER结果源数据里有一条年龄为空时程序用NULL插入没问题但某天源系统里出现了一个超出INTEGER范围的数值同步任务瞬间暴毙排查了半小时才定位到是类型溢出。再就是空字符串和NULL的差异。Oracle里和NULL在不少语境下是等价的你用空字符串插入一个VARCHAR2字段查出来是NULL。但PostgreSQL严格区分空字符串就是空字符串NULL就是NULL。这会导致一个现象Oracle迁移过来的数据里所有“应该为空”的字段到了PG里变成了, 或者反过来。业务代码里如果写if (value null)判断结果永远对不上。处理办法是在迁移的SQL里显式转换-- Oracle导出时把空串转成NULL SELECT NULLIF(column_name, ) FROM source_table;或者在PG插入时判断INSERT INTO target_table (name) VALUES (CASE WHEN 某个导出值 THEN NULL ELSE 某个导出值 END);这些听起来繁琐但迁移数据质量的好坏往往就体现在这种细节上。3.4 大批量跨库插入的推荐姿势真正做Oracle到PG的全量迁移时很少有人是一条条INSERT硬插的。常见姿势有这么几种我按推荐程度排序通过ETL工具如Kettle、DataX、Nifi配置好源库和目标库连接字段映射配好工具自动帮你做类型转换。适合一次性全量迁移。Oracle导出为文件PG批量COPY从Oracle导出CSV或定长文本然后在PG里用COPY FROM快速导入。COPY是PG里最快的数据导入方式比逐条INSERT快一个数量级。程序内循环读取分批INSERT适合增量同步或数据做了清洗转换的场景用JDBC批量接口分批提交。我实际做过的项目里全量用DataX增量用程序定时任务两者都跑得挺稳。如果你只是临时导一次数据COPY方案是最省事的# 假设源数据已经导出到 data.csv COPY target_table (col1, col2, col3) FROM /path/to/data.csv WITH (FORMAT csv, DELIMITER ,, NULL NULL);注意COPY命令需要在数据库服务器本地文件系统上有访问权限或者用\copy在psql客户端执行后者走客户端文件路径相对更灵活。如果文件在远程机器上可以先用psql的\copy或者程序方式。4. JDBC插入用户数据从入门到写不出“能用”的代码热搜词里好几条跟JDBC相关“第1关jdbc插入用户数据”“jdbc插入用户数据”。这应该是学校作业或者入门教程里面常见的标题。但我想说的是JDBC插入这件事从“能跑”到“能上生产”中间差着十万八千里。4.1 最基础的PreparedStatement入门教程大概率会教你这样写String sql INSERT INTO users (user_name, email, age) VALUES (?, ?, ?); try (PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, userName); ps.setString(2, email); ps.setInt(3, age); ps.executeUpdate(); }这个写法本身没错PreparedStatement用参数占位符避免了字符串拼接带来的SQL注入风险也算Java里推荐的标准姿势。但很多人第一次接触JDBC时容易犯的错是想当然地以为Statement就够了直接拿字符串拼接SQL// 反面教材千万不要这么写 String sql INSERT INTO users (user_name) VALUES ( userName ); Statement stmt conn.createStatement(); stmt.executeUpdate(sql);这种写法一旦userName里带个单引号SQL语法就错了再进一步如果userName内容被精心构造就是经典的SQL注入。热搜词里“sql注入”“sql注入万能密码绕过”这些相关词的出现不是偶然现实中因为拼接SQL被拖库的案例实在太多。记住一条铁律任何外部输入永远不要用字符串拼接进SQL。哪怕你的项目没有安全团队盯着这个习惯就是保护自己的底线。4.2 连接管理不要每次插入都new连接入门教程里通常会在main方法里写死一个Connection但不告诉你连接是怎么来的。到了真实项目里你不可能每次插入都DriverManager.getConnection一次——那会消耗大量的时间在建立TCP连接、握手认证上数据库也受不了。实际项目中连接怎么管理主流方案是连接池。最常见的两个库是HikariCP和Druid。配置大概长这样# HikariCP 核心配置 jdbcUrljdbc:mysql://localhost:3306/app_db usernameapp_user passwordyour_password maximumPoolSize20 minimumIdle5 connectionTimeout30000 idleTimeout600000连接池的好处不只是减少了连接创建开销更重要的是限制了数据库的最大连接数。没有连接池的代码在高并发下很容易把数据库的连接数打满然后整个系统雪崩——这是我在一个抢购活动项目里亲身体验过的数据库连接池配置是50活动一开抢几十个服务实例一起抢连接数据库直接被拖死。4.3 批量插入addBatch和rewriteBatchedStatementsJDBC批量插入的标准姿势是String sql INSERT INTO orders (order_no, user_id, amount) VALUES (?, ?, ?); try (PreparedStatement ps conn.prepareStatement(sql)) { for (Order order : orderList) { ps.setString(1, order.getOrderNo()); ps.setLong(2, order.getUserId()); ps.setBigDecimal(3, order.getAmount()); ps.addBatch(); // 每500条批量提交一次避免一次攒太多 if (orderList.size() % 500 0) { ps.executeBatch(); } } ps.executeBatch(); // 提交剩余 }这里面有一个MySQL用户特别容易踩的坑MySQL的JDBC驱动默认并没有真正执行多行插入。驱动默认会把你的PreparedStatement逐条发给服务器所谓的batch只是客户端缓冲区。性能提升很有限。要让MySQL的JDBC真正走批量插入需要连接串上追加一个参数jdbc:mysql://localhost:3306/app_db?rewriteBatchedStatementstrue加上这个参数之后MySQL驱动才会把连续的addBatch语句重写为INSERT INTO ... VALUES (...), (...), (...)这种多值语句性能提升可能是数量级的。我第一次从几百条每秒提升到上千甚至上万条每秒就是加了这个参数。PostgreSQL的JDBC驱动默认就会批量发送不需要额外参数。SQL Server的JDBC驱动也可以通过useBulkCopyForBatchInsert等配置实现更高效的批量写入但那是另一个话题。4.4 事务边界批量的另一半灵魂批量插入往往伴随着事务控制两者是硬币的两面。全部成功一起提交任何一条失败全部回滚——这是最理想的语义。但批量插入的时间越长事务持有的锁就越久。在InnoDB里INSERT会加行级锁而长时间未提交的事务还会导致其他事务等待甚至是死锁的温床。所以实际项目中我通常把批量控制在事务里但控制批次大小比如每500条一批每批一个事务。对于“中途失败要不要回滚全部”的问题完全取决于业务。导账单时一条错了全部回滚然后程序终止方便人工修复后重新导但如果是消息消费的场景一条消息处理失败影响的是这条消息对应的数据不应该把所有消息都回滚。这个决策要在设计阶段就想清楚别写到一半再纠结。5. 插入数据与SQL注入你以为你懂了其实并没有5.1 注入的本质是什么SQL注入在新闻里已经见怪不怪了但很多人理解的并不透彻。注入的本质是你的程序把用户输入的数据当成了SQL指令来执行。举个例子-- 假设用户输入的用户名是admin -- SELECT * FROM users WHERE user_name admin -- AND password xxx这里输入的内容里包含SQL语法片段导致原本的查询条件被篡改。如果你拼接的是INSERT语句场景更危险-- 假设某个功能是用户输入备注拼到SQL里 INSERT INTO comments (content) VALUES (用户输入的内容) -- 如果用户输入); DROP TABLE comments; -- -- 最终SQL就会变成 INSERT INTO comments (content) VALUES (); DROP TABLE comments; --)网上流传的“万能密码绕过”也好各种注入payload也好本质都归结为一点你不该让数据变成指令。5.2 参数化为什么能防注入PreparedStatement用?占位符的时候驱动会把你传入的字符串当成“数据”而非“SQL片段”。数据库端处理时参数和SQL文本是分开传递的用户的输入永远不会被解释成SQL语法。所以参数化能够防注入不是靠你对输入做了过滤而是从架构上把数据和指令分离了。理解了这一点你就会明白为什么“写几个关键词替换的过滤函数”并不能真正防注入——比如把单引号替换成两个单引号、把--删掉这种基于黑名单的思路永远会被绕过。黑名单不可能覆盖所有攻击变体而参数化是白名单式的彻底分离。5.3 ORM框架和MyBatis的坑现代开发里手写JDBC已经不多见更多是MyBatis、Hibernate、JPA这类框架。但框架不会自动帮你防注入——你得知道它的规则。MyBatis里#{value}是预编译参数安全${value}是字符串直接拼接注入风险。很多新人以为只要用了MyBatis就安全了其实ORDER BY ${sortColumn}这种动态排序、LIKE %${keyword}%这种模糊查询都是经典注入点。!-- 安全写法 -- select idqueryUsers resultTypeUser SELECT * FROM users WHERE user_name #{name} /select !-- 危险写法排序字段如果来自前端千万不要这样 -- select idqueryUsers resultTypeUser SELECT * FROM users ORDER BY ${sortField} ${sortOrder} /select排序字段这种场景我通常的做法是白名单校验前端传一个字段名代码里映射到固定白名单里的实际列名不进SQL模板。前端传name就对应user_name列前端传createTime就对应created_at列。这样既保留了动态排序的灵活性又不给注入留空间。5.4 插入场景的附加安全面插入数据的SQL注入防范里还有个容易被忽略的角度数据内容本身可能被其他系统消费。你插入了一条带HTML标签或者脚本的内容如果后续有页面直接渲染这些数据就变成了存储型XSS攻击。这就是为什么后端插入数据时往往还要做一些内容校验和清洗而不是原样存库。我处理过一个论坛系统用户发帖内容里嵌了脚本虽然SQL层面是参数化安全的但前端渲染时没做转义导致每个打开帖子的用户都“被”执行了一段脚本一度让整个站点口碑崩掉。从那以后我养成了习惯插入的数据要带着“将来会被谁以什么方式使用”的视角来审查。6. 慢SQL优化插入慢的时候到底在慢什么热搜词里有“慢sql优化”“并行sql优化”而插入场景里的慢SQL往往比查询慢更让人头疼。因为查询慢你还能用EXPLAIN看执行计划插入慢的时候很多人的第一反应是“数据库是不是出问题了”然后就没了方向。6.1 插入慢的排查路径一条INSERT执行很慢可能的原因有很多我建议按下面的顺序排查第一步看执行计划确认是不是索引太多。每条INSERT都要维护表上的所有索引。一张表如果有5个索引每次插入等于写了6份数据。索引不是越多越好这句话在插入频繁的表上尤其适用。索引太多导致插入慢的典型场景是一个日志表上建了十几个索引结果写入QPS上不去一些索引明明是低频查询才用的。第二步看是否有锁等待。用数据库的锁监控工具查一下是不是有别的会话锁了表或行。最常见的场景是夜间批量任务在更新同一张表白天业务的插入在旁边排队等着。这种问题本质上是业务设计冲突不是SQL本身的问题。MySQL里可以用SHOW ENGINE INNODB STATUS看最近死锁和锁等待PG里可以查pg_locks视图。第三步看刷盘相关参数。每秒插入很多条数据但整体吞吐不高通常要检查事务提交的fsync频率。MySQL里innodb_flush_log_at_trx_commit1是最安全的但每次提交都要刷盘SSD上也会明显影响吞吐改成2能提升不少性能但异常断电可能丢最近1秒的数据。这个参数怎么选取决于业务对数据丢失的容忍度金融业务老老实实用1日志类业务可以考虑2。第四步看触发器、外键、级联操作。前面提到过INSERT不一定是单表操作。表上有触发器时每条插入都会在校验阶段执行触发逻辑慢的触发器可以把原本毫秒级的插入拖到秒级。6.2 为什么批量插入后反而更慢一个我反复见过的现象开发同学把逐条INSERT改成批量INSERT之后数据库负载反而飙升了。原因通常是批量插入的重写之后单条SQL包含几千个VALUES执行时需要在服务器端做更多的排序、唯一性检查。尤其当目标表有唯一索引时INSERT本身要检查一大堆值是否冲突。数据量过大时一次事务持有锁的时间太长甚至把其他会话的业务查询拖死。解决方案就是前面说的分片。我比较常用的经验值是MySQL单批500到1000条PG单批1000到2000条。这个数不是玄学是事务日志大小、网络包大小、服务器解析开销之间的平衡点。具体数值建议在自己的环境里压测压测方法很简单分别用100、500、1000、2000、5000做同一份数据的插入测试画个耗时曲线拐点基本就是最佳批次大小。6.3 并行插入适当并发别盲目并发POSTGRES的热搜词里有“并行sql优化”MySQL里对应的就是多线程插入。并行插入确实能提升吞吐但有两个前提数据库硬件资源还有富余。CPU没跑满、磁盘IO没打满的时候并发才有意义。如果数据库已经是瓶颈加再多线程只会让情况更糟。并发度要可控。我建议从4到8个并发线程开始试观察数据库的CPU和IO指标。别一上来就是几十个线程尤其是同一个事务里开并行很多数据库压根不吃这一套。还有一点容易被忽略并发插入相同表时锁冲突可能抵消并行收益。尤其是有自增主键的表并发插入时主键索引的最右端是一个热点线程越多锁竞争越激烈。反而是一两个线程的时候InnoDB可以走AUTO-INC的批量分配优化插入效率更高。这就是为什么有时候并发从4调到8性能没涨还跌了。7. 插入之后的事数据质量与去重策略热搜词里出现了“sql去除空值”“sql语句去重”“清洗---sql语句去重”。这些词和插入数据放一起看其实是同一个故事的下半场数据插进去了但插进去的数据是不是干净、可用的7.1 去除空值和NULL别把脏数据留给下游插入时最常见的脏数据就是空字符串、只有空格的字符串和NULL的混淆。在MySQL里默认不是NULL查询条件WHERE email IS NULL根本查不到email 的记录。很多报表数据“对不上”查到最后都是这种空值混乱问题。插入前的清洗动作我一般放在代码里不放在SQL里。Java代码里如果拿到的是null存库时就存null如果是空字符串要么转null要么干脆不插入这个字段。PostgreSQL的NULLIF也很方便前面提过。关键是一套代码里最好只有一个规则别一会儿存null一会儿存空串。7.2 插入时的去重设计从源头避免重复去重听起来是查询的事情但真正有效的去重是在插入阶段就拦截掉的。你后面写再多的DISTINCT、GROUP BY都只是弥补前面的偷懒。源头去重有几种层次数据库唯一索引最根本的保障。如果业务上用户手机号不应该重复那就建唯一索引。程序写得再烂数据库也会兜底挡住重复。业务前置校验插入前查询一次是否已存在。适合非强一致场景比如防止用户短时间内重复提交表单。幂等设计给数据加一个业务唯一键比如订单号、请求流水号插入时带这个字段配合唯一索引天然幂等。APP点击多次提交、消息队列重复投递都靠这个防重。我见过最典型的案例是支付回调处理第三方支付平台为了确保消息送达会多次回调同一个订单。如果系统里没有幂等设计每次回调都插入一条处理记录账目就乱了。正确做法是订单处理表里加一个callback_id字段建唯一索引回调来了先尝试插入冲突就UPDATE状态这样无论重复回调多少次数据都只有一条。7.3 插入后的一致性验证数据插入完成后尤其是迁移、批量导入这类场景不要直接宣布“完成”要做验证。我通常用这几条SQL组合-- 对比源表和目标表的行数 SELECT COUNT(*) FROM source_table; SELECT COUNT(*) FROM target_table; -- 抽查关键字段不一致的记录 SELECT * FROM ( SELECT id, SUM(CASE WHEN a.name b.name THEN 0 ELSE 1 END) AS diff_count FROM source_export a LEFT JOIN target_table b ON a.id b.id GROUP BY id ) t WHERE diff_count 0;行数一致不代表内容一致还要抽样对比。尤其是跨数据库迁移时DATE截断、空串变NULL、数字精度丢失都有可能在你没注意的地方悄悄发生。8. 从“插入”到“数据生命周期”我的一些体会和扩展思路写到这里想把视角稍微拉高一点。插入数据是整个数据生命周期的最前端但它不是终点。数据插对了、插快了、插安全了后面的查询、分析、清洗、归档才能站得住脚。我个人在实际操作中最大的体会是插入这一环的质量往往取决于你在建表时考虑了多少未来的事。索引建多了插入慢索引建少了查询慢。默认值、字符集、时间时区这些当时花半小时定下来的东西未来能帮你省下好几个通宵。所以每次设计表我习惯问自己几个问题这个表是读多还是写多哪些字段可能有唯一性需求将来会有哪种类型的查询把这些想清楚再动手建表比写一万个聪明的INSERT技巧都重要。最后再分享一个我在多个项目里验证过的小技巧给所有的批量插入任务加上一个“可重跑”的设计。即插入逻辑设计成无论跑几次最终的数据结果都一致。这在实现上通常就是“唯一索引 冲突处理”但它带来的收益非常大——数据同步任务半夜失败了你不用从头排查那些已经插进去的数据直接重新跑一遍就行脏数据被发现了你可以放心地把整张表清掉重导。能做到这一点的系统可以说在数据质量这件事上就赢了一大半。数据无小事哪怕是一条最简单的INSERT背后也藏着事务、并发、安全、一致性这些大命题。希望这篇文章能帮你少踩几个我踩过的坑。