SQL 数据插入与更新技巧

mysql用insert ignore或on duplicate key update处理重复主键，postgresql必须用on conflict；update务必检查where条件防全表误更新；insert需显式指定列名；批量插入应拆分为500–1000行以内。

INSERT INTO 重复主键时怎么不报错

MySQL 和 PostgreSQL 都支持在冲突时跳过或覆盖，但语法完全不同，硬套会直接报错 Error 1062 或 duplicate key value violates unique constraint。

MySQL 用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE；PostgreSQL 必须用 INSERT ... ON CONFLICT，没有 IGNORE 这种写法。

• INSERT IGNORE：遇到重复主键/唯一索引就静默跳过，不报错也不插入，但也不会返回影响行数（可能为 0）
• ON DUPLICATE KEY UPDATE：只适用于 MySQL，且必须有主键或唯一索引才能触发，UPDATE 后面不能写子查询，也不能引用被插入的虚拟表（如 VALUES(col) 可用，但 select 不行）
• PostgreSQL 的 ON CONFLICT (col) DO UPDATE SET ... 必须显式指定冲突列，不能只写 ON CONFLICT DO NOTHING（除非表只有一个唯一约束）

UPDATE 时 WHERE 条件漏掉主键导致全表误更新

这是线上事故高频原因——UPDATE users SET status = 'active' 少了 WHERE id = 123，整张表数据就变了。数据库不会帮你校验逻辑，只认语法。

安全做法不是靠记忆，而是靠习惯和工具约束：

• 所有 UPDATE 语句写完第一件事：高亮检查 WHERE 子句是否存在、是否含主键或足够筛选的条件
• 开发环境强制加 SET SQL_SAFE_UPDATES = 1（MySQL），它会拒绝没有 WHERE 或 LIMIT 的 UPDATE/delete
• 用 SELECT 先验证：把 UPDATE 的 WHERE 拿出来跑一遍 SELECT count(*)，确认命中行数合理

INSERT SELECT 和 VALUES 混用时字段顺序容易错位

INSERT INTO t(a,b,c) SELECT x,y,z FROM s 看似简单，但一旦 SELECT 字段顺序和目标表定义不一致，或者中间加了表达式（比如 COALESCE(x, '')），就极易插错列——尤其是目标表字段多、注释少的时候。

最稳妥的方式是显式对齐，而不是依赖位置：

• 永远用 INSERT INTO t(col1, col2, ...) VALUES (...) 或 INSERT INTO t(col1, col2, ...) SELECT ...，明确写出列名
• 避免 INSERT INTO t SELECT ...（无目标列列表），这种写法强依赖源表和目标表字段顺序完全一致，迁移或改表后立刻失效
• 如果 SELECT 中用了函数或别名，确保别名和目标列名一一对应，不要靠“第几个字段”去猜

批量插入时 VALUES 太多导致报错 Packet too large

MySQL 默认 max_allowed_packet = 4M，一条含 5000 行 VALUES 的 INSERT 很容易超限，报错信息就是 Packets larger than max_allowed_packet are not allowed。

不是越大越好，也不是只能调服务端参数：

• 客户端可临时设置：连接后执行 SET session max_allowed_packet = 64*1024*1024（64MB），但注意内存占用和超时风险
• 更推荐拆分：单条 INSERT 控制在 500–1000 行内，具体看平均行长度；用程序循环拼接，比硬扛一个大包更稳
• PostgreSQL 对单条语句长度限制更松，但同样建议拆，因为锁粒度和 WAL 日志压力会随语句变长明显上升

字段类型不匹配、隐式转换失败、事务中没及时提交——这些细节不出现在报错第一行，但往往卡在批量插入的中间某条记录上，查起来比语法错误更费时间。