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 TABLEDCL 负责权限和用户控制比如 GRANT、REVOKEDQL 负责查询数据也就是 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 11或者带上明确的注释让审查的人一眼就知道这是有意为之。最怕的就是漏写 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传成字符串abcMySQL 会尝试把字符串转成数字转不过去就直接报错。列名可以省略不写这时候 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 IGNOREINSERT IGNORE INTO user(id, name, email) VALUES(1, 张三, zhangsanexample.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 INTOREPLACE INTO user(id, name) VALUES(1, 李四);REPLACE 遇到冲突时会先把现有行 DELETE 掉再插入新行。副作用非常明显自增 ID 会变化外键关联会被破坏如果表里还有其他字段未提供会被默认值覆盖或置空。所以我对 REPLACE 的态度很明确除非你确认这个表就是用来做“整体覆盖”的否则尽量别用ON DUPLICATE KEY UPDATE 几乎总是更好的选择。2.4 插入数据时容易被忽略的细节默认值、自增列与字符集最后说一下 INSERT 使用中容易翻车的几个细节。第一自增列可以直接省略不写也可以显式写成 NULLMySQL 会自动生成下一个自增值。你甚至可以手动指定一个大的 ID比如id 1000插入成功后后续自增会从 1000 之后继续。这个特性可以用来做数据搬迁时保留原 ID但要注意如果手动指定的 ID 已经存在会触发主键冲突。第二关于默认值。表结构里带有 DEFAULT 的列插入时可以不写。比如建表时设置了created_at DATETIME DEFAULT CURRENT_TIMESTAMP插入时只要你没写这个字段系统就会自动填当前时间。但如果列是NOT NULL且没有默认值你又没写它MySQL 就会报错最经典的就是 “Field xxx doesnt 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: 0matched 表示条件命中了多少行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_updates1防止脚本逻辑写错时一口气把整张表干掉。如果不方便全局开启还有一个笨但实用的办法先 SELECT 确认影响行数。比如你要更新一批用户状态先跑SELECT id, status FROM users WHERE last_login_at 2023-01-01;确认返回的行数符合预期再把同样的 WHERE 复制到 UPDATE 语句里。数据量大时这样多花几秒但能挡住最危险的错误。3.3 多表关联更新与分批次更新实际业务中数据不会只待在一张表里经常需要根据另一张表的条件来更新当前表。MySQL 支持多表关联更新语法是 UPDATE ... JOINUPDATE 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 请求执行时拿到 stock10写回 9B 请求再执行时拿到的是 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;两者有很多区别我用表格列一下对比维度DELETETRUNCATE语句类型DMLDDL是否重置自增否是是否能带 WHERE可以不可以是否逐行触发触发器是否是否可回滚在事务内可以回滚通常不能回滚删除速度较慢逐行处理很快直接释放表数据页空间释放不立即释放磁盘空间释放大部分空间取决于表配置从这张表能得出很多实战结论。如果你只想清空一张表且不需要回滚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 的 DELETEDELETE 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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 15id1需要做两件事扣减库存、增加已售数量。最基本的一条 UPDATEUPDATE 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。可以分两步走先改数据库、表、字段的字符集再确认连接层也是 utf8mb4ALTER TABLE product CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;也要检查连接参数如果用的是 JDBC可以在连接串里加上characterEncodingutf8mb4如果是命令行或图形工具确认连接字符集设置正确。这个问题出现频率极高尤其是新老系统混合、历史遗留库的迁移场景里。6.2 认证协议与连接错误MySQL 8.0 的默认认证插件是caching_sha2_password而很多老版本的客户端工具、JDBC 驱动、编程语言库默认用的是mysql_native_password。于是连接时报错Authentication plugin caching_sha2_password cannot be loaded或者更隐蔽一点客户机版本太旧报错提示客户端不支持服务端要求的认证协议。处理方式有两个方向。一是升级客户端和驱动到支持 MySQL 8 的版本这是最推荐的做法二是把用户的认证插件改成老协议ALTER USER usernamehost 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在数字转换时变成 0MySQL 会认为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 才能担得起“生产可用”这四个字。