尧图网页设计 
简介一份面向SQL初学者及需要夯实数据库基础操作能力的开发者的实用资料内容围绕插入数据的三种常用方法展开第一种是最常见的INSERT INTO ... VALUES语句适合逐条插入记录第二种是INSERT INTO ... SELECT语句支持从已有表或查询结果中选取数据写入目标表并介绍了T-SQL中可自动创建目标表的SELECT INTO变体第三种是省略目标列名的简写写法强调此时SELECT列的顺序必须与目标表定义完全一致同时所有非空列均需提供有效值否则插入会失败或被拒绝。资料还梳理了多项实用小贴士包括提前检查主键与唯一性约束、利用多值列表进行批量插入、将多步写入放进事务以保证一致性、针对重复键与权限问题进行错误处理、以及对于大数据量场景使用存储过程或LOAD DATA INFILE工具提升性能。该资源以单个PDF文档提供压缩后仅34KB内容紧凑、便于快速查阅已有664人学习浏览适合备考数据库基础操作或日常需要确认SQL插入细节的开发者。1. 插入数据这件事很多人在第一步就埋了雷不管是给业务表补数、做报表导入还是写接口入库“sql 插入数据”都是绕不开的一步。很多人把 INSERT 当成最简单的语法背完就扔等到线上数据出问题才发现插入的姿势决定了性能和安全。常见做法其实可以归成三种单条 INSERT INTO ... VALUES、多行 VALUES 批量插入、INSERT INTO ... SELECT 从查询结果直接灌入。三者不是谁替代谁而是按数据量和来源场景分工。这篇内容写给正在写增删改查、做数据迁移和维护旧系统的开发者。先说一个反直觉结论真正拖垮写入的往往不是 SQL 本身而是循环里逐条提交、没包事务、没用批量这三个坑会在这篇里逐个拆开讲。2. 方法一INSERT INTO ... VALUES 单条插入最小可用写法与五个参数细节2.1 单条插入的语法骨架和返回结果单条插入是最基础的写法语法上没有太多可以发挥的空间但字段列表这件事我建议每次都要显式写出来。INSERT INTO user_info (user_name, age, created_at) VALUES (zhangsan, 28, NOW());逻辑很简单INSERT INTO 指定表名括号里列出要写入的字段VALUES 后面跟一个元组元组里的值按字段顺序一一对应。这里最重要的是字段顺序和值顺序必须一致一旦字段列表和 VALUES 的个数对不上数据库会直接报列数不匹配个数恰好一致但类型对不上时会发生隐式类型转换前面能插入成功后面统计和查询就会出幺蛾子。不推荐写成INSERT INTO user_info VALUES (zhangsan, 28, NOW())这种省略字段列表的写法。省略后数据库按整张表的物理字段顺序来匹配表结构一旦加列、调顺序这条 SQL 就静默写错列。这种错在测试环境很难被查出来生产环境上线后才发现数据错位后悔药都没得吃。单条插入的返回结果也值得注意。MySQL 客户端里执行成功会显示Query OK, 1 row affected这个数字就是影响行数JDBC 中则通过executeUpdate()的返回值拿到影响行数。用它判断插入是否生效比直接假设成功要可靠。2.2 自增主键取回三种数据库的不同写法插入之后要拿到刚生成的自增主键这是接口开发里最常见的诉求。三种数据库的写法差异很大新手最容易在这里翻车。-- MySQL取当前会话最后一次自增ID SELECT LAST_INSERT_ID(); -- SQL Server取当前作用域内最后一次插入的标识列 SELECT SCOPE_IDENTITY(); -- PostgreSQL / Oracle 12cRETURNING 子句直接返回 INSERT INTO user_info (user_name, age) VALUES (lisi, 30) RETURNING id;MySQL 的LAST_INSERT_ID()是基于连接会话的不是全局的。意思是连接池里 A 连接插入的数据B 连接去查LAST_INSERT_ID()拿不到 A 的结果即使同一个连接也要在 INSERT 语句执行完立刻查询中间夹了别的写操作就可能被覆盖。SQL Server 里优先用SCOPE_IDENTITY()它只返回当前作用域内的标识列值老的IDENTITY会被表上的触发器干扰触发器往别的表插入数据后IDENTITY可能返回的是那个表的 ID这就是经典的“查到了但不是我要的”。PostgreSQL 的RETURNING最直接一条语句同时完成插入和取 ID少一次往返。JDBC 场景下还可以用getGeneratedKeys()拿自增主键PreparedStatement ps conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.executeUpdate(); ResultSet rs ps.getGeneratedKeys(); if (rs.next()) { long id rs.getLong(1); }参数Statement.RETURN_GENERATED_KEYS是 JDBC 规范里预留的开关告诉驱动执行完后把自增列的值返回来。前提是数据库驱动支持MySQL Connector/J 和 SQL Server JDBC 驱动都支持算是跨数据库最通用的写法。2.3 参数化插入占位符替代字符串拼接插入数据时最危险的不是性能而是 SQL 注入。很多人觉得 SQL 注入只是登录框的事其实插入数据时拼接字符串一样中招。// 危险写法直接拼接用户输入 String sql INSERT INTO user_info (user_name) VALUES ( userName ); stmt.executeUpdate(sql); // 安全写法参数化 String sql INSERT INTO user_info (user_name, age) VALUES (?, ?); PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, userName); ps.setInt(2, age); ps.executeUpdate();拼接写法的核心问题在于用户输入里的单引号会提前闭合 SQL 语句后面的内容变成可执行代码。参数化写法的本质是让数据库把?占位符当成数据值来处理而不是当成 SQL 语法的一部分从机制上封死注入路径。Commons-DBUtils 的QueryRunner、Spring 的JdbcTemplate底层也都是这一套写业务代码时不要自己拼 SQL 再交给框架执行。参数化之外还有一个被忽略的点日期和时间字段不要直接传字符串尽量用setDate、setTimestamp这类类型化方法。字符串传给日期字段时数据库要做一次隐式转换转换规则在不同数据库之间并不完全一致比如 MySQL 对2024-02-30这种不存在的日期可能直接报错也可能按零值写入完全取决于sql_mode的配置属于典型的黑匣子行为。3. 方法二多行 VALUES 批量插入把分钟级写入拉回秒级的差距3.1 一条语句插多行的语法和性能对比批量插入的第一种常用方法是把多条 VALUES 合并成一条语句这是我在数据导入场景里用得最多的写法。INSERT INTO user_info (user_name, age, created_at) VALUES (zhangsan, 28, NOW()), (lisi, 30, NOW()), (wangwu, 25, NOW());和单条插入相比语法上只是 VALUES 后面多了几组括号每组括号之间用逗号分隔整体就是一次数据库请求。性能差距来自三个方面网络往返次数从 N 次变成 1 次数据库解析 SQL 的开销从 N 次变成 1 次日志和索引维护也可以按批处理。我自己在本地 MySQL 上做过粗测10 万行数据用单条循环插入大概要几十秒到几分钟改成 1000 行一组的批量插入耗时能降到原来的十分之一左右具体数字取决于表上的索引数量和磁盘类型但量级差距是稳定的。多行 VALUES 不是所有数据库都支持同一种写法。MySQL 和 PostgreSQL 直接支持SQL Server 2008 之后也支持这种多行 VALUES 语法Oracle 走的是INSERT ALL INTO ... VALUES ... INTO ... VALUES ... SELECT * FROM dual写法完全不同。跨数据库开发时先确认目标库方言再拼批量语句别把 MySQL 的写法原样搬到 Oracle 上。3.2 事务、批量大小与 max_allowed_packet批量怎么调才稳批量插入不是无脑把 10 万行塞进一条语句。数据包过大会触发数据库的报文上限MySQL 会直接报Got a packet bigger than max_allowed_packet bytes这个问题我在导数据的时候踩过不止一次。-- 查看当前上限单位是字节 SHOW VARIABLES LIKE max_allowed_packet; -- 会话内临时调大 SET GLOBAL max_allowed_packet 67108864;max_allowed_packet默认值在不同版本里并不一致常见的默认值在 4MB 到 64MB 之间。批量插入的单条 SQL 总长度一旦超过这个值整个批次都会失败而且报错只在上层应用里显示为通信异常排查时容易误判成网络问题。永久修改要在配置文件[mysqld]段下加max_allowed_packet128M然后重启实例。批量大小我一般按 500 到 1000 行一组来调而不是一味追求越大越好。理由有三点第一单批过大时事务日志和锁持有时间变长主从同步的延迟会明显增高第二一旦中间某行数据触发唯一键冲突或非空约束整批回滚的成本变高第三行数不大时错误定位容易哪一批失败就处理哪一批。数据量是几十万行时用分批插入比一条巨型 SQL 更稳妥。事务也要显式包起来。批量插入默认 autocommit 的环境下每行其实是一次隐式提交意味着每行都要刷一次日志性能折扣非常大。START TRANSACTION; INSERT INTO user_info (user_name, age) VALUES (a, 1), (b, 2), (c, 3); COMMIT;START TRANSACTION开启一个事务中间的 INSERT 语句在同一个事务内执行最后 COMMIT 统一提交。好处不只是快还让“全有或全无”成为可能。注意事务别开太大几万行一个事务已经算大了几十万行一个事务会导致 undo 日志膨胀回滚时可能比插入还慢。3.3 JDBC 的 addBatch 与 rewriteBatchedStatements代码里的批量姿势Java 生态里做批量插入最常见的错误是写一个 for 循环循环里执行executeUpdate。这种方式即使 SQL 写得再漂亮网络往返也救不回来。JDBC 规范里准备了批量 APIString sql INSERT INTO user_info (user_name, age) VALUES (?, ?); PreparedStatement ps conn.prepareStatement(sql); conn.setAutoCommit(false); for (User user : userList) { ps.setString(1, user.getName()); ps.setInt(2, user.getAge()); ps.addBatch(); if (batchCount % 500 0) { ps.executeBatch(); } } ps.executeBatch(); conn.commit();addBatch()先把参数累积到驱动内存里executeBatch()才真正发给数据库执行。按 500 条一批执行既避免内存被参数占满也避免事务跨度太长。真正让 MySQL 批量起飞的是连接串上的一个参数jdbc:mysql://localhost:3306/app_db?rewriteBatchedStatementstruerewriteBatchedStatementstrue的作用是让 MySQL Connector/J 把多条单行 INSERT 重写成一条多行 VALUES 语句再发给服务端这正好对接 3.1 节的多行批量语法。没有这个参数时executeBatch()只是逐条发送数据库端性能收益有限。这个参数是 MySQL 驱动特有的换成 PostgreSQL 或 SQL Server 驱动的连接串参数名不适用但都各自的批量路径。批量处理的代码和 3.2 说的事务要配套使用。setAutoCommit(false)之后所有executeBatch()的结果在commit()之前都不会落盘失败时rollback()可以整体撤销。这里有一个必须忍住的冲动不要在executeBatch()之后立刻commit()把提交留给循环结束后统一做否则批量就白做了。4. 方法三INSERT ... SELECT把查询结果直接灌进目标表的三种场景4.1 INSERT ... SELECT 基本语法与字段映射第三种常用方法是不需要 VALUES 的 INSERT它直接从一张表或一个查询结果里取数据落进另一张表。典型场景是数据仓库分层、报表临时表、把老表数据归档到历史表。INSERT INTO user_info_2023 (user_id, user_name, age) SELECT id, user_name, age FROM user_info WHERE created_at 2023-01-01 AND created_at 2024-01-01;执行逻辑是先跑 SELECT 子句把结果集按列顺序映射到 INSERT 指定的字段上。这里两个硬性要求必须同时满足目标字段个数和 SELECT 返回列数一致每个位置的类型兼容。类型不兼容时数据库不会提前打招呼而是做隐式转换最常见的后果是字符串转数字时被截断、日期字符串被转成零值数据进来之后才发现不对。字段列表仍然建议显式写出来。SELECT 侧的字段名只影响源表不影响目标表匹配如果 INSERT 侧省略字段列表目标表就按物理列顺序接收数据和 2.1 说的风险一样。数据迁移时目标表结构通常和源表有差异显式写出字段列表几乎是必须的。4.2 数据迁移与表结构同步从一张表灌数到另一张表做数据迁移时INSERT ... SELECT 是数据库同步类需求里最基础的一块拼图。表结构相同的两表之间可以直接迁移表结构不同时SELECT 侧做字段转换即可。-- 字段缺失时给默认值 INSERT INTO dwd_user (user_id, user_name, age, source_system) SELECT id, user_name, age, ERP FROM ods_user WHERE status 1;这种写法在 ETL 里很常见源系统的表没有source_system字段插入目标表时需要补一个固定标记值直接在 SELECT 列表里写一个常量字符串就能完成。做这类迁移时我一般会在前面加一行 COUNT 验证SELECT COUNT(*) FROM ods_user WHERE status 1;先估算数据量再决定是一次性 INSERT ... SELECT 还是分段处理。数据量大的时候一次性写入会把源表和目标表都锁住业务查询全部排队处理方式是加 WHERE 条件分段比如按 id 区间或按日期分片每片执行一次 INSERT ... SELECT。这本质上就是在手动实现一个简化版的数据库同步工具比拉起一套同步服务轻量得多。SQL Server 的SELECT INTO容易被误当成 INSERT ... SELECT 的同义词其实不是。SELECT * INTO new_table FROM old_table会新建一张表适合备份和临时表INSERT ... SELECT 要求目标表已经存在。两者的选择标准很简单表已经在正式库里就绪用 INSERT ... SELECT需要快速生成快照用 SELECT INTO。4.3 INSERT ... SELECT 与 ON DUPLICATE KEY UPDATE 的分工边界方法三沿用了 INSERT 的关键字自然也会被拿来处理“有则更新、无则插入”的幂等需求。MySQL 的INSERT ... ON DUPLICATE KEY UPDATE就是为这个场景准备的。INSERT INTO user_info (user_id, user_name, age) SELECT id, user_name, age FROM tmp_user_batch ON DUPLICATE KEY UPDATE user_name VALUES(user_name), age VALUES(age);ON DUPLICATE KEY UPDATE的触发条件是唯一键或主键冲突冲突后执行 UPDATE 子句。注意VALUES(user_name)在新版 MySQL 8.0.20 之后标记为弃用推荐直接用别名写法AS new_user ... SET user_name new_user.user_name但 5.7 及以下还能用。这个写法本质上是一种 upsert和标题里说的三种基础插入方法是“组合关系”而不是“并列关系”先得有 INSERT ... SELECT 的基础才能挂上冲突处理子句。不是所有场景都适合用这条语句。业务上明确“只新增、不允许覆盖”时不要图省事加上 ON DUPLICATE KEY UPDATE因为一旦源数据里混入重复键旧数据会被静默修改数据血缘就说不清了。需要严格保存历史变更时用 INSERT ... SELECT 直接插入即可重复键报错反而是一种保护。这里的原则是upsert 是幂等写入的工具不是默认选择。5. 插入数据避坑五个让我半夜爬起来看日志的经典问题5.1 严格模式把“过长字符串”变成了报错现象导入一批数据在测试环境执行得好好的到生产环境同样的 SQL 报Data too long for column整批回滚。原因测试库和生产库的sql_mode不一致。MySQL 的STRICT_TRANS_TABLES开启后字符串超出字段长度会直接报错关闭时数据库只是截断文本并给一个 warning数据照样写进去。很多老库默认宽松模式应用层又没做长度校验开发环境复现不了生产问题。解决先SHOW VARIABLES LIKE sql_mode确认生产库配置然后在应用层对插入数据做长度校验或者统一把两个环境都设置为严格模式让问题在开发阶段就暴露。写导入脚本时我习惯先插一行最长的样本数据测出字段的真实边界再决定要不要扩容字段。5.2 中文乱码表字符集、连接字符集、文件字符集三方不一致现象INSERT 语句在命令行执行后中文显示正常程序一写入就变成问号或者反过来命令行乱码。原因字符集在三个层面各自独立——MySQL 实例/库表的字符集、客户端连接字符集、SQL 语句文件本身的字符集。三者不一致时即使数据在应用内存里是正常的落库后也会变成乱码。最常见的是表用utf8mb4但连接字符集还停留在latin1。解决建表统一用utf8mb4连接串加characterEncodingutf8MySQL 命令行执行前执行SET NAMES utf8mb4。导入文件本身也要存成 UTF-8 编码Windows 上从 Excel 导出的 CSV 经常是 GBK转码后再入库。排查乱码问题时直接在数据库客户端用SELECT HEX(字段) FROM 表看十六进制能快速判断是源头数据编码问题还是传输过程编码问题。5.3 SQL Server 自增列显式插入值IDENTITY_INSERT 该开就得开现象向 SQL Server 带自增列的表插入一行指定了 ID 的数据报错Cannot insert explicit value for identity column for table when IDENTITY_INSERT is set to OFF。原因SQL Server 默认禁止向自增列显式写值这是防止主键冲突的保护机制。做数据迁移、把 A 库数据搬到 B 库保 ID 不变时这条限制就成了拦路虎。解决在插入语句所在会话里打开开关SET IDENTITY_INSERT user_info ON; INSERT INTO user_info (user_id, user_name, age) VALUES (10001, zhangsan, 28); SET IDENTITY_INSERT user_info OFF;SET IDENTITY_INSERT的作用范围只在当前会话而且同一时间只能对一张表开启。插入完成立刻OFF关闭避免后续正常插入时误带了自增列的值。MySQL 没有这个限制允许向自增列显式插入值但显式插入大值后下一次自增值会跳到该值之上数据结构看起来会“断档”这本身不是错误不要误判。5.4 Oracle 里空字符串就是 NULL非空约束下插入空串报错现象代码里插入空字符串Oracle 报cannot insert NULL into (列名)。原因Oracle 把空字符串视为 NULL这和 MySQL、SQL Server 完全不同。开发人员习惯了 MySQL 语义时会在非空字段上插入空串Oracle 直接按 NULL 拒绝。解决应用层把空串统一转换成明确的值或者在 SQL 里用NULLIF(字段, )做转换。反过来从 Oracle 查询数据时IS NULL条件也可能匹配到曾经存过空串的数据统计口径一致性问题通常在数据同步到 MySQL 时爆出来。做跨数据库数据迁移时这一点必须提前列进转换规则里。5.5 慢插入每条 INSERT 都自动提交才是隐藏杀手现象同样的数据量同事的批量脚本几秒钟跑完我的循环插入跑了十几分钟看起来 SQL 一模一样就是慢。原因连接默认开启 autocommit循环里的每次executeUpdate()都是一次完整的事务提交。数据库需要同步日志、释放锁、更新索引面的开销被循环放大加上客户端和数据库之间的往返延迟总耗时几乎线性增长。解决按第 3 章的方式处理——多行 VALUES 批量插入事务包住整批JDBC 加rewriteBatchedStatementstrue。如果循环结构没法改至少要把setAutoCommit(false)打开每 N 条commit()一次。慢插入问题排查时先看数据库的 general log 或慢查询日志确认是不是每行一条 INSERT 的模式再对症改代码。6. 小贴士插入后验证、回表判断与一个坚持多年的习惯6.1 插入后用一条 SELECT 验证影响行数插入完成后除了程序返回的影响行数我习惯再补一条按条件统计的 SQL两边数字对得上才算完。MySQL 客户端里“Query OK, 1 row affected”只代表语句执行成功不代表数据内容一定符合预期批量导入时更是如此每批结束数一下ROW_COUNT()累加值再执行SELECT COUNT(*) FROM 目标表 WHERE 批次号 ?做交叉校验。SQL Server 里ROWCOUNT能拿到上一条语句影响的行数但要注意客户端如果开启了SET NOCOUNT ON返回的行计数会被吞掉导致程序收不到影响行数。这个坑出现在报表和批量脚本里时特别隐蔽排查时需要先确认连接串和会话默认设置。6.2 考虑主键顺序对写入性能的影响插入性能不只是 SQL 写法的问题还和主键生成方式强相关。InnoDB 表的主键是聚簇索引数据按主键顺序物理排列。自增主键的插入值总体递增新数据直接追加在数据页尾部页分裂少UUID 或业务随机串做主键时每插入一行可能落在两个页面之间触发页分裂插入性能随着数据量增大明显衰减。避免踩坑的方法很朴素优先用自增主键非要用 UUID就把索引设计成非聚簇的唯一索引而不是聚簇主键。这属于设计阶段的小贴士等到慢日志里出现大量random insert再回头改代价就大了。6.3 一个坚持多年的习惯这些年做数据导入我养成了一个固定习惯不管数据量多少先把单批次大小定成 500 行在事务里试跑一批确认没有约束报错、没有字符集问题再放开循环全量执行。试跑时顺便看一次执行计划和数据库侧的错误日志能避免 10 万行的批量任务跑到第 8 万行才突然回滚。这个习惯救过我好几次有一次就是第一批试跑直接暴露了源表数据里混着超长文本当时只花了五秒定位问题。希望你也能在批量插入前多留一步验证希望帮到你。本文还有配套的精品资源点击获取 SQL插入数据实战：单条INSERT、批量插入与INSERT SELECT详解
简介一份面向SQL初学者及需要夯实数据库基础操作能力的开发者的实用资料内容围绕插入数据的三种常用方法展开第一种是最常见的INSERT INTO ... VALUES语句适合逐条插入记录第二种是INSERT INTO ... SELECT语句支持从已有表或查询结果中选取数据写入目标表并介绍了T-SQL中可自动创建目标表的SELECT INTO变体第三种是省略目标列名的简写写法强调此时SELECT列的顺序必须与目标表定义完全一致同时所有非空列均需提供有效值否则插入会失败或被拒绝。资料还梳理了多项实用小贴士包括提前检查主键与唯一性约束、利用多值列表进行批量插入、将多步写入放进事务以保证一致性、针对重复键与权限问题进行错误处理、以及对于大数据量场景使用存储过程或LOAD DATA INFILE工具提升性能。该资源以单个PDF文档提供压缩后仅34KB内容紧凑、便于快速查阅已有664人学习浏览适合备考数据库基础操作或日常需要确认SQL插入细节的开发者。1. 插入数据这件事很多人在第一步就埋了雷不管是给业务表补数、做报表导入还是写接口入库“sql 插入数据”都是绕不开的一步。很多人把 INSERT 当成最简单的语法背完就扔等到线上数据出问题才发现插入的姿势决定了性能和安全。常见做法其实可以归成三种单条 INSERT INTO ... VALUES、多行 VALUES 批量插入、INSERT INTO ... SELECT 从查询结果直接灌入。三者不是谁替代谁而是按数据量和来源场景分工。这篇内容写给正在写增删改查、做数据迁移和维护旧系统的开发者。先说一个反直觉结论真正拖垮写入的往往不是 SQL 本身而是循环里逐条提交、没包事务、没用批量这三个坑会在这篇里逐个拆开讲。2. 方法一INSERT INTO ... VALUES 单条插入最小可用写法与五个参数细节2.1 单条插入的语法骨架和返回结果单条插入是最基础的写法语法上没有太多可以发挥的空间但字段列表这件事我建议每次都要显式写出来。INSERT INTO user_info (user_name, age, created_at) VALUES (zhangsan, 28, NOW());逻辑很简单INSERT INTO 指定表名括号里列出要写入的字段VALUES 后面跟一个元组元组里的值按字段顺序一一对应。这里最重要的是字段顺序和值顺序必须一致一旦字段列表和 VALUES 的个数对不上数据库会直接报列数不匹配个数恰好一致但类型对不上时会发生隐式类型转换前面能插入成功后面统计和查询就会出幺蛾子。不推荐写成INSERT INTO user_info VALUES (zhangsan, 28, NOW())这种省略字段列表的写法。省略后数据库按整张表的物理字段顺序来匹配表结构一旦加列、调顺序这条 SQL 就静默写错列。这种错在测试环境很难被查出来生产环境上线后才发现数据错位后悔药都没得吃。单条插入的返回结果也值得注意。MySQL 客户端里执行成功会显示Query OK, 1 row affected这个数字就是影响行数JDBC 中则通过executeUpdate()的返回值拿到影响行数。用它判断插入是否生效比直接假设成功要可靠。2.2 自增主键取回三种数据库的不同写法插入之后要拿到刚生成的自增主键这是接口开发里最常见的诉求。三种数据库的写法差异很大新手最容易在这里翻车。-- MySQL取当前会话最后一次自增ID SELECT LAST_INSERT_ID(); -- SQL Server取当前作用域内最后一次插入的标识列 SELECT SCOPE_IDENTITY(); -- PostgreSQL / Oracle 12cRETURNING 子句直接返回 INSERT INTO user_info (user_name, age) VALUES (lisi, 30) RETURNING id;MySQL 的LAST_INSERT_ID()是基于连接会话的不是全局的。意思是连接池里 A 连接插入的数据B 连接去查LAST_INSERT_ID()拿不到 A 的结果即使同一个连接也要在 INSERT 语句执行完立刻查询中间夹了别的写操作就可能被覆盖。SQL Server 里优先用SCOPE_IDENTITY()它只返回当前作用域内的标识列值老的IDENTITY会被表上的触发器干扰触发器往别的表插入数据后IDENTITY可能返回的是那个表的 ID这就是经典的“查到了但不是我要的”。PostgreSQL 的RETURNING最直接一条语句同时完成插入和取 ID少一次往返。JDBC 场景下还可以用getGeneratedKeys()拿自增主键PreparedStatement ps conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.executeUpdate(); ResultSet rs ps.getGeneratedKeys(); if (rs.next()) { long id rs.getLong(1); }参数Statement.RETURN_GENERATED_KEYS是 JDBC 规范里预留的开关告诉驱动执行完后把自增列的值返回来。前提是数据库驱动支持MySQL Connector/J 和 SQL Server JDBC 驱动都支持算是跨数据库最通用的写法。2.3 参数化插入占位符替代字符串拼接插入数据时最危险的不是性能而是 SQL 注入。很多人觉得 SQL 注入只是登录框的事其实插入数据时拼接字符串一样中招。// 危险写法直接拼接用户输入 String sql INSERT INTO user_info (user_name) VALUES ( userName ); stmt.executeUpdate(sql); // 安全写法参数化 String sql INSERT INTO user_info (user_name, age) VALUES (?, ?); PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, userName); ps.setInt(2, age); ps.executeUpdate();拼接写法的核心问题在于用户输入里的单引号会提前闭合 SQL 语句后面的内容变成可执行代码。参数化写法的本质是让数据库把?占位符当成数据值来处理而不是当成 SQL 语法的一部分从机制上封死注入路径。Commons-DBUtils 的QueryRunner、Spring 的JdbcTemplate底层也都是这一套写业务代码时不要自己拼 SQL 再交给框架执行。参数化之外还有一个被忽略的点日期和时间字段不要直接传字符串尽量用setDate、setTimestamp这类类型化方法。字符串传给日期字段时数据库要做一次隐式转换转换规则在不同数据库之间并不完全一致比如 MySQL 对2024-02-30这种不存在的日期可能直接报错也可能按零值写入完全取决于sql_mode的配置属于典型的黑匣子行为。3. 方法二多行 VALUES 批量插入把分钟级写入拉回秒级的差距3.1 一条语句插多行的语法和性能对比批量插入的第一种常用方法是把多条 VALUES 合并成一条语句这是我在数据导入场景里用得最多的写法。INSERT INTO user_info (user_name, age, created_at) VALUES (zhangsan, 28, NOW()), (lisi, 30, NOW()), (wangwu, 25, NOW());和单条插入相比语法上只是 VALUES 后面多了几组括号每组括号之间用逗号分隔整体就是一次数据库请求。性能差距来自三个方面网络往返次数从 N 次变成 1 次数据库解析 SQL 的开销从 N 次变成 1 次日志和索引维护也可以按批处理。我自己在本地 MySQL 上做过粗测10 万行数据用单条循环插入大概要几十秒到几分钟改成 1000 行一组的批量插入耗时能降到原来的十分之一左右具体数字取决于表上的索引数量和磁盘类型但量级差距是稳定的。多行 VALUES 不是所有数据库都支持同一种写法。MySQL 和 PostgreSQL 直接支持SQL Server 2008 之后也支持这种多行 VALUES 语法Oracle 走的是INSERT ALL INTO ... VALUES ... INTO ... VALUES ... SELECT * FROM dual写法完全不同。跨数据库开发时先确认目标库方言再拼批量语句别把 MySQL 的写法原样搬到 Oracle 上。3.2 事务、批量大小与 max_allowed_packet批量怎么调才稳批量插入不是无脑把 10 万行塞进一条语句。数据包过大会触发数据库的报文上限MySQL 会直接报Got a packet bigger than max_allowed_packet bytes这个问题我在导数据的时候踩过不止一次。-- 查看当前上限单位是字节 SHOW VARIABLES LIKE max_allowed_packet; -- 会话内临时调大 SET GLOBAL max_allowed_packet 67108864;max_allowed_packet默认值在不同版本里并不一致常见的默认值在 4MB 到 64MB 之间。批量插入的单条 SQL 总长度一旦超过这个值整个批次都会失败而且报错只在上层应用里显示为通信异常排查时容易误判成网络问题。永久修改要在配置文件[mysqld]段下加max_allowed_packet128M然后重启实例。批量大小我一般按 500 到 1000 行一组来调而不是一味追求越大越好。理由有三点第一单批过大时事务日志和锁持有时间变长主从同步的延迟会明显增高第二一旦中间某行数据触发唯一键冲突或非空约束整批回滚的成本变高第三行数不大时错误定位容易哪一批失败就处理哪一批。数据量是几十万行时用分批插入比一条巨型 SQL 更稳妥。事务也要显式包起来。批量插入默认 autocommit 的环境下每行其实是一次隐式提交意味着每行都要刷一次日志性能折扣非常大。START TRANSACTION; INSERT INTO user_info (user_name, age) VALUES (a, 1), (b, 2), (c, 3); COMMIT;START TRANSACTION开启一个事务中间的 INSERT 语句在同一个事务内执行最后 COMMIT 统一提交。好处不只是快还让“全有或全无”成为可能。注意事务别开太大几万行一个事务已经算大了几十万行一个事务会导致 undo 日志膨胀回滚时可能比插入还慢。3.3 JDBC 的 addBatch 与 rewriteBatchedStatements代码里的批量姿势Java 生态里做批量插入最常见的错误是写一个 for 循环循环里执行executeUpdate。这种方式即使 SQL 写得再漂亮网络往返也救不回来。JDBC 规范里准备了批量 APIString sql INSERT INTO user_info (user_name, age) VALUES (?, ?); PreparedStatement ps conn.prepareStatement(sql); conn.setAutoCommit(false); for (User user : userList) { ps.setString(1, user.getName()); ps.setInt(2, user.getAge()); ps.addBatch(); if (batchCount % 500 0) { ps.executeBatch(); } } ps.executeBatch(); conn.commit();addBatch()先把参数累积到驱动内存里executeBatch()才真正发给数据库执行。按 500 条一批执行既避免内存被参数占满也避免事务跨度太长。真正让 MySQL 批量起飞的是连接串上的一个参数jdbc:mysql://localhost:3306/app_db?rewriteBatchedStatementstruerewriteBatchedStatementstrue的作用是让 MySQL Connector/J 把多条单行 INSERT 重写成一条多行 VALUES 语句再发给服务端这正好对接 3.1 节的多行批量语法。没有这个参数时executeBatch()只是逐条发送数据库端性能收益有限。这个参数是 MySQL 驱动特有的换成 PostgreSQL 或 SQL Server 驱动的连接串参数名不适用但都各自的批量路径。批量处理的代码和 3.2 说的事务要配套使用。setAutoCommit(false)之后所有executeBatch()的结果在commit()之前都不会落盘失败时rollback()可以整体撤销。这里有一个必须忍住的冲动不要在executeBatch()之后立刻commit()把提交留给循环结束后统一做否则批量就白做了。4. 方法三INSERT ... SELECT把查询结果直接灌进目标表的三种场景4.1 INSERT ... SELECT 基本语法与字段映射第三种常用方法是不需要 VALUES 的 INSERT它直接从一张表或一个查询结果里取数据落进另一张表。典型场景是数据仓库分层、报表临时表、把老表数据归档到历史表。INSERT INTO user_info_2023 (user_id, user_name, age) SELECT id, user_name, age FROM user_info WHERE created_at 2023-01-01 AND created_at 2024-01-01;执行逻辑是先跑 SELECT 子句把结果集按列顺序映射到 INSERT 指定的字段上。这里两个硬性要求必须同时满足目标字段个数和 SELECT 返回列数一致每个位置的类型兼容。类型不兼容时数据库不会提前打招呼而是做隐式转换最常见的后果是字符串转数字时被截断、日期字符串被转成零值数据进来之后才发现不对。字段列表仍然建议显式写出来。SELECT 侧的字段名只影响源表不影响目标表匹配如果 INSERT 侧省略字段列表目标表就按物理列顺序接收数据和 2.1 说的风险一样。数据迁移时目标表结构通常和源表有差异显式写出字段列表几乎是必须的。4.2 数据迁移与表结构同步从一张表灌数到另一张表做数据迁移时INSERT ... SELECT 是数据库同步类需求里最基础的一块拼图。表结构相同的两表之间可以直接迁移表结构不同时SELECT 侧做字段转换即可。-- 字段缺失时给默认值 INSERT INTO dwd_user (user_id, user_name, age, source_system) SELECT id, user_name, age, ERP FROM ods_user WHERE status 1;这种写法在 ETL 里很常见源系统的表没有source_system字段插入目标表时需要补一个固定标记值直接在 SELECT 列表里写一个常量字符串就能完成。做这类迁移时我一般会在前面加一行 COUNT 验证SELECT COUNT(*) FROM ods_user WHERE status 1;先估算数据量再决定是一次性 INSERT ... SELECT 还是分段处理。数据量大的时候一次性写入会把源表和目标表都锁住业务查询全部排队处理方式是加 WHERE 条件分段比如按 id 区间或按日期分片每片执行一次 INSERT ... SELECT。这本质上就是在手动实现一个简化版的数据库同步工具比拉起一套同步服务轻量得多。SQL Server 的SELECT INTO容易被误当成 INSERT ... SELECT 的同义词其实不是。SELECT * INTO new_table FROM old_table会新建一张表适合备份和临时表INSERT ... SELECT 要求目标表已经存在。两者的选择标准很简单表已经在正式库里就绪用 INSERT ... SELECT需要快速生成快照用 SELECT INTO。4.3 INSERT ... SELECT 与 ON DUPLICATE KEY UPDATE 的分工边界方法三沿用了 INSERT 的关键字自然也会被拿来处理“有则更新、无则插入”的幂等需求。MySQL 的INSERT ... ON DUPLICATE KEY UPDATE就是为这个场景准备的。INSERT INTO user_info (user_id, user_name, age) SELECT id, user_name, age FROM tmp_user_batch ON DUPLICATE KEY UPDATE user_name VALUES(user_name), age VALUES(age);ON DUPLICATE KEY UPDATE的触发条件是唯一键或主键冲突冲突后执行 UPDATE 子句。注意VALUES(user_name)在新版 MySQL 8.0.20 之后标记为弃用推荐直接用别名写法AS new_user ... SET user_name new_user.user_name但 5.7 及以下还能用。这个写法本质上是一种 upsert和标题里说的三种基础插入方法是“组合关系”而不是“并列关系”先得有 INSERT ... SELECT 的基础才能挂上冲突处理子句。不是所有场景都适合用这条语句。业务上明确“只新增、不允许覆盖”时不要图省事加上 ON DUPLICATE KEY UPDATE因为一旦源数据里混入重复键旧数据会被静默修改数据血缘就说不清了。需要严格保存历史变更时用 INSERT ... SELECT 直接插入即可重复键报错反而是一种保护。这里的原则是upsert 是幂等写入的工具不是默认选择。5. 插入数据避坑五个让我半夜爬起来看日志的经典问题5.1 严格模式把“过长字符串”变成了报错现象导入一批数据在测试环境执行得好好的到生产环境同样的 SQL 报Data too long for column整批回滚。原因测试库和生产库的sql_mode不一致。MySQL 的STRICT_TRANS_TABLES开启后字符串超出字段长度会直接报错关闭时数据库只是截断文本并给一个 warning数据照样写进去。很多老库默认宽松模式应用层又没做长度校验开发环境复现不了生产问题。解决先SHOW VARIABLES LIKE sql_mode确认生产库配置然后在应用层对插入数据做长度校验或者统一把两个环境都设置为严格模式让问题在开发阶段就暴露。写导入脚本时我习惯先插一行最长的样本数据测出字段的真实边界再决定要不要扩容字段。5.2 中文乱码表字符集、连接字符集、文件字符集三方不一致现象INSERT 语句在命令行执行后中文显示正常程序一写入就变成问号或者反过来命令行乱码。原因字符集在三个层面各自独立——MySQL 实例/库表的字符集、客户端连接字符集、SQL 语句文件本身的字符集。三者不一致时即使数据在应用内存里是正常的落库后也会变成乱码。最常见的是表用utf8mb4但连接字符集还停留在latin1。解决建表统一用utf8mb4连接串加characterEncodingutf8MySQL 命令行执行前执行SET NAMES utf8mb4。导入文件本身也要存成 UTF-8 编码Windows 上从 Excel 导出的 CSV 经常是 GBK转码后再入库。排查乱码问题时直接在数据库客户端用SELECT HEX(字段) FROM 表看十六进制能快速判断是源头数据编码问题还是传输过程编码问题。5.3 SQL Server 自增列显式插入值IDENTITY_INSERT 该开就得开现象向 SQL Server 带自增列的表插入一行指定了 ID 的数据报错Cannot insert explicit value for identity column for table when IDENTITY_INSERT is set to OFF。原因SQL Server 默认禁止向自增列显式写值这是防止主键冲突的保护机制。做数据迁移、把 A 库数据搬到 B 库保 ID 不变时这条限制就成了拦路虎。解决在插入语句所在会话里打开开关SET IDENTITY_INSERT user_info ON; INSERT INTO user_info (user_id, user_name, age) VALUES (10001, zhangsan, 28); SET IDENTITY_INSERT user_info OFF;SET IDENTITY_INSERT的作用范围只在当前会话而且同一时间只能对一张表开启。插入完成立刻OFF关闭避免后续正常插入时误带了自增列的值。MySQL 没有这个限制允许向自增列显式插入值但显式插入大值后下一次自增值会跳到该值之上数据结构看起来会“断档”这本身不是错误不要误判。5.4 Oracle 里空字符串就是 NULL非空约束下插入空串报错现象代码里插入空字符串Oracle 报cannot insert NULL into (列名)。原因Oracle 把空字符串视为 NULL这和 MySQL、SQL Server 完全不同。开发人员习惯了 MySQL 语义时会在非空字段上插入空串Oracle 直接按 NULL 拒绝。解决应用层把空串统一转换成明确的值或者在 SQL 里用NULLIF(字段, )做转换。反过来从 Oracle 查询数据时IS NULL条件也可能匹配到曾经存过空串的数据统计口径一致性问题通常在数据同步到 MySQL 时爆出来。做跨数据库数据迁移时这一点必须提前列进转换规则里。5.5 慢插入每条 INSERT 都自动提交才是隐藏杀手现象同样的数据量同事的批量脚本几秒钟跑完我的循环插入跑了十几分钟看起来 SQL 一模一样就是慢。原因连接默认开启 autocommit循环里的每次executeUpdate()都是一次完整的事务提交。数据库需要同步日志、释放锁、更新索引面的开销被循环放大加上客户端和数据库之间的往返延迟总耗时几乎线性增长。解决按第 3 章的方式处理——多行 VALUES 批量插入事务包住整批JDBC 加rewriteBatchedStatementstrue。如果循环结构没法改至少要把setAutoCommit(false)打开每 N 条commit()一次。慢插入问题排查时先看数据库的 general log 或慢查询日志确认是不是每行一条 INSERT 的模式再对症改代码。6. 小贴士插入后验证、回表判断与一个坚持多年的习惯6.1 插入后用一条 SELECT 验证影响行数插入完成后除了程序返回的影响行数我习惯再补一条按条件统计的 SQL两边数字对得上才算完。MySQL 客户端里“Query OK, 1 row affected”只代表语句执行成功不代表数据内容一定符合预期批量导入时更是如此每批结束数一下ROW_COUNT()累加值再执行SELECT COUNT(*) FROM 目标表 WHERE 批次号 ?做交叉校验。SQL Server 里ROWCOUNT能拿到上一条语句影响的行数但要注意客户端如果开启了SET NOCOUNT ON返回的行计数会被吞掉导致程序收不到影响行数。这个坑出现在报表和批量脚本里时特别隐蔽排查时需要先确认连接串和会话默认设置。6.2 考虑主键顺序对写入性能的影响插入性能不只是 SQL 写法的问题还和主键生成方式强相关。InnoDB 表的主键是聚簇索引数据按主键顺序物理排列。自增主键的插入值总体递增新数据直接追加在数据页尾部页分裂少UUID 或业务随机串做主键时每插入一行可能落在两个页面之间触发页分裂插入性能随着数据量增大明显衰减。避免踩坑的方法很朴素优先用自增主键非要用 UUID就把索引设计成非聚簇的唯一索引而不是聚簇主键。这属于设计阶段的小贴士等到慢日志里出现大量random insert再回头改代价就大了。6.3 一个坚持多年的习惯这些年做数据导入我养成了一个固定习惯不管数据量多少先把单批次大小定成 500 行在事务里试跑一批确认没有约束报错、没有字符集问题再放开循环全量执行。试跑时顺便看一次执行计划和数据库侧的错误日志能避免 10 万行的批量任务跑到第 8 万行才突然回滚。这个习惯救过我好几次有一次就是第一批试跑直接暴露了源表数据里混着超长文本当时只花了五秒定位问题。希望你也能在批量插入前多留一步验证希望帮到你。本文还有配套的精品资源点击获取