MySQL SQL基础语法与实战：增删改查、JOIN、窗口函数与索引优化-北京尧图网络

1. 先搞清楚版本、字符集和连接环境刚带实习生那会儿我最怕听到一句话——“MySQL 基础语法我学过啊”。然后他写出来的 SQL 里SELECT后面跟着没进GROUP BY的列UPDATE忘写WHERE改数据之前还不先SELECT确认一遍。基础语法这东西看着简单可真到生产环境里决定的往往是你几点下班。这篇东西我打算按自己的实际使用习惯来捋一遍 MySQL 的 SQL 基础语法从建库建表到增删改查从 JOIN、聚合到窗口函数再到索引和慢 SQL 的初步排查。适合刚入门、SQL 写得出来但心里没底的人也适合写了几年但一直靠“抄同事的语句”混日子的老手——你可能在里面找到几个一直没搞明白为什么这么写的点。SQL 本身是门声明式的语言你描述“要什么”而不是“怎么拿”。这个特性决定了初学者最容易踩的坑语句语法完全合法结果却和你想的完全不一样。下面所有内容都围绕这个矛盾展开。1.1 为什么建议直接上 8.0现在网上搜 mysql 安装教程还能看到一堆 5.7 的老文章。我的建议很直接新项目直接 8.0甚至是 8.0.3x 之后的版本。原因不复杂——窗口函数ROW_NUMBER、RANK、LAG这类是 8.0 才有的CTE也就是WITH ... AS (...)也是 8.0 才有的JSON 字段的函数支持在 8.0 也完善得多。没有窗口函数的日子是什么样的你要算“每个部门工资排名前三”得写一堆自连接或者用户变量语句又长又慢还容易出错。8.0 之后一行ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)解决。这不是语法糖这是实打实的效率差距。官方下载渠道认准 MySQL 官网的 Community Server 版本就够用了社区版和企业版在语法层面没有区别差别主要在备份工具、监控插件、技术支持这些外围。学习阶段完全不需要纠结。注意如果你接手的是老系统别急着升级版本。8.0 有几个默认行为变化会咬人比如默认字符集从latin1变成utf8mb4、GROUP BY不再隐式排序、认证插件默认变成caching_sha2_password老客户端连不上。升级前先在测试库跑一遍业务 SQL。1.2 字符集与排序规则utf8mb4 和 utf8 不是一回事这是我认为最值得单独拎出来讲的一个点因为它坑过太多人。在 MySQL 里utf8这个名字是历史遗留的误称它实际指的是utf8mb3每个字符最多用 3 个字节。而真正的 UTF-8 标准最多需要 4 个字节。哪些字符需要 4 个字节Emoji、部分生僻汉字、一些特殊符号。所以用utf8存用户昵称遇到 emoji 就报错或者变成问号。正确做法是建库建表时统一用utf8mb4CREATE DATABASE demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;排序规则COLLATE决定字符串怎么比较和排序。utf8mb4_0900_ai_ci里的ai是 accent insensitive忽略重音ci是 case insensitive忽略大小写。这意味着A a在 MySQL 默认情况下是成立的。如果你需要区分大小写比如校验码、token 这类字段就得用utf8mb4_0900_as_cs或者在查询时加BINARY。这里有个隐蔽的坑表和字段的字符集可以不一致。库是utf8mb4某个字段却建成了latin1做 JOIN 的时候就可能出现隐式转换索引直接失效。所以我的习惯是建表时把字符集和排序规则都显式写出来不依赖继承。1.3 客户端工具怎么选命令行客户端mysql是必须会用的因为服务器上通常只有它。关键参数记几个-h指定主机-P端口-u用户-p后面紧跟密码不加空格-e直接执行一条语句-D指定库。mysql -h 127.0.0.1 -P 3306 -u root -p -D demo -e SELECT NOW();图形化工具方面MySQL Workbench 是官方的建模、ER 图、执行计划可视化都齐全缺点是启动慢、界面笨重在远程连接下偶尔卡死。Navicat 系列体验更顺手但要注意版本授权问题个人学习阶段有不少免费替代品可选功能上做日常查询和表结构管理都够用。工具本身不影响你写的 SQL别在这上面纠结太久。2. 库与表DDL语法的骨架与选型逻辑DDL数据定义语言这块语法不多CREATE、ALTER、DROP、TRUNCATE四个动词撑起大半。但真正的功夫在选型和约束设计上——表结构一旦定型后面改起来的成本远高于写代码。2.1 建库建表的最小可用模板我写建表语句有个固定的骨架分享给你参考CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 金额, status TINYINT NOT NULL DEFAULT 0 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_order_no (order_no), KEY idx_user_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;几个刻意的选择解释一下。主键用BIGINT UNSIGNED而不是INT。INT有符号上限是 21 亿看起来很多但订单、日志、消息这类高频写入的表一两年就能撞到天花板。到那时改主键类型是场灾难。金额用DECIMAL而不是FLOAT或DOUBLE。浮点数是二进制近似存储0.1 0.2在浮点下不等于0.3。钱相关的字段用浮点迟早会出现对不上账的情况而且很难排查。引擎明确写InnoDB。5.5 之后 InnoDB 就是默认引擎了但显式写出来是个好习惯因为 MyISAM 不支持事务、不支持行锁、崩溃后不能自动恢复除了极少数只读归档场景现在几乎没有理由再用它。created_at和updated_at交给数据库自动维护能让业务代码少写一行赋值也避免了各语言时区处理不一致导致的混乱。注意不要用timestamp存时间戳除非你清楚它 2038 年的上限。用DATETIME更稳取值范围到 9999 年。两者的时区行为也不同TIMESTAMP会按时区转换DATETIME存什么就是什么。用哪个取决于你的业务是否需要跨时区。2.2 数据类型选型别再用 varchar(255) 糊弄VARCHAR(255)是个万金油也是个坏习惯。它带来的问题不是存不下而是第一内存中的临时表会按定义长度分配空间部分场景宽字段会让排序、分组变得很吃力。第二255 这个长度会让人放弃思考“这个字段到底该多长”结果就是一个手机号字段也能塞进去 200 个字符。第三在联合索引里字段定义长度直接影响单个索引页能放多少条记录。我的选型经验大致是这样数据性质推荐类型理由手机号、身份证号CHAR(11) / VARCHAR(18)定长用 CHAR避免尾部空格问题需注意订单号、UUIDCHAR(32) 或 VARCHAR(36)等长查询快UUID 含横杠用 36状态、类型枚举TINYINT1 字节配合代码里的常量映射金额、比率DECIMAL精确计算不丢精度短文本标题、名称VARCHAR(64~200)按业务上限加一点余量长文本、日志TEXT / JSON注意不能直接建普通索引布尔TINYINT(1)MySQL 没有真正的 BOOLEAN状态字段我强烈建议用数字而不是字符串。存PAID和存1在索引体积、比较速度上都有差距更重要的是枚举字符串一多业务代码里到处是魔法字符串改起来要命。用一个常量和数字的映射关系清晰又省空间。另外NULL和空字符串是两回事。NULL表示“未知”它不参与大多数比较运算WHERE status 1这种写法会漏掉status IS NULL的行。我的习惯是能设NOT NULL DEFAULT的字段就设上把NULL留给真正语义上“未知”的字段。2.3 字段约束与默认值把校验前移到数据库约束是数据库帮你兜底的最后一道防线。应用层的校验可能因为代码分支漏掉也可能因为并发绕过但数据库约束不会。常用的几类NOT NULL非空约束最基础也最有用。UNIQUE KEY唯一约束业务上的唯一性订单号、用户名必须靠它保证不能只靠代码里先查后插那个在并发下必然出问题。PRIMARY KEY主键隐含非空和唯一。FOREIGN KEY外键。互联网业务里经常不用因为外键在高并发写入时会产生额外的锁开销。但内部管理系统、中小项目用上它能省掉大量脏数据排查工作。DEFAULT默认值能让插入语句少写很多字段。ALTER改表时要特别注意在 8.0 之前加字段、改字段类型这类操作经常是“锁表重建整表”大表上执行可能持续几十分钟。8.0 支持了INSTANT算法只改元数据加字段几乎是秒级完成但改名、改类型仍然代价很高。ALTER TABLE t_order ADD COLUMN remark VARCHAR(200) DEFAULT NULL, ALGORITHMINSTANT;ALGORITHMINSTANT明确指定算法如果不支持会直接报错而不是悄悄降级去锁表这个行为对生产环境很重要——报错比默默锁表好得多。3. DML四件套增删改查的正确姿势插入、更新、删除、查询日常 90% 的 SQL 都在这四个动词里。它们语法简单但每一个都有让人翻车的细节。3.1 INSERT 的几种写法和批量插入的取舍最基础的写法INSERT INTO t_order (user_id, order_no, amount) VALUES (1001, NO20240101, 99.00);字段列表一定要显式写。省略字段列表的写法INSERT INTO t_order VALUES (...)依赖表的物理列顺序一旦有人加了个字段或者调整了顺序语句就会静静地把数据插错列——而且不一定报错。批量插入INSERT INTO t_order (user_id, order_no, amount) VALUES (1001, NO001, 10.00), (1002, NO002, 20.00), (1003, NO003, 30.00);批量插入比循环单条插入快一个数量级因为减少了网络往返和事务提交次数。但也别一次塞几万条——单条 SQL 过大可能超过max_allowed_packet而且失败时整个语句回滚重试成本高。我一般控制在 500 到 1000 条一批。INSERT INTO ... SELECT是个高频场景比如把历史数据挪到归档表INSERT INTO t_order_archive (id, user_id, order_no, amount) SELECT id, user_id, order_no, amount FROM t_order WHERE created_at 2024-01-01;一个容易忽略的点INSERT ... ON DUPLICATE KEY UPDATE可以做到“存在则更新、不存在则插入”但它有个副作用——每执行一次都可能让自增主键跳号因为自增是在冲突检测前分配的。如果业务对 ID 连续性有要求其实大多数场景都不该有要留意这点。3.2 UPDATE 与“更新子查询”那个老坑UPDATE只有一条铁律写WHERE并且在执行前先把同样的条件换成SELECT跑一遍。-- 第一步确认影响范围 SELECT COUNT(*) FROM t_order WHERE status 0 AND created_at 2024-01-01; -- 第二步改成 UPDATE UPDATE t_order SET status 9 WHERE status 0 AND created_at 2024-01-01;这个习惯救过我很多次。特别是在生产库上手动执行语句时SELECT和UPDATE之间哪怕只隔 10 秒条件读错一个字段影响面就是天壤之别。再说那个经典报错ERROR 1093 (HY000): You have an error in your SQL syntax... You cant specify target table xxx for update in FROM clause完整场景大概是这样-- 报错写法 UPDATE t_order SET amount 0 WHERE user_id IN (SELECT user_id FROM t_order WHERE status 9);MySQL 不允许在UPDATE的子查询里直接引用正在被更新的表。原因和它的实现有关更新过程中的数据视图和子查询的读取会互相干扰产生不确定的结果。绕过办法是套一层派生表让子查询的结果先物化UPDATE t_order SET amount 0 WHERE user_id IN ( SELECT user_id FROM (SELECT user_id FROM t_order WHERE status 9) AS tmp );或者更清爽的写法用 JOINUPDATE t_order o JOIN (SELECT DISTINCT user_id FROM t_order WHERE status 9) t ON o.user_id t.user_id SET o.amount 0;我更推荐 JOIN 版本可读性和执行计划都更可控。子查询套派生表那种写法在数据量大时可能因为物化产生临时表性能反而更差。另外要提醒UPDATE语句一定要配LIMIT吗在明确的批量修正场景下加LIMIT是种保护机制避免一次锁住太多行。但要注意UPDATE ... LIMIT在没有ORDER BY时更新哪些行是不确定的别指望它能“只改前面几条”。3.3 DELETE、TRUNCATE、DROP 的边界三个动词都能“让数据消失”但行为完全不同操作是否可回滚是否重置自增是否走日志触发触发器适用场景DELETE可以否逐行记录是按条件删部分数据TRUNCATE不能是记录页释放否清空整表DROP不能-记录元数据否删除表结构DELETE是 DML逐行删除并写 undo log所以可以回滚也正因如此大表全删会非常慢。TRUNCATE是 DDL直接重建表速度快到几乎瞬时但不可回滚执行前必须确认。有个容易被忽略的行为DELETE不会重置AUTO_INCREMENT删完再插ID 会接着之前的最大值往后走。很多人以为删空了表 ID 就能从 1 开始结果发现不是。还有DELETE不带WHERE的情况。我见过有人为了“清空测试数据”写了DELETE FROM t_order;然后手滑在错误的库上执行。所以现在我的习惯是生产环境的手动删除一律先写成SELECT确认无误后加LIMIT 1试删最后才放开条件。3.4 SELECT 的执行顺序与书写顺序这是我认为基础语法里最值钱的一个知识点。你写SELECT的顺序和 MySQL 实际执行的顺序是不一样的。书写顺序大致是SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT实际执行顺序是FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT这个错位解释了很多“为什么”。比如为什么WHERE里不能用SELECT中定义的别名因为WHERE执行时SELECT还没轮到。为什么ORDER BY里可以用别名因为它在SELECT之后。为什么HAVING能过滤聚合结果WHERE不能因为聚合发生在GROUP BY之后、HAVING之前而WHERE更早。理解了执行顺序你对 SQL 的理解就从“背语法”升级到“推理语法”。遇到不熟悉的写法先想想它在执行链的哪个位置基本就能判断能不能用。4. 查询进阶JOIN、聚合、子查询、窗口函数基础查询写熟之后真正的瓶颈就转移到“怎么把多张表拼起来”和“怎么算统计口径”上。这两块是面试必问也是日常工作里最容易写出慢 SQL 的地方。4.1 JOIN 到底怎么连JOIN的本质是集合运算。INNER JOIN求交集LEFT JOIN保留左表全部RIGHT JOIN保留右表全部FULL OUTER JOINMySQL 不直接支持得用LEFT JOIN UNION RIGHT JOIN模拟。SELECT u.id, u.name, o.order_no, o.amount FROM t_user u LEFT JOIN t_order o ON o.user_id u.id AND o.status 1 WHERE u.created_at 2024-01-01;这里有个关键细节ON里的条件和WHERE里的条件对LEFT JOIN来说语义完全不同。写在ON里不满足条件的右表行不参与匹配但左表行依然保留对应字段为NULL。写在WHERE里如果条件是针对右表字段的会把右表为NULL的行也过滤掉LEFT JOIN就退化成INNER JOIN了。我见过太多人写LEFT JOIN之后WHERE o.status 1然后抱怨“我的左表数据怎么少了”。这不是 MySQL 的 bug是语义理解的问题。关于JOIN的性能有个常见的认知误区认为JOIN本身很慢。实际上没索引的JOIN慢是慢在笛卡尔积。只要关联字段上有索引JOIN的执行方式8.0 之后主要是Nested Loop Join部分场景有Hash Join效率是很高的。JOIN和子查询怎么选经验法则是能用JOIN表达的优先用JOIN。因为早期 MySQL 对子查询的优化比较差尤其是IN (SELECT ...)和NOT IN (SELECT ...)经常被改写成低效的EXISTS或者产生物化临时表。8.0 之后优化器强了很多子查询性能有明显改善但JOIN依然是更稳的选择。注意NOT IN有个致命陷阱如果子查询结果里有NULL整个NOT IN会永远返回空结果集。这个坑非常隐蔽用NOT EXISTS或者LEFT JOIN ... WHERE 右表.id IS NULL可以规避。4.2 GROUP BY 与 HAVING 的分工聚合查询的骨架SELECT里放分组字段和聚合函数GROUP BY里放分组字段HAVING过滤聚合结果。SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total FROM t_order WHERE created_at 2024-01-01 GROUP BY user_id HAVING total 1000 ORDER BY total DESC LIMIT 10;WHERE和HAVING的分工很清楚WHERE在分组前过滤原始行能大幅减少参与聚合的数据量HAVING在分组后过滤只能对聚合结果动手。能放在WHERE里的条件绝不要放到HAVING这是性能差异不是风格问题。ONLY_FULL_GROUP_BY这个 SQL 模式值得说一句。8.0 默认开启意思是SELECT列表里的非聚合列必须出现在GROUP BY里。很多人嫌它烦第一反应是关掉它。我的建议是别关。这个模式是在帮你发现逻辑漏洞——如果一列既没分组也没聚合你选它到底想拿哪个值MySQL 5.7 之前会随便取一行结果不可预测那才是真正危险的。真需要取“分组内某一行”的值用GROUP_CONCAT或者窗口函数别靠关闭模式来绕过。4.3 子查询与派生表子查询按位置分三类标量子查询返回单个值、行子查询、表子查询FROM后面也就是派生表。按关联性分非关联子查询可独立执行和关联子查询依赖外层行。-- 标量子查询 SELECT id, amount, (SELECT name FROM t_user u WHERE u.id o.user_id) AS user_name FROM t_order o; -- 派生表 SELECT t.user_id, t.cnt FROM (SELECT user_id, COUNT(*) AS cnt FROM t_order GROUP BY user_id) t WHERE t.cnt 5;派生表在 8.0 之后可以被优化器合并进外层查询性能好了很多。但要注意派生表必须有别名否则报语法错误这个错误信息有时候表述得不太直白。关联子查询要谨慎使用。它对外层每一行都要执行一次内层查询外层 10 万行就是 10 万次。虽然优化器有时能改写成JOIN但依赖优化器的“有时”不够可靠。看执行计划确认才是正经做法。4.4 窗口函数SQL里的算排名利器窗口函数是 8.0 最值得学的特性。它和GROUP BY的区别在于GROUP BY会把多行折叠成一行窗口函数不会它给每一行附加计算结果。SELECT user_id, order_no, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn, SUM(amount) OVER (PARTITION BY user_id) AS user_total, LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount FROM t_order;PARTITION BY相当于分组但不折叠ORDER BY决定窗口内的顺序。三个最常用的排名函数区别要记牢函数相同值处理结果示例ROW_NUMBER()不并列强制编号1, 2, 3, 4RANK()并列后跳号1, 2, 2, 4DENSE_RANK()并列后不跳号1, 2, 2, 3“取每组前 N 条”这个需求用窗口函数写起来是最干净的SELECT * FROM ( SELECT user_id, order_no, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM t_order ) t WHERE t.rn 3;注意窗口函数不能写在WHERE里因为它的计算在WHERE之后。必须套一层子查询这个限制来自执行顺序理解了第 3.4 节就不觉得奇怪了。5. 索引、排序与慢SQL的第一轮优化写到一定程度你会从“SQL 怎么写对”转向“SQL 怎么跑快”。这一步绕不开索引和执行计划。5.1 索引创建语法与最左前缀CREATE INDEX idx_user_status ON t_order (user_id, status); CREATE UNIQUE INDEX uk_order_no ON t_order (order_no); ALTER TABLE t_order ADD INDEX idx_created (created_at);联合索引的核心规则是最左前缀。(user_id, status)这个索引能支持WHERE user_id ?、WHERE user_id ? AND status ?但不支持单独WHERE status ?。原因在于 InnoDB 的索引是 B 树结构数据先按user_id排序user_id相同时再按status排序。跳过第一列status在整个索引里是乱序的没法用来定位。有个例外情况WHERE status ?虽然不能走索引查找但如果优化器判断扫描整个索引比扫描整张表便宜覆盖索引场景可能会走索引扫描。这是两回事别混淆。联合索引的字段顺序怎么定两个原则等值查询的字段放前面范围查询的字段放后面。因为范围查询之后的字段无法再用索引定位。区分度高的字段放前面。区分度是COUNT(DISTINCT col) / COUNT(*)越接近 1 越适合做索引前导列。索引不是越多越好。每个索引都占存储而且会拖慢写入——插一行数据要同步维护所有索引。一张表的索引数量最好控制在 5 个以内超过就要审视是不是有冗余索引比如已有(a, b)再建(a)就是冗余的。5.2 排序、分页与深分页ORDER BY能用上索引的前提是排序字段和索引顺序一致且排序方向统一。ORDER BY a ASC, b DESC这种混合方向在 8.0 之前基本用不上索引8.0 支持了降序索引才有改善。-- 可以走索引 SELECT * FROM t_order WHERE user_id 1 ORDER BY created_at DESC LIMIT 20; -- 配合 (user_id, created_at) 索引分页是另一个高频痛点。LIMIT 1000000, 20这种深分页MySQL 会先扫描并丢弃前 100 万行越翻越慢。常见优化手法-- 方案一基于游标记住上一页最后一条的 ID SELECT * FROM t_order WHERE id 1000000 ORDER BY id LIMIT 20; -- 方案二延迟关联先用覆盖索引拿到主键再回表 SELECT o.* FROM t_order o JOIN (SELECT id FROM t_order ORDER BY created_at DESC LIMIT 1000000, 20) t ON o.id t.id;方案一需要业务上能接受“不能跳页”适合信息流方案二改动小适合后台列表。方案二的原理是让子查询在索引上完成分页只回表 20 次而不是回表 100 万次。注意LIMIT后面跟两个参数时逗号前面是偏移量后面是条数。写成LIMIT 20 OFFSET 1000000语义相同可读性更好不容易看错。5.3 EXPLAIN 看什么EXPLAIN是你和优化器对话的唯一入口。在语句前加一个词就能看到执行计划EXPLAIN SELECT * FROM t_order WHERE user_id 1001 ORDER BY created_at DESC LIMIT 10;输出的列不少初学阶段重点盯四个列名关注点type访问类型从好到差system const eq_ref ref range index ALL。出现ALL说明全表扫描要警惕key实际使用的索引。如果是NULL说明没走索引rows预估扫描行数越小越好。这个值只是估算可能偏差很大Extra补充信息。看到Using filesort说明有额外排序看到Using temporary说明用了临时表这两个在大表上都要想办法消掉Using filesort不一定真的在磁盘上排序内存里排也叫这个名字别被字面意思吓到。但如果rows很大又带filesort那就是实打实的性能瓶颈。8.0 之后可以用EXPLAIN ANALYZE它会真正执行语句并给出实际耗时和行数比估算的EXPLAIN准得多。代价是语句真的会跑一遍所以别在写操作上随便用。6. 存储过程与日常自动化存储过程是 MySQL 里争议比较大的一块。互联网业务里用得少但在数据批处理、定时任务、报表生成这些场景它依然有独特价值。6.1 存储过程的适用边界先说清楚什么时候不该用业务逻辑尽量别写进存储过程。原因是调试困难、版本管理麻烦DDL 脚本不容易纳入 Git 流程、数据库迁移时基本要重写。什么时候可以用批量造测试数据、定时清理历史数据、跨库数据同步这类“贴着数据库跑”的任务。这些场景把逻辑放进去能省掉网络往返和外部调度依赖。语法骨架DELIMITER $$ CREATE PROCEDURE p_clean_old_order(IN p_before DATE) BEGIN DECLARE v_affected INT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT error AS result; END; START TRANSACTION; DELETE FROM t_order WHERE created_at p_before LIMIT 10000; SET v_affected ROW_COUNT(); COMMIT; SELECT v_affected AS deleted_rows; END $$ DELIMITER ;DELIMITER那两行是必须的。存储过程体内有分号客户端会把它当成语句结束所以要先临时把分隔符改成$$写完再改回来。这是新手最容易卡住的地方。EXIT HANDLER是异常兜底出错时回滚并返回标记。加上它存储过程才不会在出错后留下半截未提交的事务。DELETE ... LIMIT 10000是分批删除的写法避免一次删太多导致长时间锁表和主从延迟。大数据量清理一般会配一个循环每批删完SLEEP一下让主从追上。6.2 一个批量造数据的例子测试环境经常需要几百万行数据来验证索引效果。用存储过程写循环是最省事的DELIMITER $$ CREATE PROCEDURE p_gen_data(IN p_count INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i p_count DO INSERT INTO t_order (user_id, order_no, amount, status) VALUES (FLOOR(1 RAND() * 10000), CONCAT(NO, LPAD(i, 10, 0)), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5)); SET i i 1; END WHILE; END $$ DELIMITER ; CALL p_gen_data(100000);这个写法逐条插入造 100 万行会比较慢。想快的话可以在循环里每轮插 1000 行用多值VALUES循环次数降到 1/1000速度能提升十倍以上。这是我在测试环境反复用到的技巧。数据造完之后记得ANALYZE TABLE t_order;一下让优化器重新收集统计信息否则执行计划可能还基于旧的数据分布来判断测出来的结果不准。7. 常见报错与排查速查SQL 报错信息有时候很精确有时候很含糊。下面这些是我这些年反复遇到的整理成一张表方便对照。7.1 高频报错对照表报错信息关键词常见原因处理方向You have an error in your SQL syntax关键字拼错、少了逗号、保留字未加反引号从报错位置附近往前找重点看前一个词Unknown column xxx in field list字段名拼错或者别名用在了不允许的位置检查WHERE里是否用了SELECT别名Table xxx doesnt exist没选库、表名大小写不一致Linux 下敏感加USE dbname或用库名限定Column xxx in field list is ambiguous多表 JOIN 时字段同名给字段加表别名前缀You cant specify target table for update in FROM clauseUPDATE的子查询里引用了被更新的表套派生表或改用JOINDuplicate entry xxx for key yyy违反唯一约束先查是否存在或改用ON DUPLICATE KEY UPDATEData too long for column xxx插入值超过字段定义长度加长字段或检查是否数据异常Incorrect string value字符集不支持该字符如 emoji排查字段字符集是否为utf8mb4Lock wait timeout exceeded有未提交的事务长期持有锁查information_schema.INNODB_TRX找长事务Deadlock found when trying to get lock两个事务加锁顺序相反统一业务中的加锁顺序缩小事务范围关于Column xxx in field list is ambiguous补充一句这个报错在JOIN场景里特别高频。我的习惯是写多表查询时所有字段都带表别名前缀哪怕没有歧义。这样后期加字段不会突然报错读代码的人也能一眼看出字段归属。7.2 几条踩坑心得SQL 注入这个话题绕不开但方向要摆正。防御的核心是永远不要拼接 SQL 字符串。用参数化查询PreparedStatement、各语言驱动提供的占位符机制把用户输入当数据而不是当代码。这是唯一可靠的方案。输入过滤、转义字符这些手段只能算补充因为绕过方式太多靠黑名单堵漏洞迟早出事。另外几条第一在生产环境执行任何UPDATE/DELETE前先把语句发给自己或同事看一眼。我跟很多团队推行过这个习惯拦住过不止一次事故。多花 30 秒省掉一晚加班。第二不要迷信 SQL 美化工具。有些格式化工具会把LEFT JOIN的ON条件重新换行把WHERE里的括号层级改掉改完语句语义都变了。格式化之后一定要用EXPLAIN或者结果行数验证一遍。第三慢查询日志是你的朋友。把slow_query_log打开设置long_query_time 1跑一天看看有哪些语句上榜。很多时候你以为的瓶颈和实际的瓶颈完全不是一回事。用mysqldumpslow或者pt-query-digest聚合一下很快就能定位到 Top 几条问题语句。第四时区问题别拖到出问题再处理。连接串里显式指定时区比如serverTimezoneAsia/ShanghaiDATETIME字段存本地时间跨时区展示的时候在应用层转换。混用TIMESTAMP和DATETIME又不统一时区设置迟早会出现“同一条数据两边看到的时间不一样”这种玄学问题。第五备份不只是mysqldump一把梭。逻辑备份恢复慢物理备份需要工具链支持理解你的恢复目标时间RTO和可接受的数据丢失量RPO再决定备份策略。备份完一定要在另一台机器上实际恢复一次没验证过的备份等于没有备份。这套语法我用了很多年从最开始背SELECT的写法到后来能看着执行计划推演出瓶颈在哪中间踩的坑基本都写在上面的章节里了。如果你刚开始学建议先把第 3 章和第 4 章的内容在本地库上敲一遍自己造点数据看结果是否符合预期。特别是LEFT JOIN和WHERE那个语义差异光看文字体会不深亲手对比一次ON和WHERE两种写法返回的行数印象会刻进去。后面再回过头看索引和执行计划路径会顺很多。

MySQL SQL基础语法与实战：增删改查、JOIN、窗口函数与索引优化

相关推荐

具身智能导航全解析：从路径规划到语义地图的工程实践

Linux 查看内存型号、插槽、频率与制造商：dmidecode 实战

Ralph for Claude Code 彻底卸载指南：2 步移除所有痕迹，重装只要 1 条命令

更多资讯

从SLAM到空间智能：英特尔谈室内机器人核心技术

pdf.js 内置 Brotli 解码器解析：external/brotli 模块、release-brotli 构建任务与 /BrotliDecode 解码链路

10kV供配电设计全流程：从负荷计算到保护整定

STM32频率测量实战：输入捕获与FFT选型、代码与避坑

Tempo 项目中的 Participle：用 Go 结构体标签构建死简单解析器的完整实战指南

PyQt5企业级开发：架构设计与性能优化实战

今日更新

AReaL 调试指南：从 Agent Workflow 验证到分布式训练死锁诊断

MATLAB实现GPS L1 C/A信号仿真与二维捕获验证

彻底搞懂ASCII、Unicode与UTF-8：从乱码根源到编码实战

本周精选

AI驱动的自动化专利分析平台技术解析

LeetCode-Go 题解：1252. Cells with Odd Values in a Matrix（奇数值单元格计数）双解法剖析

在线答疑系统Java毕设实战：Spring Boot前后端分离与状态流转

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？