增删改查这四件事，几乎所有接触MySQL的人第一天就会写。INSERT、SELECT、UPDATE、DELETE，单独拿出来看每一句都简单，但真正在生产环境里把它们用好，需要知道的东西远不止"会写"而已。我自己见过不少项目，刚上线的时候增删改查很顺，等到数据量上来、并发一高，问题全从这四句话里冒出来。这篇文章就围绕MySQL里最基本的增删改查操作，把每一步可能踩的坑、应该养成的习惯、以及背后的原理一起梳理一遍，适合刚学数据库的同学，也适合写了一阵子SQL但没系统整理过的开发者。
1. 先给增删改查定个位：四句话背后是数据的生命周期
1.1 CRUD为什么"基础却不简单"
MySQL里最常见、也最基础的操作就是增删改查，行业里常把它简称为CRUD，也就是Create、Read、Update、Delete四个单词的首字母。很多人觉得它是SQL入门阶段就会的东西，没必要花时间研究。但实际工作中我发现，恰恰是基础操作最容易写出"看起来对、跑起来慢、出了问题难查"的SQL。
原因在于，增删改查不只是四句命令，它覆盖了数据从写入、读到修改、删除的完整生命周期。你的所有业务逻辑最终都会映射到这四类语句上，一旦某个环节考虑不周，影响的是整条链路。比如你写了一条INSERT，没注意到唯一索引冲突，结果就是用户反复注册成功；你写了一条UPDATE，漏了WHERE条件，结果就是全表数据被改掉。这些事故追根溯源，问题都不在SQL语法上，而在对操作边界的理解上。
1.2 一个业务场景把四个操作串起来
拿最常见的用户下单举例。用户注册时，系统执行INSERT把用户信息写进user表；订单列表页需要读出当前用户的订单，执行SELECT；支付成功后要更新订单状态，执行UPDATE；如果用户申请退款并取消订单，触发的可能是DELETE。这四个操作在一个业务里循环反复，任何一个环节出现数据问题，比如重复插入、读到脏数据、更新丢失、删除误伤，都会直接反馈给用户。
所以我一直建议学习者不要孤立地背语句，而是从业务角度理解"增删改查在解决什么问题"。这样写出来的SQL才有边界感，你知道一条语句会影响哪些表、哪些行，也就会主动去想"它会不会误伤别的东西"。
2. INSERT：把数据写进表里，先搞清楚字段和值的对应关系
2.1 VALUES写法的两种形式与字段省略风险
先看最基本的单行插入：
INSERT INTO user (username, age, email) VALUES ('张三', 28, 'zhangsan@example.com');这段SQL把一条用户记录写进user表。注意我显式列出了username、age、email三个字段，这是更推荐的写法。MySQL也允许省略字段列表直接写：
INSERT INTO user VALUES (1, '张三', 28, 'zhangsan@example.com');省略字段版本的缺点是，它要求VALUES里的数据顺序、数量和表结构完全一致，一旦表结构调整，比如新加了一个字段，这种SQL就会报错，更糟糕的情况是不报错但把值写错列。显式列名不仅让语句可读性更强，也避免表结构变化导致的低级错误。这个习惯在维护时间较长的项目里价值非常大，因为线上表结构隔几个月加个字段是很常见的事。
2.2 批量插入与INSERT...SELECT
实际业务里经常需要一次写入多条数据，比如批量导入用户。MySQL支持一条INSERT写入多行：
INSERT INTO user (username, age, email) VALUES ('张三', 28, 'zhangsan@example.com'), ('李四', 32, 'lisi@example.com'), ('王五', 25, 'wangwu@example.com');一条INSERT写入多行，相比循环执行单条插入，减少了客户端与服务器之间的网络往返，速度提升非常明显。但注意，批量插入并不是"越多越好"，行数过大时单条SQL执行时间变长，锁持有时间随之变长，对高并发写入的表反而是负担。我通常控制在500到1000行一批，具体还要看单行字段多少和服务器的写入能力。
INSERT...SELECT用来把已有查询结果写入另一张表，在做表归档、备份或者初始化派生表时非常实用：
INSERT INTO user_bak (username, age, email) SELECT username, age, email FROM user WHERE age > 30;这个语句的执行逻辑是先跑SELECT，再把结果插入目标表，所以目标表不要和SELECT来源表是同一张正在被业务大量修改的表，否则可能产生重复数据或引发锁等待问题。曾经有同事用它给线上表做数据迁移，SELECT没加WHERE，直接把几百万行原表数据插到了目标表，结果目标表瞬间多出几百万条记录，主键还冲突，花了很久才清理干净。
2.3 插入时最常见的三类失败
实务中INSERT报错就那么几类，提前知道能少走很多弯路。
第一类是主键或唯一索引冲突，错误信息形如Duplicate entry '1' for key 'PRIMARY'，表示主键或唯一索引已存在相同值。这通常意味着业务上有重复插入，解决办法是按业务逻辑选择：直接返回提示让用户知道、用INSERT ... ON DUPLICATE KEY UPDATE更新已有记录，或者用INSERT IGNORE忽略本次插入。其中ON DUPLICATE KEY UPDATE在实际项目里非常常用，因为它能实现"有则更新、无则插入"的幂等语义。
第二类是非空约束不满足，报错Field 'xxx' doesn't have a default value。某个字段没给默认值又没传值就会出现。这类问题在表设计阶段就要想清楚：哪些字段允许为空、哪些必须有默认值，不能完全指望应用层每次都传对参数。
第三类是字符集问题，插入中文出现乱码或报Incorrect string value，十有八九是表、连接或字段字符集不一致。统一使用utf8mb4可以避免绝大多数情况，同时连接字符集也要设置正确，命令行会话里可以通过SET NAMES utf8mb4快速排查。别小看字符集，它引发的数据事故往往隐蔽又难清理。
3. SELECT：查询质量决定你离真相有多近
3.1 SELECT *与指定列：不只是性能洁癖
查询最容易出问题的不是语法，而是习惯。SELECT *在新手代码里出现频率极高，虽然它在行数少的表上跑起来看不出什么异常，但它会把表的所有列都查出来，哪怕业务只需要两三个字段。这会导致网络传输量增加，也无法利用覆盖索引来减少回表。
简单解释一下覆盖索引：索引本身就包含查询需要的字段，MySQL可以直接从索引读取结果而不必回到主表。如果你经常查username和age，又建了复合索引(username, age)，那么SELECT username, age ...走索引就直接出结果；SELECT *则大概率要回表查完整行。数据量小的时候无所谓，数据量大了，回表次数多出来的时间就是实打实的延迟。
从维护角度讲，显式列出字段的SQL也更清晰。别人看代码时能立刻知道这条查询需要哪些数据，而不是先去看表结构里有哪些列。
3.2 WHERE条件：存储引擎的筛选艺术
WHERE是查询里最核心的部分。一个常见问题：字段类型是varchar，查询时用数字比较：
SELECT * FROM user WHERE phone = 13800138000;假如phone字段是varchar类型，MySQL可能会把phone转成数字再比较，导致该字段上的索引失效，查询退化成全表扫描。正确写法是带上引号：
SELECT * FROM user WHERE phone = '13800138000';这种问题的可怕之处在于它不报错，结果也经常是对的，但性能悄悄恶化。数据量小的时候感觉不到，一旦表到了几百万行，一个本该走索引的查询变成全表扫描，响应时间直接从毫秒级跳到秒级。
判断索引有没有用上，最直接的手段是EXPLAIN。我执行任何一条耗时异常或者逻辑复杂的SELECT，都会先看EXPLAIN的输出，重点看type列，从const、ref、range到ALL，越靠后越危险。type=ALL意味着全表扫描，数据量大的时候基本等于灾难。关于EXPLAIN，后面章节还会再展开。
3.3 排序与分页：LIMIT越翻越慢的真相
应用里最常见的分页写法是：
SELECT * FROM user ORDER BY id DESC LIMIT 10, 20;它表示跳过前10行，取接下来20行。这个写法在小数据量时没问题，但当页码很大时，比如LIMIT 100000, 20，MySQL依然要先读取前100000行再丢弃，越往后越慢。问题不在LIMIT本身，而在偏移量越大，扫描的无用行就越多。
常见的优化思路有两种。一种是用覆盖索引先拿到主键，再做回表关联：
SELECT u.* FROM user u JOIN (SELECT id FROM user ORDER BY id DESC LIMIT 100000, 20) t ON u.id = t.id;另一种更彻底，如果业务允许，改成"上一页最大ID"的游标式分页。按id倒序翻下一页时，用WHERE id < 上一页最后一条的id ORDER BY id DESC LIMIT 20，数据库永远只扫描20行，性能稳定。这种方案在移动端下拉加载和后台列表里都很实用，缺点是跳页不方便，需要产品侧配合。
3.4 聚合查询：GROUP BY和HAVING的配合逻辑
统计场景离不开聚合。比如统计每个年龄段的人数：
SELECT age, COUNT(*) AS cnt FROM user GROUP BY age;GROUP BY把age相同的行分到一组，COUNT(*)统计每组行数，这类查询配合索引会更快。如果要在分组之后做过滤，要用HAVING而不是WHERE：
SELECT age, COUNT(*) AS cnt FROM user GROUP BY age HAVING cnt > 1;WHERE是分组前过滤，HAVING是分组后过滤，这个顺序很多人会混淆。本质上，WHERE作用于原始行，HAVING作用于分组结果，二者不能互相替代。另外需要注意，MySQL的ONLY_FULL_GROUP_BY模式下，SELECT的字段必须出现在GROUP BY中或者是聚合函数，否则会报错。这是SQL规范化的体现，虽然一开始会觉得麻烦，但能从语法层面杜绝一批"能跑但逻辑不严谨"的查询。
4. UPDATE：改数据之前先想清楚边界
4.1 常规更新与表达式更新
更新一条数据是最常见的操作：
UPDATE user SET age = 29 WHERE username = '张三';UPDATE的执行流程是：先根据WHERE条件找到需要修改的行，然后修改对应字段。这里有一个容易被忽视的细节：对数字字段做相对更新时，要利用MySQL的原子更新能力：
UPDATE account SET balance = balance - 100 WHERE id = 1;而不是先SELECT出来在程序里算好再UPDATE。前一种写法是数据库层面的原子操作，并发下不会因为"读-改-写"三步之间插入其他事务而导致更新丢失；后者在高并发场景下很容易出现两个人同时读取到相同余额、各自计算后写回的情况，最后的结果完全错误。这个原则在库存扣减、余额变动、计数增减里都适用。
4.2 漏掉WHERE条件的后果与防御习惯
UPDATE最危险的瞬间是忘写WHERE。UPDATE user SET age = 29;会直接把全表所有人的age都改成29。这种事故我听过的、见过的都不少，而且往往发生在没有备份的测试环境或者刚上线的功能里。
我自己养成了几个防御习惯，在这里分享给各位：
- 执行UPDATE之前，先写一条同WHERE条件的SELECT，确认影响范围，比如
SELECT COUNT(*) FROM user WHERE username = '张三';。 - 用事务包住UPDATE，先不COMMIT，检查影响行数和结果，确认无误再提交。
- 生产环境的高危操作，提前把原数据备份到临时表。备份语句很简单，
CREATE TABLE user_bak_20250101 AS SELECT * FROM user WHERE ...，关键步骤成本很低，但带来的安全感极高。
宁可多花十秒钟确认，也绝不省这一步。数据库操作里"手快"从来不是优点，稳才是。
4.3 多表关联更新：JOIN UPDATE的威力与风险
有时需要根据另一个表的条件来更新当前表，MySQL支持UPDATE...JOIN语法：
UPDATE user u JOIN `order` o ON u.id = o.user_id SET u.level = 'VIP' WHERE o.amount > 10000;这条语句会把所有订单金额超过10000的用户等级更新为VIP。使用JOIN更新时，要注意JOIN后的表不能出现在SET子句的左侧目标之外的位置，语法上也要小心表别名冲突。
多表更新虽然强大，但它涉及的行数和锁范围都比单表更新大很多。执行前一定要评估影响行数，最好也是先用同条件的SELECT看看会命中多少用户。曾经有人在生产环境执行类似的关联UPDATE，没注意order表里有大量历史数据，结果把一批不该升级的用户都升级了，最后靠备份回滚才解决。多表更新的重点不是写不出来，而是写之前想清楚"这个JOIN的关联关系是不是足够精确"。
4.4 别让函数破坏了索引
对索引列做函数运算，是UPDATE和SELECT都会踩的坑。比如：
SELECT * FROM `order` WHERE YEAR(create_time) = 2024;这种写法无法有效使用create_time上的索引，因为MySQL要先对每一行的create_time做YEAR()计算，才能比较。更好的写法是范围查询：
SELECT * FROM `order` WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';记住一个判断方法：写WHERE时尽量保持列本身独立，别套函数。这个原则同样适用于UPDATE的WHERE条件，因为索引失效意味着MySQL要扫描并锁住更多行，代价比SELECT更大。我见过线上UPDATE因为WHERE DATE(create_time) = CURDATE()这种写法把整个大表锁住的案例，那滋味真的很不好受。
5. DELETE：删除数据不是清空那么简单
5.1 DELETE、TRUNCATE、DROP三条语句的边界
删除数据常用的不止DELETE，还有TRUNCATE和DROP，三者边界完全不同。很多新手会混用，这里用表格梳理一下：
| 语句 | 类型 | 删除对象 | WHERE条件 | 可回滚性 | 速度 |
|---|---|---|---|---|---|
| DELETE | DML | 数据行 | 支持 | 支持（事务内） | 慢 |
| TRUNCATE | DDL | 整表数据 | 不支持 | 一般不回滚 | 快 |
| DROP | DDL | 表结构加数据 | 不支持 | 不可回滚 | 很快 |
DELETE是DML语句，删除行数据时如果包在事务里，可以回滚；TRUNCATE是DDL语句，清空整表数据但保留表结构，速度快，不过一般不逐行记录日志，可回滚性很差；DROP则直接把表结构都删掉，如果没有备份，数据基本就没了。
所以日常业务里的删除，默认都应该用DELETE，并且带上WHERE条件。TRUNCATE和DROP更像是运维场景下的工具，比如清空临时表、重建表结构，而不是业务代码里随手用的语句。
5.2 大表清理的正确做法：分批删除
线上生产环境，直接DELETE FROM big_table WHERE create_time < '2024-01-01'一次删除几百万行，会导致长事务、大锁、binlog量暴增，甚至拖垮主从复制。我处理大表过期数据，最稳的方式是分批删除：
DELETE FROM log WHERE create_time < '2024-01-01' LIMIT 1000;循环执行上面的语句，每批删除1000行，每批之间稍作停顿，让主从同步和磁盘IO缓一缓。这里LIMIT的作用是限制本批次删除行数。
要注意，务必要在WHERE里加明确的过滤条件，不能只写DELETE FROM log LIMIT 1000，那会让MySQL随机删除一些行，结果完全不受控。加上WHERE条件后，每次删除的都是同一范围的前1000行，反复执行直到影响行数小于1000，就说明范围清理完了。这个操作通常放在凌晨低峰期跑，并且要有监控和告警配套。
5.3 误删之后的恢复思路：备份是唯一的后悔药
误删数据之后的恢复，前提是平时做了备份。最可靠的是定期全量备份加binlog增量日志。恢复的基本思路是：找到误删的时间点，用备份恢复到误删前一刻，再用binlog把那一刻之后、误操作之前的事务重新执行，最后跳过误操作本身。这个过程需要熟悉mysqlbinlog工具，操作繁琐，但至少数据能找回来。
比恢复更重要的是预防。重要表的设计上可以考虑"软删除"，也就是加一个status或deleted字段，用UPDATE标记删除状态，而不是直接DELETE。这样做虽然多了一点查询条件，但给了数据"后悔药"。对用户、订单、账户这类核心数据，我强烈建议做成软删除；日志、流水这类非核心数据，才考虑物理删除。
6. 从写对到写稳：增删改查之外还要留心的三个细节
6.1 事务边界：多条增删改查如何连成整体
一个业务操作往往由多个增删改查组成。比如转账，要更新A账户余额、更新B账户余额，如果两条UPDATE只有一条成功，钱就平白消失了。解决方式是把它们包进事务：
START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE account SET balance = balance + 100 WHERE id = 2; COMMIT;InnoDB默认是自动提交，单条语句自带事务，多条语句如果不显式开启事务，它们各自独立提交，无法保证原子性。把多条增删改查放进一个事务，要么全部成功，要么全部回滚。
这里还有几个实用经验。事务里千万别混入无意义的慢查询或者外部接口调用，比如在事务执行到一半的时候去请求一个HTTP服务，等结果回来再继续。事务持续时间越长，锁持有时间越长，对其他会话的阻塞就越大。我在代码评审时看到别人开一个事务，中间夹着耗时的RPC调用，总会提醒他拆出去，因为这是并发阻塞最常见的源头。
6.2 并发写入时的锁等待问题
增删改查在高并发下，还会遇到锁等待。比如两个事务同时更新同一行，后提交的会被阻塞，阻塞时间超过innodb_lock_wait_timeout（默认50秒）就会报错：
Lock wait timeout exceeded; try restarting transaction这个错误本质上不是SQL写错，而是并发策略没设计好。排查方法是查询当前事务等待情况：
SELECT * FROM information_schema.innodb_trx\G看哪些事务长时间没有提交。常见原因是应用层开启事务后，在事务里做了其他耗时操作，比如外部请求、大文件读取，导致事务迟迟不结束，后续更新全部排队。优化方向是：事务里只做必要的数据库操作，减少事务粒度，同时合理设置超时阈值。曾经有个生产事故，就是因为一个后台导入功能在事务里逐行插入几千条数据，中间还偶尔调用外部接口，导致线上用户下单更新全部卡了二十多秒。
6.3 让每条增删改查都过一遍EXPLAIN
不管增删改查，只要涉及数据量大或者线上关键路径，我建议都跑一遍EXPLAIN确认执行计划。特别是UPDATE和DELETE，它们的WHERE条件和SELECT一样，都可能因为索引失效、隐式转换变成全表扫描。全表扫描的UPDATE会把所有行锁住，风险比SELECT大得多。
EXPLAIN SELECT * FROM user WHERE phone = '13800138000'; EXPLAIN UPDATE user SET age = 29 WHERE phone = '13800138000';观察type列和rows列，如果type是ALL或者rows非常大，就要回去检查字段类型、索引设计和SQL写法。写一条SQL之前花十秒钟看执行计划，能省掉线上事故之后花几个小时排查的痛苦。这个习惯坚持下来，你对SQL执行逻辑的理解会上升一个台阶，慢慢就能建立"哪些写法会走索引、哪些写法会导致扫描"的条件反射。
写到这里想说一点个人体会：很多看起来炫酷的数据库技能，比如分库分表、读写分离、大数据迁移，最终落地时处理的仍然是增删改查这四个基本动作。把基本动作做到极致，比背一堆高深术语实在得多。我自己的习惯是动手执行前，先在脑子里把"这条SQL要读写哪些表、影响多少行、走哪个索引、是否在事务里"过一遍，然后再敲回车。文章里的这些坑基本都来自真实生产环境，希望它们能帮你在数据库这条路上少摔几跤。
