1. 动手之前装好 MySQL建一张能折腾的表然后用 INSERT 喂第一行数据先说一个我自己的感受网上 MySQL 教程铺天盖地但大多数教程对“增删改查”这四个字的态度是——给一条 INSERT、一条 SELECT、一条 UPDATE、一条 DELETE然后教你背语法。看起来会了一上生产就翻车。热搜里天天有人问 mysql update 语法怎么写、mysql 排序怎么排、mysql 连接池怎么配、mysql 存储过程怎么声明其实根源都在基础 CRUD 没扎透。这篇文章我不打算讲那些“语法背一遍就完”的东西而是把从环境准备到增删改查的完整链路拆开揉碎把我实际工作中踩过的坑、验证过的做法、以及热搜里高频问题的底层原因一并讲清楚。适合刚学 MySQL 的新手也适合工作了两三年但写 SQL 主要靠复制粘贴的朋友。先解决环境问题。网上铺天盖地的 mysql 安装教程、mysql 安装配置教程、mysql 安装教程 8.0核心就是三步下载安装包、初始化数据目录、启动服务。如果你用 Linux以 CentOS 为例官方 Yum 源装 MySQL 8.0 最常见的坑有两个一是默认密码策略太严导致你设一个类似123456的密码直接被拒二是安装完不知道初始密码去哪找。前者可以在/etc/my.cnf里临时加一句validate_password.policyLOW后者可以在日志里搜temporary password或执行grep temporary password /var/log/mysqld.log拿到初始密码后第一件事是ALTER USER改密码然后创建我们这篇文章要用的测试库和测试表。下面是我一直用来演示 CRUD 的表结构CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4; USE shop; CREATE TABLE IF NOT EXISTS t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, username VARCHAR(50) NOT NULL COMMENT 用户名, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, age TINYINT UNSIGNED DEFAULT 0 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_age (age) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里多说一句utf8mb4是必须的MySQL 8.0 里默认字符集也已经是它。以前很多老系统用utf8存个 emoji 表情直接报错这是历史遗留问题新表别踩。TINYINT UNSIGNED存年龄足够VARCHAR(50)是用户名比较合理的长度上限既省空间又能防一些人恶意塞超长字符串。然后插入第一条数据INSERT INTO t_user (username, email, age, status) VALUES (zhangsan, zhangsanexample.com, 25, 1);INSERT 语法本身不复杂但有几个细节值得记一下如果你插入的字段列表里没有created_at和updated_at默认值机制会自动补上当前时间这就是 DEFAULT CURRENT_TIMESTAMP 的作用如果想一次插入多条用逗号分隔多组 VALUES 比循环执行 INSERT 效率高得多。批量插入尽量控制单条 SQL 的规模我一般控制在 500~1000 行一组太长了会增大 binlog 体积和主从同步延迟。2. 查询不是 SELECT 完事过滤、排序、分页和聚合里藏着的坑热搜里 mysql 排序、mysql 数据库命令大全常年挂在榜上说明很多人对 SELECT 的理解还停留在“会用”层面。我把查询拆成几个高频场景来讲。2.1 WHERE 过滤和 NULL 的相爱相杀SELECT id, username, email, age FROM t_user WHERE status 1 AND age 20;这个写法大家都会。真正容易出问题的是 NULL。比如你想查所有没填邮箱的用户写WHERE email NULL是永远查不到数据的因为 NULL 不参与等值比较正确写法是WHERE email IS NULL。反过来WHERE email ! NULL也是错的要用WHERE email IS NOT NULL。更隐蔽的场景是和 NOT IN 搭配。如果你写WHERE id NOT IN (SELECT user_id FROM t_order WHERE user_id IS NOT NULL)当子查询结果里出现 NULL 时整个 NOT IN 的返回结果是空集一条数据都查不出来。这个坑我见过不止一次排查了半小时最后发现是子查询里某行数据该字段是 NULL。解决方案是写成 NOT EXISTSSELECT * FROM t_user u WHERE NOT EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.id );2.2 ORDER BY 排序不止是 ORDER BY agemysql 排序的热搜常年有但很多人不知道排序的底层逻辑。ORDER BY age DESC, id ASC的意思是先按 age 降序age 相同再按 id 升序。这里有个细节如果你只写了ORDER BY age DESC当 age 相同的时候MySQL 不保证返回顺序是稳定的。虽然 InnoDB 在大多数情况下会按主键顺序返回但这不属于 SQL 标准承诺的行为。所以在做分页时建议排序字段里带上主键保证结果顺序可预期。排序的另一个坑是字符集和排序规则。同一个字段如果用了不同的 collationJOIN 或比较时可能报Illegal mix of collations错误。解决方法是统一库、表、字段的字符集和排序规则推荐 utf8mb4 对应的utf8mb4_0900_ai_ciMySQL 8.0 默认或者兼容老系统的utf8mb4_general_ci。2.3 LIMIT 分页深分页的痛你早晚会遇到SELECT * FROM t_user ORDER BY id LIMIT 100000, 20;这种写法在数据量小时没问题一旦偏移量到了几十万甚至上百万查询会越来越慢。原因是 MySQL 需要先把前 100000 条数据全部读出来再丢弃只返回最后 20 条。你在 Workbench 里看执行计划可能还显示用了索引但实际扫描量就是 100000 行。我常用的优化手段是“延迟关联”也叫延迟连接SELECT u.* FROM t_user u INNER JOIN ( SELECT id FROM t_user ORDER BY id LIMIT 100000, 20 ) t ON u.id t.id;这个思路是先让子查询只查主键 id走覆盖索引扫描再和原表做关联取得完整行数据。实测在千万级数据量下表深分页耗时能从几百毫秒降到几十毫秒。2.4 GROUP BY 和 HAVING聚合查询的语义要理清SELECT status, COUNT(*), AVG(age) FROM t_user GROUP BY status HAVING COUNT(*) 1;WHERE 是分组前的过滤HAVING 是分组后的过滤。这个教科书知识很多人背了但用的时候容易把 HAVING 当成万能过滤器到处用。能用 WHERE 过滤的字段就先用 WHERE因为 WHERE 能借索引快速过滤HAVING 只能等分组结果出来后过滤性能差距很大。还有一个高频面试点分组后查非分组字段。比如想知道每个年龄段里最早创建的用户的 username很多人会直接查SELECT username, age, MIN(created_at) FROM t_user GROUP BY age。这在 MySQL 里不报错但返回的 username 是不确定的同一条 SQL 在不同版本 MySQL 上结果可能不一样。正确的做法是用子查询或者窗口函数SELECT username, age, created_at FROM ( SELECT username, age, created_at, ROW_NUMBER() OVER (PARTITION BY age ORDER BY created_at) AS rn FROM t_user ) t WHERE t.rn 1;窗口函数 MySQL 8.0 才支持如果你还在 5.7那就老老实实用上面那段子查询写法。2.5 JOIN 的含义内连接、左连接、右连接到底怎么选mysql 数据库 join 含义这个热搜词出现频率极高说明 JOIN 是真能拦住一批人。JOIN 的本质是表与表之间按关联条件做集合运算拿两张表举个例子INNER JOIN只返回两边都匹配的行等于求交集。LEFT JOIN左表全部保留右表没匹配上的填 NULL等于左表全体加上交集。RIGHT JOIN和 LEFT JOIN 对称实际开发中我很少用完全能用 LEFT JOIN 调转表顺序替代。FULL OUTER JOINMySQL 不支持要用 UNION 拼。JOIN 最容易踩的坑是关联条件不是唯一键导致结果翻倍。比如一个用户有多条订单记录你JOIN t_order后用户表里的同一行会跟着订单数重复出现这时候你再 COUNT 用户数量数字就不对了。解决思路是先按用户维度聚合订单表再 JOIN 用户表。3. UPDATE 语法与其背后的性能与安全双重问题热搜里 mysql update 语法是单独的词条说明很多人查过它。UPDATE 基础语法就一句UPDATE t_user SET age 26, updated_at NOW() WHERE id 1;但实际开发里围绕 UPDATE 的坑比 INSERT 和 SELECT 加起来都多。3.1 没写 WHERE 的后果从删库到跑路只要一秒钟误更新全表是 MySQL 新手事故里最典型的一种。UPDATE t_user SET status 0;不加 WHERE整张表全被改了。MySQL Workbench 默认开启安全模式Safe Updates如果表上有主键它不允许执行没有 WHERE 条件或没有主键条件的 UPDATE/DELETE这功能对新手是保护但有时也会误伤——比如你用WHERE name zhangsan更新数据而 name 不是主键Workbench 也会拦。临时关掉可以在 SQL 编辑器里执行SET SQL_SAFE_UPDATES 0;但说实话我建议生产环境永远别关宁可多写一条按主键查询的语句。3.2 子查询更新目标表一个由来已久的经典报错热搜词里 mysql 中更新子查询 经常出现是因为很多人写过这类 SQLUPDATE t_user SET age age 1 WHERE id IN ( SELECT user_id FROM t_order WHERE total_amount 1000 );在很多版本里会报错You cant specify target table t_user for update in FROM clause。原因是 MySQL 不允许直接对 UPDATE 的目标表做子查询引用。解决办法是包一层临时表UPDATE t_user SET age age 1 WHERE id IN ( SELECT user_id FROM ( SELECT user_id FROM t_order WHERE total_amount 1000 ) tmp );包一层子查询后MySQL 会把它当成临时结果集就能绕过这个限制。其实 MySQL 新版8.0.29 左右开始对这个限制有所放松但为了兼容老环境建议还是用包一层的写法。3.3 多表更新UPDATE JOIN 的用法业务里经常要“用 A 表数据更新 B 表字段”比如把订单表里的总金额回写到用户表的累计消费字段UPDATE t_user u INNER JOIN ( SELECT user_id, SUM(total_amount) AS total FROM t_order WHERE pay_status 1 GROUP BY user_id ) o ON u.id o.user_id SET u.total_spent o.total;这种 UPDATE JOIN 语法是 MySQL 特有支持的一种扩展写法Oracle、PostgreSQL 不这么写面试的时候很容易被人考到。关联更新时如果关联字段上有重复值结果可能不符合预期所以务必确保右表是聚合后的结果每个用户只出现一次。3.4 大表 UPDATE 的性能风险行锁升级与主从延迟我在生产环境见过一次经典事故业务跑批任务一条 UPDATE 无条件更新整张千万级大表结果把线程全部卡在行锁等待上。InnoDB 默认行锁但在无索引字段的 WHERE 条件下它需要全表扫描定位行本质上是锁住了大量行甚至触发锁升级到表锁或间隙锁。表现就是数据库 CPU 不高但 DML 全部堆积监控面板上一片锁等待红点。针对大表批量更新我总结了一套可落地的策略-- 分批更新示例每次只更新 1000 条 UPDATE t_user SET age age 1 WHERE id BETWEEN ? AND ? AND age 100;再配合循环调用每批之间留几百毫秒间隔。好处是每批持有锁时间极短不会阻塞其他业务也能显著降低主从复制延迟。批量 UPDATE 时顺手把updated_at NOW()更新掉能方便后续排查问题。4. 删除操作DELETE、TRUNCATE 对比锁和事务边界要拎清DELETE 的热搜词不少这里单独作为一章。删除有两种DELETE 和 TRUNCATE它们底层实现完全不同选错了就是生产事故。4.1 DELETE 语法、多表删除与误删预防DELETE FROM t_user WHERE id 1;DELETE 是逐行删除每删一行都会记录 undo log所以可以配合事务回滚。它支持 WHERE 条件、ORDER BY、LIMIT。比如你想删除表中创建时间最早的 10 条数据DELETE FROM t_user ORDER BY created_at ASC LIMIT 10;这个语法在 Oracle 里不合法MySQL 支持但面试时容易露馅很多人以为 DELETE 不能带 ORDER BY 和 LIMIT。注意DELETE ... LIMIT不加 ORDER BY 时删除的行是不确定的建议总是带上 ORDER BY至少加个主键排序。多表删除也常见比如删除没有订单的用户DELETE u FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE o.user_id IS NULL;这种写法比先查子查询再删要高效。4.2 TRUNCATE 和 DELETE 的本质区别TRUNCATE 不是逐行删除而是直接丢弃数据页并重建表结构速度极快但有几个致命限制不能加 WHERE、不能回滚、会重置自增 ID。所以 TRUNCATE 只适合清空临时表或测试数据生产环境几乎用不到。对比项DELETETRUNCATE删除方式逐行删除重建表数据页WHERE 条件支持不支持事务回滚支持InnoDB 下不支持自增 ID不重置继续累加重置为初始值执行速度慢极快触发器触发逐行触发删除触发器不触发锁范围行锁/间隙锁表级元数据锁磁盘空间释放不立即释放需 OPTIMIZE TABLE直接释放4.3 行锁、表锁、锁等待删除引发的连锁反应InnoDB 的行锁和间隙锁对并发 DML 影响极大。一个 UPDATE 或 DELETE 如果 WHERE 条件没走索引InnoDB 可能需要扫描所有记录并给扫描到的每条记录加锁。这不仅影响性能还极易引发死锁。查看当前锁等待最简单的方式SELECT * FROM information_schema.innodb_trx\G; SELECT * FROM performance_schema.data_lock_waits\G;正常生产环境使用这个查询可以看到事务 ID、运行时间、等待锁的信息。根据我排查线上死锁的经验绝大多数死锁场景都是两个事务分别持有对方需要的行锁且 UPDATE/DELETE 的顺序不一致。比如事务 A 先更新 id1 再更新 id2事务 B 先更新 id2 再更新 id1就很容易死锁。解决思路很简单所有事务都用相同的加锁顺序。4.4 误删之后怎么办事务回滚与二进制日志的救命用法我见过有人误执行了没有 WHERE 的 DELETE整张业务表没了当场汗都下来了。这里分享一套我自己的恢复思路不一定能完全找回所有数据但能救命。第一步如果删除操作发生在事务里且没提交直接ROLLBACK就行。第二步如果已经提交了马上查 binlog 是否是开启状态SHOW VARIABLES LIKE log_bin;确认 binlog 开启后可以用官方自带的mysqlbinlog工具解析日志找到删除前的时间点把误删的 SQL 反向解析出来。具体命令mysqlbinlog --no-defaults --start-datetime2025-01-01 10:00:00 --stop-datetime2025-01-01 10:30:00 /var/lib/mysql/mysql-bin.000001把输出的 SQL 拿到测试库回放然后手工补齐。第三步也是最重要的一步平时做好备份开启 binlog并定期做恢复演练。这些准备工作很枯燥但真到了误删的时候它们就是最后的保险。5. 增删改查之外索引、存储过程、Workbench 和连接池的高频疑问热搜词里 mysql 创建索引、mysql 声明存储过程、navicat 连接 mysql、mysql 的数据库连接池 这些高频词其实都和增删改查的写法和性能强相关。这一章把这些问题串起来省得你到处找零碎的教程。5.1 索引怎么建从一次慢查询说起假设线上有一条查询经常出现日志里slow_log显示耗时 3 秒SELECT * FROM t_user WHERE age 25 AND status 1 ORDER BY id DESC;但EXPLAIN显示typeALL说明全表扫描了。原因很简单age虽然有单列索引idx_age但查询条件里既有age又有status加上排序字段id单列索引发挥不了组合过滤效果。正确做法是调整索引ALTER TABLE t_user DROP INDEX idx_age; ALTER TABLE t_user ADD INDEX idx_age_status (age, status);性能会从 3 秒降到几十毫秒。如果你还觉得慢可以考虑在(age, status, id)上建复合索引让ORDER BY id DESC也走索引排序。索引的核心是最左前缀原则复合索引相当于把多个字段拼接成一个排序结构查询条件里必须包含索引最左侧的字段才能命中。比如建立(age, status)索引后只有WHERE age ?或WHERE age ? AND status ?才能走索引。只查WHERE status ?时这个索引基本没用。创建索引的热搜还有一种典型场景字段是唯一业务键如手机号、身份证号很多人直接建普通索引结果业务上允许了重复数据这种问题应该用 UNIQUE KEY 约束从源头防治。5.2 存储过程声明语法和 DELIMITER 的坑热搜里 mysql 声明存储过程 频率很高说明很多人卡在这个门槛上。一个标准的 MySQL 存储过程长这样DELIMITER // CREATE PROCEDURE proc_update_user_age(IN user_id BIGINT, IN new_age TINYINT) BEGIN UPDATE t_user SET age new_age WHERE id user_id; SELECT ROW_COUNT() AS affected; END // DELIMITER ;注意DELIMITER必须写。MySQL 客户端默认用分号作为语句结束符但存储过程内部也有分号如果不临时改结束符客户端会在存储过程定义中途直接断开执行。在 Navicat 或 Workbench 中写存储过程时通常选择对应的可视化操作也能自动处理这个细节。还有一个实际经验能不用存储过程就别用。存储过程调试困难版本管理不友好对连接池和主从复制也不够透明。大多数业务逻辑放应用层反而更容易维护。存储过程更多用于定时任务批处理或数据库层计算密集场景需要有明确的理由才值得引入。5.3 Workbench 和 Navicat客户端工具怎么选mysql workbench 使用教程、navicat 连接 mysql 热搜背后是两类工具的使用困惑。我的建议是Workbench 官方免费适合 DBA 和喜欢用 ER 图的人但界面风格偏工程化第一次上手略懵。Navicat 界面友好适合日常开发能连多种数据库SSH 隧道和可视化建表都很顺手不过是商业软件。无论用哪个工具连接 MySQL 8.0 时经常遇到一个报错Authentication plugin caching_sha2_password cannot be loaded。这是因为 MySQL 8.0 默认认证插件改了老版本客户端不认识。解决办法有两种要么升级客户端驱动到支持 caching_sha2_password 的版本要么把用户改回 mysql_native_passwordALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码;新项目建议别改直接升级驱动因为 mysql_native_password 插件在新版本中已经被标记废弃。5.4 连接池为什么只有一条 SQL 也不慢mysql 的数据库连接池 是热搜里的常客。连接池的作用是复用数据库连接避免每条 SQL 都创建连接、建立 TCP、做认证、断开连接这一整套开销。以 Java 生态常用的 HikariCP 为例核心配置我不展开太多只说三个我在热搜里反复看到的坑。第一个坑是连接池最大连接数设得过高。每个连接背后都会占用数据库内存和线程资源连接满天飞反而容易打垮实例。一般经验是小型应用池子 10~20 个连接足够中大型按核心业务并发估算用压测数据说话。第二个坑是连接空闲超时和 MySQL 的 wait_timeout 不匹配导致连接池里积累了不可用的“死连接”。处理办法是把 HikariCP 的connection-test-query或connection-init-sql配上简单的探测 SQLspring.datasource.hikari.connection-test-querySELECT 1第三个坑是连接泄漏。应用代码里拿连接后没释放连接池被耗尽表现为系统突然所有请求排队日志里报connection is not available, request timed out。解决办法是配合事务边界用 try-with-resources 或框架托管同时监控连接池活跃连接数。6. 把前面所有内容串起来一个完整案例的自检过程很多人在基础上手后依然不知道一套“规范”的增删改查到底长什么样。我拿一个实际需求练一遍手模拟一个持续更新的用户中心。6.1 需求新增用户、批量导入、按状态分页查询、修改用户邮箱、删除无价值用户第一步批量导入一次插 100 条INSERT INTO t_user (username, email, age, status) VALUES (user01, user01example.com, 21, 1), (user02, user02example.com, 22, 1), ... (user100, user100example.com, 30, 1);第二步分页查询“状态为正常且年龄在 18 到 30 之间”的用户按创建时间倒序SELECT id, username, email, age, status, created_at FROM t_user WHERE status 1 AND age BETWEEN 18 AND 30 ORDER BY created_at DESC LIMIT 20 OFFSET 0;翻页到第 1000 页时要换成延迟关联写法。第三步把指定用户的邮箱改掉UPDATE t_user SET email new_emailexample.com WHERE id ?;第四步删除 30 天前创建且状态已禁用、并且无订单记录的用户DELETE u FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE o.user_id IS NULL AND u.status 0 AND u.created_at DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000;这个删除操作配合定时任务分批执行避免一次删太多导致锁等待。完整链路执行完可以统一用SELECT COUNT(*) FROM t_user WHERE status 1;验证数据量是否符合预期再检查慢查询日志里有没有新增的慢 SQL。6.2 检查清单每次发布增删改查脚本前过一遍[ ] 所有 UPDATE 和 DELETE 都确认带上了精确的 WHERE 条件[ ] 涉及时间比较的字段有索引别在 WHERE 里对字段做函数计算如WHERE DATE(created_at) ...这样索引会失效[ ] 大批量循环 DML 前确认 binlog 格式并使用小批量 LIMIT[ ] 在测试环境实际跑一次 EXPLAIN确认没有全表扫描[ ] 生产执行前做好备份大操作尤其需要[ ] 变更完成后观察慢查询、锁等待、主从延迟。这个检查清单看着基础但几乎所有线上 SQL 引起的故障都能在其中一条上找到对应。别嫌麻烦养成习惯后能给你省下大把和时间赛跑的功夫。我在实际项目中带过不少刚入行的开发发现一个规律很多人把“会写增删改查”等同于“会用 MySQL”但真正让他们区分水平高低的不是语法写得多花哨而是遇到慢查询能不能定位、遇到锁等待能不能分析、遇到误删能不能稳住心态恢复。这篇文章从环境安装、INSERT、SELECT、UPDATE、DELETE 到索引、存储过程、连接池基本把增删改查涉及的旁支知识捋了一遍。你把它当作一份“MySQL 增删改查完全体”的笔记来读就行遇到实际场景再回来翻对应章节比硬背教程有用得多。