尧图网页设计 
MySQL的SQL语句说简单也简单说难也难。每天都有大量开发者在查数据、做统计、清洗去重、修表加索引的时候被几个细节卡住网上搜“mysql排序”“sql语句去重”“mysql事务处理”“mysql锁的分类”这类关键词的人从来就没少过。这篇我把自己多年用MySQL核心SQL语句的实战经验整理成一套可以直接对照参考的操作集覆盖增删改查、排序去重分组、表结构管理、事务锁、常用函数、存储过程、慢SQL排查以及我踩过的几个典型坑。新手可以用它建立完整的SQL骨架写过不少SQL但总在细节上翻车的同学也能拿它查漏补缺。1. 核心语句的骨架增删改查DMLDML是所有SQL里出现频率最高的部分SELECT、INSERT、UPDATE、DELETE几乎覆盖了日常90%的操作。很多人觉得这部分太基础不值得看但真正写起来条件和索引的配合、批量写入的取舍、误操作防护全都是细节。1.1 SELECT查询从入门到条件过滤SELECT的基本结构其实就一行话SELECT要查的列FROM来自哪张表WHERE过滤哪些行然后GROUP BY分组HAVING过滤分组结果ORDER BY排序最后LIMIT限制返回行数。核心SQL语句的骨架基本都在这里。实际工作中最常用的写法类似这样SELECT id, name, amount, created_at FROM orders WHERE status 1 AND created_at 2025-01-01 ORDER BY amount DESC LIMIT 20;我见过太多人把WHERE条件写得很随意结果查出来的数据不对或者慢得离谱。这里有几个高频坑要特别留意。第一个是索引列上使用函数比如WHERE DATE(created_at) 2025-01-01这会让索引失效全表扫描。正确的做法是写成范围条件WHERE created_at 2025-01-01 AND created_at 2025-01-02。第二个是隐式类型转换字段类型是VARCHAR你却传了数字MySQL会尝试把字段转成数字索引同样失效。第三个是LIKE前置通配符比如LIKE %关键词B树索引没法从中间开始定位只能扫全表。LIMIT分页也是老生常谈的问题。当offset特别大时比如LIMIT 100000, 20MySQL还是要先把前面10万行都查出来再丢掉白白浪费大量IO。我一般建议用“上次翻页最后一条记录的id”来替代offset比如WHERE id 100000 ORDER BY id LIMIT 20。这样每页扫描的代价是可控的翻到百万页也不会明显变慢。1.2 INSERT / UPDATE / DELETE写操作里常见的那几个坑INSERT最常用的就是VALUES多行插入比如INSERT INTO t (col1, col2) VALUES (1, a), (2, b)一次插入多行比循环单行插入效率高很多。还有一个容易混淆的场景是“存在就更新不存在就插入”很多人自己写三段逻辑实际上一条SQL就能搞定INSERT INTO user_score (user_id, score) VALUES (1001, 90) ON DUPLICATE KEY UPDATE score VALUES(score);前提是user_id上有唯一索引或主键。这个写法的意思是插入时如果冲突了就执行后面的更新语句。需要注意MySQL 8.0.20之后官方推荐用别名写法VALUES()函数已标记为废弃但大部分场景仍然可用。另一个类似的是INSERT IGNORE它的语义是冲突时直接忽略适合做幂等写入比如补数据脚本反复执行不会产生重复记录。UPDATE最典型的坑就是忘记WHERE。我不止一次看到有人写UPDATE t SET status 0想把某一类数据改掉结果漏看了WHERE条件直接把整张表的状态全改了。这里有个我坚持了很多年的习惯写UPDATE之前先用同条件SELECT跑一遍确认影响范围。最好把操作包在事务里万一改错了还能回滚。DELETE也一样大表删除时一次性DELETE上万行会锁很久我通常会用循环分批删每批加LIMIT比如DELETE FROM t WHERE created_at 2025-01-01 LIMIT 1000循环执行到影响行数为0为止。另外有一组概念必须分清楚DELETE、TRUNCATE、DROP。DELETE是逐行删除走事务可以回滚但不释放表空间TRUNCATE是清空表不能回滚释放表空间自增ID会重置DROP是直接把表结构都删掉。操作是否可以回滚是否释放空间自增重置适用场景DELETE是否否按条件删除部分数据TRUNCATE否是是快速清空整张临时表DROP否是连结构-完全废弃一张表2. 排序、去重与分组数据清洗里的三板斧数据清洗是SQL语句最常用的场景之一尤其是去重。网上搜“sql语句去重”“sql语句去重查询”的人特别多搜索引擎里这几个词的热度一直很高。排序、去重、分组这三件事经常组合出现但每一件都有不少边界情况。2.1 ORDER BY 排序的隐藏细节ORDER BY的语法本身很简单SELECT ... ORDER BY col1 ASC, col2 DESC。但有几个隐藏行为值得说清楚。第一个是NULL值的排序位置。MySQL默认NULL在升序时排最前降序时排最后。如果你希望把NULL固定排到最后可以配合ISNULL(col)或者COALESCE(col, 0)来干预。第二个是排序字段与索引的配合。如果ORDER BY的字段上有索引MySQL可能直接用索引顺序返回数据避免额外的filesort操作。filesort并不一定慢但当结果集特别大时它会把数据先放到临时文件里排序性能会明显下降。第三个是中文排序的坑。utf8mb4_general_ci和utf8mb4_unicode_ci对中文的排序规则不一样后者按Unicode编码排序前者在某些字符上可能不符合直觉。如果业务对中文排序有明确要求建议使用CONVERT(column USING gbk)之类的显式转码方式排序不要依赖默认排序规则。SELECT name FROM user ORDER BY CONVERT(name USING gbk);2.2 DISTINCT 去重的正确姿势与边界DISTINCT是去重最直观的写法但很多人对它的语义有误解。SELECT DISTINCT col1, col2不是分别对col1和col2去重而是对(col1, col2)的组合去重两列完全相同的行才被合并。如果你只想对col1去重同时又想看到col2的值DISTINCT做不到。这时应该用GROUP BY col1配合其他策略。统计去重数量用COUNT(DISTINCT col)是标准写法比如统计有多少个不同用户下单SELECT COUNT(DISTINCT user_id) FROM orders。这个SQL在数据量大时性能并不好因为需要扫描并维护一个较大的去重集合。如果表里有现成的用户维度汇总表优先查汇总表。清洗数据时最常用的其实是“查重复记录”的写法这是很多“sql语句去重”搜索背后的真实需求。找出某一列重复的数据行SELECT user_id, COUNT(*) AS cnt FROM user_login_log GROUP BY user_id HAVING COUNT(*) 1;如果需求是“每个分组里保留一条记录”比如按email去重保留id最小的那一行可以用子查询DELETE t1 FROM user t1 JOIN ( SELECT MIN(id) AS keep_id FROM user GROUP BY email ) t2 ON t1.email t2.email AND t1.id ! t2.keep_id;值得注意的是这类“删除重复”的操作在数据量很大时子查询会生成临时表务必先小范围测试确认逻辑无误再全量执行。2.3 分组统计与HAVING过滤GROUP BY配合聚合函数是统计分析的常见手段。MySQL 5.7以上版本默认开启ONLY_FULL_GROUP_BY模式SELECT后面的列必须出现在GROUP BY子句中或者被聚合函数包裹。否则直接报错。比如SELECT user_id, name, SUM(amount) FROM orders GROUP BY user_id这里的name没被聚合也没有在GROUP BY里会直接报错。这是很常见的报错尤其在低版本迁移到高版本后频繁出现。HAVING和WHERE的区别一句话总结WHERE在分组前过滤行HAVING在分组后过滤分组结果。WHERE不能用聚合函数HAVING专门用来筛选聚合结果。比如筛选订单数超过10个的用户GROUP BY user_id HAVING COUNT(*) 10。还有一个经常被问到的问题如何取每个分组内某字段最大的一条记录。比如每个分类下金额最高的订单。老写法是用子查询JOINSELECT a.* FROM orders a JOIN ( SELECT category_id, MAX(amount) AS max_amount FROM orders GROUP BY category_id ) b ON a.category_id b.category_id AND a.amount b.max_amount;新写法更简洁用窗口函数ROW_NUMBER()这也是现在面试和工作中越来越常见的写法SELECT category_id, id, amount FROM ( SELECT category_id, id, amount, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM orders ) t WHERE rn 1;3. 表结构管理DDL改表、加索引与执行脚本很多开发者对DDL不重视但改表结构和加索引是上线前的常规动作出问题往往就是大问题比如锁住了线上表的写入。3.1 修改表结构时的几点注意ALTER TABLE的常用操作就那几类ADD COLUMN加列DROP COLUMN删列MODIFY COLUMN改字段类型CHANGE COLUMN改字段名和类型还有ALTER COLUMN SET DEFAULT设置默认值。比如动态搜索里最常见的“mysql设置默认值为0”的需求语句很简单ALTER TABLE t ALTER COLUMN status SET DEFAULT 0;真正需要注意的是执行ALTER TABLE的影响。不同版本和不同操作触发的算法不一样有的操作瞬间完成有的需要重建表。MySQL 8.0支持ALGORITHMINSTANT部分ADD COLUMN操作可以秒级完成但MODIFY字段类型这类操作大概率要走INPLACE甚至COPY在几百万行的大表上执行会长时间占用资源。线上操作我一般选凌晨低峰期并且提前用pt-online-schema-change或gh-ost这类工具原理是先建临时表、同步增量数据、最后切换表名实现近乎无锁的DDL。无论什么操作改之前先备份。MySQL没有后悔药一条DDL下去结构就变了。备份至少要做到能从mysqldump或者云平台快照恢复。我自己的习惯是先看SHOW CREATE TABLE确认当前结构再写变更SQL在测试库上执行一次最后才上生产。3.2 创建索引的核心思路索引是SQL查询性能的关键但很多人加索引比较随意想到哪个字段就加哪个。这里分享一个我常用的分析顺序先看WHERE条件里的字段再看JOIN的关联字段然后是ORDER BY和GROUP BY的字段。这些是索引最容易发挥价值的位置。索引类型主要就四类PRIMARY KEY主键索引、UNIQUE唯一索引、普通INDEX、FULLTEXT全文索引。建索引的语法简单比如CREATE INDEX idx_user_id ON orders(user_id); ALTER TABLE orders ADD UNIQUE INDEX uk_order_no(order_no);选择字段时要注意区分度区分度太低的字段比如状态值只有0和1两种加索引意义不大优化器大概率还是全表扫描。对于较长的字符串字段可以用前缀索引比如CREATE INDEX idx_email ON user(email(10))用前10个字符做索引能大幅减少索引体积。组合索引要特别关注“最左前缀”原则。创建了(idx_a, idx_b, idx_c)之后查询中用到idx_a、idx_aidx_b、idx_aidx_bidx_c都能走索引但只用idx_b或idx_c时索引不会生效。这个限制源于B树索引的结构类似于查字典时必须先知道首字母才能定位。所以组合索引的字段顺序很重要区分度高、查询频率高的字段放前面。这里有个需要提醒的点一张表不是索引越多越好。每个索引都会拖慢INSERT、UPDATE、DELETE的写入速度因为写入时也要同步变更索引。我通常控制在5个以内删除掉长期没有命中记录的冗余索引。可以通过SHOW INDEX FROM t查看现状也可以借助慢SQL日志反向分析哪些索引没人用。3.3 执行SQL脚本的几种方式日常工作中经常需要批量执行SQL脚本比如初始化表结构、导入测试数据。最简单的就是直接用命令行把SQL文件作为输入重定向给客户端mysql -uroot -p init.sql或者在MySQL客户端内部用source命令SOURCE /path/to/init.sql;这里容易忽略的是max_allowed_packet参数。导入包含较大BLOB字段或批量插入的脚本时如果单条SQL超过限制会报错packet too large。建议提前确认SHOW VARIABLES LIKE max_allowed_packet。另外脚本文件本身的字符集要统一推荐UTF-8并在连接时声明--default-character-setutf8mb4避免中文乱码以及由此引出的各种字符集转换问题。还有个细节脚本里如果包含存储过程定义会用到DELIMITER //这样的分隔符切换source时不会自动处理需要原样保留。我自己吃过这个亏把带存储过程的脚本粘到Navicat里执行结果符号解析错误半天没找到原因。4. 事务、锁与数据一致性别让数据在并发下悄悄出错事务和锁是中级开发绕不开的坎也是“mysql事务处理”“mysql锁的分类”这类搜索里最常被追问的内容。再加上面试题里事务隔离级别、死锁排查几乎必问这块的实操价值非常高。4.1 事务的ACID与常用SQL事务的核心是ACID原子性、一致性、隔离性、持久性。用转账来解释最直观A给B转账扣钱和加钱必须同时成功要么都执行要么都回滚这就是原子性。事务里所有操作都完成后统一COMMIT提交中间任何一步出错ROLLBACK回滚到事务开始前的状态。MySQL事务默认是自动提交的也就是说单条SQL自带事务。手动控制事务的写法是START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;中间如果发现第二步更新了不存在的用户直接ROLLBACK两条语句的效果都会被撤销。事务隔离级别影响的是多个并发事务同时操作同一批数据时彼此能看到什么。MySQL的默认隔离级别是REPEATABLE READ可重复读在这个级别下一个事务内多次读取同一数据结果一致配合MVCC多版本并发控制实现了读写互不阻塞这是InnoDB比很多数据库做得好的地方之一。四个隔离级别从低到高分别是READ UNCOMMITTED读未提交、READ COMMITTED读已提交、REPEATABLE READ可重复读、SERIALIZABLE串行化。级别越高隔离越强并发性能越差。网上很多“mysql面试题”都在考这四者的区别我建议结合自己的业务去记忆报表类查询可以降低隔离级别提升并发涉及支付的写操作则必须考虑更高隔离级别防止数据错乱。事务最容易踩的坑是长事务。一个事务里跑了大量查询和更新甚至包含了应用层的外部调用事务长时间不提交会一直占着连接还可能导致undo日志膨胀、锁等待堆积。我的经验是事务尽量短小只把需要原子性保证的写操作放进去查询尽量放外面。4.2 锁的分类与日常排查思路提到事务就不得不提锁。从粒度上看MySQL的锁主要有表锁和行锁InnoDB默认使用行锁而MyISAM只有表锁。行锁能最大程度支持并发但代价是锁管理成本更高。从类型上看InnoDB又包含共享锁S和排他锁X普通查询不加锁只有走SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE以及INSERT/UPDATE/DELETE时才会加对应锁。FOR UPDATE是排他锁其他事务连读都会被阻塞共享锁则允许多个事务同时读但都不允许写。还有一种特殊的锁叫间隙锁。在REPEATABLE READ隔离级别下InnoDB不仅锁住命中的行还会锁住行与行之间的间隙防止其他事务插入新数据这就是间隙锁它是InnoDB解决幻读的关键机制。但也正是因为间隙锁范围更新或删除时经常出现莫名的锁等待甚至死锁。死锁的成因是多个事务循环等待对方的锁。比如事务A锁了行1再要行2事务B锁了行2再要行1两边等不到对方释放数据库会检测到死锁并自动回滚其中一个事务。遇到死锁不要慌先到日志里看最后一个死锁现场SHOW ENGINE INNODB STATUS;重点看LATEST DETECTED DEADLOCK那段里面会明确写清楚两个事务分别持有和等待什么锁。我的排查习惯是先看代码里事务顺序是否一致比如访问多张表或行时按固定顺序加锁能大幅降低死锁概率。另外经常排查正在运行的事务用下面这条SQL找出长时间未提交的idle事务这类事务往往是锁问题的元凶SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started;5. 常用函数、存储过程与慢SQL排查函数和存储过程是SQL能力的分水岭也是“mysql函数大全及举例”“mysql存储过程”这类搜索词背后的需求。当然函数再多也会忘记我建议只掌握高频的部分其余遇到再查。5.1 高频函数用法举例字符串处理是函数里的重头戏。CONCAT用于拼接字符串注意MySQL不像SQL Server直接用拼接写错会返回0。REPLACE做替换SUBSTRING截取子串TRIM去掉首尾空格。这些在清洗脏数据时用的频率很高比如把电话号码里的横杠去掉就是SELECT REPLACE(phone, -, )。日期函数是最容易记混的一类。经常有人搜“mysql datepart”这是因为SQL Server里有DATEPART而MySQL里没有。MySQL用的是EXTRACT(DAY FROM date)或者DATE_FORMAT(date, %Y-%m-%d)。如果想把一个日期转成“2025年03月”这种展示格式可以写DATE_FORMAT(created_at, %Y年%m月)。计算两个日期相差天数用DATEDIFF给日期加一天用DATE_ADD(date, INTERVAL 1 DAY)。聚合函数是统计的基础COUNT、SUM、AVG、MAX、MIN这五个必须熟练掌握。需要注意COUNT()和COUNT(col)的区别COUNT()统计所有行包括NULLCOUNT(col)只统计该列非NULL的行。如果业务上要精确计数这两个结果不一致会导致隐性问题。还有一个高频逻辑函数是CASE WHEN它本质上就是SQL里的if-else。比如给订单打标签SELECT id, amount, CASE WHEN amount 1000 THEN 大额订单 WHEN amount 100 THEN 普通订单 ELSE 小额订单 END AS order_level FROM orders;这里有个重要提醒在WHERE条件中调用函数会导致该列索引失效比如WHERE YEAR(created_at) 2025正确写法是范围条件。函数更适合放在SELECT里做展示层的处理过滤条件尽量保持原始字段可索引状态。5.2 存储过程的编写与调用存储过程是把一段SQL逻辑固化到数据库端通过名字调用有点像一个数据库内的函数。典型的创建方式如下DELIMITER // CREATE PROCEDURE get_user_orders(IN uid INT) BEGIN SELECT * FROM orders WHERE user_id uid; END // DELIMITER ;DELIMITER //是为了让MySQL把整个BEGIN...END块当作一条完整语句来解析不切换分隔符的话分号处就会被提前截断。调用时用CALL get_user_orders(1001);删除时DROP PROCEDURE get_user_orders。但说实话我的观点是存储过程尽量少用。它在特定历史时期有价值比如逻辑完全固化、不允许应用层直接访问表结构的场景。但在现代开发里存储过程有几个明显的缺点不好做版本管理代码评审不方便不好做水平扩展应用层拆分成微服务时数据库端的逻辑藕断丝连调试手段原始打日志都困难。除非业务逻辑极度稳定比如批量月末结转这类报表任务否则我更推荐把业务逻辑放在应用层。5.3 慢SQL排查与执行计划排查慢SQL是数据库运维里最日常的工作之一。第一步是打开慢查询日志两个参数就够了SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;超过2秒的SQL会被记录到日志中这会把问题SQL暴露出来。拿到慢SQL之后用EXPLAIN看执行计划EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC;EXPLAIN的输出里type字段是重点。从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL就说明全表扫描基本可以确定索引没建对或没生效。key显示实际命中的索引rows是预估扫描行数Extra里如果出现Using filesort或者Using temporary意味着发生了额外的排序或临时表操作这类SQL要重点优化。优化慢SQL的三板斧第一补索引根据WHERE条件建立合适的单列或组合索引第二改SQL把SELECT *改成只取需要的列把条件里的函数去掉把多表关联的驱动表选小表第三做降级比如报表类查询可以走从库复杂统计提前做汇总表。我实际优化过的最夸张的一个案例一条报表SQL从30秒降到0.3秒只做了一件事把组合索引的字段顺序按查询条件重新排了。索引顺序的影响真的很大。6. 常见问题排查实录那些让我记忆深刻的坑这一章不是理论全是我实际踩过的坑和排查思路。每一条都是从报错现场出发再倒推解决方案希望对你有直接参考价值。6.1 UPDATE误操作后的还原思路先说个真实经历。我曾经接手过一套系统某天下午同事在测试环境执行清洗脚本写UPDATE时把WHERE条件漏了直接UPDATE整表把state字段全部更新成了0。当时已经没有备份靠binlog才恢复。这里把恢复思路完整说一遍。前提是binlog开启且格式是ROW。MySQL 8.0默认是ROW格式5.7不一定需要确认。恢复步骤是先用mysqlbinlog工具把日志导出定位到错误UPDATE的位置解析它前后的SQL然后手动生成一条反向的UPDATE语句把被错误改掉的数据改回去。如果误操作是前几秒发生的也可以借助云数据库平台的“按时间点恢复”功能直接回滚到误操作前的状态。但从那次事故以后我给自己定了三条铁律任何UPDATE/DELETE之前必须先用同条件SELECT确认行数和内容写操作必须包在一个事务里确认无误后再提交上线脚本统一走版本管理SQL文件提交到代码仓库禁止手敲到生产环境。这三条规则看着简单但真的能挡住大部分灾难。6.2 连接过程中的报错排查SSL与服务启动失败连接报错是我在社区里回答过最多的问题类型之一。MySQL 8.0默认开启了SSL相关参数启动时发现客户端连接不上报错里带SSL字样比如SSL connection error。很多时候是因为客户端用的驱动版本太老不支持新的SSL协商方式。如果内网环境安全性可控一个临时方案是关闭SSL相关参数在my.cnf里设置skip_ssl重启服务。但严格来说更建议升级驱动而不是直接关SSL。另一个高频问题是服务无法启动。在Windows上执行net start mysql报“服务无法启动”是最典型的情况。排查思路有章法第一步看错误日志MySQL会把启动失败的原因写进data目录下的error log文件文件名通常是主机名.err第二步检查my.ini配置比如basedir和datadir路径是否写错端口有没有被占用第三步确认数据目录的权限尤其Linux环境下启动用户必须有权限读取数据目录。我整理了一张排查速查表现象可能原因处理方向连接报SSL错误客户端驱动过老升级驱动或临时skip_ssl服务无法启动配置路径错误、端口占用、目录权限查看error日志逐项排查中文乱码字符集不一致连接参数指定utf8mb4访问容器内MySQL失败端口未映射或网络模式问题检查映射端口和容器网络6.3 安装与版本切换的碎碎念MySQL的安装和版本切换也是很多人搜索的高频话题比如“linux mysql 8.0.44下载”“mysql 5.7.44安装过程”“rpm安装mysql”“docker安装mysql失败”。这些标题背后其实都是同一个诉求快速把能吃上SQL的环境搭起来。这里简单说几个实用经验。5.7和8.0的SQL行为有两个显著差异第一8.0默认使用caching_sha2_password密码认证插件老版本客户端比如很老的PHP驱动连不上需要改成mysql_native_password第二8.0默认开了ONLY_FULL_GROUP_BY之前的写法可能直接报错。如果你是从5.7升8.0这两个差异基本必踩。安装方式上Linux我用yum或rpm的方式比较多装完后用systemctl start mysqld管理第一次启动会在日志里生成临时密码。Windows上我喜欢下载zip包直接解压配置注意在my.ini里手动指定basedir和datadir。Docker方式最快一条命令就能拉起来docker run --name mysql -e MYSQL_ROOT_PASSWORDmy-secret-pw -d mysql:8.0但生产环境用容器跑MySQL务必挂载数据卷否则容器删除数据全没。我个人写SQL的固定习惯说给你们参考查询语句先写WHERE条件再写SELECT返回列从人的思维逻辑上讲过滤条件往往比展示字段更容易确定任何影响数据的操作事前开一个事务跑完核对行数再提交每一条慢SQL都用EXPLAIN过一遍执行计划再看一眼索引情况。这套习惯帮我挡掉不少低级事故也让我在排查别人留下的烂摊子时能快速定位问题。如果你正准备系统梳理MySQL核心SQL语句建议把上面这些章节当成一份可复用的操作手册需要哪块就按编号翻到对应位置比漫无目的搜各种报错要高效得多。 MySQL核心SQL语句实战：从排序去重到事务锁的完整手册
MySQL的SQL语句说简单也简单说难也难。每天都有大量开发者在查数据、做统计、清洗去重、修表加索引的时候被几个细节卡住网上搜“mysql排序”“sql语句去重”“mysql事务处理”“mysql锁的分类”这类关键词的人从来就没少过。这篇我把自己多年用MySQL核心SQL语句的实战经验整理成一套可以直接对照参考的操作集覆盖增删改查、排序去重分组、表结构管理、事务锁、常用函数、存储过程、慢SQL排查以及我踩过的几个典型坑。新手可以用它建立完整的SQL骨架写过不少SQL但总在细节上翻车的同学也能拿它查漏补缺。1. 核心语句的骨架增删改查DMLDML是所有SQL里出现频率最高的部分SELECT、INSERT、UPDATE、DELETE几乎覆盖了日常90%的操作。很多人觉得这部分太基础不值得看但真正写起来条件和索引的配合、批量写入的取舍、误操作防护全都是细节。1.1 SELECT查询从入门到条件过滤SELECT的基本结构其实就一行话SELECT要查的列FROM来自哪张表WHERE过滤哪些行然后GROUP BY分组HAVING过滤分组结果ORDER BY排序最后LIMIT限制返回行数。核心SQL语句的骨架基本都在这里。实际工作中最常用的写法类似这样SELECT id, name, amount, created_at FROM orders WHERE status 1 AND created_at 2025-01-01 ORDER BY amount DESC LIMIT 20;我见过太多人把WHERE条件写得很随意结果查出来的数据不对或者慢得离谱。这里有几个高频坑要特别留意。第一个是索引列上使用函数比如WHERE DATE(created_at) 2025-01-01这会让索引失效全表扫描。正确的做法是写成范围条件WHERE created_at 2025-01-01 AND created_at 2025-01-02。第二个是隐式类型转换字段类型是VARCHAR你却传了数字MySQL会尝试把字段转成数字索引同样失效。第三个是LIKE前置通配符比如LIKE %关键词B树索引没法从中间开始定位只能扫全表。LIMIT分页也是老生常谈的问题。当offset特别大时比如LIMIT 100000, 20MySQL还是要先把前面10万行都查出来再丢掉白白浪费大量IO。我一般建议用“上次翻页最后一条记录的id”来替代offset比如WHERE id 100000 ORDER BY id LIMIT 20。这样每页扫描的代价是可控的翻到百万页也不会明显变慢。1.2 INSERT / UPDATE / DELETE写操作里常见的那几个坑INSERT最常用的就是VALUES多行插入比如INSERT INTO t (col1, col2) VALUES (1, a), (2, b)一次插入多行比循环单行插入效率高很多。还有一个容易混淆的场景是“存在就更新不存在就插入”很多人自己写三段逻辑实际上一条SQL就能搞定INSERT INTO user_score (user_id, score) VALUES (1001, 90) ON DUPLICATE KEY UPDATE score VALUES(score);前提是user_id上有唯一索引或主键。这个写法的意思是插入时如果冲突了就执行后面的更新语句。需要注意MySQL 8.0.20之后官方推荐用别名写法VALUES()函数已标记为废弃但大部分场景仍然可用。另一个类似的是INSERT IGNORE它的语义是冲突时直接忽略适合做幂等写入比如补数据脚本反复执行不会产生重复记录。UPDATE最典型的坑就是忘记WHERE。我不止一次看到有人写UPDATE t SET status 0想把某一类数据改掉结果漏看了WHERE条件直接把整张表的状态全改了。这里有个我坚持了很多年的习惯写UPDATE之前先用同条件SELECT跑一遍确认影响范围。最好把操作包在事务里万一改错了还能回滚。DELETE也一样大表删除时一次性DELETE上万行会锁很久我通常会用循环分批删每批加LIMIT比如DELETE FROM t WHERE created_at 2025-01-01 LIMIT 1000循环执行到影响行数为0为止。另外有一组概念必须分清楚DELETE、TRUNCATE、DROP。DELETE是逐行删除走事务可以回滚但不释放表空间TRUNCATE是清空表不能回滚释放表空间自增ID会重置DROP是直接把表结构都删掉。操作是否可以回滚是否释放空间自增重置适用场景DELETE是否否按条件删除部分数据TRUNCATE否是是快速清空整张临时表DROP否是连结构-完全废弃一张表2. 排序、去重与分组数据清洗里的三板斧数据清洗是SQL语句最常用的场景之一尤其是去重。网上搜“sql语句去重”“sql语句去重查询”的人特别多搜索引擎里这几个词的热度一直很高。排序、去重、分组这三件事经常组合出现但每一件都有不少边界情况。2.1 ORDER BY 排序的隐藏细节ORDER BY的语法本身很简单SELECT ... ORDER BY col1 ASC, col2 DESC。但有几个隐藏行为值得说清楚。第一个是NULL值的排序位置。MySQL默认NULL在升序时排最前降序时排最后。如果你希望把NULL固定排到最后可以配合ISNULL(col)或者COALESCE(col, 0)来干预。第二个是排序字段与索引的配合。如果ORDER BY的字段上有索引MySQL可能直接用索引顺序返回数据避免额外的filesort操作。filesort并不一定慢但当结果集特别大时它会把数据先放到临时文件里排序性能会明显下降。第三个是中文排序的坑。utf8mb4_general_ci和utf8mb4_unicode_ci对中文的排序规则不一样后者按Unicode编码排序前者在某些字符上可能不符合直觉。如果业务对中文排序有明确要求建议使用CONVERT(column USING gbk)之类的显式转码方式排序不要依赖默认排序规则。SELECT name FROM user ORDER BY CONVERT(name USING gbk);2.2 DISTINCT 去重的正确姿势与边界DISTINCT是去重最直观的写法但很多人对它的语义有误解。SELECT DISTINCT col1, col2不是分别对col1和col2去重而是对(col1, col2)的组合去重两列完全相同的行才被合并。如果你只想对col1去重同时又想看到col2的值DISTINCT做不到。这时应该用GROUP BY col1配合其他策略。统计去重数量用COUNT(DISTINCT col)是标准写法比如统计有多少个不同用户下单SELECT COUNT(DISTINCT user_id) FROM orders。这个SQL在数据量大时性能并不好因为需要扫描并维护一个较大的去重集合。如果表里有现成的用户维度汇总表优先查汇总表。清洗数据时最常用的其实是“查重复记录”的写法这是很多“sql语句去重”搜索背后的真实需求。找出某一列重复的数据行SELECT user_id, COUNT(*) AS cnt FROM user_login_log GROUP BY user_id HAVING COUNT(*) 1;如果需求是“每个分组里保留一条记录”比如按email去重保留id最小的那一行可以用子查询DELETE t1 FROM user t1 JOIN ( SELECT MIN(id) AS keep_id FROM user GROUP BY email ) t2 ON t1.email t2.email AND t1.id ! t2.keep_id;值得注意的是这类“删除重复”的操作在数据量很大时子查询会生成临时表务必先小范围测试确认逻辑无误再全量执行。2.3 分组统计与HAVING过滤GROUP BY配合聚合函数是统计分析的常见手段。MySQL 5.7以上版本默认开启ONLY_FULL_GROUP_BY模式SELECT后面的列必须出现在GROUP BY子句中或者被聚合函数包裹。否则直接报错。比如SELECT user_id, name, SUM(amount) FROM orders GROUP BY user_id这里的name没被聚合也没有在GROUP BY里会直接报错。这是很常见的报错尤其在低版本迁移到高版本后频繁出现。HAVING和WHERE的区别一句话总结WHERE在分组前过滤行HAVING在分组后过滤分组结果。WHERE不能用聚合函数HAVING专门用来筛选聚合结果。比如筛选订单数超过10个的用户GROUP BY user_id HAVING COUNT(*) 10。还有一个经常被问到的问题如何取每个分组内某字段最大的一条记录。比如每个分类下金额最高的订单。老写法是用子查询JOINSELECT a.* FROM orders a JOIN ( SELECT category_id, MAX(amount) AS max_amount FROM orders GROUP BY category_id ) b ON a.category_id b.category_id AND a.amount b.max_amount;新写法更简洁用窗口函数ROW_NUMBER()这也是现在面试和工作中越来越常见的写法SELECT category_id, id, amount FROM ( SELECT category_id, id, amount, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM orders ) t WHERE rn 1;3. 表结构管理DDL改表、加索引与执行脚本很多开发者对DDL不重视但改表结构和加索引是上线前的常规动作出问题往往就是大问题比如锁住了线上表的写入。3.1 修改表结构时的几点注意ALTER TABLE的常用操作就那几类ADD COLUMN加列DROP COLUMN删列MODIFY COLUMN改字段类型CHANGE COLUMN改字段名和类型还有ALTER COLUMN SET DEFAULT设置默认值。比如动态搜索里最常见的“mysql设置默认值为0”的需求语句很简单ALTER TABLE t ALTER COLUMN status SET DEFAULT 0;真正需要注意的是执行ALTER TABLE的影响。不同版本和不同操作触发的算法不一样有的操作瞬间完成有的需要重建表。MySQL 8.0支持ALGORITHMINSTANT部分ADD COLUMN操作可以秒级完成但MODIFY字段类型这类操作大概率要走INPLACE甚至COPY在几百万行的大表上执行会长时间占用资源。线上操作我一般选凌晨低峰期并且提前用pt-online-schema-change或gh-ost这类工具原理是先建临时表、同步增量数据、最后切换表名实现近乎无锁的DDL。无论什么操作改之前先备份。MySQL没有后悔药一条DDL下去结构就变了。备份至少要做到能从mysqldump或者云平台快照恢复。我自己的习惯是先看SHOW CREATE TABLE确认当前结构再写变更SQL在测试库上执行一次最后才上生产。3.2 创建索引的核心思路索引是SQL查询性能的关键但很多人加索引比较随意想到哪个字段就加哪个。这里分享一个我常用的分析顺序先看WHERE条件里的字段再看JOIN的关联字段然后是ORDER BY和GROUP BY的字段。这些是索引最容易发挥价值的位置。索引类型主要就四类PRIMARY KEY主键索引、UNIQUE唯一索引、普通INDEX、FULLTEXT全文索引。建索引的语法简单比如CREATE INDEX idx_user_id ON orders(user_id); ALTER TABLE orders ADD UNIQUE INDEX uk_order_no(order_no);选择字段时要注意区分度区分度太低的字段比如状态值只有0和1两种加索引意义不大优化器大概率还是全表扫描。对于较长的字符串字段可以用前缀索引比如CREATE INDEX idx_email ON user(email(10))用前10个字符做索引能大幅减少索引体积。组合索引要特别关注“最左前缀”原则。创建了(idx_a, idx_b, idx_c)之后查询中用到idx_a、idx_aidx_b、idx_aidx_bidx_c都能走索引但只用idx_b或idx_c时索引不会生效。这个限制源于B树索引的结构类似于查字典时必须先知道首字母才能定位。所以组合索引的字段顺序很重要区分度高、查询频率高的字段放前面。这里有个需要提醒的点一张表不是索引越多越好。每个索引都会拖慢INSERT、UPDATE、DELETE的写入速度因为写入时也要同步变更索引。我通常控制在5个以内删除掉长期没有命中记录的冗余索引。可以通过SHOW INDEX FROM t查看现状也可以借助慢SQL日志反向分析哪些索引没人用。3.3 执行SQL脚本的几种方式日常工作中经常需要批量执行SQL脚本比如初始化表结构、导入测试数据。最简单的就是直接用命令行把SQL文件作为输入重定向给客户端mysql -uroot -p init.sql或者在MySQL客户端内部用source命令SOURCE /path/to/init.sql;这里容易忽略的是max_allowed_packet参数。导入包含较大BLOB字段或批量插入的脚本时如果单条SQL超过限制会报错packet too large。建议提前确认SHOW VARIABLES LIKE max_allowed_packet。另外脚本文件本身的字符集要统一推荐UTF-8并在连接时声明--default-character-setutf8mb4避免中文乱码以及由此引出的各种字符集转换问题。还有个细节脚本里如果包含存储过程定义会用到DELIMITER //这样的分隔符切换source时不会自动处理需要原样保留。我自己吃过这个亏把带存储过程的脚本粘到Navicat里执行结果符号解析错误半天没找到原因。4. 事务、锁与数据一致性别让数据在并发下悄悄出错事务和锁是中级开发绕不开的坎也是“mysql事务处理”“mysql锁的分类”这类搜索里最常被追问的内容。再加上面试题里事务隔离级别、死锁排查几乎必问这块的实操价值非常高。4.1 事务的ACID与常用SQL事务的核心是ACID原子性、一致性、隔离性、持久性。用转账来解释最直观A给B转账扣钱和加钱必须同时成功要么都执行要么都回滚这就是原子性。事务里所有操作都完成后统一COMMIT提交中间任何一步出错ROLLBACK回滚到事务开始前的状态。MySQL事务默认是自动提交的也就是说单条SQL自带事务。手动控制事务的写法是START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;中间如果发现第二步更新了不存在的用户直接ROLLBACK两条语句的效果都会被撤销。事务隔离级别影响的是多个并发事务同时操作同一批数据时彼此能看到什么。MySQL的默认隔离级别是REPEATABLE READ可重复读在这个级别下一个事务内多次读取同一数据结果一致配合MVCC多版本并发控制实现了读写互不阻塞这是InnoDB比很多数据库做得好的地方之一。四个隔离级别从低到高分别是READ UNCOMMITTED读未提交、READ COMMITTED读已提交、REPEATABLE READ可重复读、SERIALIZABLE串行化。级别越高隔离越强并发性能越差。网上很多“mysql面试题”都在考这四者的区别我建议结合自己的业务去记忆报表类查询可以降低隔离级别提升并发涉及支付的写操作则必须考虑更高隔离级别防止数据错乱。事务最容易踩的坑是长事务。一个事务里跑了大量查询和更新甚至包含了应用层的外部调用事务长时间不提交会一直占着连接还可能导致undo日志膨胀、锁等待堆积。我的经验是事务尽量短小只把需要原子性保证的写操作放进去查询尽量放外面。4.2 锁的分类与日常排查思路提到事务就不得不提锁。从粒度上看MySQL的锁主要有表锁和行锁InnoDB默认使用行锁而MyISAM只有表锁。行锁能最大程度支持并发但代价是锁管理成本更高。从类型上看InnoDB又包含共享锁S和排他锁X普通查询不加锁只有走SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE以及INSERT/UPDATE/DELETE时才会加对应锁。FOR UPDATE是排他锁其他事务连读都会被阻塞共享锁则允许多个事务同时读但都不允许写。还有一种特殊的锁叫间隙锁。在REPEATABLE READ隔离级别下InnoDB不仅锁住命中的行还会锁住行与行之间的间隙防止其他事务插入新数据这就是间隙锁它是InnoDB解决幻读的关键机制。但也正是因为间隙锁范围更新或删除时经常出现莫名的锁等待甚至死锁。死锁的成因是多个事务循环等待对方的锁。比如事务A锁了行1再要行2事务B锁了行2再要行1两边等不到对方释放数据库会检测到死锁并自动回滚其中一个事务。遇到死锁不要慌先到日志里看最后一个死锁现场SHOW ENGINE INNODB STATUS;重点看LATEST DETECTED DEADLOCK那段里面会明确写清楚两个事务分别持有和等待什么锁。我的排查习惯是先看代码里事务顺序是否一致比如访问多张表或行时按固定顺序加锁能大幅降低死锁概率。另外经常排查正在运行的事务用下面这条SQL找出长时间未提交的idle事务这类事务往往是锁问题的元凶SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started;5. 常用函数、存储过程与慢SQL排查函数和存储过程是SQL能力的分水岭也是“mysql函数大全及举例”“mysql存储过程”这类搜索词背后的需求。当然函数再多也会忘记我建议只掌握高频的部分其余遇到再查。5.1 高频函数用法举例字符串处理是函数里的重头戏。CONCAT用于拼接字符串注意MySQL不像SQL Server直接用拼接写错会返回0。REPLACE做替换SUBSTRING截取子串TRIM去掉首尾空格。这些在清洗脏数据时用的频率很高比如把电话号码里的横杠去掉就是SELECT REPLACE(phone, -, )。日期函数是最容易记混的一类。经常有人搜“mysql datepart”这是因为SQL Server里有DATEPART而MySQL里没有。MySQL用的是EXTRACT(DAY FROM date)或者DATE_FORMAT(date, %Y-%m-%d)。如果想把一个日期转成“2025年03月”这种展示格式可以写DATE_FORMAT(created_at, %Y年%m月)。计算两个日期相差天数用DATEDIFF给日期加一天用DATE_ADD(date, INTERVAL 1 DAY)。聚合函数是统计的基础COUNT、SUM、AVG、MAX、MIN这五个必须熟练掌握。需要注意COUNT()和COUNT(col)的区别COUNT()统计所有行包括NULLCOUNT(col)只统计该列非NULL的行。如果业务上要精确计数这两个结果不一致会导致隐性问题。还有一个高频逻辑函数是CASE WHEN它本质上就是SQL里的if-else。比如给订单打标签SELECT id, amount, CASE WHEN amount 1000 THEN 大额订单 WHEN amount 100 THEN 普通订单 ELSE 小额订单 END AS order_level FROM orders;这里有个重要提醒在WHERE条件中调用函数会导致该列索引失效比如WHERE YEAR(created_at) 2025正确写法是范围条件。函数更适合放在SELECT里做展示层的处理过滤条件尽量保持原始字段可索引状态。5.2 存储过程的编写与调用存储过程是把一段SQL逻辑固化到数据库端通过名字调用有点像一个数据库内的函数。典型的创建方式如下DELIMITER // CREATE PROCEDURE get_user_orders(IN uid INT) BEGIN SELECT * FROM orders WHERE user_id uid; END // DELIMITER ;DELIMITER //是为了让MySQL把整个BEGIN...END块当作一条完整语句来解析不切换分隔符的话分号处就会被提前截断。调用时用CALL get_user_orders(1001);删除时DROP PROCEDURE get_user_orders。但说实话我的观点是存储过程尽量少用。它在特定历史时期有价值比如逻辑完全固化、不允许应用层直接访问表结构的场景。但在现代开发里存储过程有几个明显的缺点不好做版本管理代码评审不方便不好做水平扩展应用层拆分成微服务时数据库端的逻辑藕断丝连调试手段原始打日志都困难。除非业务逻辑极度稳定比如批量月末结转这类报表任务否则我更推荐把业务逻辑放在应用层。5.3 慢SQL排查与执行计划排查慢SQL是数据库运维里最日常的工作之一。第一步是打开慢查询日志两个参数就够了SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;超过2秒的SQL会被记录到日志中这会把问题SQL暴露出来。拿到慢SQL之后用EXPLAIN看执行计划EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC;EXPLAIN的输出里type字段是重点。从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL就说明全表扫描基本可以确定索引没建对或没生效。key显示实际命中的索引rows是预估扫描行数Extra里如果出现Using filesort或者Using temporary意味着发生了额外的排序或临时表操作这类SQL要重点优化。优化慢SQL的三板斧第一补索引根据WHERE条件建立合适的单列或组合索引第二改SQL把SELECT *改成只取需要的列把条件里的函数去掉把多表关联的驱动表选小表第三做降级比如报表类查询可以走从库复杂统计提前做汇总表。我实际优化过的最夸张的一个案例一条报表SQL从30秒降到0.3秒只做了一件事把组合索引的字段顺序按查询条件重新排了。索引顺序的影响真的很大。6. 常见问题排查实录那些让我记忆深刻的坑这一章不是理论全是我实际踩过的坑和排查思路。每一条都是从报错现场出发再倒推解决方案希望对你有直接参考价值。6.1 UPDATE误操作后的还原思路先说个真实经历。我曾经接手过一套系统某天下午同事在测试环境执行清洗脚本写UPDATE时把WHERE条件漏了直接UPDATE整表把state字段全部更新成了0。当时已经没有备份靠binlog才恢复。这里把恢复思路完整说一遍。前提是binlog开启且格式是ROW。MySQL 8.0默认是ROW格式5.7不一定需要确认。恢复步骤是先用mysqlbinlog工具把日志导出定位到错误UPDATE的位置解析它前后的SQL然后手动生成一条反向的UPDATE语句把被错误改掉的数据改回去。如果误操作是前几秒发生的也可以借助云数据库平台的“按时间点恢复”功能直接回滚到误操作前的状态。但从那次事故以后我给自己定了三条铁律任何UPDATE/DELETE之前必须先用同条件SELECT确认行数和内容写操作必须包在一个事务里确认无误后再提交上线脚本统一走版本管理SQL文件提交到代码仓库禁止手敲到生产环境。这三条规则看着简单但真的能挡住大部分灾难。6.2 连接过程中的报错排查SSL与服务启动失败连接报错是我在社区里回答过最多的问题类型之一。MySQL 8.0默认开启了SSL相关参数启动时发现客户端连接不上报错里带SSL字样比如SSL connection error。很多时候是因为客户端用的驱动版本太老不支持新的SSL协商方式。如果内网环境安全性可控一个临时方案是关闭SSL相关参数在my.cnf里设置skip_ssl重启服务。但严格来说更建议升级驱动而不是直接关SSL。另一个高频问题是服务无法启动。在Windows上执行net start mysql报“服务无法启动”是最典型的情况。排查思路有章法第一步看错误日志MySQL会把启动失败的原因写进data目录下的error log文件文件名通常是主机名.err第二步检查my.ini配置比如basedir和datadir路径是否写错端口有没有被占用第三步确认数据目录的权限尤其Linux环境下启动用户必须有权限读取数据目录。我整理了一张排查速查表现象可能原因处理方向连接报SSL错误客户端驱动过老升级驱动或临时skip_ssl服务无法启动配置路径错误、端口占用、目录权限查看error日志逐项排查中文乱码字符集不一致连接参数指定utf8mb4访问容器内MySQL失败端口未映射或网络模式问题检查映射端口和容器网络6.3 安装与版本切换的碎碎念MySQL的安装和版本切换也是很多人搜索的高频话题比如“linux mysql 8.0.44下载”“mysql 5.7.44安装过程”“rpm安装mysql”“docker安装mysql失败”。这些标题背后其实都是同一个诉求快速把能吃上SQL的环境搭起来。这里简单说几个实用经验。5.7和8.0的SQL行为有两个显著差异第一8.0默认使用caching_sha2_password密码认证插件老版本客户端比如很老的PHP驱动连不上需要改成mysql_native_password第二8.0默认开了ONLY_FULL_GROUP_BY之前的写法可能直接报错。如果你是从5.7升8.0这两个差异基本必踩。安装方式上Linux我用yum或rpm的方式比较多装完后用systemctl start mysqld管理第一次启动会在日志里生成临时密码。Windows上我喜欢下载zip包直接解压配置注意在my.ini里手动指定basedir和datadir。Docker方式最快一条命令就能拉起来docker run --name mysql -e MYSQL_ROOT_PASSWORDmy-secret-pw -d mysql:8.0但生产环境用容器跑MySQL务必挂载数据卷否则容器删除数据全没。我个人写SQL的固定习惯说给你们参考查询语句先写WHERE条件再写SELECT返回列从人的思维逻辑上讲过滤条件往往比展示字段更容易确定任何影响数据的操作事前开一个事务跑完核对行数再提交每一条慢SQL都用EXPLAIN过一遍执行计划再看一眼索引情况。这套习惯帮我挡掉不少低级事故也让我在排查别人留下的烂摊子时能快速定位问题。如果你正准备系统梳理MySQL核心SQL语句建议把上面这些章节当成一份可复用的操作手册需要哪块就按编号翻到对应位置比漫无目的搜各种报错要高效得多。