企业官网建设流程全解析

CRUD 这四个字母，几乎所有写业务代码的人都认识，翻译过来就是数据库的增删改查。我带过一个转行的新人，前端的底子，SELECT 用得比谁都溜，但真让他往表里插一条数据、改一条数据、删一条数据，反而犹豫半天，问我说：“这三个操作不是特别简单吗，为什么要单独学？”当时我就笑了。INSERT、UPDATE、DELETE 看起来确实是三句话的事，但真到了生产环境，这三个操作背后全是细节——漏写 WHERE 可以瞬间清空一张表，更新字段时忽略类型转换会让整列索引失效，批量插入时一条 SQL 没写好能直接打爆 binlog。这篇文章就把 MySQL 的 DML 语言讲透，围绕增、删、改这三类核心操作，拆解语法、原理、实操和安全边界。不管你是刚学会 SELECT 的萌新，还是写了两年代码但从来没系统整理过 DML 细节的开发者，这篇内容应该都能帮你补上那些容易被忽略的知识点。

1. 先搞懂 DML 在 SQL 体系里的地盘，后面才不迷糊

1.1 DML 和 DDL、DQL 的分工

SQL 语句按功能可以分成几个大类：DDL 负责定义数据结构，比如 CREATE TABLE、ALTER TABLE、DROP TABLE；DCL 负责权限和用户控制，比如 GRANT、REVOKE；DQL 负责查询数据，也就是 SELECT；而 DML，全称 Data Manipulation Language，数据操纵语言，负责对表里的数据本身做增删改。说白了，DDL 是盖房子、改户型，DML 是往房子里搬家具、挪家具、丢家具，DCL 是分配钥匙。

这个界限有个小坑要注意。有些教材把 SELECT 也归进 DML，因为查询也是在“操纵”数据；但 MySQL 官方文档更多把 SELECT 单列为 DQL。在实际面试或写文档时，如果被问到 DML 包含哪几条命令，我更建议你回答：INSERT、UPDATE、DELETE 是 DML 的核心三件套。SELECT 单独说成查询语言更清晰，也符合现在绝大多数资料的主流划分。

1.2 增删改的统一底层逻辑：先定位，再操作

DML 的三个操作，背后有一个统一的底层逻辑，我用一句话就可以说清楚：先定位到要操作的行，再对行执行动作。INSERT 是从外部准备一行新数据，定位到表末尾或指定位置塞进去；UPDATE 是先按 WHERE 条件找到目标行，再修改字段值；DELETE 是先按条件找到目标行，再整体移除。

别小看这个逻辑。它意味着两件事：第一，WHERE 条件的质量决定了操作的影响范围；第二，MySQL 在执行 UPDATE 和 DELETE 时，本质上是“先查后改”，它是先把满足条件的行读出来，再逐步处理。所以，索引对 DML 性能的影响和对 SELECT 一样关键——无索引的 UPDATE 和 DELETE 在大表上会非常慢，而且会锁住大量行，这一点我们在后面还会反复提到。

1.3 学习 DML 前要建立的三个习惯

结合我自己的实战经验，再补三个学习 DML 之前最好就建立起来的习惯。

第一个习惯：执行任何 UPDATE 和 DELETE 之前，先把同样的 WHERE 条件拿去跑一遍 SELECT。复制一下条件，把 UPDATE 换成 SELECT，看看到底会命中哪些行。这个动作看起来浪费时间，但能避免 90% 以上的误操作。生产环境里删错数据的教训，基本都是省略了这个步骤才发生的。

第二个习惯：永远给 UPDATE 和 DELETE 写 WHERE 条件，哪怕你的需求是全表操作。如果你真的需要更新全表，可以写成UPDATE table SET ... WHERE 1=1，或者带上明确的注释，让审查的人一眼就知道这是有意为之。最怕的就是漏写 WHERE，然后一脸无辜地说“我不是故意的”。数据库不会管你是不是故意的。

第三个习惯：把表结构、索引情况、数据量级放在一起考虑。一条 SQL 在 1 万行的表上跑没问题，到 1 亿行的表上可能就是灾难。DML 不只是语法问题，更是一个性能问题。带着这三个习惯去学后面的内容，你会学得更快。

2. INSERT 插入数据：五种写法与一组实操细节

2.1 标准 INSERT 语法：列名与值的对齐逻辑

INSERT 最基础的写法长这样：

INSERT INTO product(id, name, stock, sold_count) VALUES(10, 'iPhone 15', 100, 0);

执行之后，MySQL 会检查表结构，把 VALUES 里的值按顺序对应到前面写的列名上。列名和值必须一一对应，数量要对得上，类型要能匹配——比如你把stock传成字符串'abc'，MySQL 会尝试把字符串转成数字，转不过去就直接报错。

列名可以省略不写，这时候 MySQL 要求你按表结构的字段顺序把所有列的值都补齐。但这个写法非常脆弱，只要表结构一变，比如中间加了一列，你的 SQL 就会错位。所以我在实际项目中几乎从不省略列名，哪怕多敲几个字，也要保证字段名显式写清楚。这也是团队协作时给别人省时间的做法。

还有一条老语法也值得知道：

INSERT INTO product SET name = 'iPhone 15', stock = 100, sold_count = 0;

这种 INSERT ... SET 写法在 MySQL 里合法，适合临时在命令行手动插数据时用，可读性好，但迁移到别的数据库时兼容性差。标准写法还是 INSERT INTO ... VALUES。

2.2 批量插入不只是省事，也省性能

单条插入一条一条执行，性能很差。INSERT 支持一次插入多行，写法非常直观：

INSERT INTO product(name, stock, sold_count) VALUES ('iPhone 15', 100, 0), ('iPhone 15 Pro', 150, 0), ('iPad Air', 200, 0);

每一组值之间用逗号分隔，MySQL 会把它们当成一条多行 INSERT 一次性执行。相比逐条 INSERT，这种方式减少了客户机和服务器之间的网络往返，也减少了 SQL 语句解析的次数。在 InnoDB 引擎下，批量插入还能减少日志写入的开销。我在初始化数据或写测试数据的时候，经常一次拼几千行，执行时间基本在半秒内。

但批量插入并不是越大越好。如果一条 INSERT 语句的体积超过了 MySQL 的max_allowed_packet参数限制，就会直接报错，常见错误是 “Packet too large”。这个参数默认值可能是 4MB 或 64MB，取决于你的 MySQL 版本和配置文件。真要导入几万行数据，更推荐分批插入，比如每次 5000 行，既不会触发包大小限制，也方便在出错时定位是哪一批数据出了问题。

除了手写多行 VALUES，还有一种常用的数据导入方式：

INSERT INTO product(name, stock, sold_count) SELECT name, stock, 0 FROM temp_product WHERE stock > 0;

这就是 INSERT ... SELECT，可以把另一张表或子查询的结果直接插入目标表。常用于临时表转正式表、数据迁移、报表汇总等场景。它同样支持批量插入的逻辑，而且省去了中间再导一遍的手工操作。

2.3 INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 的适用场景

实际开发里，光会用基础 INSERT 远远不够，因为经常会遇到“数据可能已经存在，存在就更新，不存在就插入”的需求。MySQL 为此提供了两个非常实用的扩展能力。

第一个是INSERT IGNORE：

INSERT IGNORE INTO user(id, name, email) VALUES(1, '张三', 'zhangsan@example.com');

执行时，如果因为主键或唯一键冲突导致插入失败，IGNORE 会忽略掉这条错误，让 SQL 正常结束，只是受影响行数会变成 0。它很适合做幂等写入，比如定时任务往统计表里灌数据，重复执行不会因为唯一键冲突而中断任务。

第二个是ON DUPLICATE KEY UPDATE，这才是真的大杀器：

INSERT INTO user_points(user_id, points) VALUES(1, 10) ON DUPLICATE KEY UPDATE points = points + 10;

它的含义是：如果插入时发生主键或唯一键冲突，则改为执行 UPDATE。上面这个例子就是典型的积分累加场景——用户第一次产生积分时插入一行，以后每次加积分就让点数在原值基础上累加。这种写法比“先 SELECT 判断有无，再决定 INSERT 还是 UPDATE”效率高很多，最重要的是避免了并发竞争：两步操作之间如果有其他请求插入或更新了同一条记录，你的判断就失效了，而 ON DUPLICATE KEY UPDATE 在数据库内部是一个原子操作。

还有一张牌叫REPLACE INTO：

REPLACE INTO user(id, name) VALUES(1, '李四');

REPLACE 遇到冲突时会先把现有行 DELETE 掉，再插入新行。副作用非常明显：自增 ID 会变化，外键关联会被破坏，如果表里还有其他字段未提供，会被默认值覆盖或置空。所以我对 REPLACE 的态度很明确：除非你确认这个表就是用来做“整体覆盖”的，否则尽量别用，ON DUPLICATE KEY UPDATE 几乎总是更好的选择。

2.4 插入数据时容易被忽略的细节：默认值、自增列与字符集

最后说一下 INSERT 使用中容易翻车的几个细节。

第一，自增列可以直接省略不写，也可以显式写成 NULL，MySQL 会自动生成下一个自增值。你甚至可以手动指定一个大的 ID，比如id = 1000，插入成功后，后续自增会从 1000 之后继续。这个特性可以用来做数据搬迁时保留原 ID，但要注意，如果手动指定的 ID 已经存在，会触发主键冲突。

第二，关于默认值。表结构里带有 DEFAULT 的列，插入时可以不写。比如建表时设置了created_at DATETIME DEFAULT CURRENT_TIMESTAMP，插入时只要你没写这个字段，系统就会自动填当前时间。但如果列是NOT NULL且没有默认值，你又没写它，MySQL 就会报错，最经典的就是 “Field 'xxx' doesn't have a default value”。生产环境里，这种错误经常出现在新增字段之后，老 SQL 没同步更新，插入时才发现新字段没值、没默认值、又不允许为空。

第三，字符集问题。MySQL 5.7 以上虽然默认字符集很多是 utf8mb4，但老库很可能还是 utf8。utf8 在 MySQL 里其实最多支持 3 个字节，存不了 emoji 表情这种 4 字节字符。如果你插入一条带 emoji 的数据，报错信息会是这样：Incorrect string value: '\xF0\x9F\x98\x80' for column 'name'。解决办法是把表和字段的字符集改成 utf8mb4。这个问题在后面的常见问题章节还会再展开，因为太多人踩过了。

3. UPDATE 更新数据：写对 WHERE 之前，先问自己三个问题

3.1 UPDATE 执行原理与受影响行数的坑

UPDATE 的基础语法：

UPDATE product SET stock = 99 WHERE id = 1;

它的执行逻辑是先根据 WHERE 条件找到所有匹配的行，再逐行修改 SET 后面指定的字段。如果在 InnoDB 引擎下，找到的行在修改前会被加上行锁，这也是为什么 UPDATE 在并发环境下会影响其他事务读取同一行数据。

这里有一个非常容易让新手困惑的现象：执行 UPDATE 后返回的受影响行数为 0，不等于没执行成功。MySQL 有一个默认行为：如果被更新的字段值和原值完全一样，它就不做实际修改，受影响行数计为 0。所以如果你执行UPDATE product SET stock = 100 WHERE id = 1，而这条记录的 stock 本来就是 100，返回值就是0 rows affected。这并不代表出错了，只是说明没有产生变化。在 MySQL 命令行客户端里，你可以通过额外的提示看到Rows matched: 1 Changed: 0 Warnings: 0，matched 表示条件命中了多少行，changed 表示实际修改了多少行，这才是更有价值的诊断信息。

另一个容易忽略的坑是：SET 里字段的赋值顺序会影响结果。MySQL 的 UPDATE 是按从左到右的顺序执行赋值的。比如：

UPDATE product SET stock = sold_count, sold_count = stock;

这条语句执行后，stock 会被赋成旧的 sold_count，然后 sold_count 会被赋成旧的 stock，两个字段完成了交换。因为第二句的 stock 已经是新值了，但第一句已经执行完，所以结果正好实现交换。这个行为在大多数数据库里并不统一，如果你写跨库代码，最好不要依赖这个特性，但至少要知道 MySQL 确实是这样执行的。

3.2 裸 UPDATE 的风险与安全写法

不带 WHERE 的 UPDATE 会更新全表所有行：

UPDATE product SET stock = 0;

如果这张表是线上商品表，这条语句一夜之间就能把所有商品的库存清零。这不是夸张——我见过不止一次，因为手滑漏写了 WHERE，导致整张表被更新，接着就是各种客诉和日志排查。所以裸 UPDATE 必须当成高危操作来对待。

防止裸 UPDATE 最有效的手段是利用 MySQL 的安全更新模式sql_safe_updates。开启后，不带 WHERE 或 WHERE 条件不包含索引列的 UPDATE 和 DELETE 会被 MySQL 直接拒绝执行。比如：

SET sql_safe_updates = 1;

我这里建议 DBA 给线上的只读账号、运维账号都开一下这个选项，尤其是在夜间任务、批量脚本这类容易出现误操作的场景。平时我自己写自动化脚本，都会在连接串或会话开头显式设置sql_safe_updates=1，防止脚本逻辑写错时一口气把整张表干掉。

如果不方便全局开启，还有一个笨但实用的办法：先 SELECT 确认影响行数。比如你要更新一批用户状态，先跑：

SELECT id, status FROM users WHERE last_login_at < '2023-01-01';

确认返回的行数符合预期，再把同样的 WHERE 复制到 UPDATE 语句里。数据量大时，这样多花几秒，但能挡住最危险的错误。

3.3 多表关联更新与分批次更新

实际业务中，数据不会只待在一张表里，经常需要根据另一张表的条件来更新当前表。MySQL 支持多表关联更新，语法是 UPDATE ... JOIN：

UPDATE orders o JOIN users u ON o.user_id = u.id SET o.user_name = u.name WHERE u.status = 'vip';

这条语句的含义是：把 orders 表与 users 表按 user_id 关联起来，只更新满足u.status = 'vip'的那些订单，把订单中的 user_name 字段更新为 users 表中对应的 name。这比先 SELECT 查出结果再逐条 UPDATE 效率高得多，而且天然是原子操作，不会出现“查出用户名字后用户改了名”的中间状态。

还有一个 MySQL 特有的技巧：UPDATE 支持 ORDER BY 和 LIMIT。比如：

UPDATE product SET stock = stock - 1 WHERE stock > 0 ORDER BY id LIMIT 10;

这条语句只会更新排序后最前面的 10 行，常用于限量抢购、任务队列等场景。但要注意它的语义非常具体，更新哪几行由 ORDER BY 决定，如果你不排序就加 LIMIT，更新的行是不确定的。这个特性在平时用得不多，但在特定的业务场景下很顺手。

分批更新的场景更常见。比如你有一张千万级的表，要统一更新其中一半的数据，一条 UPDATE 如果一次更新百万行，会把大量行锁住很长时间，主从复制也会因为 binlog 太大而受到影响。正确做法是分批次更新，比如每批 5000 行：

UPDATE product SET price = price * 0.9 WHERE id BETWEEN 1 AND 5000 AND update_flag = 0; UPDATE product SET price = price * 0.9 WHERE id BETWEEN 5001 AND 10000 AND update_flag = 0;

或者是利用自增 ID 的范围不断推进，每批执行完稍微停顿一下再继续下一批。这个思路对 DELETE 同样适用，后面会再讲。

3.4 并发更新：从 count += 1 到乐观锁

先看一个经典的并发更新场景：商品库存扣减。如果代码里写成：

UPDATE product SET stock = stock - 1 WHERE id = 1;

在 MySQL 中，这个stock = stock - 1是原子操作。假设有两个请求同时要买同一件商品，A 请求执行时拿到 stock=10，写回 9；B 请求再执行时拿到的是 9，写回 8。结果正确，不会出现两个请求都扣成 9 的情况。因为 InnoDB 的行锁保证了对同一行的更新操作是串行化的。

但如果你的代码逻辑是先查出 stock 的值，再在应用层计算新的值，最后再 UPDATE 回去：

-- 应用层执行： SELECT stock FROM product WHERE id = 1; -- 得到 10 -- 应用层计算：10 - 1 = 9 UPDATE product SET stock = 9 WHERE id = 1; -- 有问题

两个请求同时查出 10，同时算出 9，同时写回，库存就只扣了 1，却卖出了 2 件商品。这就是经典的“丢失更新”问题。解决办法有很多，最常用的一种是乐观锁：给表加一个 version 字段，更新时校验版本号：

UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 10;

受影响行数为 0 时，说明 version 已经被别人改过了，需要重新读取数据再重试。这种写法在秒杀、抢购、订单扣减场景里非常常见。核心原则就一句话：能用一个原子 UPDATE 解决的并发问题，不要拆成 SELECT + 计算 + UPDATE 三步。

4. DELETE 删除数据：清空、回滚与批删，每步都是选择

4.1 DELETE 基础语法与清空陷阱

DELETE 的语法和 UPDATE 非常像：

DELETE FROM product WHERE id = 100;

如果不带 WHERE，整张表的数据都会被清空：

DELETE FROM product;

这是最危险的 SQL 之一。很多误删事故就是这么来的：写 SQL 的人本来想删一条记录，结果漏了 WHERE，再回神的时候整张表已经空空如也。你在网上搜“MySQL 删错数据”，能看到的真实案例一大把。所以 DELETE 的安全习惯和 UPDATE 完全一样：先 SELECT 验证、再执行 DELETE、生产环境开启 sql_safe_updates。

另一个跟“清空”有关的细节：DELETE 清空表后，表的自增计数器并不会重置。也就是说，你删掉了所有数据，再插入新数据时，ID 会接着之前的最大值继续增加，而不是从 1 开始。这一点在测试环境里经常让人困惑，明明表空了，新插入的数据 ID 却是 101。原因很简单：DELETE 是逐行删除数据，它不碰表结构和自增计数器。如果你希望自增从头开始，就需要用到下面要说的 TRUNCATE。

4.2 TRUNCATE 和 DELETE 到底怎么选

删除整张表的数据，除了 DELETE，还有一条常用语句：

TRUNCATE TABLE product;

两者有很多区别，我用表格列一下：

从这张表能得出很多实战结论。如果你只想清空一张表且不需要回滚，TRUNCATE 是更快的选择；如果你需要按条件删除部分数据，只能用 DELETE；如果你在事务中先 DELETE 再发现问题想回滚，只要还没 COMMIT，是可以靠 ROLLBACK 把数据找回来的。但 TRUNCATE 是 DDL，执行时会隐式提交，即使包裹在事务里也极难回滚，这一点要特别当心。

还有一个容易忽略的差别：DELETE 每一行都会走完整的删除流程，触发删除触发器、逐条写 binlog，所以大表 DELETE 超级慢；TRUNCATE 直接从表空间层面释放数据页，速度飞快。所以“清空全表”这个需求，能用 TRUNCATE 就不要用 DELETE。

4.3 多表删除与重复数据清理实战

DELETE 除了单表删除，也支持多表删除。MySQL 的语法有两种写法，效果类似：

DELETE o FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'banned';

这条语句会删除 orders 表中 user 被 ban 掉的所有订单。还有一种写法：

DELETE FROM o USING orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'banned';

两种写法都可行，第一种更好读。实际业务里，做订单清理、日志归档、下线用户数据时很常用。

再说一个删除重复数据的经典案例。假设 user 表里因为之前导入数据时没有唯一键，出现了同邮箱的多条记录，现在要保留每个邮箱的最小 ID，删除其他重复行。在 MySQL 中可以这样写：

DELETE t1 FROM users t1 JOIN users t2 ON t1.email = t2.email AND t1.id > t2.id;

这条 SQL 的语义要好好理解一下：把 users 表自己和自己关联，只要找到同一邮箱下 ID 更大的记录，就把它删掉。因为同一邮箱的重复记录中，ID 最小的那条永远不会是“被删方”，所以最后保留的就是每组里的最小 ID。这个操作在数据清洗、去重、修复脏数据时几乎天天要用。执行前务必先跑一遍同样的 SELECT 查一下要删哪些行，别一上来就删。

4.4 大批量删除如何避免生产事故

最后说大批量删除，这是 DBA 和运维同学最关注的话题。一条 SQL 删几百万行数据，会带来三个问题：第一，长时间持有大量行锁，影响线上正常读写；第二，binlog 会变得很大，给主从复制增加延迟；第三，Undo Log 膨胀，可能导致磁盘爆掉。

解决思路是分批删除。常见做法是循环执行带 LIMIT 的 DELETE：

DELETE FROM order_log WHERE created_at < '2024-01-01' LIMIT 1000;

多次执行，每次只删 1000 行，直到受影响行数为 0，表示数据已经删完。这种方式每条 DELETE 的锁范围都极小，主从延迟可控，也不会瞬间产生超大 binlog。如果删除的是核心业务表，中间还要加一点 sleep，甚至在低峰期执行。

另外，删数据之前，备份永远是第一位的。哪怕你对自己的 SQL 再有信心，也要先导出数据。常见的做法是：

CREATE TABLE order_log_backup_20240101 AS SELECT * FROM order_log WHERE created_at < '2024-01-01';

或者用 mysqldump 导出对应的数据文件。如果担心备份太大、太慢，至少要在删数据前确认有近期的全量备份和 binlog 可追溯。删除是不可逆的，一旦误删，成本极高。

5. 上手实操：从建表到完整增删改，一次做完

5.1 建表与初始数据写入

前面的章节拆了很多语法，这节把东西串起来，用一个最典型的电商商品表来做一次完整演练。先建表：

CREATE TABLE `product` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '商品ID', `name` varchar(64) NOT NULL COMMENT '商品名称', `stock` int NOT NULL DEFAULT 0 COMMENT '库存', `sold_count` int NOT NULL DEFAULT 0 COMMENT '已售数量', `version` int NOT NULL DEFAULT 0 COMMENT '乐观锁版本号', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

这张表的几个设计细节值得说一下：INCENT 自增列做主键；stock 和 sold_count 都设了默认值 0，插入时可以省略；version 字段给并发更新预留；created_at 和 updated_at 是标准的时间戳设计，更新记录时ON UPDATE CURRENT_TIMESTAMP会自动刷新时间。

接下来插入几条初始数据：

INSERT INTO product(name, stock, sold_count) VALUES ('iPhone 15', 100, 0), ('iPhone 15 Pro', 120, 0), ('iPad Mini', 200, 0);

执行完成后可以用 SELECT 验证一下：

SELECT * FROM product;

一切正常。注意我们插入时没有写 id、version、created_at、updated_at，都交给了数据库自动处理。这就是默认值和自增列在工作。

5.2 商品库存扣减的 UPDATE 与事务控制

现在来模拟一个真实的下单流程。用户购买一个 iPhone 15（id=1），需要做两件事：扣减库存、增加已售数量。最基本的一条 UPDATE：

UPDATE product SET stock = stock - 1, sold_count = sold_count + 1 WHERE id = 1;

这就是前面讲过的原子更新，直接对字段做数学运算，不经过应用层计算，并发安全。但在秒杀场景下，还要防止库存被扣成负数。可以加一个库存条件：

UPDATE product SET stock = stock - 1, sold_count = sold_count + 1 WHERE id = 1 AND stock > 0;

如果受影响行数为 0，说明商品已经没库存了，直接返回“已售罄”。这种写法在真实秒杀系统里很常见，一条 SQL 同时完成了校验和扣减。

但如果下单流程还要写订单表，单独一条 UPDATE 就不够了，因为“扣库存”和“写订单”必须同时成功或同时失败。这时要引入事务：

START TRANSACTION; UPDATE product SET stock = stock - 1, sold_count = sold_count + 1 WHERE id = 1 AND stock > 0; -- 影响行数为 0 则抛异常并回滚 INSERT INTO order_log(product_id, qty, created_at) VALUES(1, 1, NOW()); COMMIT;

如果在执行 INSERT 时出错，或者业务代码里检测到扣减失败，就执行ROLLBACK，库存扣减和订单写入会一起撤销。事务是 DML 操作最重要的配套机制，它保证了多条增删改语句的一致性。没有事务的情况下，扣了库存却写不了订单，商城就要出大事了。

再升级一步，如果希望在扣减时同时防止并发问题，可以加乐观锁：

UPDATE product SET stock = stock - 1, sold_count = sold_count + 1, version = version + 1 WHERE id = 1 AND version = 0;

version 在并发更新时被其他事务改掉，本条 UPDATE 就会成功执行 0 行，需要重试。这与前面讲过的内容是一样的套路。

5.3 订单清理与数据归档中的 DELETE

数据总有要清理的一天。假设电商平台决定删除三个月前的测试订单，可以这样操作：

DELETE FROM order_log WHERE created_at < '2024-01-01';

如果你不确定会删多少行，先跑 SELECT 看一眼：

SELECT COUNT(*) FROM order_log WHERE created_at < '2024-01-01';

数据量大时，改成带 LIMIT 的循环删除。这一段就是前面讲过的“分批删除”思路，放到真实项目里，可以用存储过程或定时任务去做。存储过程的写法如下（细节可以有版本差异）：

DELIMITER $$ CREATE PROCEDURE batch_delete_old_orders() BEGIN DECLARE affected_rows INT DEFAULT 1; WHILE affected_rows > 0 DO DELETE FROM order_log WHERE created_at < '2024-01-01' LIMIT 1000; SET affected_rows = ROW_COUNT(); -- 等一下再继续，防止对线上造成压力 DO SLEEP(1); END WHILE; END$$ DELIMITER ;

这个存储过程会一直删，直到某次 DELETE 影响行数为 0，说明旧数据已经全部清理完毕。每次只删 1000 行，每次停顿 1 秒，是对线上环境比较温和的清理方式。

5.4 配合 SQL 工具实操的几个建议

实操时你用命令行也好，用 Navicat 这类图形工具也好，有几点经验值得记住。图形工具确实直观，查数据方便，但执行 DELETE 和 UPDATE 之前一定要确认工具是否有“安全提醒”机制。有些工具在 SQL 解析后能看到将要影响多少行，这个信息务必看一眼，别直接回车。

我自己用命令行更多一些，因为生产环境排查问题时往往没有图形工具可用，反而是命令行最快。多练练mysql -u root -p -h host这些命令。写 SQL 时注意结尾的分号，多行语句在命令行里要敲;才能真正执行。执行高危操作前，保证当前窗口没有开启自动提交：你可以显式地START TRANSACTION，万一误操作了，还能ROLLBACK救回来。

另外养成一个好习惯：执行完 DML 后，马上用 SELECT 验证结果。INSERT 有没有插进去，UPDATE 改对了几行，DELETE 删得干不干净，都要查出来看一眼再走，不要看一眼“Query OK”就溜了。

6. 增删改高频报错速查：报错信息、原因与处理

6.1 字符集与 emoji 插入失败的排查

最经典的报错长这样：

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'name' at row 1

这个\xF0\x9F\x98\x80是一段 UTF-8 编码的 4 字节字符，最常见的就是 emoji。MySQL 的 utf8 字符集最多存 3 个字节，存不下它。解决办法是把表结构从 utf8 升级成 utf8mb4。可以分两步走：先改数据库、表、字段的字符集，再确认连接层也是 utf8mb4：

ALTER TABLE product CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

也要检查连接参数，如果用的是 JDBC，可以在连接串里加上characterEncoding=utf8mb4；如果是命令行或图形工具，确认连接字符集设置正确。这个问题出现频率极高，尤其是新老系统混合、历史遗留库的迁移场景里。

6.2 认证协议与连接错误

MySQL 8.0 的默认认证插件是caching_sha2_password，而很多老版本的客户端工具、JDBC 驱动、编程语言库默认用的是mysql_native_password。于是连接时报错：

Authentication plugin 'caching_sha2_password' cannot be loaded

或者更隐蔽一点，客户机版本太旧，报错提示客户端不支持服务端要求的认证协议。处理方式有两个方向。一是升级客户端和驱动到支持 MySQL 8 的版本，这是最推荐的做法；二是把用户的认证插件改成老协议：

ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password'; FLUSH PRIVILEGES;

注意这种改法会降低安全性，最好只在暂时无法升级客户端的过渡期使用。还有一个很容易碰到的连接报错是时区问题，报错里会看到：

The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized

听起来和 DML 无关，但不解决它你连 DML 都执行不了，只能先去SET time_zone = '+08:00'，或者修改全局时区参数。这类问题上网搜索时可以用“MySQL 8 认证插件 连接失败”之类的关键词，能找到很多现成案例。

6.3 InnoDB 锁等待超时怎么定位

执行 UPDATE 或 DELETE 时，如果卡住很久然后报错：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这意味着你的事务在等待一个被其他事务持有的行锁，超过了innodb_lock_wait_timeout的默认 50 秒。出现这种情况，优先要查两个系统表。

SELECT * FROM information_schema.innodb_trx;

这个表里能看到当前所有活跃事务、运行的 SQL、锁等待时间等信息。如果发现某个事务已经跑了很久还没有提交，它很可能是罪魁祸首——持有锁但不释放。另外还可以用：

SHOW ENGINE INNODB STATUS;

在输出里搜索LATEST DETECTED DEADLOCK或TRANSACTIONS部分，可以看到锁等待相关的细节。定位到阻塞事务对应的会话 ID 后，可以确认是不是误操作、开发人员忘了提交事务，或者某个长事务真的需要执行那么久。必要时可以KILL掉持有锁的会话，但操作前要确认不会影响其他业务。总之，锁等待问题在并发 DML 场景下非常常见，定位思路就是：找到持锁事务，判断是否合理，再决定等待、提交或 kill。

6.4 类型转换与外键约束：两个最隐蔽的坑

最后说两个特别隐蔽的坑。

第一个是类型隐式转换导致 WHERE 判断意外命中。看这个例子：

SELECT name FROM users WHERE phone = 18811112222;

如果 phone 字段是 varchar 类型，MySQL 会把 phone 列的所有值转成数字再去和 18811112222 比较。一旦某些值不是纯数字，比如'abc'，在数字转换时变成 0，MySQL 会认为0 = 18811112222为假，这还算好。更危险的是当你执行 DELETE 时：

DELETE FROM users WHERE phone = 0;

如果你想把所有 phone 为 0 的记录删掉，但 phone 是 varchar，而表中又存在'abc'之类的非数字字符串，那它们全部会被当成 0 匹配上，直接被删除。这个坑在工作里真的出现过。解决办法很简单：字符串字段和字符串常量比较时，一定要加引号。

DELETE FROM users WHERE phone = '0';

第二个坑是外键约束。如果你的子表里有外键引用着父表，删除父表记录时会报错：

ERROR 1451 (HY000): Cannot delete or update a parent row: a foreign key constraint fails

类似地，插入子表数据时，如果引用的父表主键不存在，会报 1452 错误。处理方式要看业务需求：要么先删除或更新引用了该父行的子表数据，要么在应用层保证操作顺序，要么干脆对表结构调整外键策略，比如在外键上配置ON DELETE CASCADE，让 MySQL 自动级联删除子表数据。这个选择没有绝对的对错，完全看你的数据一致性的要求。但不管选哪个，都要先理解外键约束的语义，不要删了父表才发现子表残留一堆孤儿数据。

我在实际工作中还见过一个很典型的应用层连环坑：业务代码里删除用户数据，没有先检查这个用户有没有订单记录，结果 DELETE 一执行，数据库就报 1451，接口直接 500。这种问题的答案是先查子表、再删父表，或者在业务逻辑里做“软删除”——用 UPDATE 把一个deleted字段置为 1，而不是真正 DELETE 掉。这个思路在我的项目里用得越来越多，因为数据越来越值钱，真删数据的机会真的应该越来越少。

就以个人经验结尾吧。我写 DML 相关代码已经有几年了，最大的体会是：增删改这三个操作，看起来是 SQL 入门的最后一步，实际上却是所有线上事故的重灾区。INSERT 出错顶多数据多点或者少点，UPDATE 和 DELETE 出错就是数据没了、状态乱了、订单多了。所以在团队里，我一直主张把 SQL 安全规范当成代码规范一样对待：执行前先 SELECT，危险操作开事务，大批量改禁用裸跑，该分批的分批，该备份的备份。把这些习惯养成自然，你写出来的 DML 才能担得起“生产可用”这四个字。