MySQL 表的增删改查尤其是“增删改查”这四个字做开发的人没几个不会写但能保证每个操作在确实该用的时候用了正确方式、在出问题的时候能快速意识到原因的人真不多。这篇文章想聊的不是教科书式的语法列表而是把这四个动作拆开看每一个操作背后的设计考量是什么实操时哪些步骤容易踩坑出了问题怎么定位和兜底。如果你已经写过一段时间的 SQL但对细节不太有把握或者被线上数据问题教训过那我写的这些经验应该能帮上忙。1. 把表的 CRUD 当成一个系统来理解1.1 CRUD 不光是四个单词是你和数据之间的一整套约定很多人把增删改查理解成“往表里写数据、读数据”其实没那么简单。每一次 INSERT、SELECT、UPDATE、DELETE背后都牵扯到表结构设计、索引利用、事务边界、锁范围、日志行为这些内容。哪怕你只做一个很小的功能模块牵一发而动全身。我习惯把一张表理解成一个带规则的仓库表结构就是货架的列、货物品类、摆放规则索引就是货架上的编号标识事务就是一次发货/入仓操作锁就是操作时占用的通道。仓库存取货物的顺畅程度取决于规则是否合理而不只是操作动作是否标准。这也是为什么我会建议你在学 CRUD 之前先好好看看字段类型、字符集、约束、索引、存储引擎这些看起来是“建表时的事”的东西因为它们会直接影响后面增删改查的效率和稳定性。1.2 先明确存储引擎、字符集和约束后面会省很多事实际操作里绝大多数业务表我都用 InnoDB原因很直接支持事务、支持行级锁、崩溃后有能力恢复。MyISAM 虽然读快但没有事务和行锁一句 UPDATE 可能就把整表锁了一旦线上有写入需求很容易被坑所以现在谨慎选择。字符集我建议默认 utf8mb4自 MySQL 5.7 之后这基本是行业共识。utf8mb4 是真正的四字节字符集能存表情符号、生僻字而 utf8 在很多版本里是三个字节存不了这些强行插入会报错或者丢数据。我遇到过因为字符集不一致导致两张表关联查询查不到数据的情况排查半天最后发现是两张表的排序规则不一致join 时索引加不上去。约束方面我在建表时总会想清楚这几个点主键必须存在最好用自增整数或有序业务 ID不用 UUID 当主键因为无序字符在插入时会导致页分裂性能掉得厉害。有唯一性的字段直接建 UNIQUE KEY后续才能安全使用“重复即更新”的写入方式。业务字段设置 DEFAULT 值和 NOT NULL避免应用层漏传字段导致脏数据出现。时间字段统一用 DATETIME 或 TIMESTAMP并设置 DEFAULT CURRENT_TIMESTAMP省去应用层手工塞时间。1.3 用什么工具实操会直接影响你的习惯我见过很多人在客户端工具里直接编辑表数据或右键删除这确实方便但也容易把很好用的 SQL 手感丢掉。我的建议是本地开发时用命令行或任何熟悉的客户端(比如某数据库可视化工具)执行 SQL但线上操作一律通过执行预先准备好的 SQL 语句来做关键写入动作绝不手动右键改。不要小看这个习惯客户端工具偷偷做的批量更新、生成的不带 WHERE 的 UPDATE都可能成为事故的导火索。所有手动操作先 SELECT 验证目标数据再执行操作最后检查影响行数。2. 新增数据INSERT 的用法和性能上的取舍2.1 全字段简写和显式列名选哪个INSERT INTO user_info VALUES (zhangsan, zsexample.com, ...) 这种简写虽然字少但极度依赖表结构顺序一旦表加了字段或者字段顺序调整语句就彻底错位。我现在写 INSERT 基本都会用列名方式INSERT INTO user_info (user_name, email, status) VALUES (zhangsan, zsexample.com, 0);这样做的好处至少有四个语句不依赖字段顺序只看字段名新增字段后旧 SQL 不受影响代码审读时一眼能看出写入字段的语义DEFAULT 字段可以完全不写让数据库自己兜底。2.2 批量写入顺序和批量大小都有讲究很多新人喜欢在循环里一条接一条执行 INSERT性能糟糕且连接开销巨大。正确方向是批量插入INSERT INTO user_info (user_name, email, status) VALUES (lisi, lisiexample.com, 0), (wangwu, wangwuexample.com, 0), (zhaoliu, zhaoliuexample.com, 0);一个常规做法是每批 500 到 1000 行左右既能减少网络往返也不会让单条 SQL 过大导致解析和同步压力暴涨。批量大小不是越大越好我测试过超过一定行数之后单条 SQL 的解析成本、binlog 大小、主从同步延迟都会明显上升尤其是 MySQL 默认 max_allowed_packet 有限制单条太大可能直接报错。另外批量写入时要知道一个隐含风险一条语句涉及多个行如果中间某一行因为约束失败要么整批回滚要么部分插入取决于是否启用了事务包装。所以建议把批量插入放进事务里执行失败时统一回滚不会留下半截数据。2.3 幂等写入ON DUPLICATE KEY UPDATE 和 INSERT IGNORE很多时候业务要求是“有则更新无则插入”最常见做法就是 INSERT ... ON DUPLICATE KEY UPDATEINSERT INTO user_info (user_name, email, status) VALUES (zhangsan, zsexample.com, 0) ON DUPLICATE KEY UPDATE email VALUES(email), status VALUES(status);这样遇到唯一键冲突时会走更新路径而不是抛出异常。需要注意 VALUES() 用法在旧版本中常用于引用新插入值如果是在一些新版本里官方更推荐用别名方式例如用 AS new 来访问新行不过实际线上老库大多兼容前一种写法。INSERT IGNORE 是另一个选择遇到冲突直接忽略写入不报错。但它最大的问题是容易掩盖问题如果冲突是因为主键重复但语义上应该报错IGNORE 会悄悄吞掉最终产出的数据和你预期的不一致。所以我在真实业务中极少用 IGNORE 去“防重复”更愿意用 ON DUPLICATE KEY UPDATE 或先 SELECT 再决定。2.4 别忽略字符集、NULL 和默认值INSERT 的时候如果传入字符串含中文、表情符号而连接字符集和表字符集不一致最常见的结果是乱码和报错。排查方式是检查连接配置里 character_set_client、character_set_connection 与表字符集是否一致。MySQL 8.0 默认已经是 utf8mb4但老库迁移或者 Navicat 连接串没指定时还是容易出问题。NULL 也是一个容易被忽视的坑。如果业务字段允许为空应用层某些语言会把空字符串和 NULL 混淆。数据库里空字符串是有效值NULL 是“未定义”索引行为、聚合函数 COUNT 的表现都不一样。写入时宁可显式传值也不要放任默认值乱跑。3. 查询数据SELECT 不只是把数据拉出来3.1 WHERE 条件下的隐性杀手函数、隐式转换、字符集SELECT 最核心的价值是“快而准”。SQL 写得对不对直接影响返回结果写得好不好则直接影响数据库负载。我总结了几条最容易让索引失效的写法第一条就是对索引列使用函数。比如 WHERE DATE(created_at) 2025-01-01这个写法看起来没毛病但数据库必须对整表每行先算函数再比较索引完全帮不上忙。正确做法是改成范围条件WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00第二条是隐式类型转换。一个 varchar 字段传数字进去比如 WHERE id 123虽然 MySQL 有可能走索引但条件字段如果本身是 varchar 而传入数值部分情况下会导致索引失效或者不必要的转型消耗。第三条是排序和查询字段的字符集排序规则不一致导致 join 和索引失效这前面提过。3.2 排序与分页深分页问题必须处理分页查询是日常用的最多的操作之一。基础写法是 ORDER BY id DESC LIMIT 20 OFFSET 2000但一旦页码往前走OFFSET 越来越大MySQL 需要扫描并丢弃前面大量行速度会明显劣化。我常用的优化办法是“延迟关联”先通过索引定位到主键再用主键去关联回表SELECT u.id, u.user_name, u.email FROM user_info u JOIN ( SELECT id FROM user_info WHERE status 0 ORDER BY id DESC LIMIT 20 OFFSET 100000 ) tmp ON u.id tmp.id ORDER BY u.id DESC;如果分页条件里带了 WHERE id last_id 的标识位还可以直接用“游标分页”SELECT id, user_name, email FROM user_info WHERE status 0 AND id 100000 ORDER BY id ASC LIMIT 20;这样不管往后翻多少页性能都稳定。3.3 需要筛选的列尽量少用 SELECT *SELECT * 最大的问题是一是不需要读的字段也发到客户端网络开销和无用内存浪费二是在多表 join 时字段名冲突难排查三是当表结构后来新增字段时如果应用层对列序敏感会产生不可预期的问题。我在写业务查询时几乎不用 SELECT *顶多是在快速排查数据时用一下并且只在行数很少的前提下用。需要检查查询是否走索引时直接在语句前加 EXPLAIN 是最快的办法EXPLAIN SELECT id, user_name FROM user_info WHERE status 0 ORDER BY id DESC LIMIT 10;重点看 type 列是不是 range 或 refkey 列是否用了预期索引rows 是不是过大。实际上我遇到很多慢查询只要一加 EXPLAIN问题就一目了然。3.4 连接查询与子查询的使用边界连接查询在数据量不大的场景下看不出区别但在数据量上来之后驱动表的选择和索引设计就非常关键。通常小表驱动大表是比较稳的策略而子查询在 MySQL 老版本里执行计划不一定优化得很好容易产生临时表。我现在遇到复杂查询时优先考虑拆解先用一条 SQL 算结果集再顺着应用层组装数据。这不只是为了性能也为了逻辑可读性。单条大而全的 SQL 在排查问题时往往比几条小 SQL 麻烦得多。4. 更新操作UPDATE 的核心是“确认行数”而不是“写代码”4.1 WHERE 是命门先 SELECT 再 UPDATE我没见过哪个 UPDATE 事故不是因为 WHERE 条件问题。最简单的执行 UPDATE user_info SET status 1直接全表更新线上直接崩。更隐蔽的是 WHERE 条件写得不准确更新了不该更新的行。我的习惯是UPDATE 之前先把 WHERE 条件原封不动用于 SELECT确认目标就是预期中的范围然后执行 UPDATE执行完再看 ROW_COUNT() 是否和预期一致。-- 第一步确认影响范围 SELECT id, user_name, status FROM user_info WHERE email zhangsanexample.com; -- 第二步执行更新 UPDATE user_info SET status 1 WHERE email zhangsanexample.com; -- 第三步确认影响行数或回查数据 SELECT ROW_COUNT();如果 ROW_COUNT() 返回的是 1而业务预期有 3 条就必须停下来查原因不能盲目重试。4.2 多表关联更新别把逻辑堆在一起多表关联更新在语法上是允许的UPDATE user_info u JOIN user_extra e ON u.id e.user_id SET u.status 1 WHERE e.score_count 0;它能减少应用层多次查询和更新的网络开销但也加大了排查难度如果 update 的行数不对你很难判断是关联条件错误还是 WHERE 条件不完整。我的建议是先在 SELECT 中用同样的 join 条件和 where 条件查一遍目标行数再决定是否执行 UPDATE。4.3 大表更新的分批策略和锁问题一次 UPDATE 上百万行不只是慢那么简单。它会产生大事务持有大量行锁甚至阻塞其他正常业务写入同时同步到从库也会拖后腿。正确做法是使用分批更新每批更新一小部分比如一千或两千行然后停一小段时间让主从同步喘口气再继续下一批。UPDATE user_info SET status 1 WHERE id IN ( SELECT id FROM ( SELECT id FROM user_info WHERE status 0 ORDER BY id ASC LIMIT 1000 ) tmp );这里包一层的目的是让 MySQL 不要直接在 UPDATE 的目标表上进行子查询避免锁问题或 “You cant specify target table for update in FROM clause” 类错误。分批更新期间最好配合监控主从延迟延迟过高就先暂停。4.4 更新频率高的表要警惕“写放大”一个表的多个索引在更新时都需要维护尤其是字段被多个索引引用更新一次会带来多次索引写入。这是物理上的写放大。如果你发现一个表的 UPDATE 非常慢除了索引太多还有可能是行长度变化导致页分裂。解决办法不是不做 UPDATE而是根据业务把更新频率高的字段组合到少数索引中避免无关索引也跟着维护。5. 删除操作DELETE 的前提是“你有后悔药”5.1 DELETE、TRUNCATE、DROP 三者要分清删除可能是最容易出事故的操作没有之一。先明确三个词的边界DELETE 是 DML按条件逐行删除走事务可以回滚行数大时速度慢会产生 binlog。TRUNCATE 是 DDL清空整表速度快不逐行记录无法按行回滚使用时必须非常慎重。DROP 是 DDL直接把表结构整个删掉表、索引、约束、存储过程联动全没了。我见过最危险的操作就是把 TRUNCATE 当成 DELETE 用一条下去表空了而且难以恢复。业务上真正需要清空全表的情况非常少一旦有这种需求先备份表或用一个新表名做切换再考虑处理旧表。5.2 删除数据先考虑“软删除”很多业务场景数据并不是物理删除而是逻辑删除。做法就是加一个 deleted 或 is_deleted 字段更新置为 1查询时统一带条件。ALTER TABLE user_info ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0 COMMENT 软删除标记; UPDATE user_info SET deleted 1 WHERE id 123;这样做最大的好处是保留完整历史数据方便审计、恢复、回溯。坏处是查询时需要额外过滤同时也让唯一约束变得难处理——比如用户删了账号再次注册时用户名被唯一键挡住。常见解法是 deleted_at 时间戳参与唯一索引一旦删除原用户名的唯一性被解除新用户还能用相同用户名注册。5.3 物理删除的操作底线分批 凌晨执行如果业务确认需要物理删除我会按这么几步走备份相关数据要么备份全表要么把将要删除的主键列表先导出到临时文件。在低峰期分批 DELETE每批 500 到 2000 行观察主从延迟。每次分批之间加 sleep避免持续占用写入通道。DELETE FROM user_info WHERE created_at 2024-01-01 AND id IN ( SELECT id FROM ( SELECT id FROM user_info WHERE created_at 2024-01-01 ORDER BY id LIMIT 1000 ) tmp );DELETE 和 UPDATE 的分批思路一致核心原则是不能让一个超大事务钳制整个数据库。5.4 误删之后的止损思路误删之后能不能救回取决于三个因素binlog 是否开启、binlog 格式和保留时长、是否有备份。MySQL 的 binlog 如果开启并保持 ROW 格式那么DELETE 之前的数据完整记录在二进制日志里理论上可以用工具解析出原始数据做反向恢复。但这个过程对大多数人来说比较复杂更何况线上未必有全量备份。我的建议是所有 DML 操作之前先拿到“目标行的完整备份”至少是 SELECT 结果存入临时表或文件。这样即便误删也能靠备份恢复而不用走到 binlog 逆向解析这一步。多次线上事故让我养成这种习惯之后日子轻松了很多。6. 常见问题与排查思路速查6.1 增删改查场景下的典型故障现象可能原因排查思路INSERT 报字符集/乱码问题连接字符集与表字符集不一致查 character_set_client、表缺省字符集、连接配置INSERT 报唯一键冲突业务数据本身重复或使用场景需要幂等检查约束和业务幂等策略改用 ON DUPLICATE KEY UPDATE查询突然变慢索引失效、统计信息过旧、深分页EXPLAIN 查看 key 列、type重构查询条件或做延迟关联UPDATE 影响的行数比预期多WHERE 条件不精确先回查 SELECT 影响范围检查条件里空值、边界值表删除后数据找不回没有备份或 binlog 未开启只能通过备份分层恢复平时养成备份习惯一张表长时间锁住大事务、长查询、锁等待SHOW PROCESSLIST 看线程状态找到阻塞源头会话6.2 一个典型的现场排查案例某业务后台反馈用户列表查询越来越慢一个只有几十万行的表LIMIT 10 OFFSET 999999 的查询竟然要好几秒。我用 EXPLAIN 看了执行计划发现扫描行数几十万并且 Using filesort。问题很明显分页条件依赖 ORDER BY update_time但这个字段没有索引MySQL 只能先把把整个结果集排好序再作分页。我的处理方式是给排序字段建索引同时把分页改成基于主键的游标方式配合延迟关联。改造后同一查询耗时回到毫秒级。这件事给我的经验是查询慢不一定要重写业务逻辑先看执行计划里索引和排序有没有被正确利用。6.3 数据操作安全意识最后这点值得反复强调数据库操作最怕的不是你不会写 SQL而是你以为你懂了。我见过不少老手栽在 DELETE 漏了 WHERE、UPDATE 条件写错、事务开太大这些基础问题上。我现在给自己定的规矩很简单也推荐你参考任何不小心影响全局的操作第一步先备份。任何 UPDATE 和 DELETE写完先 SELECT 同条件确认。任何大范围操作走分批绝不让一个大事务压住线上链路。任何时候都要确认影响行数和预期不一致就是危险信号。包括我自己在内不少人都不是被复杂的性能问题绊倒的而是被最简单的那一行 WHERE 绊倒的。MySQL 的增删改查说起来就四个动作但每一个动作背后都需要对数据保持敬畏心。建表时多花五分钟设计约束写 SQL 时多花五秒确认条件出问题时才能少熬五个小时。希望这篇内容对你接下来的实际操作有点帮助哪怕只让你多了一次想 oping 先 SELECT 的动作也算没白看。
