拓冰网站定制 
1. 先把“加数据”这件事拆清楚INSERT不提列名埋雷率90%1.1 最基础的一条语句问题往往不在语句本身SQL里“添加数据”的默认答案就是INSERT标准句式长这样INSERT INTO users (name, email, age) VALUES (张三, zhangsanexample.com, 25);看起来人畜无害实际上我已经记不清见过多少条报错是从这一行开始的。最常见的三种字符类型必须用单引号包起来数字可以裸写但一旦混入字母或特殊符号就完蛋列名和VALUES里的值必须一一对应顺序不能乱数量不能少你写的列名必须真实存在数据库不会好心地帮你猜“你大概是想插到name里吧”。我在给团队做代码评审时几乎每次都要求把列名写全。原因很简单一旦你写成下面这种省略版INSERT INTO users VALUES (张三, zhangsanexample.com, 25);就等于把自己的命运交给了表结构的当前定义。哪天表新增了一个created_at字段或者有人调整了列顺序这条语句立刻报“列名或所提供值的数目与表定义不匹配”甚至更隐蔽——数据错位把邮箱写进了年龄列年龄写进了邮箱列整张表变成一团乱麻。所以我的第一条建议特别朴素永远显式列出列名。写起来多几个字母但换来的是半年后你回看脚本时还能一眼看懂以及表结构变更时能准确报错而不是静默错位。1.2 省略列名时自增列和默认列就是那个隐藏陷阱SQL Server、MySQL、PostgreSQL这类有自增主键的表新手最容易踩的坑是这么写-- users 表结构: id 自增, name, email, created_at 默认当前时间 INSERT INTO users VALUES (李四, lisiexample.com, GETDATE());看起来好像没毛病错了。id列在你没写列名的情况下也必须给出值。要么你写个0或者NULL赌数据库让你过要么直接报错。在SQL Server里你会看到类似“表 users 中列 id 的值为NULL该列不允许NULL值”的提示在MySQL里则往往是“Column count doesnt match value count at row 1”。正确做法有二。一是按我上面说的把列名写出来让自增列自己工作INSERT INTO users (name, email) VALUES (李四, lisiexample.com);二是如果真的因为某些历史原因必须写全列请在自增列的位置给DEFAULT关键字INSERT INTO users VALUES (DEFAULT, 李四, lisiexample.com, DEFAULT);第二种写法在SQL Server和PostgreSQL里都认MySQL也支持。但我个人还是更推荐第一种因为它把“数据库自动处理的列”和“应用必须提供的列”分得清清楚楚理解成本低很多。1.3 排错第一步永远是核对列名而不是怀疑数据之前有朋友在群里贴过一个报错内容是SQLite的sqliteexception(1): while preparing statement, no such column: test_url他一眼觉得是SQLite坏了跑来问我怎么处理。我让他把SQL发出来结果他写的是INSERT INTO config (test_url, app_key) VALUES (http://..., abc);而实际表里唯一相关的列叫test_url_new。这就是典型的“列名不存在”错误数据库把这条语句准备出来做编译时就直接拒绝了。遇到类似情况第一件事不是检查数据而是先对着表结构把每个列名核实一遍。拼写错误、大小写错误、下划线位置错误这三种占了九成以上。另外还有一个常被忽略的外键约束导致插入失败。比如orders表有个user_id外键指向users表的id你插入一条order时user_id填了个不存在的用户报错信息会提示外键冲突。这时候别急着怀疑数据格式先看看关联的那张表里到底有没有对应主键。2. 批量写入的正确起手式一条INSERT装下多条记录2.1 多行VALUES一条语句搞定几十条数据项目里经常遇到这种需求要把一批历史数据补录进数据库或者测试时需要造一批假数据。很多人的第一反应是写个循环一次插一条。这当然能跑但无论是应用层还是数据库端效率都低得让人牙疼。其实绝大多数主流数据库都支持多行VALUES写法非常简单INSERT INTO users (name, email) VALUES (张三, zhangsanexample.com), (李四, lisiexample.com), (王五, wangwuexample.com);MySQL、PostgreSQL、SQL Server从2008开始都支持这种写法。Oracle稍微不同——它用INSERT ALL这种看起来像“同时开好几枪”的语法INSERT ALL INTO users (name, email) VALUES (张三, zhangsanexample.com) INTO users (name, email) VALUES (李四, lisiexample.com) SELECT * FROM DUAL;注意最后那行SELECT * FROM DUAL不能省这是Oracle多行插入的结构性要求。多行VALUES的最大好处不是省几行代码而是把多次网络往返合并成一次。对应用来说一次SQL请求和十次SQL请求的性能差距是数量级的。我见过一个接口光插入就跑了三秒的改成多行VALUES后直接降到几十毫秒原因就在这里。2.2 INSERT...SELECT把查询结果直接灌进另一张表如果说多行VALUES适合“手工摆数据”那INSERT...SELECT就是“从一张表搬数据到另一张表”的标准动作。语法简单得惊人INSERT INTO user_backup (id, name, email, created_at) SELECT id, name, email, created_at FROM users WHERE created_at 2024-01-01;这条语句能干很多事做分表归档、建临时分析表、把测试环境的字典表同步到生产环境、把A表的数据清洗后写入B表。使用时有三个细节要注意SELECT出来的列数和类型必须和INSERT目标列对得上数据库不会帮你做类型“硬翻译”像把字符串塞进数值列多数情况下直接报错目标表里的自增列、默认值列同样可以不写让数据库自己处理但如果你明确写了就按照显式值来大批量搬数时源表的过滤条件一定要写清楚别一上来SELECT*否则插入几个小时后才后悔回滚成本很高。这个语法几乎适用于所有数据库MySQL、SQL Server、PostgreSQL、Oracle都认。区别仅仅在细节SQL Server可以用TOP限制行数Oracle要用ROWNUMMySQL是LIMIT。写的时候注意自己那边的方言就好。2.3 跨数据库搬数据最气人的是字符串长度和类型很多人以为“都是SQL语法差不多”结果一跨数据库就栽跟头。我自己就在Oracle上被ORA-01704教育过ORA-01704: string literal too long这个报错的场景通常是表里有个CLOB字段你想直接插一段超长文本。比如这样INSERT INTO feedback (id, content) VALUES (1, 这里是几百KB的正文……);Oracle对“直接写在SQL里的字符串字面量”有长度上限超过这个上限就不让你解析哪怕目标列是CLOB也没用。解决方案有两个。一是拼接INSERT INTO feedback (id, content) VALUES (1, TO_CLOB(前半段) || TO_CLOB(后半段));二是用绑定变量从程序里传进去。第二种我更喜欢因为大文本内容本来就不该硬塞进SQL文件里。顺带说一句SQL Server的NVARCHAR(MAX)、MySQL的LONGTEXT在直接插入超长字面量时相对宽容但同样不建议把几百KB的文本写死在SQL里后续维护会想哭。跨数据库搬数据时另一个容易出问题的是日期和时间。比如MySQL的DATETIME、SQL Server的DATETIME2、Oracle的TIMESTAMP格式和对NULL的处理各有不同。搬数前最好统一转成字符串再转目标类型或者在目标库里显示用CONVERT、CAST指定格式-- SQL Server 插入日期时显式指定样式 INSERT INTO events (event_time) VALUES (CONVERT(DATETIME, 2024-11-05 14:30:00, 120));3. 空值、默认值和GUID主键插入脚本里真正的隐形杀手3.1 手动给NULL会直接覆盖掉默认值新手对NULL和默认值的理解通常是我不填这个列数据库就用默认值。这句话只对了一半准确说法是你不写这个列时用默认值但你写了NULL进去数据库就老老实实存NULL。举个例子有个订单表CREATE TABLE orders ( id INT IDENTITY PRIMARY KEY, order_no VARCHAR(32), status VARCHAR(20) DEFAULT created, created_at DATETIME DEFAULT GETDATE() );如果你执行INSERT INTO orders (order_no, status, created_at) VALUES (SO001, NULL, NULL);最终的结果是status为NULL、created_at为NULL。为什么因为你的SQL里明确写了这两个列的值为NULL数据库认为这是你的意图。尤其是created_at这种字段存成NULL以后业务统计“今天有多少订单”时它会被直接漏掉排查起来非常隐蔽。正确做法是想让默认值生效就干脆别写这个列INSERT INTO orders (order_no) VALUES (SO001);如果你在程序里写插入逻辑还要小心另一个问题从前端传过来的空字符串。很多人会把空字符串当成“没填”随手拼进INSERT语句导致数据库里存的不是NULL也不是默认值而是一堆。我一般建议在写入前统一做一个映射空字符串转成NULL或者直接丢弃这个字段让它走数据库默认值。3.2 主键是GUID时忘记默认值等于给自己找罪受搜索词里有个现象很典型新增数据后程序里拿不到新记录的OBJECTID。这种事在MongoDB里常见在SQL Server中使用UNIQUEIDENTIFIER做主键时也经常发生。通常是表结构这么定义的CREATE TABLE products ( id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), name NVARCHAR(100) );应用层插入时如果写的是INSERT INTO products (name) VALUES (测试商品);那id会自动生成然后用SELECT SCOPE_IDENTITY()去拿新主键的写法就失效了——因为SCOPE_IDENTITY()拿的是自增数值型IDGUID不是自增列。这种情况下必须通过查询返回或者ORM的返回机制来拿。更糟的情况是建表时漏了DEFAULTid UNIQUEIDENTIFIER NOT NULL -- 没写默认值这时候应用层如果不显式传GUID插入直接失败。于是有人图省事在程序里生成一个全零的GUID塞进去或者传空字符串结果整张表的主键变成一堆00000000-0000-0000-0000-000000000000后面想改都改不动。我的经验是GUID主键的默认值一定在数据库层解决。SQL Server用NEWID()需要性能优化可以用NEWSEQUENTIALID()MySQL用UUID()PostgreSQL直接用UUID类型加gen_random_uuid()。应用层不要自己去管主键生成除非你的需求确实要求在应用层生成并跨系统共享。3.3 插入前的类型校验让疯长的字符串别砸进数值列“db2 sql判断数字字符串函数”这类搜索词背后对应的是同一个真实痛点我往一个数值列插入数据结果传过来的值是“abc”数据库直接报转换错误。所以插入前最好先判断字符串是不是数字。SQL Server里有现成的TRY_CAST和TRY_CONVERT转换失败就返回NULL不会报错INSERT INTO metrics (value) VALUES (TRY_CAST(input AS INT));MySQL的写法是REGEXPINSERT INTO metrics (value) VALUES (CASE WHEN 123 REGEXP ^[0-9]$ THEN 123 ELSE NULL END);PostgreSQL可以用regexp_match或者直接CAST(123 AS INTEGER)配合异常处理。DB2的写法相对麻烦可以用TRANSLATE函数把非数字字符替换掉再对比原值CASE WHEN TRANSLATE(input_str, , 0123456789) THEN DECIMAL(input_str, 10, 2) ELSE NULL END总之核心思路只有一个在INSERT之前把类型问题解决掉不要指望数据库报错之后再来补救。报错最轻微的影响是这条数据没插入严重的是整个事务全回滚前面好不容易插入的几千条全没了。4. 数据一旦重复清洗比插入还累去重插入套餐4.1 NOT EXISTS先查后插为什么经常“失灵”“SQL语句去重”这个话题在搜索榜上居高不下原因很简单数据一多重复就成了必然。我见到最多的插入去重写法是IF NOT EXISTS (SELECT 1 FROM users WHERE email newexample.com) BEGIN INSERT INTO users (name, email) VALUES (新人, newexample.com); END;这种写法在小流量、单线程环境下没问题但它有两个硬伤。第一个硬伤是并发。假设前端连点两次提交两个请求同时通过了NOT EXISTS的判断然后各自插入一条重复数据就这么诞生了。数据库的约束检查永远是最终防线业务代码里的“先查后插”只是辅助。第二个硬伤是条件漏判。如果你判断的字段允许NULLNOT EXISTS很可能查不到任何记录然后插入成功可你原本想找出的是“完全一样的数据”而不是“NULL和NULL算不算重复”——结果重复还是进来了。所以我的判断标准是去重不能只靠应用层逻辑数据库层面必须有唯一索引或主键作为兜底。有了唯一索引哪怕应用层漏了判断数据库也会拦下重复数据最多报个错而已。4.2 MySQL的I套餐IGNORE和ON DUPLICATE KEY UPDATEMySQL在这块的设计是我见过最实用的。如果表上建了唯一索引或主键插入去重可以直接这么写INSERT IGNORE INTO users (id, name, email) VALUES (1, 张三, zhangsanexample.com);意思是如果这条数据已经存在主键或唯一键冲突立即忽略不报错也不更新。有一种“静默跳过”的感觉适合那种“没有就加有就算了”的场景。如果要做得更细比如“没有就插入有就更新某些字段”用ON DUPLICATE KEY UPDATEINSERT INTO users (id, name, email, login_count) VALUES (1, 张三, zhangsanexample.com, 1) ON DUPLICATE KEY UPDATE login_count login_count 1, updated_at NOW();这句的语义是主键1已经存在时不插入新记录而是把login_count加1、updated_at更新一下。在计数类、登录统计类的业务里非常好用。4.3 SQL Server和PostgreSQLMERGE是去重插入的通用答案SQL Server没有MySQL那么贴心的INSERT IGNORE但有个更重型的语法MERGE。如果想把“源表的数据合并进目标表有则更新、无则插入、多余则删除”MERGE一套全包MERGE INTO users AS target USING (SELECT zhangsanexample.com AS email, 张三 AS name) AS source ON target.email source.email WHEN MATCHED THEN UPDATE SET target.name source.name WHEN NOT MATCHED THEN INSERT (name, email) VALUES (source.name, source.email);PostgreSQL也支持MERGE从PG15开始正式可用更早版本通常用ON CONFLICTINSERT INTO users (name, email) VALUES (张三, zhangsanexample.com) ON CONFLICT (email) DO UPDATE SET name EXCLUDED.name;这里EXCLUDED代表“准备插入但因为冲突被排除的那条记录”用它来引用新值非常方便。我个人觉得PostgreSQL的ON CONFLICT比MERGE更好读MERGE虽然功能强大但参数一多就容易看晕。4.4 已经重复了怎么办用ROW_NUMBER清理存量数据如果你没有做去重插入结果表里已经堆了一堆重复记录就要考虑清洗。最常见的需求是每个邮箱只保留一条记录其余删掉。SQL Server里可以这样写WITH ranked AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users ) DELETE FROM ranked WHERE rn 1;这段逻辑用窗口函数给相同email的分组编号保留id最小的一条把其余的删掉。MySQL 8.0之后也可以用同样的写法MySQL 5.7及以前版本没有窗口函数只能靠自连接DELETE u1 FROM users u1 INNER JOIN users u2 ON u1.email u2.email AND u1.id u2.id;这种自连接写法的效果是保留每个邮箱的最小id删除比它大的所有重复记录。在数据量不大的表上完全够用但如果是百万级重复数据建议先备份、再分批删除。5. 从命令行走进程序Python和框架里的原生SQL注意这三个点5.1 参数化执行INSERT是基本功不是加分项很多人学了SQL语法之后第一件事就是把它拼进Python代码里sql fINSERT INTO users (name, email) VALUES ({name}, {email}) cursor.execute(sql)看起来方便实则是个大坑。只要name或email里有一个单引号这个SQL就直接语法报错如果这个值来自用户输入更严重的风险是SQL注入——最简单的万能密码绕过本质都是利用拼接SQL时把用户输入的引号和关键字硬塞进了原语句。正确写法是参数化让数据库驱动帮你处理转义# sqlite3 / MySQLdb / psycopg2 通用风格 cursor.execute( INSERT INTO users (name, email) VALUES (?, ?), (name, email) )用?占位符把值作为参数传过去。数据库端拿到的是参数而不是被拼接进去的SQL代码用户的输入即使长得像SQL也只会被当成普通字符串处理。这是我在项目里最强调的一条红线任何包含用户输入字段的INSERT必须用参数化方式执行。5.2 Prisma这类ORM里怎么执行原生INSERT现在不少项目用Prisma这类ORM正常场景下是不需要写原生SQL的但有时候表结构比较复杂或者需要批量执行一些数据库特有的语法就要用到原生SQL。Prisma里的思路和Python其实一样只不过把?换成了模板字符串的插值await prisma.$executeRaw INSERT INTO users (name, email) VALUES (${name}, ${email}) ;注意这里的${name}并不是直接拼字符串Prisma会把它编译成参数。如果你用的是普通字符串拼接await prisma.$executeRaw(INSERT INTO users (name) VALUES (${name}))那依然有注入风险因为$executeRaw识别的是模板标签普通模板字符串插值它管不着。所以用Prisma时记住一条原生SQL一律用$executeRaw加模板变量别为了省事去拼字符串。如果是查询数据对应的是$queryRaw两者的使用边界在Prisma文档里写得很清楚但逻辑都一样——能不用原生SQL就不用用了就参数化。5.3 executemany和循环execute性能差一个数量级程序里插入多条数据时很多人会写for循环for user in user_list: cursor.execute(INSERT INTO users (name, email) VALUES (?, ?), (user.name, user.email))每条数据一次网络往返100条数据就100次往返。更好的选择是用executemany一次提交cursor.executemany( INSERT INTO users (name, email) VALUES (?, ?), [(u.name, u.email) for u in user_list] )executemany在底层会尽量复用同一条语句只是每次绑定不同的参数整体耗时比循环execute低一个量级。如果你用的是别的语言或框架找对应的Batch API就是比如Java的addBatch()、Go的BatchInsert。6. 插入太慢、日志暴涨时的处理思路把事务当朋友用6.1 慢SQL通常不是“写法不对”而是“每条都自己提交”有不少人跑完批量插入后跑来问我为什么插几万条数据要十分钟我看了一眼代码发现循环里每次执行完都自动提交。这等价于让数据库每插入一条记录就强制落盘一次同时每个提交都要生成一条事务日志记录。数据量一大磁盘I/O直接成为瓶颈。不要误会自动提交本身不是坏事它保护了数据一致性。但在大批量插入场景下正确的思路是“攒一批提交一次”。SQL Server里你可以显式包一个事务BEGIN TRANSACTION; INSERT INTO big_table (col1, col2) VALUES (...); INSERT INTO big_table (col1, col2) VALUES (...); COMMIT TRANSACTION;两条之间如果发生错误整个事务可以回滚。通常我会按数量分批比如5000条或10000条一个事务。一次事务太大日志文件会激增锁范围也不可控一次事务太小又回到反复提交的老路上。6.2 大事务批量插入的几个实用备案除了分批提交还有几个思路可以叠加。第一个是减少索引开销。插入的效率受索引数量的影响很大一张表如果建了七八个索引每插一条记录要更新的索引节点数量非常可观。如果是临时导入历史数据可以先DROP索引或禁用索引导完再重建。生产环境的在线数据迁移不建议这么干但离线归档的场景非常合适。第二个是选对工具。几万行用INSERT多行VALUES问题不大几十万几百万行就别再手写SQL了。SQL Server有BULK INSERT和bcpMySQL有LOAD DATA INFILEPostgreSQL有COPY。这些工具走的是数据库原生加载通道不经过SQL解析器速度比逐条INSERT快得不是一星半点。第三个是在SQL Server里注意恢复模式。如果数据库配置成完整恢复模式大批量插入会产生海量日志。临时把数据库切成简单恢复模式或大容量日志恢复模式导完再切回来日志量能大幅下降。这个操作需要评估业务容忍度不是所有环境都允许你动恢复模式但确实是最有效的“日志暴涨解药”之一。6.3 我自己的实操底线干这行久了我的习惯基本固定成一套模板先确认表结构列名、约束、默认值心里有数写INSERT时永远列出明确列名批量数据优先多行VALUES或专用导入工具数据量超过几十万直接上BULK/COPY程序里执行SQL全部走参数化不拼字符串每个事务控制在一万条以内预留回滚余地导入前看一眼目标表的索引情况能临时禁用的就临时禁用跑完一定做一次去重校验用唯一索引兜底。最后分享一个小技巧在SQL Server里插入测试数据时如果只想快速生成十万行不需要写循环直接用递归CTE加多行INSERT或SELECT INTOWITH nums AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM nums WHERE n 100000 ) INSERT INTO test_table (num, note) SELECT n, test FROM nums OPTION (MAXRECURSION 0);这条语句在评审时被同事夸过好几次属于那种“能干活又长脸”的写法。但对于日常业务数据我始终提醒自己添加数据的核心不是“能插进去”而是“插进去之后不会让整张表烂掉”。写INSERT简单写好INSERT不简单。 SQL插入数据避坑指南：从INSERT到批量写入与去重优化
1. 先把“加数据”这件事拆清楚INSERT不提列名埋雷率90%1.1 最基础的一条语句问题往往不在语句本身SQL里“添加数据”的默认答案就是INSERT标准句式长这样INSERT INTO users (name, email, age) VALUES (张三, zhangsanexample.com, 25);看起来人畜无害实际上我已经记不清见过多少条报错是从这一行开始的。最常见的三种字符类型必须用单引号包起来数字可以裸写但一旦混入字母或特殊符号就完蛋列名和VALUES里的值必须一一对应顺序不能乱数量不能少你写的列名必须真实存在数据库不会好心地帮你猜“你大概是想插到name里吧”。我在给团队做代码评审时几乎每次都要求把列名写全。原因很简单一旦你写成下面这种省略版INSERT INTO users VALUES (张三, zhangsanexample.com, 25);就等于把自己的命运交给了表结构的当前定义。哪天表新增了一个created_at字段或者有人调整了列顺序这条语句立刻报“列名或所提供值的数目与表定义不匹配”甚至更隐蔽——数据错位把邮箱写进了年龄列年龄写进了邮箱列整张表变成一团乱麻。所以我的第一条建议特别朴素永远显式列出列名。写起来多几个字母但换来的是半年后你回看脚本时还能一眼看懂以及表结构变更时能准确报错而不是静默错位。1.2 省略列名时自增列和默认列就是那个隐藏陷阱SQL Server、MySQL、PostgreSQL这类有自增主键的表新手最容易踩的坑是这么写-- users 表结构: id 自增, name, email, created_at 默认当前时间 INSERT INTO users VALUES (李四, lisiexample.com, GETDATE());看起来好像没毛病错了。id列在你没写列名的情况下也必须给出值。要么你写个0或者NULL赌数据库让你过要么直接报错。在SQL Server里你会看到类似“表 users 中列 id 的值为NULL该列不允许NULL值”的提示在MySQL里则往往是“Column count doesnt match value count at row 1”。正确做法有二。一是按我上面说的把列名写出来让自增列自己工作INSERT INTO users (name, email) VALUES (李四, lisiexample.com);二是如果真的因为某些历史原因必须写全列请在自增列的位置给DEFAULT关键字INSERT INTO users VALUES (DEFAULT, 李四, lisiexample.com, DEFAULT);第二种写法在SQL Server和PostgreSQL里都认MySQL也支持。但我个人还是更推荐第一种因为它把“数据库自动处理的列”和“应用必须提供的列”分得清清楚楚理解成本低很多。1.3 排错第一步永远是核对列名而不是怀疑数据之前有朋友在群里贴过一个报错内容是SQLite的sqliteexception(1): while preparing statement, no such column: test_url他一眼觉得是SQLite坏了跑来问我怎么处理。我让他把SQL发出来结果他写的是INSERT INTO config (test_url, app_key) VALUES (http://..., abc);而实际表里唯一相关的列叫test_url_new。这就是典型的“列名不存在”错误数据库把这条语句准备出来做编译时就直接拒绝了。遇到类似情况第一件事不是检查数据而是先对着表结构把每个列名核实一遍。拼写错误、大小写错误、下划线位置错误这三种占了九成以上。另外还有一个常被忽略的外键约束导致插入失败。比如orders表有个user_id外键指向users表的id你插入一条order时user_id填了个不存在的用户报错信息会提示外键冲突。这时候别急着怀疑数据格式先看看关联的那张表里到底有没有对应主键。2. 批量写入的正确起手式一条INSERT装下多条记录2.1 多行VALUES一条语句搞定几十条数据项目里经常遇到这种需求要把一批历史数据补录进数据库或者测试时需要造一批假数据。很多人的第一反应是写个循环一次插一条。这当然能跑但无论是应用层还是数据库端效率都低得让人牙疼。其实绝大多数主流数据库都支持多行VALUES写法非常简单INSERT INTO users (name, email) VALUES (张三, zhangsanexample.com), (李四, lisiexample.com), (王五, wangwuexample.com);MySQL、PostgreSQL、SQL Server从2008开始都支持这种写法。Oracle稍微不同——它用INSERT ALL这种看起来像“同时开好几枪”的语法INSERT ALL INTO users (name, email) VALUES (张三, zhangsanexample.com) INTO users (name, email) VALUES (李四, lisiexample.com) SELECT * FROM DUAL;注意最后那行SELECT * FROM DUAL不能省这是Oracle多行插入的结构性要求。多行VALUES的最大好处不是省几行代码而是把多次网络往返合并成一次。对应用来说一次SQL请求和十次SQL请求的性能差距是数量级的。我见过一个接口光插入就跑了三秒的改成多行VALUES后直接降到几十毫秒原因就在这里。2.2 INSERT...SELECT把查询结果直接灌进另一张表如果说多行VALUES适合“手工摆数据”那INSERT...SELECT就是“从一张表搬数据到另一张表”的标准动作。语法简单得惊人INSERT INTO user_backup (id, name, email, created_at) SELECT id, name, email, created_at FROM users WHERE created_at 2024-01-01;这条语句能干很多事做分表归档、建临时分析表、把测试环境的字典表同步到生产环境、把A表的数据清洗后写入B表。使用时有三个细节要注意SELECT出来的列数和类型必须和INSERT目标列对得上数据库不会帮你做类型“硬翻译”像把字符串塞进数值列多数情况下直接报错目标表里的自增列、默认值列同样可以不写让数据库自己处理但如果你明确写了就按照显式值来大批量搬数时源表的过滤条件一定要写清楚别一上来SELECT*否则插入几个小时后才后悔回滚成本很高。这个语法几乎适用于所有数据库MySQL、SQL Server、PostgreSQL、Oracle都认。区别仅仅在细节SQL Server可以用TOP限制行数Oracle要用ROWNUMMySQL是LIMIT。写的时候注意自己那边的方言就好。2.3 跨数据库搬数据最气人的是字符串长度和类型很多人以为“都是SQL语法差不多”结果一跨数据库就栽跟头。我自己就在Oracle上被ORA-01704教育过ORA-01704: string literal too long这个报错的场景通常是表里有个CLOB字段你想直接插一段超长文本。比如这样INSERT INTO feedback (id, content) VALUES (1, 这里是几百KB的正文……);Oracle对“直接写在SQL里的字符串字面量”有长度上限超过这个上限就不让你解析哪怕目标列是CLOB也没用。解决方案有两个。一是拼接INSERT INTO feedback (id, content) VALUES (1, TO_CLOB(前半段) || TO_CLOB(后半段));二是用绑定变量从程序里传进去。第二种我更喜欢因为大文本内容本来就不该硬塞进SQL文件里。顺带说一句SQL Server的NVARCHAR(MAX)、MySQL的LONGTEXT在直接插入超长字面量时相对宽容但同样不建议把几百KB的文本写死在SQL里后续维护会想哭。跨数据库搬数据时另一个容易出问题的是日期和时间。比如MySQL的DATETIME、SQL Server的DATETIME2、Oracle的TIMESTAMP格式和对NULL的处理各有不同。搬数前最好统一转成字符串再转目标类型或者在目标库里显示用CONVERT、CAST指定格式-- SQL Server 插入日期时显式指定样式 INSERT INTO events (event_time) VALUES (CONVERT(DATETIME, 2024-11-05 14:30:00, 120));3. 空值、默认值和GUID主键插入脚本里真正的隐形杀手3.1 手动给NULL会直接覆盖掉默认值新手对NULL和默认值的理解通常是我不填这个列数据库就用默认值。这句话只对了一半准确说法是你不写这个列时用默认值但你写了NULL进去数据库就老老实实存NULL。举个例子有个订单表CREATE TABLE orders ( id INT IDENTITY PRIMARY KEY, order_no VARCHAR(32), status VARCHAR(20) DEFAULT created, created_at DATETIME DEFAULT GETDATE() );如果你执行INSERT INTO orders (order_no, status, created_at) VALUES (SO001, NULL, NULL);最终的结果是status为NULL、created_at为NULL。为什么因为你的SQL里明确写了这两个列的值为NULL数据库认为这是你的意图。尤其是created_at这种字段存成NULL以后业务统计“今天有多少订单”时它会被直接漏掉排查起来非常隐蔽。正确做法是想让默认值生效就干脆别写这个列INSERT INTO orders (order_no) VALUES (SO001);如果你在程序里写插入逻辑还要小心另一个问题从前端传过来的空字符串。很多人会把空字符串当成“没填”随手拼进INSERT语句导致数据库里存的不是NULL也不是默认值而是一堆。我一般建议在写入前统一做一个映射空字符串转成NULL或者直接丢弃这个字段让它走数据库默认值。3.2 主键是GUID时忘记默认值等于给自己找罪受搜索词里有个现象很典型新增数据后程序里拿不到新记录的OBJECTID。这种事在MongoDB里常见在SQL Server中使用UNIQUEIDENTIFIER做主键时也经常发生。通常是表结构这么定义的CREATE TABLE products ( id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), name NVARCHAR(100) );应用层插入时如果写的是INSERT INTO products (name) VALUES (测试商品);那id会自动生成然后用SELECT SCOPE_IDENTITY()去拿新主键的写法就失效了——因为SCOPE_IDENTITY()拿的是自增数值型IDGUID不是自增列。这种情况下必须通过查询返回或者ORM的返回机制来拿。更糟的情况是建表时漏了DEFAULTid UNIQUEIDENTIFIER NOT NULL -- 没写默认值这时候应用层如果不显式传GUID插入直接失败。于是有人图省事在程序里生成一个全零的GUID塞进去或者传空字符串结果整张表的主键变成一堆00000000-0000-0000-0000-000000000000后面想改都改不动。我的经验是GUID主键的默认值一定在数据库层解决。SQL Server用NEWID()需要性能优化可以用NEWSEQUENTIALID()MySQL用UUID()PostgreSQL直接用UUID类型加gen_random_uuid()。应用层不要自己去管主键生成除非你的需求确实要求在应用层生成并跨系统共享。3.3 插入前的类型校验让疯长的字符串别砸进数值列“db2 sql判断数字字符串函数”这类搜索词背后对应的是同一个真实痛点我往一个数值列插入数据结果传过来的值是“abc”数据库直接报转换错误。所以插入前最好先判断字符串是不是数字。SQL Server里有现成的TRY_CAST和TRY_CONVERT转换失败就返回NULL不会报错INSERT INTO metrics (value) VALUES (TRY_CAST(input AS INT));MySQL的写法是REGEXPINSERT INTO metrics (value) VALUES (CASE WHEN 123 REGEXP ^[0-9]$ THEN 123 ELSE NULL END);PostgreSQL可以用regexp_match或者直接CAST(123 AS INTEGER)配合异常处理。DB2的写法相对麻烦可以用TRANSLATE函数把非数字字符替换掉再对比原值CASE WHEN TRANSLATE(input_str, , 0123456789) THEN DECIMAL(input_str, 10, 2) ELSE NULL END总之核心思路只有一个在INSERT之前把类型问题解决掉不要指望数据库报错之后再来补救。报错最轻微的影响是这条数据没插入严重的是整个事务全回滚前面好不容易插入的几千条全没了。4. 数据一旦重复清洗比插入还累去重插入套餐4.1 NOT EXISTS先查后插为什么经常“失灵”“SQL语句去重”这个话题在搜索榜上居高不下原因很简单数据一多重复就成了必然。我见到最多的插入去重写法是IF NOT EXISTS (SELECT 1 FROM users WHERE email newexample.com) BEGIN INSERT INTO users (name, email) VALUES (新人, newexample.com); END;这种写法在小流量、单线程环境下没问题但它有两个硬伤。第一个硬伤是并发。假设前端连点两次提交两个请求同时通过了NOT EXISTS的判断然后各自插入一条重复数据就这么诞生了。数据库的约束检查永远是最终防线业务代码里的“先查后插”只是辅助。第二个硬伤是条件漏判。如果你判断的字段允许NULLNOT EXISTS很可能查不到任何记录然后插入成功可你原本想找出的是“完全一样的数据”而不是“NULL和NULL算不算重复”——结果重复还是进来了。所以我的判断标准是去重不能只靠应用层逻辑数据库层面必须有唯一索引或主键作为兜底。有了唯一索引哪怕应用层漏了判断数据库也会拦下重复数据最多报个错而已。4.2 MySQL的I套餐IGNORE和ON DUPLICATE KEY UPDATEMySQL在这块的设计是我见过最实用的。如果表上建了唯一索引或主键插入去重可以直接这么写INSERT IGNORE INTO users (id, name, email) VALUES (1, 张三, zhangsanexample.com);意思是如果这条数据已经存在主键或唯一键冲突立即忽略不报错也不更新。有一种“静默跳过”的感觉适合那种“没有就加有就算了”的场景。如果要做得更细比如“没有就插入有就更新某些字段”用ON DUPLICATE KEY UPDATEINSERT INTO users (id, name, email, login_count) VALUES (1, 张三, zhangsanexample.com, 1) ON DUPLICATE KEY UPDATE login_count login_count 1, updated_at NOW();这句的语义是主键1已经存在时不插入新记录而是把login_count加1、updated_at更新一下。在计数类、登录统计类的业务里非常好用。4.3 SQL Server和PostgreSQLMERGE是去重插入的通用答案SQL Server没有MySQL那么贴心的INSERT IGNORE但有个更重型的语法MERGE。如果想把“源表的数据合并进目标表有则更新、无则插入、多余则删除”MERGE一套全包MERGE INTO users AS target USING (SELECT zhangsanexample.com AS email, 张三 AS name) AS source ON target.email source.email WHEN MATCHED THEN UPDATE SET target.name source.name WHEN NOT MATCHED THEN INSERT (name, email) VALUES (source.name, source.email);PostgreSQL也支持MERGE从PG15开始正式可用更早版本通常用ON CONFLICTINSERT INTO users (name, email) VALUES (张三, zhangsanexample.com) ON CONFLICT (email) DO UPDATE SET name EXCLUDED.name;这里EXCLUDED代表“准备插入但因为冲突被排除的那条记录”用它来引用新值非常方便。我个人觉得PostgreSQL的ON CONFLICT比MERGE更好读MERGE虽然功能强大但参数一多就容易看晕。4.4 已经重复了怎么办用ROW_NUMBER清理存量数据如果你没有做去重插入结果表里已经堆了一堆重复记录就要考虑清洗。最常见的需求是每个邮箱只保留一条记录其余删掉。SQL Server里可以这样写WITH ranked AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users ) DELETE FROM ranked WHERE rn 1;这段逻辑用窗口函数给相同email的分组编号保留id最小的一条把其余的删掉。MySQL 8.0之后也可以用同样的写法MySQL 5.7及以前版本没有窗口函数只能靠自连接DELETE u1 FROM users u1 INNER JOIN users u2 ON u1.email u2.email AND u1.id u2.id;这种自连接写法的效果是保留每个邮箱的最小id删除比它大的所有重复记录。在数据量不大的表上完全够用但如果是百万级重复数据建议先备份、再分批删除。5. 从命令行走进程序Python和框架里的原生SQL注意这三个点5.1 参数化执行INSERT是基本功不是加分项很多人学了SQL语法之后第一件事就是把它拼进Python代码里sql fINSERT INTO users (name, email) VALUES ({name}, {email}) cursor.execute(sql)看起来方便实则是个大坑。只要name或email里有一个单引号这个SQL就直接语法报错如果这个值来自用户输入更严重的风险是SQL注入——最简单的万能密码绕过本质都是利用拼接SQL时把用户输入的引号和关键字硬塞进了原语句。正确写法是参数化让数据库驱动帮你处理转义# sqlite3 / MySQLdb / psycopg2 通用风格 cursor.execute( INSERT INTO users (name, email) VALUES (?, ?), (name, email) )用?占位符把值作为参数传过去。数据库端拿到的是参数而不是被拼接进去的SQL代码用户的输入即使长得像SQL也只会被当成普通字符串处理。这是我在项目里最强调的一条红线任何包含用户输入字段的INSERT必须用参数化方式执行。5.2 Prisma这类ORM里怎么执行原生INSERT现在不少项目用Prisma这类ORM正常场景下是不需要写原生SQL的但有时候表结构比较复杂或者需要批量执行一些数据库特有的语法就要用到原生SQL。Prisma里的思路和Python其实一样只不过把?换成了模板字符串的插值await prisma.$executeRaw INSERT INTO users (name, email) VALUES (${name}, ${email}) ;注意这里的${name}并不是直接拼字符串Prisma会把它编译成参数。如果你用的是普通字符串拼接await prisma.$executeRaw(INSERT INTO users (name) VALUES (${name}))那依然有注入风险因为$executeRaw识别的是模板标签普通模板字符串插值它管不着。所以用Prisma时记住一条原生SQL一律用$executeRaw加模板变量别为了省事去拼字符串。如果是查询数据对应的是$queryRaw两者的使用边界在Prisma文档里写得很清楚但逻辑都一样——能不用原生SQL就不用用了就参数化。5.3 executemany和循环execute性能差一个数量级程序里插入多条数据时很多人会写for循环for user in user_list: cursor.execute(INSERT INTO users (name, email) VALUES (?, ?), (user.name, user.email))每条数据一次网络往返100条数据就100次往返。更好的选择是用executemany一次提交cursor.executemany( INSERT INTO users (name, email) VALUES (?, ?), [(u.name, u.email) for u in user_list] )executemany在底层会尽量复用同一条语句只是每次绑定不同的参数整体耗时比循环execute低一个量级。如果你用的是别的语言或框架找对应的Batch API就是比如Java的addBatch()、Go的BatchInsert。6. 插入太慢、日志暴涨时的处理思路把事务当朋友用6.1 慢SQL通常不是“写法不对”而是“每条都自己提交”有不少人跑完批量插入后跑来问我为什么插几万条数据要十分钟我看了一眼代码发现循环里每次执行完都自动提交。这等价于让数据库每插入一条记录就强制落盘一次同时每个提交都要生成一条事务日志记录。数据量一大磁盘I/O直接成为瓶颈。不要误会自动提交本身不是坏事它保护了数据一致性。但在大批量插入场景下正确的思路是“攒一批提交一次”。SQL Server里你可以显式包一个事务BEGIN TRANSACTION; INSERT INTO big_table (col1, col2) VALUES (...); INSERT INTO big_table (col1, col2) VALUES (...); COMMIT TRANSACTION;两条之间如果发生错误整个事务可以回滚。通常我会按数量分批比如5000条或10000条一个事务。一次事务太大日志文件会激增锁范围也不可控一次事务太小又回到反复提交的老路上。6.2 大事务批量插入的几个实用备案除了分批提交还有几个思路可以叠加。第一个是减少索引开销。插入的效率受索引数量的影响很大一张表如果建了七八个索引每插一条记录要更新的索引节点数量非常可观。如果是临时导入历史数据可以先DROP索引或禁用索引导完再重建。生产环境的在线数据迁移不建议这么干但离线归档的场景非常合适。第二个是选对工具。几万行用INSERT多行VALUES问题不大几十万几百万行就别再手写SQL了。SQL Server有BULK INSERT和bcpMySQL有LOAD DATA INFILEPostgreSQL有COPY。这些工具走的是数据库原生加载通道不经过SQL解析器速度比逐条INSERT快得不是一星半点。第三个是在SQL Server里注意恢复模式。如果数据库配置成完整恢复模式大批量插入会产生海量日志。临时把数据库切成简单恢复模式或大容量日志恢复模式导完再切回来日志量能大幅下降。这个操作需要评估业务容忍度不是所有环境都允许你动恢复模式但确实是最有效的“日志暴涨解药”之一。6.3 我自己的实操底线干这行久了我的习惯基本固定成一套模板先确认表结构列名、约束、默认值心里有数写INSERT时永远列出明确列名批量数据优先多行VALUES或专用导入工具数据量超过几十万直接上BULK/COPY程序里执行SQL全部走参数化不拼字符串每个事务控制在一万条以内预留回滚余地导入前看一眼目标表的索引情况能临时禁用的就临时禁用跑完一定做一次去重校验用唯一索引兜底。最后分享一个小技巧在SQL Server里插入测试数据时如果只想快速生成十万行不需要写循环直接用递归CTE加多行INSERT或SELECT INTOWITH nums AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM nums WHERE n 100000 ) INSERT INTO test_table (num, note) SELECT n, test FROM nums OPTION (MAXRECURSION 0);这条语句在评审时被同事夸过好几次属于那种“能干活又长脸”的写法。但对于日常业务数据我始终提醒自己添加数据的核心不是“能插进去”而是“插进去之后不会让整张表烂掉”。写INSERT简单写好INSERT不简单。