别只会写 SELECT！这 10 个 MySQL 技巧太香了，效率直接翻倍
**
作为每天和数据库打交道的程序员，我发现太多人还停留在 “SELECT * 走天下” 的阶段。明明一行 SQL 能搞定的事，偏要写十几行代码；明明能优化到毫秒级的查询，非要卡成 “龟速”……
今天掏家底分享 10 个实战派 MySQL 技巧，全是我踩过坑、验过证的干货。不管是做报表统计、处理重复数据，还是优化慢查询，学会了直接省出喝咖啡的时间！
一、JSON 字段：动态数据的 “免扩容” 方案
痛点：用户自定义配置、商品多规格这类动态字段，加字段嫌麻烦，存字符串又难解析？
技巧：MySQL 5.7 + 支持 JSON 类型，直接存键值对，灵活又好查！
-- 创建用户偏好表（JSON存动态配置）
CREATE TABLE user_preferences (
id INT PRIMARY KEY,
user_id INT,
preferences JSON -- 动态字段
) COMMENT '用户偏好表';
-- 插入数据
INSERT INTO user_preferences VALUES
(1, 1, '{"theme":"dark","fontSize":14,"notifications":true}'),
(2, 2, '{"theme":"light","fontSize":16,"notifications":false}');
-- 精准查“主题”配置（-> 符号提取值）
SELECT preferences->'$.theme' AS user_theme FROM user_preferences WHERE user_id=1;
-- 结果："dark"
真香点：不用改表结构就能扩字段，还支持索引优化，比存字符串香 10 倍！
二、WITH ROLLUP：报表统计 “一键加总计”
痛点：统计各部门薪资，还要算全局总和，难道要查两次？
技巧：GROUP BY 加 WITH ROLLUP，一次查询出分组 + 总计。
-- 统计各部门人数、薪资及总计
SELECT
department AS '部门',
COUNT(*) AS '人数',
SUM(salary) AS '薪资总和'
FROM employees
GROUP BY department WITH ROLLUP;
-- 结果直接带总计行
-- 部门 人数 薪资总和
-- 技术部 3 50000
-- 市场部 2 25000
-- NULL 7 96000 -- 自动生成的总计
避坑提示：总计行的分组字段会显示 NULL，前端判断时注意处理。
三、CASE WHEN：多条件统计 “一行搞定”
痛点：统计活跃 / 非活跃用户、不同状态订单，要写多条 SQL？
技巧：CASE WHEN 做条件聚合，单条查询出所有维度。
-- 一次统计多种状态用户数
SELECT
SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) AS '活跃用户',
SUM(CASE WHEN status='inactive' THEN 1 ELSE 0 END) AS '非活跃用户',
SUM(CASE WHEN create_time>='2025-01-01' THEN 1 ELSE 0 END) AS '新增用户'
FROM users;
-- 结果清晰直观
-- 活跃用户 非活跃用户 新增用户
-- 120 35 48
实战场景：做运营报表、数据看板必备，省去后端拼接数据的麻烦。
四、INSERT IGNORE：重复数据 “自动跳过”
痛点：批量导入数据，遇到主键冲突直接报错，整批都失败？
技巧：用 INSERT IGNORE，重复数据静默跳过，不影响其他数据。
-- 导入用户数据，已存在的id=1记录直接跳过
INSERT IGNORE INTO users (id, name, email) VALUES
(1, '张三', 'zhangsan@example.com'), -- 重复，跳过
(3, '王五', 'wangwu@example.com'); -- 新增，成功
适用场景：日志导入、历史数据迁移，不用先查后插，代码少一半。
五、ON DUPLICATE KEY UPDATE：“存在更新，不存在插入”
痛点：更新用户信息，要先查有没有这个用户，再决定插还是更？
技巧：用这个语句实现 “upsert”，一条 SQL 搞定两种逻辑。
-- 按id判断：有则更新，无则插入
INSERT INTO users (id, name, email) VALUES
(1, '张三', 'zhangsan_new@example.com'), -- 已存在，更新邮箱
(4, '赵六', 'zhaoliu@example.com') -- 不存在，插入
ON DUPLICATE KEY UPDATE
name = VALUES(name), -- 用新值更新
email = VALUES(email);
注意：必须有主键或唯一索引才能触发，否则会变成普通插入。
六、FIND_IN_SET：逗号分隔字段 “精准查询”
痛点：商品分类存成 “1,2,3”，想查含分类 1 的商品怎么搞？
技巧：别用 LIKE ！FIND_IN_SET 专门处理集合查询。
-- 商品表：category_ids存逗号分隔的分类ID
SELECT * FROM products WHERE FIND_IN_SET('1', category_ids);
-- 正确匹配：category_ids为1、1,2、1,3的记录
-- 不会匹配：10、21（LIKE会误判）
避坑提示：虽然好用，但尽量少用逗号存集合，量大了建议拆成关联表。
七、GROUP_CONCAT：多行数据 “合并成字符串”
痛点：查部门员工列表，结果是多行，前端要循环拼接？
技巧：GROUP_CONCAT 直接合并，后端少写 10 行处理代码。
-- 按部门合并员工姓名（逗号分隔，按姓名排序）
SELECT
department AS '部门',
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS '员工列表'
FROM employees
GROUP BY department;
-- 结果直接能用
-- 部门 员工列表
-- 技术部 李四, 王五, 张三
-- 市场部 赵六, 钱七
小技巧：默认长度限制 1024，可通过 SET group_concat_max_len=102400 扩大。
八、EXISTS：子查询优化 “速度起飞”
痛点：用 IN 做子查询，数据量大时卡到超时？
技巧：换成 EXISTS，只判断 “是否存在”，效率提升 N 倍。
-- 低效：IN子查询（会全表扫描子查询结果）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city='北京');
-- 高效：EXISTS（找到匹配就停止，不用查全部）
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id=o.user_id AND u.city='北京');
原理：EXISTS 是 “短路判断”，只要子查询有结果就返回，大数据量下优势明显。
九、ROW_NUMBER ()：分页排序 “不丢数据”
痛点：用 LIMIT 分页，遇到相同排序值（如相同薪资），翻页可能丢数据？
技巧：用窗口函数 ROW_NUMBER () 加唯一排序条件。
-- 按薪资降序分页，用id保证排序唯一
SELECT * FROM (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY salary DESC, id ASC) AS rn
FROM employees
) t WHERE rn BETWEEN 11 AND 20;
实战场景：排行榜、分页列表，再也不怕数据重复或丢失。
十、EXPLAIN：慢查询 “一键诊断”
痛点：SQL 跑半天，不知道卡在哪？
技巧：在查询前加 EXPLAIN，瞬间看清执行计划。
-- 分析查询为什么慢
EXPLAIN SELECT * FROM orders WHERE user_id=123 AND create_time>='2025-01-01';
关键看这 2 列：
type：显示连接类型，出现 “ALL” 就是全表扫描，要加索引；
key：显示用到的索引，为 NULL 说明没走索引，赶紧优化。
附避坑清单：这些错误别再犯了！
禁用 SELECT * ：浪费带宽，暴露敏感字段；
LIKE 别用前导 % ：%北京 会让索引失效；
大分页不用 OFFSET ：用 WHERE id > 1000 LIMIT 10 替代 LIMIT 1000,10；
事务别写太长 ：避免长时间锁表影响并发。
以上 10 个技巧，每个都在实际项目中救过我的急。其实 MySQL 的强大远不止于此，关键是别停留在基础操作，多琢磨 “有没有更优解”。
你平时最常用哪个 MySQL 技巧？或者踩过什么 SQL 坑？欢迎在评论区交流，点赞过万再出进阶版！
