📖 本篇简介：本章是查询写法踩坑的下篇，聚焦写操作、统计、分页稳定性、读写分离等更"危险"的 12 个坑——有些写错就是事故级的（全表 UPDATE）。覆盖无 WHERE 写操作、批量更新、空串与 NULL、NOT IN 陷阱、分页不稳定、读写分离延迟、COUNT 大表与重复键。示例基于 MySQL 8.0。读完你能写出既正确又安全的 SQL。
目录
- 坑 1：UPDATE/DELETE 忘了 WHERE
- 坑 2：批量更新一条条循环
- 坑 3：空字符串和 NULL 混用
- 坑 4：NOT IN 子查询里藏着 NULL
- 坑 5：分页查询不带 ORDER BY
- 坑 6：写主库立刻去从库读
- 坑 7：大表上 COUNT(*) 硬数
- 坑 8：INSERT 重复键直接报错
- 坑 9：字符串列存数字排序错乱
- 坑 10：WHERE 里堆复杂 OR 嵌套
- 坑 11：在 WHERE 里用函数拼条件
- 坑 12：以为 LIMIT 能防止 UPDATE 全表
- 速查表：写法安全速记
- 注意事项：写操作安全 Checklist
- 自测：你能避坑吗
坑 1：UPDATE/DELETE 忘了 WHERE
场景 / 错误示范：
-- 改密码，忘了加 WHERE
UPDATE user SET password = 'abc';
-- 全表所有用户的密码都被改成了 abc！
-- 或者：
DELETE FROM order_t; -- 没加 WHERE，整表清空
为什么是坑：这是最致命的 SQL 错误。UPDATE/DELETE 不带 WHERE 会作用到全表，一行写错就是全库事故。和 SELECT 不同，写操作改错无法"再查一次就行"，必须靠备份恢复。这类事故在生产上反复发生。
正确做法：
-- 永远先 SELECT 确认要改的行，再写 UPDATE/DELETE
SELECT * FROM user WHERE id = 123; -- 先确认范围
UPDATE user SET password = 'abc' WHERE id = 123; -- 再写，带 WHERE
-- 危险操作前先在事务里干，确认行数对了再 COMMIT
START TRANSACTION;
DELETE FROM order_t WHERE create_time < '2020-01-01';
-- SELECT ROW_COUNT() 确认删了多少行，对了再 COMMIT
COMMIT;
加分点：生产库用事务包裹写操作，ROW_COUNT() 确认影响行数符合预期再提交。开启 sql_safe_updates（没 WHERE 的 UPDATE/DELETE 直接报错）是个好习惯。
坑 2：批量更新一条条循环
场景 / 错误示范：
# 要给 1 万个用户加积分，循环单条 UPDATE
for uid in uid_list: # 1 万次
cur.execute("UPDATE user SET score=score+10 WHERE id=?", (uid,))
# 1 万次网络往返 + 1 万次事务提交，慢得不可忍受
为什么是坑：每条 SQL 一次网络往返、一次事务提交，1 万行就是 1 万次 round-trip。网络延迟和事务开销被放大 1 万倍，批量操作变巨慢，还持有大量行锁。这是典型的"把数据库当游标用"的写法。
正确做法：
-- 用 IN 或 CASE 一条 SQL 批量更新
UPDATE user SET score = score + 10 WHERE id IN (1,2,3,...,10000);
-- 或分批：每批 500~1000 条，避免一条大事务锁太多行
UPDATE user SET score = score + 10 WHERE id IN (前 500 个 id);
UPDATE user SET score = score + 10 WHERE id IN (接下来 500 个 id);
# 应用层分批提交，控制单批大小
for batch in chunks(uid_list, 500):
cur.executemany("UPDATE user SET score=score+10 WHERE id=?", batch)
加分点：批量更新要在"一条大 SQL"和"分批小 SQL"间取平衡：太大锁太多、太小往返太多。每批 500~1000 是常见经验值。
坑 3：空字符串和 NULL 混用
场景 / 错误示范：
-- 存手机号：有时存 ''，有时存 NULL，没有约定
INSERT INTO user(phone) VALUES (''), (NULL), ('13800001111');
-- 查询：WHERE phone != '' 漏掉了 phone IS NULL 的行
SELECT * FROM user WHERE phone != '';
-- NULL 行既不 = '' 也 != ''，查不出来
为什么是坑：空串 '' 和 NULL 是两回事：'' 是"有值但为空"，NULL 是"未知/没值"。业务里不约定用哪个，查询时 != ''、= '' 都会漏掉 NULL 那批，统计和筛选就对不上。这是数据质量问题，根源在写入端没统一约定。
正确做法：
-- 全表统一：要么都存 NULL，要么都存空串，二选一
-- 推荐（呼应第42章）：NOT NULL DEFAULT ''，业务上"没值"就存空串
ALTER TABLE user MODIFY phone VARCHAR(20) NOT NULL DEFAULT '';
-- 清理历史脏数据
UPDATE user SET phone = '' WHERE phone IS NULL;
-- 之后查询统一用 = '' / != ''，不再混 NULL
SELECT * FROM user WHERE phone = '';
加分点：一个字段只能有一种"空"的表示。建表时就 NOT NULL DEFAULT 定死，从写入端杜绝混用。
坑 4：NOT IN 子查询里藏着 NULL
场景 / 错误示范：
-- 查"不在黑名单里的用户"
SELECT * FROM user
WHERE id NOT IN (SELECT user_id FROM blacklist);
-- 一条都查不出来！因为 blacklist.user_id 里有 NULL
为什么是坑：NOT IN (..., NULL, ...) 等价于 id != a AND id != NULL AND ...，而 id != NULL 永远是 NULL（非真），整个 WHERE 结果恒假，查不到任何行。这个坑极隐蔽——SQL 看着对，结果却是空的。
正确做法：
-- 子查询里过滤掉 NULL
SELECT * FROM user
WHERE id NOT IN (SELECT user_id FROM blacklist WHERE user_id IS NOT NULL);
-- 更稳的写法：用 NOT EXISTS
SELECT * FROM user u
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = u.id);
加分点：NOT IN 对子查询里的 NULL 极其敏感，是面试高频坑。遇到 NOT IN 先想"子查询里有没有 NULL"，或者干脆用 NOT EXISTS 避开。
坑 5：分页查询不带 ORDER BY
场景 / 错误示范：
-- 分页只写 LIMIT，不写 ORDER BY
SELECT * FROM t LIMIT 20 OFFSET 40;
-- 两次查同页，结果可能不一样！因为没排序，返回顺序不保证
为什么是坑：不指定 ORDER BY，MySQL 返回行的顺序是不保证的（受存储、并行查询影响）。翻页时可能出现"上一页见过的行又出现在下一页"或"某行直接消失"，前端用户看到的数据时对时错。这是分页功能不稳定的隐形 bug。
正确做法：
-- 分页必须带 ORDER BY，且排序列唯一稳定
SELECT * FROM t ORDER BY id LIMIT 20 OFFSET 40;
-- id 唯一，每次翻页顺序确定，不会重复不会漏
加分点：排序列最好是主键或唯一键，否则 ORDER BY 一个有重复值的列，相同值之间顺序仍不确定，最好再加个唯一列做次级排序 ORDER BY status, id。
坑 6：写主库立刻去从库读
场景 / 错误示范：
# 注册后立刻跳转到"个人中心"，但个人中心走的是从库
cur.execute("INSERT INTO user ...") # 写主库
profile_query = "SELECT * FROM user WHERE id=?"
requests.get("...") # 读从库
# 从库还没同步到这条新数据，查出来是空，用户看到空白页
为什么是坑：主从复制有延迟（通常毫秒到秒级）。刚写完主库马上读从库，大概率读不到刚写的数据。这是读写分离架构最经典的坑——"刚写就读不到"。用户体感就是"我刚注册怎么没账号"。
正确做法：
# 方案一：写操作后的"紧接着的读"，强制走主库（写后读主）
# 应用层做"会话一致性"：同一请求里写过的数据，后续读都走主库
# 方案二：业务上容忍短暂延迟，前端跳转前稍等或重试
# INSERT 后先提示成功，刷新时若从库还没同步，做一次重试
# 方案三：监控主从延迟，延迟大时切读主库
SHOW REPLICA STATUS\G -- 看 Seconds_Behind_Source
加分点："写后读"走主库是读写分离的标准策略。把需要强一致的读（如支付后查余额）都路由到主库，其他读才走从库。
坑 7：大表上 COUNT(*) 硬数
场景 / 错误示范：
-- 产品要"这个表一共多少条"，直接 COUNT(*)
SELECT COUNT(*) FROM huge_order_t;
-- 表里 5 亿行，数了半天还把内存吃了
为什么是坑：InnoDB 的 COUNT(*) 要数真实行数（没有像 MyISAM 那样存好精确值），大表上全表数一遍极慢，还可能堵住其他查询。而且很多业务根本不需要精确值，"大概多少万"就够了，硬数精确值代价巨大。
正确做法：
-- 不需要精确：用 SHOW TABLE STATUS 的估算值
SHOW TABLE STATUS LIKE 'huge_order_t'; -- Rows 列是估算值，毫秒返回
-- 需要精确计数：用二级索引（比聚簇索引小）
SELECT COUNT(*) FROM huge_order_t; -- InnoDB 会选最小的二级索引来数
-- 更聪明：计数异步维护一张汇总表，写入时更新
加分点："总数多少"这类需求要先问产品"要多精确"。近似值用估算，精确值用汇总表，别在大表上直接 COUNT。
坑 8：INSERT 重复键直接报错
场景 / 错误示范：
-- 用户重复提交，同一唯一键插两次
INSERT INTO user(phone, name) VALUES ('13800001111', '张三');
INSERT INTO user(phone, name) VALUES ('13800001111', '张三');
-- 第二条：ERROR 1062 Duplicate entry
为什么是坑：并发或重试场景下，重复插入直接抛异常，用户体验差。直接报错然后让用户重试，没有利用数据库"唯一约束兜底 + 优雅处理"的能力。
正确做法：
-- 用 INSERT ... ON DUPLICATE KEY UPDATE，重复时转为更新
INSERT INTO user(phone, name) VALUES ('13800001111', '张三')
ON DUPLICATE KEY UPDATE name = VALUES(name);
-- 或忽略重复
INSERT IGNORE INTO user(phone, name) VALUES ('13800001111', '张三');
加分点：ON DUPLICATE KEY UPDATE 适合"不存在就插入、存在就更新"的 upsert 语义；INSERT IGNORE 适合"有就跳过"。别让唯一键冲突冒泡成 500。
坑 9：字符串列存数字排序错乱
场景 / 错误示范：
-- 版本号用 VARCHAR 存
CREATE TABLE t(ver VARCHAR(20));
INSERT INTO t VALUES ('1'), ('2'), ('10');
SELECT ver FROM t ORDER BY ver;
-- 结果：1, 10, 2（字符串字典序！不是数字序）
为什么是坑：字符串排序按字典序，"10" 排在 "2" 前面（因为 '1' < '2'）。把版本号、编号这类本质是数字的东西存进 VARCHAR 再排序，结果完全反直觉。这是"数据类型选错"在查询侧的体现。
正确做法：
-- 本质是数字的，用数字类型存
ALTER TABLE t MODIFY ver INT;
SELECT ver FROM t ORDER BY ver; -- 1, 2, 10 正确
-- 版本号这种含小数点的（1.10、2.0），拆成多个数字列或约定零填充
-- 如存 '0001.0010' 再按字符串排，字典序就和版本序一致
加分点：建表时想清楚这列"是给人读的文本，还是要参与排序/计算的数字"。是数字就用数字类型，别图省事用 VARCHAR。
坑 10：WHERE 里堆复杂 OR 嵌套
场景 / 错误示范：
-- 一个 WHERE 里堆了十几个 OR/AND 嵌套
SELECT * FROM t
WHERE (a=1 AND b=2) OR (a=3 AND b=4) OR (a=5 AND b=6) OR ...;
-- 优化器难以利用索引，往往退化成全表扫
为什么是坑：多层 OR 组合让优化器很难选索引。每个 OR 分支都要能走索引才行，否则整体退化。这种 SQL 通常是"业务条件动态拼出来的"，拼得越复杂，优化器越懵。
正确做法：
-- 能用 IN 就用 IN
SELECT * FROM t WHERE (a,b) IN ((1,2),(3,4),(5,6));
-- 或拆成 UNION，每个分支各自走索引
SELECT * FROM t WHERE a=1 AND b=2
UNION
SELECT * FROM t WHERE a=3 AND b=4
UNION
SELECT * FROM t WHERE a=5 AND b=6;
加分点：动态拼 SQL 时要有意识控制 WHERE 复杂度，别把十几个 OR 全塞一个查询里。必要时拆 UNION 让每个分支走自己的索引。
坑 11：在 WHERE 里用函数拼条件
场景 / 错误示范：
-- 想查"今天注册的用户"，写成
SELECT * FROM user WHERE DATEDIFF(NOW(), create_time) = 0;
-- create_time 包了函数，索引失效（和第44章坑1同源）
为什么是坑：和上一章"索引列包函数"是同一个问题，这里强调它在"业务常用查询"里反复出现。很多人写"今天、本月"统计时习惯性在列上套函数，把索引搞失效。
正确做法：
-- 改成范围查询，列保持裸着
SELECT * FROM user
WHERE create_time >= CURDATE()
AND create_time < CURDATE() + INTERVAL 1 DAY;
-- 走 create_time 索引
加分点："今天/本月/近 N 天"这类时间条件，一律转成 >= 和 < 的半开区间，别在列上套 DATEDIFF/DATE/YEAR。
坑 12：以为 LIMIT 能防止 UPDATE 全表
场景 / 错误示范：
-- 怕删太多，加个 LIMIT 安慰自己
DELETE FROM order_t WHERE status = 0 LIMIT 1000;
-- 但没 ORDER BY，删的是哪 1000 行不确定，业务可能删错
为什么是坑：DELETE ... LIMIT 不带 ORDER BY 时，删哪些行由存储决定，不可控。开发者以为 LIMIT 兜住了安全，其实删的范围是随机的，可能把不该删的删了。LIMIT 在这里是"批量分批"手段，不是"安全保证"。
正确做法：
-- 分批删除要可控：带明确条件和顺序
DELETE FROM order_t
WHERE status = 0 AND id < 100000 -- 明确范围
ORDER BY id LIMIT 1000;
-- 一批批处理，每批之间确认，直到删完
-- 更稳：先 SELECT 出要删的 id，确认后再按 id 删
加分点：分批 DELETE/UPDATE 是清理大表的常用手段，但每批都要有明确的、可预测的 WHERE 和 ORDER BY，别靠 LIMIT 随机删。
速查表：写法安全速记
| 写法 | 风险 | 正确做法 |
|---|---|---|
| UPDATE/DELETE 无 WHERE | 全表事故 | 先 SELECT 确认，事务里 ROW_COUNT 核对 |
| 循环单条 UPDATE | 慢、锁多 | 批量 IN/CASE，每批 500~1000 |
| 空串/NULL 混用 | 查询漏行 | 统一 NOT NULL DEFAULT '' |
| NOT IN 含 NULL | 恒空 | 子查询过滤 NULL 或用 NOT EXISTS |
| 分页无 ORDER BY | 结果不稳定 | ORDER BY 唯一列 |
| 写主库读从库 | 读不到新数据 | 写后读走主库 |
| 大表 COUNT(*) | 慢 | SHOW TABLE STATUS 估算 |
| INSERT 重复键 | 报错 | ON DUPLICATE KEY UPDATE |
| VARCHAR 存数字排序 | 字典序错 | 用数字类型 |
| 复杂 OR 嵌套 | 全表扫 | IN 或 UNION |
| 时间列套函数 | 索引失效 | 半开区间 |
| DELETE LIMIT 无 ORDER | 删错行 | 带明确范围和顺序 |
注意事项：写操作安全 Checklist
任何 UPDATE/DELETE 上线前，过一遍这张清单：
- [ ] WHERE 条件写了吗？是不是预期范围？
- [ ] 先 SELECT 过影响范围吗？大概多少行？
- [ ] 在事务里执行、ROW_COUNT 核对过吗？
- [ ] 大表写操作分批了吗？避开业务高峰了吗？
- [ ] 开启了
sql_safe_updates之类的保护吗？ - [ ] 操作前备份过了吗？
一句话：读错了重查就行，写错了要靠备份恢复。写操作的谨慎程度要十倍于读操作。
自测：你能避坑吗
题 1：WHERE id NOT IN (SELECT uid FROM b) 一条都查不出来，可能的原因？
<details>
<summary>参考答案</summary>
子查询结果里有 NULL。NOT IN (..., NULL) 等价于 id != ... AND id != NULL，后者恒为 NULL（非真），整个条件恒假。要么子查询加 WHERE uid IS NOT NULL，要么改用 NOT EXISTS。
</details>
题 2：分页 SQL LIMIT 20 OFFSET 40 不带 ORDER BY 有什么问题？
<details>
<summary>参考答案</summary>
不指定排序，返回行的顺序不保证，翻页时可能重复或漏行。必须 ORDER BY 一个唯一稳定的列（如主键 id），保证每次翻页顺序确定。
</details>
题 3：用户刚写完主库，立刻读从库却查不到，为什么？
<details>
<summary>参考答案</summary>
主从复制有延迟，从库还没同步到刚写的数据。这是读写分离的经典坑。"写后读"应路由到主库，或监控主从延迟做降级。
</details>
题 4：批量更新 1 万行，为什么不要在应用里循环单条 UPDATE？
<details>
<summary>参考答案</summary>
每条一次网络往返+一次事务提交，1 万次 round-trip 慢且锁多。应改成 UPDATE ... WHERE id IN (...) 批量写，或每批 500~1000 分批，控制单事务大小。
</details>
题 5：UPDATE user SET password='x' 忘了 WHERE，怎么在制度上防住？
<details>
<summary>参考答案</summary>
开启 sql_safe_updates，让没有 WHERE 或 LIMIT 的 UPDATE/DELETE 直接报错；操作在事务里做、ROW_COUNT 核对行数再 COMMIT；先 SELECT 确认影响范围；高危库只给应用账号最小权限。
</details>
题 6：VARCHAR 列存版本号 '1','2','10'，ORDER BY 结果是什么？怎么修？
<details>
<summary>参考答案</summary>
按字符串字典序排成 1,10,2，因为 '1'<'2'。本质是数字的列应用数字类型存；版本号这种带小数点的，拆数字列或零填充后再排序。
</details>
题 7：大表 SELECT COUNT(*) 很慢，但产品只是要"大概多少条"，怎么办？
<details>
<summary>参考答案</summary>
不需要精确值就别硬数，用 SHOW TABLE STATUS 的 Rows 估算值（毫秒返回）；真要精确计数，InnoDB 会选最小二级索引来数；高频计数维护一张异步汇总表，写入时累加。
</details>
题 8：为什么 DELETE FROM t WHERE status=0 LIMIT 1000（不带 ORDER BY）可能删错行？
<details>
<summary>参考答案</summary>
不带 ORDER BY 时，删哪 1000 行由存储决定、不可预测，可能把不该删的删了。分批删除要带明确 WHERE 范围和 ORDER BY，每批可控可核对，别让 LIMIT 随机决定删谁。
</details>
题 9：业务要"今天注册了多少用户"，写成 WHERE DATEDIFF(NOW(),create_time)=0 有什么问题？
<details>
<summary>参考答案</summary>
create_time 上套了 DATEDIFF 函数，索引失效，全表扫。应改写成半开区间：create_time >= CURDATE() AND create_time < CURDATE()+INTERVAL 1 DAY，让列保持裸着走索引。
</details>
题 10：空字符串 '' 和 NULL 在查询时有什么区别？建表时怎么统一？
<details>
<summary>参考答案</summary>
'' 是"有值但为空"，NULL 是"未知/没值"，二者比较行为不同（!= '' 会漏掉 NULL 行）。建表时统一 NOT NULL DEFAULT ''，让业务上"没值"都存空串，查询行为就一致了。
</details>
下一篇预告：第 48 章 聚合与分组踩坑
文章回复
0 条公开回复