做后端开发的这些年以来MySQL 的增删改查应该是我写过的数量最多的一类 SQL也是面试里被问得最没脾气的基础题。但恰恰是这类人人都会的操作线上事故率反而最高有人一条 UPDATE 忘了 WHERE 把整表状态改错有人 DELETE 大表把数据库锁到报警还有人插入时没处理唯一键冲突导致业务数据重复。这篇内容不打算把增删改查的语法从头抄一遍而是从一张真实业务表出发把插入、查询、更新、删除背后那些写对的细节、容易翻车的场景以及我自己的处理套路完整讲一遍。适合刚入门 MySQL 的读者建立正确习惯也适合写过一段时间但总在边缘试探的开发者对照自查。1. 为什么号称最简单的增删改查事故率反而最高1.1 语法都会但写对是另一回事增删改查这四个动作本质上是任何业务系统的数据生命周期新增一条记录读取它修改它最后删除它。几乎所有 Java、Python、Go 项目都在做这件事。可一旦把会写 SQL等同于能做好数据操作问题就来了。我见过不少把 INSERT 当垃圾桶的写法不管数据有没有重复先插进去再说等到列表页出现两条一模一样的订单才去补去重逻辑也见过把 DELETE 当橡皮擦的玩法业务上要删掉这个用户就直接物理 DELETE结果关联表里一堆外键孤儿数据统计报表全乱套。这些问题的共性是什么是只把增删改查当成了语句没把它当成数据操作流程。一条简单的 INSERT 背后其实藏着几个决策点你要不要做唯一性校验要不要捕获主键冲突批量插入时是一次提交还是循环单条提交这些问题不思考清楚光靠语法正确上线以后照样会把数据库搞出各种状况。1.2 增删改查真正在考的是数据安全意识判断一个人是不是真的会用 MySQL 增删改查我通常不看语法而是看几个问题插入前你想过唯一键冲突吗还是赌运气查询时你只关心能不能查出数据还是关心走没走索引、回表几次更新时你的 WHERE 条件是否足够严谨有没有先 SELECT 确认过范围删除时你分得清物理删除、逻辑删除、TRUNCATE 的适用场景吗这些问题的答案决定了你所写的 CRUD 是测试环境能用还是生产环境可靠。接下来我会按照一张实际业务表从建表到删数据的完整链路逐一展开。2. 建表所有增删改查体验的源头2.1 表结构是 CRUD 的地基很多人写增删改查的第一反应是打开 Navicat 或 DataGrip 直接建表然后对着业务接口一顿操作。但我建议你反过来先想清楚这张表未来几年会被怎么增、怎么查、怎么改、怎么删再动手写 CREATE TABLE。因为表结构直接决定了后续所有 CRUD 的写法和效率。举个最直观的例子如果用户表的主键用的是 VARCHAR 手机号那么所有外键关联都会继承这个宽主键二级索引体积会明显大于 BIGINT 主键插入和查询速度都会受影响如果字符集用了 latin1存中文没问题但要存 emoji 就会报错。这些问题在写增删改查语句之前就已经埋下了后面怎么优化 SQL 都只是打补丁。2.2 一张用户表的建表 SQL 与字段设计细节我这里用一张最典型的用户表来演示包含账号、昵称、邮箱、状态、创建时间和更新时间。CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, username VARCHAR(50) NOT NULL COMMENT 用户名, nickname VARCHAR(50) DEFAULT NULL COMMENT 昵称, email VARCHAR(100) NOT NULL COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态: 1-正常 0-禁用, 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), UNIQUE KEY uk_username (username), KEY idx_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这里有几个值得说道的细节主键用 BIGINT UNSIGNED 自增而不是用业务字段。自增主键在 InnoDB 下是聚簇索引插入时顺序递增页分裂概率低写入性能稳定业务字段做主键一旦将来变更规则改动成本极高。字符集必须 utf8mb4。MySQL 的 utf8 实际上最多只能存 3 字节遇到 emoji 或者某些生僻字会直接写入失败所以新表一律 utf8mb4排序规则用 utf8mb4_unicode_ci 或更新一点的 utf8mb4_0900_ai_ci 都可以。唯一索引提前建。用户名这种天然有唯一性要求的字段一定要用 UNIQUE KEY 兜底。这不是给插入添麻烦而是给并发场景上保险后面讲 INSERT 时还会继续聊。created_at 和 updated_at 用 DEFAULT 自动维护。ON UPDATE CURRENT_TIMESTAMP 可以让你在更新行时不用手写更新时间省掉很多应用层代码。2.3 建表后立刻做这几件事建表不是写完 DDL 就结束了我习惯立刻执行几条语句确认表结构SHOW CREATE TABLE user; DESC user; SHOW INDEX FROM user;SHOW CREATE TABLE 能帮你确认最终的建表语句是否符合预期DESC 看字段顺序和类型SHOW INDEX 检查索引是否生效。这一步看起来多余但能提前发现字符集被库级别默认值覆盖、索引没建上等奇葩问题比上线后翻车强得多。3. INSERT不只是往里塞数据3.1 单条插入与批量插入的性能差异INSERT 是最容易被低估的操作。新手阶段我经常在循环里逐条插入插入一万条数据要执行一万次 SQL每次都要做网络往返、SQL 解析、事务提交慢得让人怀疑数据库是不是坏了。批量插入的效率要高得多INSERT INTO user (username, nickname, email, status) VALUES (zhangsan, 张三, zhangsanexample.com, 1), (lisi, 李四, lisiexample.com, 1), (wangwu, 王五, wangwuexample.com, 1);把多条记录合并成一条 INSERT 语句一次网络往返就能完成InnoDB 还可以针对连续插入做优化。实测下来同样一万条数据逐条插入可能需要几十秒批量插入往往一秒内就能完成。但批量插入也不是越大越好。一次塞几万条会导致单个事务过大undo log 膨胀还容易长时间占用表级锁或行锁把其他业务的 CRUD 拖慢。我一般建议单批控制在 500 到 1000 条具体情况根据行宽调整。行宽大、字段多的表批大小就得调小。3.2 主键或唯一键冲突时的三条路插入时最经典的问题就是记录可能已经存在你怎么办三种常见做法各有各的适用场景。方案行为适用场景INSERT IGNORE冲突时静默忽略不报错也不更新只想要不存在才插入的场景INSERT ... ON DUPLICATE KEY UPDATE冲突时执行指定的更新需要存在则更新不存在则插入的 upsert 场景REPLACE INTO先删旧行再插新行极少用会触发删除可能带来额外的锁和自增消耗举例说明。注册接口里要保证用户名唯一如果用户已存在想直接提示已注册用 INSERT IGNORE 配合受影响行数判断最合适INSERT IGNORE INTO user (username, nickname, email, status) VALUES (zhangsan, 张三, zhangsanexample.com, 1);执行后检查 MySQL 返回的 affected rows1 表示插入成功0 表示冲突被忽略。这种方式比先 SELECT 再 INSERT 更可靠因为 SELECT 和 INSERT 之间存在时间差并发场景下两个请求可能同时通过查重然后都去插入最终还是靠唯一索引兜底报错。而 upsert 场景则直接使用 ON DUPLICATE KEY UPDATEINSERT INTO user (username, nickname, email, status) VALUES (zhangsan, 张三, zhangsanexample.com, 1) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), email VALUES(email);这里有一个 MySQL 8.0.20 之后的注意点VALUES() 函数在行值语法里已经被标记为 deprecated官方推荐使用别名方式写法是AS new_user配合new_user.nickname。如果项目版本较新建议直接用新语法避免将来升级时收到一堆警告。3.3 REPLACE INTO 为什么我不推荐有些教程喜欢用 REPLACE INTO 做存在就更新我建议谨慎。它内部实现是先 DELETE 旧记录再 INSERT 新记录这意味着会触发外键的级联删除删除和插入是两步操作锁范围更大并发性能更差自增主键的 ID 会被消耗掉造成主键跳跃大部分存在则更新的需求用 ON DUPLICATE KEY UPDATE 就够了它是在原行上做更新不需要销毁重建。这是我踩过几次 REPLACE 的坑之后换过来的结论尤其是业务里有审计日志、有外键关联的表REPLACE 的破坏力是隐形的。4. SELECT查询的效能往往在写 SQL 之前就定了4.1 查询不只是能查出数据SELECT 是增删改查里出现频率最高的操作但也是很多性能问题的起点。我见过的低效查询五花八门SELECT * 把几十个字段全查出来WHERE 条件里对索引列做函数运算导致索引失效分页查询用 LIMIT 100000, 20 越翻越慢。先说字段选择。在业务代码里明确列出需要的列而不是 SELECT *有几点实际收益减少网络传输字节数、避免把不需要的大字段比如 TEXT、BLOB拖进内存、也能让代码审查的人一眼看出你拿到了哪些数据。只有排查问题时我才会上 SELECT * 快速看全貌正常业务 SQL 一定写明确列。再看 WHERE 写法。索引列一旦参与函数运算或隐式类型转换优化器很可能放弃索引。举例-- 这会全表扫因为对 create_at 用了 DATE 函数 SELECT * FROM order WHERE DATE(create_at) 2024-01-01; -- 这能走索引 SELECT * FROM order WHERE create_at 2024-01-01 00:00:00 AND create_at 2024-01-02 00:00:00;同样的查询意图第二种写法既不影响正确性又能利用索引做范围扫描。养成不在索引列上做无谓包装的习惯很多慢查询能少一大半。4.2 用 EXPLAIN 快速判断查询质量写一条 SELECT 之后我几乎强迫自己执行一次 EXPLAIN这比拍脑袋猜应该走索引了吧可靠得多EXPLAIN SELECT id, nickname, email FROM user WHERE email zhangsanexample.com;重点看几个关键字样的列type从好到差依次是 system、const、eq_ref、ref、range、index、ALL。出现 ALL 就说明全表扫描要警惕。key实际使用的索引名为 NULL 说明没走索引。rows优化器估计要扫描的行数这个数字和实际扫描量越接近性能预期越准。Extra出现 Using filesort 或 Using temporary 时通常意味着排序或去重用到了临时文件数据量大时性能会很难看。EXPLAIN 不是银弹它的 rows 是估算值不一定精确但用来排查慢查询、判断 SQL 改写方向已经足够了。我处理线上慢查询的第一动作永远是先拿慢 SQL 去 EXPLAIN 一遍看是不是没走索引再考虑要不要改 SQL。4.3 JOIN 和子查询的取舍没有绝对答案多表查询是 SELECT 里最容易产生认知偏差的地方。有些人信奉一律不用 JOIN遇到跨表就拆成多次查询在应用层组装也有人反过来万事 JOIN把十几张表连成一大坨。我的经验是先看数据量和关联方式再看索引情况不要走极端。两张表各自有索引的小表关联JOIN 完全没问题但如果一张表几千万行、一张表几万行JOIN 顺序和驱动表选错代价会非常大。实际工作里我更常做的是对复杂业务的查询先小数据集验证结果集再用 EXPLAIN 看执行计划必要时用 STRAIGHT_JOIN 或子查询改写来调整关联顺序。子查询也分情况。MySQL 8.0 的优化器对子查询的优化能力比 5.7 强很多但并非所有子查询都被优化得很完美。比如 IN 后面跟一个大表的子查询数据量上涨后效率可能明显下降改写为 JOIN 反而更高效。碰到具体问题建议用 EXPLAIN 对比改前改后的执行计划数据说了算。5. UPDATE更新数据时先想后果再动手5.1 忘记 WHERE 条件的典型现场更新操作出事故的概率在四个操作里排第一原因很简单INSERT 的后果是多了条数据查询的后果是慢删除的后果一般有提醒而 UPDATE 一旦漏了 WHERE影响面是全部行有时候直到数据错得离谱才被发现。我印象很深的一次故障同事想更新某个测试账号的状态写的是UPDATE user SET status 0 WHERE username test01但执行的时候发现测试库里这个用户已经被删掉了症状是更新了 0 行任务没报错但数据没变。于是他加了句算了直接更新所有测试账号吧SQL 改成UPDATE user SET status 0 WHERE username LIKE test%。结果执行时忘了加用户名条件整张表的用户状态全部变成禁用。那次事故虽然没有线上影响但给团队上了一课UPDATE 前必须先在测试环境执行同条件 SELECT确认影响行数。5.2 四条保命法则我个人总结的 UPDATE 安全守则如下先 SELECT 后 UPDATE先用完全相同的 WHERE 条件执行 SELECT确认要影响的行数再去 UPDATE。WHERE 条件必须收窄能用主键、唯一索引定位就不要用模糊条件范围越大风险越高。搭配 LIMIT 控制影响行数如果只打算更新一条就写UPDATE ... WHERE ... LIMIT 1。MySQL 的 UPDATE 是支持 LIMIT 的这能兜住条件写宽了的情况。观察 affected rows执行完看一眼受影响行数和预期对不上就立刻回查数据。5.3 批量更新的温和方案批量更新常见需求是根据一组 ID 更新各自不同的字段值。有些人会写循环一条条 UPDATE性能差也有人试图拼一条超级 UPDATE用 CASE WHEN 实现UPDATE user SET status CASE id WHEN 1 THEN 0 WHEN 2 THEN 1 WHEN 3 THEN 0 END WHERE id IN (1, 2, 3);这种写法只用一条 SQL 完成多条记录的差异化更新比循环更新高效得多。但要小心如果 WHERE 里的 ID 列表漏了某个 ID那这个 ID 的 status 会被更新成 NULL因为 CASE 没有匹配到任何分支时返回 NULL。所以写这种 SQL 必须保证 IN 列表和 CASE 分支完全覆盖并且每条分支都有明确值。对于更新量更大的场景比如几十万行一条 UPDATE 锁定的行太多容易造成长时间锁等待。我一般会按主键分段循环更新比如每次更新 1000 条配合小事务提交既能控制锁粒度也能避免 undo log 暴涨。6. DELETE数据清理的边界感6.1 DELETE、TRUNCATE、DROP 要分清楚删除相关的命令有三兄弟很多人混着用实际上适用场景差别很大操作特点适用场景DELETEDML逐行删除可加 WHERE可回滚不重置自增值删除指定业务数据TRUNCATEDDL清空全表速度极快隐式提交不可回滚重置自增值清空临时表、快速重建空表DROPDDL删除整张表结构和数据废弃不再需要的表TRUNCATE 在某些场景下很诱人比如清空日志表但它有一个坑它是隐式提交的一旦执行无法通过事务回滚而且会重置 AUTO_INCREMENT。如果只是要清数据但保留表结构给后续使用一定要想清楚是否能接受不可回滚。6.2 大表删除的隐蔽代价DELETE 大表时真正的开销不只是删行本身还涉及三个层面undo log 膨胀被删除的行需要记录 undo 信息以支持并发读删几十万行会产生大量 undo可能拖慢同一实例上的其他查询。锁范围扩大InnoDB 默认会对 DELETE 扫描到的行加锁范围越大锁越多可能出现锁等待甚至死锁。产生碎片物理删除后页内出现空洞后续插入可能造成页分裂表空间膨胀。所以线上删除大量数据我的做法是分批删DELETE FROM user WHERE status 0 AND id 10000 LIMIT 1000;每批删 1000 行左右观察删除耗时和数据库负载再循环执行直到删除行数影响为 0。这一步看起来笨但能显著降低对业务的影响。配合在业务低峰期执行效果更好。6.3 能被数据恢复兜底的软删除业务系统里我越来越推荐软删除也就是加一个字段标记删除状态而不是物理 DELETE。最常用的是deleted_at字段为 NULL 表示未删除非空表示删除时间ALTER TABLE user ADD COLUMN deleted_at DATETIME DEFAULT NULL COMMENT 软删除时间;执行软删除只是更新UPDATE user SET deleted_at NOW() WHERE id 10086;以后所有业务查询默认带WHERE deleted_at IS NULL条件。软删除的优点是数据还在误删可以恢复缺点是所有查询都要记得加条件漏加就会出现已删除用户还出现在列表里的 bug。为了防漏我通常会在表上建一个联合索引或者视图把未删除的过滤条件统一封装让业务尽量走同一入口。7. 事务把多条 CRUD 变成一句要么全成要么全败7.1 为什么单条 UPDATE 也可能需要事务我评估一条 SQL 是否需要事务看的不是语句数量而是逻辑上的原子性。比如转账这个经典例子A 账户扣 100 元、B 账户加 100 元这明明是两条 UPDATE但它们必须同时成功或同时失败。如果不用事务扣款成功、入账失败账就对不上了。就算是单条语句也可能有隐含的多步逻辑。比如插入订单后还要更新库存插入日志表后还要更新统计表。只要数据存在逻辑依赖就应该用事务把它们包成一个整体。7.2 隔离级别不是越严越好MySQL InnoDB 默认隔离级别是 REPEATABLE READ它通过多版本并发控制MVCC和间隙锁让普通 SELECT 不会被其他事务的未提交修改影响。理解隔离级别对写 CRUD 很重要因为不同级别下你看到的数据一致性是不同的。隔离级别脏读不可重复读幻读说明READ UNCOMMITTED可能可能可能基本不用READ COMMITTED否可能可能多数其他数据库默认REPEATABLE READ否否在 InnoDB 下基本防住MySQL 默认SERIALIZABLE否否否并发极低业务开发里我几乎不会去改全局隔离级别默认的 RR 在大多数场景下表现良好。但要注意一点RR 下的当前读比如 SELECT ... FOR UPDATE会加锁锁的范围可能包括条件涉及的间隙如果两个事务以不同顺序更新相近的记录比较容易出现死锁。遇到死锁别慌错误码是 1213MySQL 会回滚其中一个事务业务层做好重试即可。7.3 一个标准的业务事务示例把用户下单过程中涉及的插入、更新操作包起来START TRANSACTION; INSERT INTO order (user_id, amount, status) VALUES (10086, 99.00, 0); UPDATE user SET balance balance - 99.00 WHERE id 10086; COMMIT;如果中途任何一步失败执行 ROLLBACK前面的操作全部回滚。这里有两个容易忽略的地方事务里不要夹杂远程调用比如调用第三方支付接口、发短信通知这些外部操作不受数据库事务控制一旦外部成功而数据库回滚会产生对不上的数据。正确做法是数据库事务先落账再异步执行外部调用并借助对账任务保证最终一致。长事务是性能杀手事务里开了事务却迟迟不提交会一直持有锁、堆积 undo 日志拖垮整个实例。写代码时尽量让事务体量小、时间短不要在事务里做耗时的业务计算。8. 几个印象深刻的线上故障复盘与自查清单8.1 三个真实故障复盘第一个故障是更新条件导致的全表更新。背景是一个管理后台的批量禁用功能前端传过来一组用户 ID后端拼 SQL 时 ID 列表为空代码里做了个if (ids.isEmpty()) return的判断。有一次参数传递异常ids 为 null代码走了另一个分支直接把 WHERE 条件跳过了一条UPDATE user SET status 0就把全表用户禁用了。自那以后我要求所有更新接口的 SQL 参数必须做显式非空校验并且在 DAO 层拒绝无条件更新的执行。第二个故障是删除历史数据引发的锁等待。当时想把半年前的日志数据清掉直接跑DELETE FROM operation_log WHERE create_at DATE_SUB(NOW(), INTERVAL 180 DAY)。日志表几千万行这条 SQL 锁了大量行运行了四分多钟期间一堆业务请求被阻塞数据库连接数飙到上限最终直接影响了线上服务。后来改成按天分批删除每次删 5000 行、间隔几秒对业务影响降到了几乎可以忽略。第三个故障是批量插入把从库拖慢。数据导入任务用一条 INSERT 塞了五万行主库执行得很快但从库在应用 relay log 时压力过大复制延迟飙到几百秒导致一段时间内读请求查不到刚写入的数据。排查后把批大小调到 1000 行一批从库延迟立刻恢复正常。批量大小真的不是越大越快要综合主库性能、从库复制能力、网络带宽一起考虑。8.2 我现在的 CRUD 自查清单写了这么多最后分享一套我每次上线前都会过一遍的自查清单算是这些年用真金白银换来的习惯INSERT唯一键冲突有考虑吗批量插入的批次大小合理吗字符集能覆盖实际写入内容吗SELECT查询列明确了吗WHERE 条件能走索引吗EXPLAIN 的 type 达到 ref 或 range 了吗分页大偏移有优化方案吗UPDATE先 SELECT 确认影响行数了吗WHERE 条件足够收窄吗有条件不用 LIMIT 兜底吗涉及金额、库存的更新是原子表达式吗比如balance balance - 100DELETE业务上能用软删除吗物理删除有没有分批TRUNCATE 之前确认过不可回滚吗事务逻辑上需要原子性的操作都包进事务了吗事务里有远程调用吗事务体量足够短吗通用项字段类型和业务值域匹配吗时间字段有时区问题吗字符集排序规则统一吗这套清单不一定覆盖所有场景但能挡住大多数低级事故。增删改查之所以值得反复讲是因为它是数据系统的最小单元把每个单元都写稳了上层业务才能睡得着觉。做 MySQL 开发这几年我最大的体会就是越基础的东西越值得用敬畏心去写——一条 UPDATE 背后可能是整个公司的用户数据一条 DELETE 背后可能是半年都找不回来的审计记录。把这些小事做严谨比追求花哨的 SQL 技巧更能体现一个开发者的成熟度。