1. 整体设计与思路拆解1.1 为什么“会写SQL”不等于“会做增删改查”很多人刚接触 MySQL 时觉得增删改查就是“背四个单词”INSERT、SELECT、UPDATE、DELETE。语法看一眼就会真正上手才发现不是那么回事。我之前带过不少新人普遍的现象是单条语句写得没问题一放进真实的业务场景就卡壳。比如为什么主键冲突时 INSERT 会直接报错为什么 DELETE 之后表还在但数据没了为什么同一个表在大数据量下 UPDATE 一下能锁半天。这些都是“会写SQL”之外的东西。可以这样理解数据库像一个仓库增删改查就是入库、出库、整理、报废这四类动作。每类动作背后都有规则——货架位置怎么分配主键与索引、什么货能进约束与类型校验、操作日志怎么记事务与binlog、多个人同时操作怎么办锁与隔离。如果你只关心“动作”本身忽略了规则后果就是数据被写坏、查询慢、误删了找不回来。这一篇是系列第一篇我先把四类操作的语法、执行逻辑、典型误区和实操演示完整串一遍。后面再单独拆索引、事务、优化器这些专题。适合刚入门 MySQL 的开发者、写后端接口要自己拼 SQL 的新人以及想系统补一遍基础的数据分析同学。1.2 一条SQL语句在MySQL里到底走过了什么路径在敲第一条语句之前我建议你先搞清楚 SQL 在服务端是怎么被处理的。这不只是概念问题对后续排查慢查询、理解优化策略都有帮助。一条普通的语句大概经过这几步客户端把 SQL 发给服务端连接器负责鉴权和建立连接如果是查询语句MySQL 会先查查询缓存8.0 之后已经移除然后进入分析器做词法分析和语法解析看看你的语句写没写错接着优化器决定要选哪条索引、以什么顺序关联表最后执行器调用存储引擎接口真正读写数据。我用一个类比来说明你把订单需求发给工厂前台先确认你的身份连接器审核员检查订单格式有没有问题分析器排产部门根据库存、成本和工期决定怎么做最划算优化器最后车间工人按排产计划干活执行器。这个路径说明了什么它说明即使你写的是同样的结果不同的写法可能让“优化器”做出完全不同的选择。比如 WHERE 条件上用了函数索引就很可能失效JOIN 的时候驱动表选错了性能会差一个量级。这些细节后续几篇会逐个展开但第一篇掌握好标准写法会给后面省很多麻烦。1.3 建议的练习环境与前置准备我不建议你在公司生产库上练手风险太大。自己笔记本装个 MySQL 8.0或者用 Docker 拉一个镜像足够用。版本建议 8.0 及以上。8.0 相比 5.7 做了不少改进默认字符集改成了 utf8mb4移除了查询缓存对窗口函数、CTE 的支持更完善。如果你还在用 5.7本文里的大部分内容同样适用只是有些默认行为有差异我会在相关位置标出来。客户端方面命令行 mysql 是基本功至少输命令要没问题。可视化工具我常用 DBeaver开源、跨平台同时还能看执行计划日常排查很舒服。Navicat 也不错看个人习惯但不要只会点界面核心 SQL 一定要能手写。建练习库的时候顺手做一件事用 utf8mb4 字符集建库。别再用老项目里常见的 utf8因为 utf8 在 MySQL 里最多存 3 字节emoji 和一些生僻字会直接变成问号这个问题后面我会专门讲。2. 核心语法细节与最容易踩的坑2.1 插入数据一条、多条和跨表导入的三种形态先看最基本的 INSERT 语法INSERT INTO 表名(列1, 列2, 列3) VALUES (值1, 值2, 值3);这里有几个地方值得较真。第一列名可以省略不写此时 VALUES 里的值必须覆盖所有列顺序要和表结构一致。但强烈不建议这么干。表结构一变你的 INSERT 就完蛋了而且代码可读性极差。写清楚列名是成本最低的自我保护。第二一条 INSERT 其实可以插多行INSERT INTO orders(order_no, user_id, amount, status) VALUES (A001, 101, 99.80, 0), (A002, 102, 120.00, 0), (A003, 103, 59.90, 1);批量插入能有效降低网络往返次数。刚接触 MySQL 的同学容易一条一条 insert跑一百万次慢得怀疑人生。改成批量后几十秒的活可能一秒内完成。第三INSERT ... SELECT 可以把查询结果直接写入目标表。这个在归档数据、初始化报表时非常实用INSERT INTO orders_archive(order_no, user_id, amount, status, created_at) SELECT order_no, user_id, amount, status, created_at FROM orders WHERE created_at 2023-01-01;注意目标表和查询结果的列对应关系类型尽量匹配否则会出现隐式转换导致写入精度损失或失败。再补几个容易出错的小坑自增主键列可以不给值MySQL 会自动生成。但如果你手动指定了 id 值后面再插入时自增计数会从“已存在的最大值 1”继续走不是从你的手动值继续。这在导数据时经常踩导完数据一插入主键冲突就来了。非空列在高版本 MySQL 里如果不给值默认会报错5.7 里可能只给个警告然后存空字符串。这涉及 SQL Mode 的 strict 设置生产库建议保持严格模式别把脏数据放进去。2.2 查询数据别小看WHERE、排序和分页SELECT 是日常用得最多的语句但大部分人也就是拿它“把数据拉出来”没想过怎么拉得又对又快。基础语法人人都知道SELECT 列1, 列2 FROM 表名 WHERE 条件 ORDER BY 列 ORDER BY 排序;我想强调几个业务里常踩的点。第一NULL 的判断永远不要用等号。-- 错误写法查不到任何记录 SELECT * FROM user WHERE phone NULL; -- 正确写法 SELECT * FROM user WHERE phone IS NULL;NULL 在 MySQL 里表示“未知”两个未知之间不能用等号比较这在理解上有点像“我不知道你不知道”没法画等号。同理条件里用phone ! 13800000000也查不出 phone 为 NULL 的记录因为未知不等于另一个值。第二排序时要注意 NULL 的排位。默认 ORDER BY 升序时 NULL 排在最前面降序时 NULL 排在最后面。如果业务上有特殊要求可以用ORDER BY field IS NULL, field来手动控制。第三分页别只写 LIMIT。大数据量下常用的是-- 第 1 页每页 20 条 SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 0; -- 第 2 页 SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 20;OFFSET 越大MySQL 还是要先扫描前面被跳过的行再往后找所以页码很深时性能会急剧下降。这种时候可以用“上一页最后一条记录的 id”来翻页这也是实践中比较有效的方法SELECT * FROM orders WHERE id 上一页最后一条的id ORDER BY id LIMIT 20;但是这种方案有一个前提排序字段必须唯一不然跨页可能出现重复或漏掉。所以翻页排序时通常都会带上主键列做次级排序。2.3 更新数据列、条件和“幂等”问题UPDATE 的坑核心在“条件”上。UPDATE 表名 SET 列1 值1, 列2 值2 WHERE 条件;如果你漏了 WHERE恭喜你全表数据都会被更新。这个低级错误在开发环境还能救生产环境就是事故。但我更想说的是另一件事很多系统里希望更新操作“幂等”——同一操作执行多少次结果都一致。比如把订单状态从“待支付”改成“已支付”执行一次和十次最终结果都应该一样。这种场景SQL 写法设计得好不好差别很大。假设你有一个状态流转表业务上只允许从未支付改到已支付。你在代码里可以先 SELECT 查出当前状态判断后再 UPDATE。但更推荐直接用一条 SQL 表达UPDATE orders SET status 1 WHERE order_no A001 AND status 0;然后检查受影响行数。如果受影响行数为 0说明订单不存在或者状态已经不是待支付。这样就把“查询再修改”的两步操作压缩成了一条原子性更强的语句。另外UPDATE 多列时列与列之间是用英文逗号分隔。初学者经常写成UPDATE orders SET status 1 AND amount amount - 5 WHERE id 1;这会把整条赋值变成一个逻辑表达式结果完全不是本意。每一对“列 值”之间必须是逗号UPDATE orders SET status 1, amount amount - 5 WHERE id 1;2.4 删除数据先说怎么保命DELETE 的杀伤力比 UPDATE 更大因为数据没了就是没了。虽然 InnoDB 在事务里可以 ROLLBACK但如果你是在自动提交模式下删完马上后悔也来不及。先看基础语法DELETE FROM 表名 WHERE 条件;DELETE 有几个必须知道的特点。DELETE 只删数据不删表结构。表还在字段还在自增计数也不会重置。DELETE 是一行一行删会逐条写 binlog大表删除时会产生大量日志可能拖慢主从同步。在生产环境删除数据前最稳妥的做法是先跑一条相同的 SELECT 确认范围再执行 DELETE。这一步很多人不屑但踩过坑的都懂它有多重要。除了 DELETE还有一个快速清空的 TRUNCATETRUNCATE TABLE 表名;TRUNCATE 是直接把表重置相当于把所有数据页释放掉速度远快于 DELETE。但它有两个代价不能加 WHERE 条件只允许整个表清空不会逐行触发所以基本无法在事务里回滚不同版本行为有差异但别指望它。如果是平时开发里想清空临时表TRUNCATE 是合适的如果想删某几条数据老老实实用 DELETE。再说外键。如果表之间有外键约束DELETE 父表数据时如果子表还有引用要么报错要么级联删除完全取决于你建表时的 ON DELETE 策略。实际项目里用不用外键见仁见智但你要清楚自己项目的策略不然删了父表才发现子表一堆孤儿数据就麻烦了。3. 实操演示从建表到完整跑通一轮增删改查3.1 先建好演示用的订单表实际操作从一张表开始。我建一个相对简单的订单表字段不多但足够覆盖日常练习场景。CREATE TABLE orders ( id int NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT 订单号, user_id int NOT NULL COMMENT 买家ID, amount decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, remark varchar(255) DEFAULT NULL COMMENT 备注, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;几个设计点说一下金额用了 decimal(10,2)不要用 float/double否则会有精度误差。状态用 tinyint 而非字符串省空间而且后续扩展状态值不需要改结构。order_no 建了唯一索引模拟业务上订单号不能重复的场景同时用它来做幂等控制。一个表尽量有一个明确的“主键”MySQL 底层聚簇索引依赖它。没主键的话 InnoDB 会自己做隐藏处理但性能总归不如你显式定义的好。updated_at 用了 ON UPDATE CURRENT_TIMESTAMP更新时自动刷新省得每次手动 update 这个字段。如果你用的是 5.7 版本第一条 insert 之前记得确认 SQL Mode否则后面语法可能有细微差异。3.2 数据写入部分的完整实践先插入第一条数据最完整、最稳妥的写法INSERT INTO orders(order_no, user_id, amount, status, remark) VALUES (20240101001, 1001, 128.00, 0, 新用户首单);执行后再插入一批数据INSERT INTO orders(order_no, user_id, amount, status, remark) VALUES (20240101002, 1002, 89.90, 1, NULL), (20240101003, 1003, 15.50, 2, 特价商品), (20240101004, 1001, 329.00, 3, 大件商品), (20240101005, 1004, 66.00, 4, 用户取消), (20240101006, 1005, 199.00, 0, NULL);如果你想看插入是否成功查一下表里现在有几条SELECT COUNT(*) FROM orders;这里有个小细节MySQL 里 COUNT() 和 COUNT(id) 的结果在没有 NULL 一般一样但 COUNT() 统计的是所有行数不取具体列引擎层做了优化日常统计都推荐 COUNT(*)。再试一次把一张旧表的数据导入订单表。假设我有张 orders_bak 表结构一样里面放了 2023 年的数据。导入脚本INSERT INTO orders(order_no, user_id, amount, status, remark, created_at) SELECT order_no, user_id, amount, status, remark, created_at FROM orders_bak WHERE created_at 2024-01-01;注意两件事。第一目标表有唯一索引源表和目标表如果订单号重复这条 INSERT 会直接报错。第二导入大量数据时尽量分批避免一个超大事务把回滚段撑爆。3.3 查询部分的完整实践给定场景查询用户 1001 的所有订单按创建时间倒序排列SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1001 ORDER BY created_at DESC;这个语句执行时MySQL 优化器大概率走 idx_user_id 索引定位到用户 1001 的数据再在内存中排序。小数据量无所谓但是如果你以后发现这个查询慢可以考虑建一个(user_id, created_at)的联合索引直接把排序也解决了。再来一个条件查询找出金额大于 100 且状态为待支付的订单SELECT * FROM orders WHERE amount 100 AND status 0 ORDER BY amount DESC;这里有个值得记住的点如果业务中频繁需要这类查询单独的 idx_user_id 起不到作用需要为 amount 或者 status 单独建索引。不过 WHERE 条件里同时用两个非索引列时要结合数据分布决定建哪个索引这个先不展开。分页查询是家常便饭SELECT order_no, amount, status, created_at FROM orders ORDER BY id LIMIT 2 OFFSET 2;这个结果相当于跳过前两条拿第三、第四条。LIMIT 和 OFFSET 的顺序不能写反。还要注意 OFFSET 的值从 0 开始第一页的 OFFSET 是 0 而不是 1。聚合统计来一个每天的订单数和销售总额SELECT DATE(created_at) AS day, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY DATE(created_at) ORDER BY day;GROUP BY 的列可以是原始列也可以是表达式。用 DATE(created_at) 分组时查询里出现的非聚合列要谨慎比如直接 SELECT order_no 在大多数 SQL 模式不会报错但取到的是组内随意一行容易造成误解。3.4 更新部分的完整实践最简单的单行更新UPDATE orders SET status 1 WHERE order_no 20240101001;执行后你可以用受影响行数判断如果返回 1说明订单存在且状态发生了修改如果返回 0可能订单不存在也可能订单已经是状态 1这要看 MySQL 客户端显示的行数语义。默认设置下它在没有实际变化时也会显示 0但满足条件的数据本身是存在的。这一点很多人在做接口时要先想清楚别把“受影响行数为0”直接等同于“数据不存在”。多列同时更新把金额小于 20 的订单直接改成已取消并备注“低金额自动取消”UPDATE orders SET status 4, remark CONCAT(IFNULL(remark, ), 低金额自动取消) WHERE amount 20 AND status IN (0, 1);注意 CONCAT 里处理了 remark 为 NULL 的情况。NULL 拼接任何字符串结果还是 NULL这是新手容易踩的坑所以先用 IFNULL 兜底。再试一个回归场景用户 1001 所有已发货状态的订单统一改成已完成UPDATE orders SET status 3 WHERE user_id 1001 AND status 2;受影响的只有符合条件的行范围条件写清楚更新操作就是安全的。3.5 删除部分的完整实践模拟一个操作把备注为空且状态为已取消的订单数据删除。先选出来看看确认范围SELECT id, order_no FROM orders WHERE remark IS NULL AND status 4;确认没问题再执行删除DELETE FROM orders WHERE remark IS NULL AND status 4;如果表里有主键更推荐根据主键列表来删DELETE FROM orders WHERE id IN (5);这个操作没有走索引扫全表的风险尤其在生产环境执行小范围删除时WHERE 里带上主键最稳。如果你想把整个表清空重建用TRUNCATE TABLE orders;注意 TRUNCATE 之后自增 id 会从 1 重新开始而 DELETE 不会。假如你只是删了几条数据但还想继续保持原来的 id 增长位置别用 TRUNCATE。4. 真实业务里反复出现的典型问题4.1 中文和emoji变成问号我见过太多项目上线后遇到这个问题。INSERT 时明明写的是中文select 出来是“???”或者 emoji 直接报错。根因是字符集。MySQL 建库时如果用老的 utf8实际最多存 3 字节emoji 是 4 字节存不进去而 utf8mb4 才是“完整版 UTF-8”。中文在 utf8 其实能存但连接层、服务端、表结构任意一环字符集不一致就会出现乱码。建议统一设置-- 建库时指定 CREATE DATABASE demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 建表时也显式指定 CREATE TABLE orders (...) DEFAULT CHARSETutf8mb4;连接层面如果是自己的代码连接串里加上characterEncodingutf8或者characterSetResultsutf8mb4具体取决于驱动。命令行连接时可以先执行SET NAMES utf8mb4;这个命令会把客户端、连接、返回结果的字符集一次性设好排查乱码的第一个动作就是它。4.2 主键冲突时到底该用INSERT还是UPDATE业务上经常遇到“有记录就更新没记录就插入”比如保存用户的最新收货地址。很多人先 SELECT 判断存在就 UPDATE不存在就 INSERT。这个逻辑在并发下会有问题两个请求同时发现不存在然后同时 INSERT其中一个就会主键冲突。MySQL 提供了几个更优雅的解法。第一条INSERT ... ON DUPLICATE KEY UPDATEINSERT INTO orders(order_no, user_id, amount, status) VALUES (20240101002, 1002, 99.00, 1) ON DUPLICATE KEY UPDATE amount VALUES(amount), status VALUES(status);这个语句在唯一索引或主键冲突时会自动转为 UPDATE。不过注意VALUES() 函数在新版本里已经开始废弃8.0.20 之后可以改成别名语法这里写法保持传统兼容性遇到升级再调整。第二条INSERT IGNOREINSERT IGNORE INTO orders(order_no, user_id, amount, status) VALUES (20240101002, 1002, 99.00, 1);冲突时直接跳过不报错。适用于“只要没冲突就插入冲突了什么都不做”的场景比如记录上报日志重复上报直接丢弃。第三种REPLACE INTOREPLACE INTO orders(order_no, user_id, amount, status) VALUES (20240101002, 1002, 99.00, 1);REPLACE 遇到冲突时会把旧记录删除重新插入所以 id 可能会变化关联了外键的数据有被级联删除的风险。除非你明确知道后果否则少用。4.3 一个DELETE让全表数据消失很多人刚学习时写过DELETE FROM 表名;把表清空了。开发环境还好生产环境就是大事故。MySQL 提供了一个保护开关叫 SQL_SAFE_UPDATES。开启后不带 WHERE 条件的 DELETE 和 UPDATE 都会被拒绝SET SQL_SAFE_UPDATES 1;这个命令需要在当前会话里执行生产上可以用在权限管理或者启动参数里强制。但要注意开了这个开关如果你确实要清空全表得用 TRUNCATE或者明确给一个恒真条件不过这么做意义不大还不如 TRUNCATE 干净。还有一个习惯值得培养删除前先 SELECT更新前先 SELECT。我自己的习惯是写一个脚本级别的“预检查”-- 第一步查数量 SELECT COUNT(*) FROM orders WHERE ...; -- 第二步查样例 SELECT * FROM orders WHERE ... LIMIT 5; -- 第三步执行变更 DELETE FROM orders WHERE ...;这个习惯花不了几秒钟但能把事故率降低九成。4.4 大表批量更新卡死有一次我需要把一张千万级订单表里的状态字段全部更新直接写了一条 UPDATE 不带分页跑了十几分钟把主库写入阻塞了很久。这个教训很深刻。原因在于单条大 UPDATE 持有一个大事务锁了很多行binlog 一写就是几个 GB。不仅时间长还会把主从延迟拉满。解决办法是分批处理UPDATE orders SET status 2 WHERE id 0 AND id 50000 AND status 1;然后循环把 id 范围往后挪。也可以在业务代码里写个循环每批提交 5000 条中间 sleep 一下给主从同步留缓冲时间。还有一个细节每批 UPDATE 的条件都带上 status 1这样可以保证同一行不会被重复更新并且多执行几次是安全的天然幂等。4.5 空值查询为什么总是失败前面说过 NULL 不能拿等号比较。但业务里容易忽略的是 NOT IN 遇到 NULL 的问题。比如你想查“不在这几个用户ID范围内的订单”SELECT * FROM orders WHERE user_id NOT IN (1001, 1002, NULL);这个查询一条数据都不会返回。因为 NOT IN 本质上等价于多个!的 AND 组合而user_id ! NULL结果是 UNKNOWN整个 WHERE 条件无法成立。遇到这种情况要么在子查询结果里过滤掉 NULL要么改用 NOT EXISTSSELECT * FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.user_id o.user_id );NOT EXISTS 对 NULL 的处理更符合直觉这种写法也更容易扩展到复杂业务。我个人的习惯是在源数据层面尽量给可空字段设默认值比如备注字段默认空字符串而不是 NULL。虽然 NULL 在数据库里有它的语义但很多业务逻辑处理起来很容易忽略甚至有的 ORM 框架对 NULL 字段的处理会产生额外开销。当然如果时间字段和状态字段不需要默认值保留 NULL 也没问题关键是你自己要清楚项目里每个字段的空值策略。再分享一个小技巧调试 SQL 时遇到“结果不对”但人眼看不出问题优先检查三个地方——字符集是否一致、NULL 是否被当成了普通值、WHERE 条件的范围是否包含边界值。这三个地方占了九成以上“看似正确却结果错误”的情况。别急着怀疑 MySQL 出了 Bug大多数时候是自己查询逻辑和字段语义没对齐。