如果你正在学后端开发或者已经在写业务接口了那 MySQL 的增删查改——也就是常说的 CRUD——一定是躲不过去的一道坎。我在带新人时经常遇到一种情况SQL 语法书翻得滚瓜烂熟一到自己写项目就各种卡壳语句明明能跑结果却是错的。这篇就把这套基础操作从头到尾捋一遍重点不是让你背语法而是讲清楚每条语句在真实项目里应该怎么用、哪些坑是看书看不出来的。我会从建库建表开始然后按“查、增、改、删”的顺序逐条拆解最后整理一套我这些年积累下来的安全操作习惯和排错思路。内容覆盖了 SQL 执行顺序、WHERE 条件陷阱、批量导入、多表更新、误删保护这些实战问题适合刚入门的开发者、正在做课程设计的在校生以及写了几条 SQL 但没系统梳理过的朋友。1. 动手前的地基建库、建表与练手数据1.1 连接 MySQL 的几种方式先别急着敲 SQL你得能连上数据库。最常见的两种方式一种是命令行终端在服务器上排查问题时最常用另一种是图形化客户端工具日常开发中看数据结构、建索引、跑查询都比命令行直观很多。命令行连接的基本姿势是这样的mysql -u root -p回车后会提示输入密码输入正确就进入mysql提示符。如果你需要远程连一台开发机可以加上主机和端口参数mysql -h 192.168.1.100 -P 3306 -u root -p我个人的习惯是日常开发用图形化工具排查线上问题回到命令行。命令行跑慢查询、看执行计划、做紧急修复时更直接不会因为图形界面刷新延迟而误判状态。另外连接参数里的-P是大写-p是小写很多初学者在这里栽过跟头-p后面直接跟密码也是可以的但不建议这样写——会在终端历史记录里留下明文密码。1.2 建库字符集和排序规则不是小事建库是有大学问的尤其是字符集选错后期全是乱码事故。一个稳妥的建库语句长这样CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;解释一下为什么要这么写。utf8mb4是真正的四字节 UTF-8能存表情符号、生僻字早期 MySQL 的utf8只有三字节遇到 emoji 就会报错或者存成乱码。COLLATE指定的是排序和比较规则utf8mb4_unicode_ci基于 Unicode 排序规则对字符比较更准确_ci表示大小写不敏感case insensitive。如果你不做特殊处理WHERE条件里abc ABC默认是相等的这一点在写业务逻辑时要心里有数。加上IF NOT EXISTS是幂等操作的习惯——脚本重复执行时不会报错这在自动化部署里特别重要。1.3 建表字段类型与约束的一次到位建表是增删查改的前提表结构建得合理后面写 SQL 顺手很多。拿一张用户表举例CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这几个字段设计的细节值得说道说道。INT UNSIGNED通常用来做主键正数范围翻倍username定成VARCHAR(50)并加唯一索引避免重复注册金额字段用DECIMAL(10,2)不能碰浮点数否则算钱时会出诡异的小数误差updated_at配合ON UPDATE CURRENT_TIMESTAMP可以实现行记录更新时自动刷新时间。ENGINEInnoDB是必须显式写的。InnoDB 支持事务、外键、行级锁是绝大多数业务场景的正确选择。旧版的 MyISAM 不支持事务一旦写入中途失败数据一致性很难保证。为了后面演示增删查改我们再建一张订单表顺便准备几行测试数据CREATE TABLE orders ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, user_id INT UNSIGNED NOT NULL COMMENT 用户ID, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0待支付1已支付2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;注意订单表的user_id字段加了普通索引idx_user_id因为查询“某个人的所有订单”是高频操作。如果这个表数据量到了百万级没索引的查询就是全表扫描慢到你怀疑人生。2. 查SELECT一周里八成时间花在这上面2.1 骨骼与执行顺序为什么条件写在 WHERE 不对SELECT 是所有操作里最常用、也最考验功底的。先看它的完整骨架SELECT 字段 FROM 表 WHERE 过滤条件 GROUP BY 分组字段 HAVING 组后过滤 ORDER BY 排序字段 LIMIT 分页限制;很多人不理解为什么有了 WHERE 还要有 HAVING为什么 GROUP BY 之后的条件不能写在 WHERE 里。这就要说到 SQL 的执行顺序了数据库实际执行时先读 FROM 的表再按 WHERE 逐行过滤然后才是 GROUP BY 分组分组完成后执行 HAVING接着才轮到 SELECT 选字段最后 ORDER BY 和 LIMIT。也就是说WHERE 在分组之前执行它管的是“行”HAVING 在分组之后执行它管的是“组”。举一个具体场景你要查“订单金额超过 100 元的用户中哪些用户的下单次数大于 5 次”。金额过滤是针对订单这一行的所以放在 WHERE下单次数统计是针对用户的需要先 GROUP BY 再 HAVINGSELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE amount 100 GROUP BY user_id HAVING COUNT(*) 5;把amount 100放到 HAVING 里也能跑但性能会差很多——因为所有行都先进来了分组后才丢掉白白浪费一轮扫描和分组计算。2.2 过滤条件LIKE、空值、优先级这些坑WHERE 里最常见的坑有三个每个我都见过真实项目踩过。第一个是OR和AND的优先级问题。AND的优先级高于OR所以你写WHERE status 1 OR status 2 AND amount 100;实际执行的是“status1 或者status2 且 amount100”跟很多人想的两件事完全不同。想表达两个独立条件必须加括号WHERE (status 1 OR status 2) AND amount 100;第二个是空值判断。SQL 里的 NULL 不是一个普通值它表示“未知”。用 NULL判断永远查不出数据必须用IS NULL或IS NOT NULL。而且 NULL 参与任何算术运算结果都是 NULL比如amount * 0.9如果 amount 为空结果就是 NULL不是 0。设计表结构时能给默认值就尽量给默认值就是为了少处理这种空值问题。第三个是模糊匹配的LIKE。%匹配任意多个字符_匹配单个字符SELECT * FROM users WHERE username LIKE zhang%;这个能查到zhang、zhangsan但查不到azhang。要注意%放在开头比如LIKE %zhang%索引基本就废了大表上会触发全表扫描。能用前缀匹配就尽量用前缀匹配。2.3 排序、分页与去重深翻页为什么慢排序和分页是列表页的标配。排序的语法很简单SELECT user_id, amount, created_at FROM orders WHERE status 1 ORDER BY created_at DESC, amount DESC LIMIT 0, 20;这里ORDER BY后面跟了多个字段表示先按下单时间倒序时间相同再按金额倒序。很多人会忽略排序字段的唯一性——如果排序字段有很多重复值翻页时容易出现同一行在不同页里反复出现或丢失这就是排序不稳定。稳妥做法是排序字段里加上主键比如ORDER BY created_at DESC, id DESC。分页查询最常见的问题是深翻页。LIMIT 100000, 20这种写法表面上没什么问题实际执行时 MySQL 要把前十万行全部取出来再扔掉数据量一大就非常慢。我曾经在一个百万级的表上跑过LIMIT 400000, 20单次查询耗时超过两秒完全没法用。优化的思路是改成“基于游标”的分页记录上一页最后一条数据的 ID下一页直接定位SELECT user_id, amount, created_at FROM orders WHERE status 1 AND id 100000 ORDER BY id ASC LIMIT 20;用这种方式翻页每一页只扫描目标范围内的数据速度快得多。去重用DISTINCT但它会拖慢查询而且只能对整行去重。想查“这个表里有多少个不同的用户下了单”可以写SELECT COUNT(DISTINCT user_id) FROM orders;2.4 聚合与分组COUNT 的细节统计数量是查操作用得最多的功能但COUNT的几个写法差别很大。COUNT(*)统计的是行数COUNT(1)和它本质上没区别但COUNT(某个字段)会跳过该字段为 NULL 的行。举一个实际的例子-- 统计所有订单数 SELECT COUNT(*) FROM orders; -- 统计有支付时间的订单数payment_time 为 NULL 的行不计数 SELECT COUNT(payment_time) FROM orders;如果你的表加了支付时间字段但大量订单还没支付这两个结果可能差很多。写统计 SQL 前先想清楚你要的是“行数”还是“非空字段数量”别拿一个COUNT(*)走天下。聚合函数里的SUM、AVG也有类似的空值陷阱它们都会忽略 NULL 行但如果整组全是 NULLSUM返回 NULL 而不是 0Java 后端拿到这个结果很容易出现拆箱空指针。稳妥的写法是COALESCE(SUM(amount), 0)把 NULL 兜成 0。还有一个高频报错MySQL 5.7 之后默认开启了only_full_group_by模式分组的 SELECT 查询里非聚合字段必须出现在 GROUP BY 中。很多新人写SELECT user_id, username, COUNT(*) FROM orders GROUP BY user_id;在旧版本可能能跑在新版本直接报错因为username没有包含在 GROUP BY 里。分组统计时先想清楚每一组里这个字段的值是否确定否则就要把它加进 GROUP BY或者去掉。3. 增INSERT从单条插入到批量导入3.1 INSERT 的标准写法新增数据最简单但写法上有讲究。推荐的方式是写清楚字段列表INSERT INTO users (username, email, balance) VALUES (user1, user1example.com, 100.00);有人为了省事直接写INSERT INTO users VALUES (user1, user1example.com, 100.00, NOW(), NOW());这种写法很危险。一旦表结构变动——增加字段、调整顺序——这条 SQL 就废了而且还不容易发现。生产环境里我见过多次这类问题代码里写了 INSERT 不带字段名上线后新环境表结构多了一列直接就炸了。写出字段名是应该刻进基因的习惯。3.2 批量插入与性能差异如果一次要插入几百条、几千条记录一条一条插入的代价极高。每条 INSERT 都要经过语法解析、权限检查、事务日志记录网络来回也要时间。批量插入能显著改善这种情况INSERT INTO users (username, email, balance) VALUES (user2, user2example.com, 200.00), (user3, user3example.com, 300.00), (user4, user4example.com, 400.00);我做过一个简单的对比同样是插入一万行数据逐条插入耗时是批量插入的几十倍。除了手动拼多条 VALUES还可以用LOAD DATA INFILE从文件批量导入但日常开发中少用因为它要处理文件路径、权限、导入格式运维成本不低。3.3 INSERT 不报错的注意事项插入失败的原因大概率是三种非空约束、唯一索引冲突、外键约束。非空约束最常见的坑是没有给 NOT NULL 且无默认值的字段传值。比如你建表时把email设为NOT NULL插入时漏掉了 emailSQL 直接报Field email doesnt have a default value。所以建表时想清楚哪些字段真的不能为空能设默认值就设默认值。唯一索引冲突的典型场景是重复插入同一用户名。业务上最理想的方案不是每次插入前先 SELECT 查一遍而是利用ON DUPLICATE KEY UPDATEINSERT INTO users (username, email, balance) VALUES (user1, user1example.com, 100.00) ON DUPLICATE KEY UPDATE balance VALUES(balance);这句话的效果是没有冲突就正常插入遇到唯一索引冲突就改成执行 UPDATE 后面的逻辑。防重和幂等在注册、下单等场景非常实用一天能省下几百条先查后插的 SQL。注意VALUES()函数在新版本中已被标记为弃用未来可能会移除。更推荐用别名写法INSERT INTO users (username, email, balance) VALUES (user1, user1example.com, 100.00) AS new ON DUPLICATE KEY UPDATE balance new.balance;另外还有一个REPLACE INTO它的语义是冲突时先删旧行再插入新行。听起来和ON DUPLICATE KEY UPDATE类似但它有两个副作用删除和插入会让自增主键跳跃而且如果该行被其他表的外键引用删除会被拒绝。所以我会优先用ON DUPLICATE KEY UPDATE只是在拿到“覆盖写”需求时用REPLACE INTO。4. 改UPDATE精确命中你要改的那一行4.1 UPDATE 的基本结构与 WHERE 的重要性修改操作的基本格式UPDATE users SET balance balance - 50.00 WHERE id 7;这句话的含义是把 ID 为 7 的用户的余额扣掉 50。关键在于WHERE id 7这个条件它决定了这次修改会影响到哪些行。不带 WHERE 的 UPDATE 是生产环境最大的事故源之一。我曾经处理过这么一次事故一个新人想改某一条配置记录写完UPDATE config SET value xxx忘了加 WHERE整张表的配置项全部变成了同一个值线上业务当场异常。虽然最后靠备份恢复了数据但影响面非常大从那次之后我要求所有人在执行 UPDATE 和 DELETE 前必须先把 SQL 复制一份把 UPDATE 改成 SELECT 先查一次。4.2 先 SELECT 后 UPDATE 的黄金习惯具体怎么执行这个习惯三步走-- 第一步查确认你要改的目标行 SELECT * FROM users WHERE id 7; -- 第二步开启事务 BEGIN; -- 第三步执行 UPDATE再看影响行数 UPDATE users SET balance balance - 50.00 WHERE id 7;在命令行端执行 UPDATE 时MySQL 会返回一行结果。早期的返回是这样Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0这里有两个关键信息Rows matched表示 WHERE 条件匹配到的行数Changed表示实际发生修改的行数。当你把某个字段改成和原来一样的值时Changed会是 0这不是更新失败是数据没有变化MySQL 默认跳过重复赋值。执行完 UPDATE 后不要立刻关掉命令行窗口停留在事务中先检查一下数据SELECT * FROM users WHERE id 7;确认无误后执行COMMIT提交事务。如果发现问题执行ROLLBACK回滚一切当作没发生。这套动作看起来多花几十秒但能挡住绝大多数低级错误。4.3 多表更新以及更新时容易忽略的隐式转换业务中经常需要根据 A 表的数据更新 B 表。比如要给下单总额超过 1000 的用户打上“高价值用户”标记UPDATE users u JOIN ( SELECT user_id, SUM(amount) AS total FROM orders WHERE status 1 GROUP BY user_id ) o ON u.id o.user_id SET u.level 1 WHERE o.total 1000;这里子查询先把每个用户的有效订单总额算好再关联更新用户表。这种写法比“逐个查再逐个更”效率高得多但也更危险——如果关联逻辑写错可能把不该更新的行也更新了。所以多表更新更需要先用 SELECT 验证关联结果SELECT u.id, o.total FROM users u JOIN ( SELECT user_id, SUM(amount) AS total FROM orders WHERE status 1 GROUP BY user_id ) o ON u.id o.user_id WHERE o.total 1000;还有一类问题是隐式类型转换。比如字段phone是 VARCHAR 类型你写WHERE phone 13812345678MySQL 会把字符串字段转成数字再比较一旦转换导致索引失效查询就慢更麻烦的是如果某条数据的 phone 是13812345678abc数字比较时它会被转成13812345678结果也“相等”了。这种隐蔽的数据错乱特别难排查。写条件时保持字段类型和值的类型一致字符串加引号数字别加引号。5. 删DELETE操作前最后一晌的理智5.1 DELETE 的基本操作与 TRUNCATE 的对比删除的标准姿势DELETE FROM orders WHERE id 1024;不带 WHERE 的 DELETE 会清空整张表这个行为的破坏力比不带 WHERE 的 UPDATE 更大因为 UPDATE 至少数据还在DELETE 之后只能靠备份恢复。清空整张表还有一个很容易踩的坑用 DELETE 还是 TRUNCATE两者差别非常大对比项DELETETRUNCATE类别DML属于事务操作DDL隐式提交无法回滚WHERE 条件支持不支持只能全表清空自增主键不回置重置为初始值执行速度逐行删除数据量大时很慢直接重建表结构非常快空间释放不立即释放磁盘空间立即释放空间触发器会触发不会触发从表格能看出来TRUNCATE TABLE orders和DELETE FROM orders表面效果都是清空但底层机制完全不同。我见过有人用 TRUNCATE 清空了一个业务表以为还能像 DELETE 那样在事务里回滚结果 TRUNCATE 执行完就自动提交了数据直接没了。另外要提一句InnoDB 的 DELETE 并不会立刻把磁盘空间归还给操作系统而只是把记录标记为删除。如果频繁删数据又观察磁盘空间没减少不用太紧张这是 InnoDB 的常规表现。想回收空间得用OPTIMIZE TABLE但这是 DDL 操作大表上执行会锁表生产环境操作前要评估好窗口期。5.2 删除前必须养成的三个习惯我给自己定的规矩每条删除 SQL 上线或执行前必须做完三件事第一先 SELECT后 DELETE。把DELETE FROM完整换成一个等价的SELECT *先看查出来的是不是你想删的那几条SELECT * FROM orders WHERE status 2 AND created_at 2024-01-01;第二数清影响行数。在 DELETE 前面加一个 COUNTSELECT COUNT(*) FROM orders WHERE status 2 AND created_at 2024-01-01;当行数和你预期偏差超过一个数量级时立刻停下来检查条件。比如你预估要删 100 条结果 COUNT 出来 10 万这中间一定有环节出了问题。第三套事务和备份。批量删除尽量别直接跑 AUTOCOMMIT而是手动包一层事务BEGIN; DELETE FROM orders WHERE status 2 AND created_at 2024-01-01; -- 确认影响行数和数据无误后再执行 COMMIT ROLLBACK;只要不 COMMIT随时可以回滚。对于这种操作我宁可多花一分钟确认也不想赌那一把。5.3 业务系统里更常见的软删除再聊一个架构层面的习惯很多业务表里的“删除”并不是物理删除而是逻辑删除也叫软删除。实现方式很简单给表加一个标记字段ALTER TABLE users ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除标记;删除操作变成 UPDATEUPDATE users SET is_deleted 1 WHERE id 7;日常查询里默认带上is_deleted 0条件。这样做的价值在于数据不会真的丢误操作可恢复历史统计也更完整。代价是每一条查询都要多写一个条件而且如果忘了加会查出“已删除”的数据——这对开发者的纪律性要求很高。不要觉得软删除是多此一举。真实业务里“删除用户”往往牵扯订单、评论、支付记录物理删除导致外键报错或者历史数据对不上账的例子太多了。能软删就先软删真要物理清理再走脚本和备份方案。6. 复盘我实盘总结的几个综合排错点6.1 字符集不一致导致的乱码和查询失败MySQL 的乱码问题排查起来比想象中麻烦。表面上你看到的是“查出来的中文变成问号”根因可能发生在三个环节客户端连接字符集、数据库/表字符集、字段字符集。最常见的修法是在连接后先执行SET NAMES utf8mb4;它一次设置客户端、连接、结果集三者的字符集。但如果在建库建表时用的是DEFAULT CHARSETutf8那 SET NAMES 也救不了存量数据因为数据存进去时就已经错了。所以建表规范上我把utf8mb4当成默认值来写任何一张新表建表语句里都带DEFAULT CHARSETutf8mb4并且连接串里也显式指定characterEncodingutf8两端对齐乱码概率就非常低。排查乱码问题时的思路顺序是先看客户端连接的字符集再看库表字段定义最后看数据源本身。一层一层验证不要一上来就改数据。6.2 为什么 UPDATE 和 DELETE 都要先想事务很多人习惯单条 SQL 自动提交改完就完事。但真实项目里一次业务操作往往涉及多条 SQL。比如下单要扣库存、建订单、记流水这三步任何一步失败其他步骤都得撤销。事务就是应对这种情况的START TRANSACTION; UPDATE products SET stock stock - 1 WHERE id 10 AND stock 1; INSERT INTO orders (user_id, product_id, amount) VALUES (1, 10, 99.00); INSERT INTO order_logs (order_id, action) VALUES (LAST_INSERT_ID(), CREATE); COMMIT;后面三条语句任何一条执行失败都可以ROLLBACK让整组操作回到起点。MySQL 的 InnoDB 引擎默认每条 SQL 是自动提交的没有事务包裹扣了库存但订单插入失败就会出现脏数据。判断是否需要事务有一个很简单的标准这次操作涉及两个以上的表或者同一个表有多条写语句相互关联就把它包进事务里。事务不要开太大保持短小否则长时间持锁会影响并发。6.3 一条 UPDATE 的排查链路为什么影响行数和预期不符最后分享一个实际排查过的例子整个过程能串起前面讲的好几个知识点。某次数据修复任务要“把订单状态为 1已支付且金额大于 0 的订单标记为已发货”。我执行了UPDATE orders SET status 3 WHERE status 1 AND amount 0;返回Rows matched: 1000, Changed: 999。奇怪匹配到 1000 行为什么只改了 999 行排查思路如下。第一步确认是否真的有一条数据在更新前已经是 status3。因为我把一次状态流转拆成了两步执行先查出了目标再执行更新如果第一次跑已经把某行更新了第二次相同条件再跑这行匹配得到但不会再被改变Changed自然少 1。这个现象在幂等更新里很常见不一定代表逻辑错误。第二步确认事务隔离级别和锁的并发影响。如果还有别的会话正在修改这段数据也可能导致状态已经变化。第三步查看 Warnings。mysql命令行返回的Warnings: 0表示没有警告如果有警告比如字符串截断、NULL 转默认值这里会体现出来。整个排查链路走下来确认是“重复执行导致已目标行无变化”逻辑本身没问题。但这件事给了我很深的印象执行 UPDATE 不要只看 Query OK要看 Rows matched、Changed、Warnings 三个值。它们讲的是三个层面的信息——条件命中、实际变化、潜在问题。只看任何一个都容易误判。6.4 一句话总结我的操作习惯如果让我把增删查改这部分经验浓缩成一句给后来者的话就是写 SELECT 时想索引写 INSERT 时想约束写 UPDATE 时想 WHERE写 DELETE 时想备份。这四个“想”字背下来很多事故根本不会发生。从建库建表到删数据MySQL 的基础操作并不复杂复杂的从来都是边界情况。字符集、NULL、隐式转换、事务回滚、执行顺序、影响行数——这些细节你在任何一本语法书里都能找到说法但只有在真实操作里踩过一遍才会真正长记性。希望这篇能帮你少踩几个。