1. 增删改查到底是什么：先想清楚这四件事的定位
我很少见到有开发者能一次写对MySQL的增删改查。刚入行时我以为CRUD就是“INSERT、SELECT、UPDATE、DELETE”四个单词各三五行语法的事，直到我花过一个下午排查一条“查不出来”的数据，最后发现是字符集不一致；也见过在生产环境执行UPDATE时忘了加WHERE，直接把整表客户价格改成了统一价。MySQL的增删改查是应用系统最底层的四根柱子，几乎所有业务功能最终都会落到这四类操作上，但柱子立不立得住，取决于你对数据约束、事务边界、索引利用和安全风险的判断。
这篇文章适合什么人看？如果你刚开始学MySQL，需要一份能直接照着写的实操手册；如果你写过一些SQL但总在细节上翻车，比如批量插入慢、查询卡、并发更新锁等待严重，这篇内容同样值得从头过一遍。我下面会用订单表做例子，把四类操作的完整语法、参数含义、背后原理和实际场景串起来讲。这四类操作的代码谁都会敲，但敲之前怎么设计表、敲之后怎么验证结果、出了问题怎么定位，才是真正的分水岭。
1.1 为什么所有业务都绕不开这四个动作
任何业务系统本质上都是“数据在流入、流转、流出”。用户下单是插入，展示订单列表是查询，改收货地址是更新，取消订单是删除。你可以不用存储过程、不用视图、不用分区表，但不可能不用到这四个最基础的数据操作。所谓“程序员的日常”，至少有六成的时间是在跟这四个动词打交道。
CRUD这四件事虽然在SQL语法上各自独立，但在真实业务里往往要串联使用。创建订单时要先查询库存，再插入订单主表，更新库存余量，最后删除购物车里对应的记录。每个环节的成败都影响下一个环节，于是事务、锁、主键策略、索引覆盖这些概念就全被带出来了。这也是为什么面试和工作中总拿CRUD当起点做考察——它简单到可以十分钟讲完语法，又复杂到能纠缠出性能事故。
我建议所有初学者都先把CRUD当成“读、写事务的最小单元”来理解，而不是四个孤立的句子。写一条INSERT时，你要想到事务日志会记什么；写一条UPDATE时，你要想到受影响的行锁范围；写一条DELETE时，你要想到被删除的数据在二级索引里如何清理。后面所有优化手段，都是在为这四个动作减少计算量、减少锁冲突、减少无效IO。
1.2 动手前先建一张能够承载业务的表
很多时候CRUD出了问题，并不是SQL本身写错了，而是表结构从一开始就设计得不合理。所以第一步是要有一张结构清晰的表。我用一张订单明细表来当演示对象，这是业务系统里最典型的场景，字段不算多，但覆盖了数值、字符串、时间、状态枚举和逻辑删除位。
CREATE TABLE `order_item` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `order_no` VARCHAR(32) NOT NULL COMMENT '订单编号', `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID', `sku_id` BIGINT UNSIGNED NOT NULL COMMENT '商品SKU', `sku_name` VARCHAR(128) NOT NULL COMMENT '商品名称快照', `price` DECIMAL(10,2) NOT NULL COMMENT '成交单价', `quantity` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '购买数量', `total_amount` DECIMAL(10,2) NOT NULL COMMENT '成交总金额', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0待支付 1已支付 2已发货 3已完成 4已取消', `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除：0未删除 1已删除', PRIMARY KEY (`id`), KEY `idx_order_no` (`order_no`), KEY `idx_user_id` (`user_id`), KEY `idx_sku_id` (`sku_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单明细表';这张表的设计里有几个值得注意的点，它们直接决定后面CRUD好不好写。第一是主键用BIGINT UNSIGNED AUTO_INCREMENT，InnoDB是聚簇索引表，主键有序自增能减少页分裂，随机主键（比如UUID）会导致大量随机IO，数据量大了之后插入性能会明显劣化。第二是DECIMAL(10,2)存金额，绝对不能存FLOAT或DOUBLE，浮点数的二进制精度问题会导致账目对不上。第三是字符集用了utf8mb4，现在的新表都该用它，因为utf8mb3存不了emoji和一些生僻汉字，而且utf8mb4也是MySQL 8.0的默认字符集。第四是deleted逻辑删除位，这不是必须的，但绝大多数公司，尤其是涉及资金、订单、审计的系统，都偏好逻辑删除而非物理删除，后面DELETE部分会专门讲。
表建好之后，往里面塞一批测试数据，方便后面演示。
INSERT INTO order_item (order_no, user_id, sku_id, sku_name, price, quantity, total_amount, status, remark) VALUES ('ORD20250110001', 1001, 5001, '机械键盘', 399.00, 1, 399.00, 1, '加急配送'), ('ORD20250110001', 1001, 5002, '鼠标垫', 29.90, 2, 59.80, 1, NULL), ('ORD20250110002', 1002, 5003, 'USB扩展坞', 129.00, 1, 129.00, 0, '待支付'), ('ORD20250110003', 1003, 5004, '4K显示器', 2499.00, 1, 2499.00, 3, '已完成'), ('ORD20250110004', 1001, 5005, '笔记本支架', 89.00, 2, 178.00, 2, '已发货');后面所有的增删改查操作，我都基于这张order_item表来演示。先讲插入，因为数据进不来，后续的查改删都是空谈。
2. 从数据进入开始：插入语句的使用细节
INSERT是CRUD里语法最宽容、踩坑却最多的一类操作。宽容在格式灵活：单行、多行、按条件插入、无则插入有则更新都支持；坑则集中在字符集、自增主键、唯一索引冲突和批量性能这些看不见的地方。很多人觉得“不就是写个INSERT INTO”，但真到线上报错了才发现自己对插入的理解还停留在大学实验课上。
2.1 INSERT基础语法与批量插入的威力
最基础的插入语句长这样：
INSERT INTO order_item (order_no, user_id, sku_id, sku_name, price, quantity, total_amount, status) VALUES ('ORD20250110005', 1002, 5006, '显示器支架', 199.00, 1, 199.00, 0);这里要提醒一下：字段名和值要一一对应。如果省略字段列表，就得给全表所有非默认字段都提供值，我强烈不建议在生产代码里省略字段列表，因为一旦表结构变更（加了字段或调整顺序），省略字段列表的INSERT会当场报错或错位插入，这种错误排查起来很痛苦。显式写出字段列表，等于给这次插入画了一条明确的边界。
批量插入是提高插入效率最直接的手段。假设你要往表里导入一万条数据，一条条INSERT，每条都要经历一次SQL解析、权限检查、事务日志写入，在MySQL 5.7和8.0下，单条插入的吞吐量会远低于一次批量插入。改写方式很简单：
INSERT INTO order_item (order_no, user_id, sku_id, sku_name, price, quantity, total_amount, status) VALUES ('ORD20250110006', 1003, 5007, 'USB-C转Lightning线', 49.00, 3, 147.00, 1), ('ORD20250110006', 1003, 5008, '充电头', 79.00, 1, 79.00, 1), ('ORD20250110007', 1004, 5009, '便携显示器', 1299.00, 1, 1299.00, 0);批量插入的价值在于网络往返次数被压缩成一次。MySQL Server和存储引擎之间少了很多次握手，binlog和redo log的写入次数也变少，总体性能提升是数量级的。我测过一个大约十万行的导入场景，逐条插入耗时接近九分钟，拆成每批五百条之后，总耗时降到十几秒。具体每批多少条合适？经验值是500到1000条，太少了效果不明显，太多了单条SQL会变得很长，容易触碰max_allowed_packet上限，也会占用过多内存做事务缓冲。
注意：大批量导入前，如果业务允许，先
ALTER TABLE ... DISABLE KEYS（仅对MyISAM有效）或者干脆先删掉非必要二级索引，导入完成后再重建索引，这个技巧对InnoDB同样适用，只是InnoDB不支持DISABLE KEYS，但可以通过减少索引数量来降低写入成本。
2.2 插入时的安全防线：预处理与SQL注入
我见过太多因为拼接字符串写INSERT/UPDATE导致的线上事故。最常见的场景是：用户输入一个昵称，后台把字符串直接拼进SQL里——像是INSERT INTO user (name) VALUES ('$name')。如果用户输入的是xxx'); DROP TABLE user; --，后果不堪设想。MySQL官方推荐的防御方案是用预处理语句（PREPARE）或参数化接口，Java的PreparedStatement、PHP的PDO预处理、Python的pymysql参数化，底层都是让SQL结构和数据分离，数据只作为参数传入，绝不参与SQL语法解析。
这里再说一个MySQL原生的预处理语法，适合在写脚本或存储过程时用：
PREPARE stmt FROM 'INSERT INTO order_item (order_no, user_id, sku_id, sku_name, price, quantity, total_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'; SET @order_no = 'ORD20250110008'; SET @user_id = 1005; SET @sku_id = 5010; SET @sku_name = '无线鼠标'; SET @price = 159.00; SET @quantity = 1; SET @total_amount = 159.00; SET @status = 0; EXECUTE stmt USING @order_no, @user_id, @sku_id, @sku_name, @price, @quantity, @total_amount, @status; DEALLOCATE PREPARE stmt;用占位符?占住值的位置，用EXECUTE ... USING传入实际值，MySQL内部会把参数按值处理，而不是重新解析成SQL片段，注入攻击就被堵死了。这条经验无论你是什么编程语言，都请落到ORM或原生驱动的使用规范里。
2.3 插入时的冲突处理：IGNORE、REPLACE与ON DUPLICATE KEY UPDATE
业务里经常遇到“这条记录可能已经存在，你得按情况处理”的插入需求。比如订单号有唯一索引，重复提交订单时不能插两条；比如用户上传商品资料，sku_id相同则更新价格而不是新增。MySQL提供了三套处理冲突的机制，我分开讲清楚，因为很多人分不清它们之间的代价差异。
INSERT IGNORE是把冲突当成“可忽略错误”处理。遇到唯一键冲突，它不再报错，而是直接跳过这条插入。适合用在“有就跳过、没有才插”的初始化场景，比如初始化配置项时。它的代价是如果表里有其他更严重的错误（比如字段超长被截断），IGNORE也会一并吞掉，导致数据异常入库而你不自知。
REPLACE INTO的行为是先删旧记录再插新记录。表面看起来“更新了”，实际产生了DELETE和INSERT两个动作，会额外触发删除操作带来的链式影响——比如自增主键值会跳号，关联这个记录的外键或被其它查询引用时会出现短暂的中间态。如果不是明确需要“彻底替换整行”，我不建议用REPLACE。
最实用的其实是ON DUPLICATE KEY UPDATE，它专门处理“有冲突就更新指定字段”的情况：
INSERT INTO order_item (order_no, user_id, sku_id, sku_name, price, quantity, total_amount, status) VALUES ('ORD20250110009', 1006, 5011, '显示器挂灯', 169.00, 1, 169.00, 0) ON DUPLICATE KEY UPDATE price = VALUES(price), quantity = VALUES(quantity), total_amount = VALUES(total_amount), updated_at = NOW();注意MySQL 8.0.20开始，VALUES()函数在ON DUPLICATE KEY UPDATE里已经标记为废弃，推荐用别名语法：
INSERT INTO order_item (order_no, user_id, sku_id, sku_name, price, quantity, total_amount, status) VALUES ('ORD20250110009', 1006, 5011, '显示器挂灯', 169.00, 1, 169.00, 0) AS new ON DUPLICATE KEY UPDATE price = new.price, quantity = new.quantity, total_amount = new.total_amount, updated_at = NOW();这里AS new定义了新值别名，后面更新时直接引用。要知道INSERT里触发的更新和真实UPDATE语句在锁行为上是有区别的，它在重复键冲突时获取的是S锁或X锁，高并发下如果插入冲突概率高，要提前测锁等待情况。
3. 查询才是核心战场：SELECT怎么写才靠谱
SELECT是CRUD里最复杂、最能直接反映技术水准的一类。它的语法我们日常用到的可能只占MySQL全部查询能力的十分之一，但恰恰是这十分之一里藏着大量优化空间和隐患。网络慢、页面卡、接口超时，八成问题都出在SELECT上。
3.1 单表查询：条件过滤、排序、分页的正确姿势
单表查询看起来简单，就是从一张表里取符合条件的行，但每个子句写不对，效果天差地别。最基础的查询：
SELECT order_no, sku_name, price, quantity, total_amount, status FROM order_item WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 10;这里值得展开说的是WHERE user_id = 1001为什么快。因为我建表时给user_id加了idx_user_id索引，MySQL会走二级索引定位到user_id=1001的主键值，再回表取完整数据。如果没这个索引，就得全表扫描，每一行都查一遍，表越大越慢。
ORDER BY created_at DESC同样需要注意。如果created_at没有索引，MySQL要拿到筛选结果之后做一次文件排序（filesort），小结果集无所谓，结果集大了就会产生临时文件和额外IO。如果你的查询经常按某个字段排序，可以把这个字段加进索引里，或者设计联合索引覆盖排序需求。最理想的是索引本身就按所需顺序排列，这样连排序操作都省了。
分页是另一个高频雷区。很多同学一上来就写：
SELECT id, order_no, sku_name, total_amount FROM order_item ORDER BY created_at DESC LIMIT 100000, 20;LIMIT后面的偏移量越大，MySQL要扫描并丢弃的行就越多。它得先读前面十万行，再扔掉，最后才返回二十行。数据量上去之后，这种写法会把数据库拖得很惨。深度分页的处理方案有很多，常见的两种：
第一种是“上一页最大ID”法，记住上一页最后一条记录的主键或排序键值，下一页直接用WHERE条件过滤：
SELECT id, order_no, sku_name, total_amount FROM order_item WHERE id > 100204 ORDER BY id LIMIT 20;这种写法直接利用主键索引跳过了前面所有行，无论翻到多深，性能几乎恒定。前提是排序字段稳定且唯一，通常用主键。
第二种是“子查询延迟关联”，先从索引里拿到分页范围内的主键，再回表取数据：
SELECT t.id, t.order_no, t.sku_name, t.total_amount FROM order_item t INNER JOIN ( SELECT id FROM order_item ORDER BY created_at DESC LIMIT 100000, 20 ) AS tmp ON t.id = tmp.id;子查询只查主键和排序字段，能把扫描代价压在最小的范围里，然后用主键去关联原表，比直接加LIMIT大偏移量要顺畅很多。
3.2 多表查询与JOIN的代价意识
业务数据通常情况下是拆开存的：订单主表存总金额和用户，订单明细表存每一项商品，商品表存SKU信息，用户表存收件人。要展示一个完整的订单列表，单表查询办不到，必须多表关联。
一个常见的JOIN示例：
SELECT o.order_no, u.nickname, o.total_amount, o.status FROM `order` o INNER JOIN `user` u ON o.user_id = u.id WHERE o.created_at >= '2025-01-01' ORDER BY o.created_at DESC LIMIT 50;执行这种JOIN时，MySQL优化器会选一张表当驱动表，用它的查询结果去另一张表匹配。对开发者来说，核心是理解“被驱动表的连接字段一定要有索引”。上面这个例子，order.user_id和user.id只要有索引，匹配成本就非常低。如果user.id没索引，MySQL就得对user表做全表扫描来匹配每一行，查一次这种JOIN就是一场灾难。
还有个小技巧：连接字段的字符集和排序规则必须一致。utf8mb4_unicode_ci关联utf8mb4_general_ci，索引可能用不上，因为MySQL需要先做转换。建库建表时统一字符集，能避免很多暗中发生的转换开销。
JOIN不是越多越好。每多一张表，优化器要选择的执行计划组合就越多，三条以上大表的JOIN，执行计划很可能就不是最优了。很多场景可以拆成多次单表查询，在应用层做合并，反而能把SQL执行时间压下去。别迷信一条大SQL查完所有数据，那只是听起来很爽。
3.3 聚合统计与分组：GROUP BY和HAVING的边界
统计类查询在报表系统里几乎是日常主食。比如“查每个用户的订单总金额”“查每一类商品卖了多少件”，这些离不开聚合函数和分组。
SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM order_item WHERE status != 4 GROUP BY user_id HAVING total_spent > 500 ORDER BY total_spent DESC;COUNT(*)统计行数，SUM(total_amount)汇总金额，GROUP BY把同一个user_id的数据归成一组，HAVING负责对聚合后的结果做过滤。注意WHERE在分组前过滤行，HAVING在分组后过滤组，两者执行顺序完全不同，写错的话统计结果会偏离预期。
MySQL 5.7.5之后默认开启了ONLY_FULL_GROUP_BY模式，SELECT后面出现的非聚合列必须出现在GROUP BY里，或者被聚合函数包裹，否则SQL直接报错。这是为了兼容SQL标准、防止语义歧义。比如：
SELECT user_id, sku_name, SUM(quantity) FROM order_item GROUP BY user_id;上面这段在宽松模式下能跑，但sku_name到底是哪一行的sku_name？MySQL会随意取一条，结果不确定。如果业务上“买了最多的那个商品名”这样的需求，应该用子查询或ANY_VALUE()显式表达意图，而不是依赖不确定行为。
3.4 索引与查询性能：为什么你的查询总是慢
查询优化的核心是索引，而索引并不是建了就完事。我举一个最典型的索引失效例子：
SELECT * FROM order_item WHERE DATE(created_at) = '2025-01-10';这个写法在逻辑上没错，但它对created_at做了函数运算，导致索引失效——因为索引里存的是原始时间值，MySQL没法用函数处理后的结果去搜索B+树。正确的写法是范围查询：
SELECT * FROM order_item WHERE created_at >= '2025-01-10 00:00:00' AND created_at < '2025-01-11 00:00:00';这样created_at保持原始形态，可以直接走索引。类似的坑还有隐式类型转换，比如字段是VARCHAR，却用整型去查；字段是字符串，却完全拿字符串去比较时忘了引号。有一个排查技巧：任何SQL看不懂为什么慢，先用EXPLAIN看执行计划，重点看type列——如果是ALL（全表扫描）或index（全索引扫描），就该想想查询或索引怎么改了。ref、const、eq_ref基本都是用了索引且定位精准的表示。
还有一类容易被忽略的问题：SELECT带了大字段（比如VARCHAR(255)的备注）且查询结果集很大时，即使走了索引也仍然会有大量回表IO。如果频繁要按订单号查列表，但列表页只显示几个核心字段，可以考虑把常用查询字段放进联合索引，形成覆盖索引，让查询只扫描索引页就能返回，减少回表。
4. 更新和删除：改数据比插数据危险得多
UPDATE和DELETE之所以要放在一起讲，是因为它们的危险系数远远高于INSERT。插入一条数据最多是“多了一行”，影响范围可控；但不带WHERE的UPDATE或DELETE会瞬间改变或抹掉全表数据，而且多数情况下没有后悔药。我见过不少人在测试环境里浪惯了，手一抖把UPDATE的WHERE漏了，然后全公司陪他一起恢复备份。
4.1 UPDATE实操：先SELECT再UPDATE、事务与行锁
更新操作最稳妥的姿势是“先查后改”。不是说要写两条SQL，而是执行前先用SELECT把将要影响的行查出来确认一遍。对于数据敏感的系统，UPDATE前把WHERE条件单独拿去做SELECT，看看返回的行数和你预期是否一致，再套上UPDATE执行。日常开发时，我建议把UPDATE写成三步模板：
第一步，确认条件：
SELECT id, order_no, status, total_amount FROM order_item WHERE order_no = 'ORD20250110001' AND status = 1;第二步，更新指定字段：
UPDATE order_item SET status = 2, updated_at = NOW() WHERE order_no = 'ORD20250110001' AND status = 1;第三步，检查影响行数。
影响行数是UPDATE里一个极重要的反馈信号。MySQL默认情况下，如果更新前后的值没有变化，影响行数会显示0（取决于CLIENT_FOUND_ROWS标记），这可能让部分ORM产生“更新失败”的误判。反过来，影响行数为0也可能意味着WHERE条件没有匹配到任何行。所以拿影响行数来判断业务结果时，要明确你到底在判断“匹配到数据但没变化”还是“压根没匹配到”。
UPDATE在事务里的表现也值得一提。InnoDB默认REPEATABLE READ隔离级别，UPDATE会对匹配到的行加排他锁（X锁），事务没提交前，这些行对其他事务的UPDATE和DELETE都是不可操作的。高并发场景下，两个事务同时改同一行，其中一个要等另一个提交或回滚才能继续。如果事务里做了大量UPDATE，尽量保持事务短小，避免长事务长时间占着行锁，拖垮整个系统的并发吞吐。
事务控制的实操示意：
START TRANSACTION; UPDATE order_item SET status = 3, updated_at = NOW() WHERE id = 1; UPDATE `order` SET status = 3, updated_at = NOW() WHERE order_no = 'ORD20250110001'; COMMIT;在START TRANSACTION和COMMIT之间，两条UPDATE要么都成功，要么都回滚，这就能保证订单主表和明细表状态一致。要是没有事务，第一条成功、第二条失败，数据就处于“明细已完成但主表未完成”的矛盾状态。用事务之前先想清楚需要保证一致性的边界在哪里。
4.2 DELETE的两个层次：物理删除与逻辑删除
直白的DELETE就是物理删除：
DELETE FROM order_item WHERE id = 5;它会把行数据从表空间里抹掉。如果是二级索引上的记录，还要同步清理索引项；如果表上有外键约束，还涉及外键检查。物理删除最麻烦的是不可恢复——除非你有备份，否则删掉了就是删掉了。所以在真实业务系统里，资金、订单、用户这类核心数据几乎一律不使用物理删除，而是用逻辑删除，也就是在最初建表时预留的deleted字段。
逻辑删除的写法：
UPDATE order_item SET deleted = 1, updated_at = NOW() WHERE id = 5;之后所有查询默认排除已删除数据：
SELECT * FROM order_item WHERE deleted = 0;逻辑删除的好处是数据还在，随时可以恢复、审计、回溯。代价则是每次查询都要多带一个deleted = 0条件，而且如果忘了写，已删除数据会重新出现在业务结果里，造成统计口径混乱。所以要么统一封装BaseMapper注入这个条件，要么在视图层做约束，绝不能靠程序员自觉。
还有一个物理删除和TRUNCATE的区分点。TRUNCATE TABLE不是DELETE，它是一种DDL操作，直接把表数据文件重置，速度极快，但它会重置自增计数器，而且无法按条件删——一旦执行就清空全表，并且不可用事务回滚（在MySQL里TRUNCATE隐式提交）。如果你只是想清空一张临时表，TRUNCATE很合适；如果想删一部分数据，只能DELETE。
4.3 大范围更新/删除的批量处理策略
一次性UPDATE或DELETE数万行数据，听起来一条SQL很简便，实际会引发长事务、大锁范围、大undo日志、复制延迟等一系列连锁反应。我在实际运维中遇到过一条UPDATE误改二十万行，主库执行完两分钟后从库还没追平，导致从库读到的数据一度不一致。
所以处理大批量更新/删除时，要拆批、限流、加停顿。比如要删除一个月前的过期日志记录，按主键范围分批执行：
DELETE FROM order_item WHERE created_at < '2024-12-01' AND id BETWEEN 0 AND 5000; -- 停顿一下，观察影响行数和主从延迟 DELETE FROM order_item WHERE created_at < '2024-12-01' AND id BETWEEN 5001 AND 10000;每批删除的行数尽量控制在5000以内，批次间加0.1到1秒的sleep，给主从复制追赶的时间。另外，大批量DELETE会让InnoDB的 purge线程和insert buffer产生较大压力，如果是每天都要跑的清理任务，更建议放到业务低峰期执行。
5. 常见问题与排查技巧实录
这一节是我自己踩坑攒下来的速查手册，每一类问题都在不同项目里出现过。整理成表格，方便你排查时快速定位。
5.1 经典报错与含义速查
| 报错信息 | 含义 | 解决思路 |
|---|---|---|
Duplicate entry 'xxx' for key 'uk_xxx' | 唯一索引冲突 | 检查业务是否重复提交，或该唯一键是否需要调整 |
Data too long for column 'remark' at row 1 | 字段长度不够 | 改字段长度，或做输入截断 |
Incorrect string value: '\xF0\x9F\x98\x80' | 字符集不支持emoji | 表/字段字符集改为utf8mb4 |
Unknown column 'xxx' in 'where clause' | 字段不存在 | 检查字段名是否写错或表结构是否变更 |
Truncated incorrect DECIMAL value: 'abc' | 数值类型被写入非数字字符串 | 检查业务传入参数类型 |
Lock wait timeout exceeded; try restarting transaction | 行锁等待超时 | 查哪些事务占用了锁，缩短事务或调整并发 |
Deadlock found when trying to get lock | 死锁 | 统一SQL加锁顺序，或改写更新逻辑 |
This function has none of DETERMINISTIC... | binlog模式下函数/存储过程问题 | 声明DETERMINISTIC或改log_bin_trust设置 |
Lock wait timeout exceeded是最常见也最让人头疼的。排查思路是用SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK和TRANSACTIONS段落，找到持有锁的事务及其SQL，然后判断是哪条业务路径在长时间占用锁。有一次一个定时任务在一个大事务里跑了十分钟，普通用户更新同一行就一直等待，最后超时。解决方式很简单，把定时任务拆成小事务，单事务内只处理少量行。
死锁则是MySQL在并发下“双方互相持有对方需要的锁”时，主动让其中一个事务回滚。破解死锁的关键是让所有事务以相同的顺序访问资源。比如更新订单主表和明细表，A事务先更主表再更明细，B事务也先更主表再更明细，就能避免交叉等待。
5.2 乱码问题：一个字符集的连环坑
乱码问题经典到IO/NET几乎天天有人问。表现是插入中文正常、查出来是问号，或者程序里显示正常、数据库命令行里显示乱码。这类问题要从三个层次排查：客户端连接字符集、数据库/表/字段字符集、返回值传输字符集。
先看数据库侧字符集：
SHOW VARIABLES LIKE 'character_set%';正常情况下，character_set_client、character_set_connection、character_set_results应当和表字符集一致，通常都是utf8mb4。不一致时，可以在连接后执行：
SET NAMES utf8mb4;程序侧（比如Java JDBC）则要确保连接字符串里显式指定characterEncoding=utf8，并且MySQL连接器版本和MySQL服务端版本匹配。有一个老坑是JDBC连接串里写了characterEncoding=utf8但服务端是utf8mb4，连接器可能会把utf8mb4降级成utf8mb3，导致emoji丢字。新版连接器的做法是连接串里明确connectionCollation=utf8mb4_unicode_ci或直接信任服务端配置。
排查乱码时，最快速的方法是先打印原始字节，确认数据在应用层是否是完整的中文，比如user_input.getBytes("UTF-8")之后再做十六进制打印。如果数据本身没问题，再逐层核对传输链路。别一上来就怀疑MySQL，很多乱码其实在程序入口就被错误编码了。
5.3 索引失效与慢查询的常见诱因
慢查询定位最简单的方式是开启慢查询日志：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;超过一秒的SQL会被记进日志，然后拿具体SQL去跑EXPLAIN逐个看执行计划。我归纳了导致索引失效最常见的几种写法：
- 在索引列上做函数运算或表达式运算，如
WHERE YEAR(created_at) = 2025。 - 隐式类型转换，如字符串字段
order_no用整型比较WHERE order_no = 10001，MySQL会尝试把字段转成数字，导致索引失效。 - 前导模糊查询，如
WHERE sku_name LIKE '%键盘%'，这个百分号在最前面，B+树无法从中间匹配，索引优化无从谈起。可以改为LIKE '键盘%'或引入全文索引。 - OR连接的条件中包含非索引列。比如
WHERE order_no = 'xxx' OR remark = 'yyy'，即便order_no有索引，remark没索引，优化器可能放弃走索引。改法是用UNION拆开，或保证所有OR条件都有索引。 - 联合索引未遵守最左前缀原则。比如索引
(user_id, created_at)，条件只写created_at范围，不写user_id，索引就用不上。
针对联合索引，我再补充一个设计心得：等值条件的字段放前面，范围条件的字段放后面。比如经常按user_id精确查、按created_at排序/范围过滤，联合索引就建成(user_id, created_at)，这样查询时既能在user_id上等值定位，又能在created_at上直接得到排序结果。
提示：写SQL时养成习惯，
WHERE条件里对索引列保持原样，不做封装，不加运算。所有函数需求（比如日期格式化）放到SELECT层或程序侧做。
我的实操领悟与习惯
做了这些年数据相关工作，我越来越觉得增删改查不是“背语法”，而是在训练一种对数据流转的直觉。每写一条INSERT，我会想这条数据未来会被哪些查询命中，要不要在这个业务节点上提前补索引；每写一条UPDATE，我会想它在事务里锁了哪些行，会不会阻塞其他动作；每写一条DELETE，我会想它的数据能不能恢复，要不要换成逻辑删除。这种习惯帮我在项目中避开过不少坑，也让我在面对慢查询时能快速定位到具体语句而不是盲目加服务器资源。
还有一个提升效率的小习惯：在电脑里维护一个自己的SQL速查笔记，不用写长篇原理，就记“什么问题用什么写法”。遇到同一种需求，直接翻自己的笔记比重新百度快得多，而且笔记里的每条SQL都是自己在业务环境里验证过的。从一张简单的订单明细表开始，把增删改查的每个细节都练扎实，你会发现后面学事务、锁、索引优化都有了一个坚实的落脚点。
