要说数据库里最基础也最绕不开的操作“增删改查”这四个字绝对是第一课。不管你是刚入职的初级开发还是写了多年业务代码的老手每天写的最多的就是那几条 SQL往表里插数据、按条件查数据、改几个字段、删掉不再需要的记录。MySQL 作为使用量最大的关系型数据库之一这套 DMLData Manipulation Language数据操纵语言语法几乎成了后端开发的通用语言。这篇文章不打算讲那些文档里已经写得很清楚的基础语法我想更多结合实际工作里会遇到的问题来聊。包括为什么说 DELETE 和 TRUNCATE 差别很大为什么 UPDATE 不加 WHERE 能把整个表改崩JOIN 查询到底应该怎么写才不出错以及你在 Navicat 里看着很简单的一条语句到生产环境里为什么会卡死整张表。这些内容适合刚学会 SQL 语法、准备真正上手做项目的人也适合写了一段时间 SQL 但总在奇怪场景里翻车的朋友。1. 内容整体设计与思路拆解1.1 核心需求解析为什么增删改查是衡量基本功的标准增删改查对应业务系统里的四类最基础需求新增数据、查询数据、修改数据、删除数据。任何一个业务系统不管是电商、后台管理系统、内容平台还是工具类应用最终落地到数据库层面全都跑不出这四个动作。我见过很多面试者简历上写着熟悉 MySQL一到手写 SQL 的环节就只记得 SELECT * FROM 表名。问起来 DELETE 和 TRUNCATE 有什么区别答不上来问 UPDATE 大批量数据时怎么避免锁表一脸茫然。这不是个别现象而是很多人在学习阶段只盯着语法本身没有建立“数据库操作是有代价的”这个概念。真正的工作场景里增删改查的难度不在于记住语法而在于三个层面。第一个层面是准确写入的数据要符合约束查出来的结果要跟预期完全一致第二个层面是效率一张千万级的表哪怕只是一个简单的 SELECT写不好也足以把数据库拖垮第三个层面是安全一次不当的 UPDATE 或 DELETE轻则产生脏数据重则直接把整个线上环境搞挂。这篇文章要拆解的就是把这三个层面融入到每一个具体的操作里。1.2 方案选型为什么围绕 MySQL 的 DML 展开市面上关系型数据库不少MySQL、PostgreSQL、Oracle、SQL Server各有各的阵营。但 MySQL 在中小型企业和互联网场景里的普及率仍然是最高的这和它的社区活跃度、部署成本、生态工具链成熟度都有关系。更重要的是MySQL 的 DML 语法在绝大多数场景下与标准 SQL 兼容你在这里掌握的技巧迁移到其他数据库时至少能保住七成的通用性。我之所以强调不看文档直接上手写是因为 MySQL 的增删改查有不少细节是“文档里有但你不知道它有多重要”的。比如 INSERT 时可以一次性插多行性能比一行一行插快很多DELETE 删除大批量数据时如果一次性删几十万行很可能直接锁住整张表正确的做法是分批删UPDATE 的时候 WHERE 条件带不带索引对线上业务的影响完全是两个世界。这些都是实操中总结出来的经验也是这篇文章想要重点传递的内容。2. 核心细节解析与实操要点2.1 插入数据INSERT 的几种常见形态与适用场景INSERT 的入门写法很简单往表里插一行数据写到字段和值一一对应。但在实际项目里这种写法往往不够用。最常见的需求是一次插入多条数据。业务跑批的时候比如每天凌晨要从临时表往正式表同步数据几千上万行记录如果用循环一条一条插不仅慢还会产生大量的日志记录。正确的做法是使用多值插入一条 INSERT 语句后面跟多个值组。INSERT INTO student (name, age, class_id) VALUES (张三, 18, 1), (李四, 19, 2), (王五, 20, 1);这种写法的优势在于减少了客户端与数据库之间的交互次数效率提升非常明显。我自己测过同样是插入一万条数据分一万次插入耗时大概在秒级以上改成一条多值插入百毫秒级别就能完成。差距不在 SQL 执行本身而在网络往返上。还有一种容易被忽视的场景是“数据存在就更新不存在就插入”。业务里经常遇到这种需求比如用户的每日积分流水当天第一次登录要 Insert后续再登录要 Update 累计次数。很多新手的做法是先去查一次判断有没有再决定执行哪条 SQL。这其实可以一步到位用 ON DUPLICATE KEY UPDATE。INSERT INTO user_login_stats (user_id, login_date, login_count) VALUES (1001, 2025-05-20, 1) ON DUPLICATE KEY UPDATE login_count login_count 1;这里要求 user_id 和 login_date 有唯一索引当插入的数据发生主键或唯一键冲突时就自动执行后面的更新语句。这个语法最大的价值在于省掉了一次 SELECT 的往返而且它是原子操作不用考虑并发情况下的竞态条件。还有一个变体是 INSERT IGNORE它的作用是忽略插入时的错误。比如批量导入数据时里面可能有几条和主键冲突你并不想让整个导入失败这时加上 IGNORE冲突的自动跳过不冲突的正常插入。2.2 查询数据SELECT 的执行逻辑远比你想的复杂SELECT 是增删改查里使用频率最高、语法最灵活也最容易写错的部分。很多新手写查询像是查字典——需要什么就写什么完全不思考 MySQL 底层是怎么执行的。理解 SELECT 的执行顺序是写好查询的关键。一条完整的 SELECT 语句在逻辑上会经过这样的顺序先加载 FROM 指定的表然后走 WHERE 过滤行接着按 GROUP BY 分组再用 HAVING 过滤分组后的结果接着处理 SELECT 后面的列和表达式然后是 ORDER BY 排序最后是 LIMIT 限制返回行数。这个顺序最直接的指导意义在于你在 WHERE 里能过滤掉的数据就别放到 HAVING 里才过滤。因为 WHERE 是在分组前执行过滤掉的行不参与分组聚合能够大幅减少计算量。反过来WHERE 里不能使用聚合函数比如想筛选出“订单数大于 5 的客户”聚合条件只能写在 HAVING 里。JOIN 是查询里另一个大坑。内连接、左连接、右连接概念上不难理解但一旦涉及多张表关联查询连接条件写错产生笛卡尔积结果集直接从一万行膨胀到一百万行。一个很实用的排查技巧是写完关联查询后先看一眼结果行数是否正常如果明显多于预期优先怀疑多表之间的关联条件是否写全了。SELECT o.order_id, u.name, o.amount FROM orders o INNER JOIN users u ON o.user_id u.user_id WHERE o.status 1 ORDER BY o.created_at DESC LIMIT 20;这里的 INNER JOIN 表示只返回两张表中关联字段匹配成功的记录。对于订单这类核心数据通常不会用 LEFT JOIN因为 LEFT JOIN 会保留主表中没有匹配上的记录而这些记录在业务上可能意味着数据异常比如订单挂了不存在的用户 ID所有用户字段都是 NULL。LIMIT 和 ORDER BY 搭配使用是分页查询的基础。但需要注意的是深分页场景下LIMIT 的偏移量越大查询越慢。这是因为 MySQL 需要先把前 offset 行的数据全部读出来再丢弃掉。后面聊到性能问题时我会专门讲怎么优化这种场景。2.3 更新与删除高风险操作的底层逻辑与红线UPDATE 和 DELETE 是 DML 里风险最高的两类操作原因很简单它们一旦执行影响范围可能就是整张表。UPDATE 的核心语法是配合 WHERE 条件选择要更改的行。在项目中你几乎永远不应该执行不带 WHERE 的 UPDATE除非你真的想把整张表的所有记录都改成同一个值。问题在于WHERE 的条件怎么写也有讲究。如果 WHERE 条件用不上索引MySQL 就得全表扫描逐行判断是否符合条件。在数据量小的表上这不是问题但一张几百万行的表全表扫描意味着几百万次判断期间表数据被锁住其他连接的写入请求全部排队。UPDATE orders SET pay_status 2 WHERE order_id 10086;order_id 是主键这条 SQL 走主键索引执行很快。但如果改成 WHERE pay_status 2而 pay_status 上没有索引那就要全表扫一遍才能找到所有待更新行。所以在设计表结构的时候那些经常出现在 WHERE、UPDATE、DELETE 条件里的字段要提前评估是否应该加索引。DELETE 的语法逻辑和 UPDATE 类似但有几个特殊点需要单独说。第一DELETE 是逐行删除并记录日志的所以删除的效率取决于受影响的行数。第二删除大量数据时通常不建议一条 DELETE 语句把所有目标数据删完尤其在高并发业务表上会引发锁竞争。推荐的做法是分批删除每批几百行到几千行删完一批停顿一下再删下一批。TRUNCATE 和 DELETE 的区别也是高频问题。TRUNCATE 是直接把整张表清空并释放存储空间它不逐行记录日志速度快得多但不能加 WHERE 条件且执行后无法回滚。如果要清空一张表且确定不需要保留数据TRUNCATE 是更好的选择如果只是删除一部分数据就必须用 DELETE。3. 实操过程与核心环节实现3.1 建库建表动手前的环境准备在写增删改查之前需要先有一张表。我以一个模拟的图书管理系统为例创建一张 book 表字段包括 ID、书名、作者、分类、价格、库存、上架时间。CREATE DATABASE IF NOT EXISTS demo_library DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci; USE demo_library; CREATE TABLE book ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, book_name VARCHAR(128) NOT NULL, author VARCHAR(64) NOT NULL, category VARCHAR(32) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, stock INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;几个建表细节值得说明。字符集选 utf8mb4 而不是 utf8是因为 utf8 在 MySQL 里最多只能存 3 个字节的字符像 emoji 这类 4 字节字符会报错。price 字段用 DECIMAL 而不是 FLOAT因为浮点数在二进制表示里会有精度误差涉及金额的地方用 DECIMAL 更稳妥。created_at 和 updated_at 直接给了默认值这样插入数据时就不用手动维护时间字段。3.2 核心环节的实现从插入到查询的完整流程演示有了表之后开始走一遍完整的增删改查流程。先插入几条数据。INSERT INTO book (book_name, author, category, price, stock) VALUES (深入理解计算机系统, 兰德尔·布莱恩特, 计算机科学, 128.00, 50), (数据库系统概论, 王珊, 数据库, 56.00, 30), (高性能MySQL, 施瓦茨, 数据库, 129.00, 20), (算法导论, 托马斯·科尔曼, 算法, 99.00, 15);插入完成后查询验证。实际开发中INSERT 之后立刻 SELECT 是常见的操作一方面是确认数据正确另一方面是把自动生成的主键 ID 取回来用于后续操作比如插入一条订单后需要用返回的订单号去插入明细。SELECT id, book_name, price, stock FROM book;如果只想查某个分类下的书并且按价格从高到低排序可以这样写。SELECT id, book_name, author, price FROM book WHERE category 数据库 ORDER BY price DESC;这条 SQL 里 WHERE 过滤了分类ORDER BY 对结果排序两个条件各自分工明确。执行顺序上是先筛出所有数据库分类的书再对这几行数据按价格排倒序。接下来演示更新操作。假设“高性能MySQL”这本书的库存被调低了 5 本同时价格调整为 139 元。UPDATE book SET price 139.00, stock stock - 5 WHERE id 3;这里有两个细节。stock stock - 5 直接基于原值做减法这种写法比先查出来再用 Java 代码计算再 UPDATE 回去要高效且准确因为它是在数据库内部原子完成的不用考虑并发覆盖问题。WHERE id 3 走主键直接命中那一行效率很高。但这里要有一个习惯执行 UPDATE 之前先用相同 WHERE 条件的 SELECT 看一眼命中的数据范围。比如我想确认 id 3 的书是不是真的是要改的那本先执行 SELECT * FROM book WHERE id 3确认无误再执行 UPDATE。这个习惯在新人阶段能帮你避免大量事故。然后是删除操作。假设算法导论这本书下架了要从系统里删除。DELETE FROM book WHERE id 4;同样删除之前最好先 SELECT 验证。3.3 进阶实操聚合、分组与联表查询的完整示例基础增删改查跑通以后再往前一步就是实际项目里那些真正有业务含义的查询。单表操作往往不够真实业务很少只有一张表通常都有用户表、订单表、明细表、日志表之间的关系。继续以图书管理系统为背景模拟一个简单的关系有一张 book 表一张 book_order 表记录每次下单book_order 表里存 book_id外键逻辑上关联 book 表的主键。CREATE TABLE book_order ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL, book_id INT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL DEFAULT 1, order_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入几条模拟的订单数据然后做一个常见的统计查询统计每本书被下单的总数量。SELECT b.book_name, SUM(o.quantity) AS total_ordered FROM book b INNER JOIN book_order o ON b.id o.book_id GROUP BY b.id, b.book_name ORDER BY total_ordered DESC;这条 SQL 做了联表、分组、聚合、排序四件事是业务系统里非常典型的一类查询。GROUP BY b.id, b.book_name 的原因是SQL 标准要求 SELECT 中出现的非聚合列必须出现在 GROUP BY 中而 MySQL 在早期版本并没有严格执行这个约束只保留了 ONLY_FULL_GROUP_BY 作为默认 sql_mode。也就是说如果你只 GROUP BY b.id却 SELECT 了 b.book_name在新版本 MySQL 里会直接报错。再举一个稍加变形的需求查出被下单过的书里总数量大于等于 2 本的书籍。这种带分组统计条件的查询需要在 GROUP BY 之后通过 HAVING 筛选。SELECT b.id, b.book_name, SUM(o.quantity) AS total_ordered FROM book b INNER JOIN book_order o ON b.id o.book_id GROUP BY b.id, b.book_name HAVING total_ordered 2;注意这里 HAVING 用的是别名 total_orderedMySQL 是允许的它在分组聚合完成之后对结果进行过滤。这和 WHERE 的区别正是前面提到的执行顺序WHERE 不能过滤聚合结果HAVING 则是专门干这件事的。4. 常见问题与排查技巧实录4.1 高频踩坑场景与典型错误对照写增删改查的时间越久踩过的坑就越多。我整理了几个高频问题几乎每个带过的实习生态都在里面翻过车。第一个坑是 UPDATE 和 DELETE 漏写 WHERE 条件。造成的结果差异从轻微到灾难都有。如果是开发环境顶多是数据改错了重新造数据如果是生产环境一条漏写 WHERE 的 UPDATE 可能把全表所有用户的余额都改成了同一个数字。这个事故基本属于“必见过”级别的经典案例。第二个坑是对 NULL 的判断写错。很多新手查某个字段为空的数据会写成 WHERE col NULL结果一条都查不出来。原因在于 SQL 里 NULL 代表未知值任何对 NULL 的等值比较结果都是 NULL而 WHERE 只认 TRUE 作为有效条件。正确写法是 WHERE col IS NULL。第三个坑是字符集不一致导致乱码。表是 utf8mb4连接却用了 gbk中文数据插进去乱码查出来也是乱码。虽然不是 DML 语法本身的问题但增删改查的每一种操作都受它影响。解决办法是统一数据库、表、字段和连接的四层字符集。第四个坑是数据类型不匹配导致隐式转换。比如 mobile 字段存的是 VARCHAR查询时写了 WHERE mobile 13800138000后面是数字类型MySQL 会把字符串字段转成数字再比较一旦列上有索引就失效了查询直接退化成扫描。第五个坑是使用保留字做字段名没加反引号。比如给一张表设计了一个字段叫 desc 或 order执行 INSERT 或 SELECT 时报语法错误。解决办法有两个要么给保留字字段加上反引号要么直接改字段名后一种更推荐。我把这些问题的对比整理成了一张表。错误写法错误原因正确写法UPDATE book SET price10缺少 WHERE全表更新UPDATE book SET price10 WHERE id1WHERE price NULLNULL 不能使用等号比较WHERE price IS NULLWHERE mobile 13800138000字符串字段与数字比较触发隐式转换WHERE mobile 13800138000SELECT * FROM orderorder 是保留字SELECT * FROMorderWHERE stock 0 AND stock 0逻辑冗余但不算错主要想说明条件设计要有针对性WHERE stock 04.2 性能与安全深翻页、大批量删除与锁等待增删改查一旦放到数据量大、并发高的环境里很多基础操作就不再是表面看起来那么简单。先说深翻页。分页查询是每套后台管理系统的标配前端页面上的表格几乎都带分页。常规写法是 LIMIT offset, count。当 offset 很小时没问题比如 LIMIT 20, 10 只是跳过 20 行。但到了 LIMIT 100000, 10 这种深度分页MySQL 要把前十万行全部扫出来再丢弃耗时指数级增长。一种常见的优化思路是“延迟关联”。先用子查询只取主键 ID再用主键关联原表查全字段。SELECT b.id, b.book_name, b.price FROM book b INNER JOIN ( SELECT id FROM book ORDER BY id LIMIT 100000, 10 ) tmp ON b.id tmp.id;子查询里只查主键走的是覆盖索引速度远快于每行都回表取全部字段拿到十条主键后再回原表取完整数据代价很小。这个优化思路在数据量大时提升非常明显。再说大批量删除。之前提到过 DELETE 要分批删这里补充一个原因每条 DELETE 在 InnoDB 里是一条事务记录删除的行数越多事务持有的行锁就越多undo log 膨胀越大。如果一次性删几十万行很可能会导致其他事务长时间等待甚至直接把数据库内存耗尽。推荐的做法是循环按小批次删除每批执行完主动停顿。DELETE FROM order_log WHERE created_at 2024-01-01 LIMIT 5000;一次删五千行执行完等事务提交、锁释放再跑下一批。如果是在生产库执行这种删除最好放在业务低峰期一边删一边观察数据库的负载和锁等待情况。锁等待是我接触过的新手里比较难排查的一类问题。现象往往是某条 SQL 执行特别慢超时报错实际上是因为有其他事务持有了某个行锁没释放。遇到这种情况第一步不是去优化那条 SQL 的语法而是先查一下当前有没有阻塞。可以用下面这条命令看当前正在运行的事务和锁信息SELECT * FROM information_schema.INNODB_TRX\G; SELECT * FROM information_schema.INNODB_LOCK_WAITS\G;查到阻塞源头后通常就是某一笔事务一直没有 COMMIT 或 ROLLBACK把锁一直攥在手里。找到它确认可以结束后把阻塞的事务处理掉后面的等待自然就解除了。4.3 一条事故复盘的完整排查思路这个思路的完整落地流程我举一个模拟的事故例子来说明。某个业务的运营后台今天突然报出一个问题运营人员点了“批量修改商品价格”之后页面上一直转圈几分钟后提示超时。一开始大家怀疑是接口代码的性能问题后来发现其他接口也变慢了慢慢意识到可能是数据库层面出了状况。排查的第一步就是看数据库当前的运行情况。用 SHOW PROCESSLIST 查看当前所有连接正在执行的语句如果发现某条 UPDATE 语句在 Copy to tmp table 状态或者Waiting for table metadata lock基本可以锁定问题范围。第二步检查那条 UPDATE 的 WHERE 条件。运营批量改价格时前端把选中的商品 ID 拼成了一个很长的 IN 列表目标数据可能有几万条。这种情况下UPDATE 一次影响几万行不仅会锁住这些行还会在 InnoDB 内部产生大量的变更记录同时因为表比较大这个操作跑了很久。第三步选择优化方案。如果业务允许可以把一次几万行的 UPDATE 拆成小批次。改成循环执行 UPDATE ... WHERE id IN (...) LIMIT 或者按主键范围分段更新每批处理几百条并提交事务将锁持有时间从分钟级降到毫秒级。这个案例里最值得学习的一点不是 UPDATE 的语法而是一种意识增删改查的每条语句在真实数据库里执行都是有成本、有锁、有影响的。学习了语法还要学会在数据量面前重新审视它。5. 写在后面的一些实际操作经验这篇文章从增删改查的语法层面聊到了执行顺序、索引对 DML 的影响、JOIN 和聚合查询的标准写法、深翻页优化、大批量删除的避坑、锁等待的排查思路基本上把工作中会用到的核心场景都覆盖了。我在实际带人的过程中发现新手最明显的一个成长节点就是在某次线上事故之后开始意识到“SQL 不是写完就算完的”。一条查询写出来之后花十秒钟想想它会在多少数据量上执行走不走索引会不会锁太多行这个习惯一旦养成踩坑的几率会大幅下降。最后还有一个小建议也是我自己的习惯凡是 UPDATE 和 DELETE先加 WHERE 条件先跑 SELECT 验证命中的是哪些数据凡是写 JOIN先确认关联字段的数据类型是不是一致避免隐式转换让索引失效。这些细节看起来琐碎但真正能让你和“背语法的人”拉开差距的恰恰就是它们。