前言

作为高频操作MySQL的开发者，我发现许多小伙伴仍停留在基础的CRUD层面。今天将分享10个我在实际工作中高频使用的MySQL技巧，这些技巧不仅提升了开发效率，还能优化查询性能。学会它们，能让你的SQL操作更优雅、更高效！

目录

1. 使用JSON类型存储灵活数据
2. 使用WITH ROLLUP实现分组统计与总计
3. 使用CASE WHEN进行条件聚合统计
4. 使用INSERT IGNORE避免重复插入
5. 使用ON DUPLICATE KEY UPDATE实现upsert操作
6. 使用FIND_IN_SET处理集合查询
7. 使用GROUP_CONCAT合并多行数据
8. 使用EXISTS优化子查询性能
9. 使用ROW_NUMBER()实现高效分页
10. 使用WITH子句简化复杂查询

1. 使用JSON类型存储灵活数据

场景：当业务需求包含动态扩展字段（如用户自定义配置）时，传统字段扩展会导致表结构臃肿。MySQL的JSON类型支持动态键值对存储，完美解决此类问题。

-- 创建用户偏好表（JSON字段存储动态配置）  
CREATE TABLE user_preferences (  
  id INT PRIMARY KEY COMMENT '主键ID',  
  user_id INT COMMENT '用户ID',  
  preferences JSON COMMENT '用户偏好设置（JSON格式）'  
) COMMENT '用户偏好表';  

-- 插入测试数据  
INSERT INTO user_preferences VALUES  
(1, 1, '{"theme": "dark", "notifications": true, "fontSize": 14}'),  
(2, 2, '{"theme": "light", "notifications": false, "fontSize": 16}'),  
(3, 3, '{"theme": "dark", "notifications": true, "fontSize": 12}');  

-- 查询指定用户的主题配置（->符号提取JSON值）  
SELECT preferences->'$.theme' AS theme FROM user_preferences WHERE user_id = 1;  
-- 结果："dark"  

2. 使用WITH ROLLUP进行分组统计

场景：生成报表时，不仅需要各分组数据，还需总计行。WITH ROLLUP可在一次查询中同时返回分组结果和全局汇总。

-- 统计各部门员工数、薪资总和及总计  
SELECT  
  department,  
  COUNT(*) AS employee_count,  
  SUM(salary) AS total_salary  
FROM employees  
GROUP BY department WITH ROLLUP;  

-- 结果示例：  
-- department | employee_count | total_salary  
-- 技术部       3              50000.00  
-- 市场部       2              25000.00  
-- 人事部       2              21000.00  
-- NULL         7              96000.00 （总计行）  

3. 使用CASE WHEN进行条件统计

场景：按不同条件（如用户状态、订单类型）统计数据时，CASE WHEN可在单条查询中完成多条件聚合。

-- 统计活跃/非活跃用户数量  
SELECT  
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_users,  
  SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_users  
FROM users;  

-- 结果示例：  
-- active_users | inactive_users  
-- 3            2  

4. 使用INSERT IGNORE避免重复插入

场景：批量导入数据时，跳过已存在的记录（基于唯一键），避免主键/唯一索引冲突报错。

-- 插入数据，重复的id=1记录会被跳过，id=3正常插入  
INSERT IGNORE INTO users (id, name, email) VALUES  
(1, '张三', 'zhangsan@example.com'),  -- 重复，跳过  
(3, '王五', 'wangwu@example.com');     -- 新增，成功  

5. 使用ON DUPLICATE KEY UPDATE实现插入或更新

场景：需要根据唯一键（如用户ID）实现“存在则更新，不存在则插入”的逻辑，替代传统的先查询再操作。

-- 插入或更新用户信息（基于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);  

6. 使用FIND_IN_SET进行集合查询

场景：当字段存储逗号分隔的集合（如多分类ID）时，快速查询包含指定元素的记录。

-- 查询包含分类ID=1的商品  
SELECT * FROM products WHERE FIND_IN_SET('1', category_ids);  

-- 结果：商品A（1,2,3）、商品C（1,4）、商品E（1,5,6）  

7. 使用GROUP_CONCAT合并多行数据

场景：将分组后的多行数据合并为单个字符串（如部门员工列表、标签集合），简化应用层拼接逻辑。

-- 按部门合并员工姓名（以逗号分隔）  
SELECT  
  department,  
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees  
FROM employees  
GROUP BY department;  

-- 结果示例：  
-- 技术部 | 李四, 王五, 张三  

8. 使用EXISTS优化子查询

场景：替代低效的IN子查询，判断子查询是否存在结果。尤其在大数据量时，EXISTS性能更优。

-- 查询包含高价商品（价格>100）的订单  
SELECT * FROM orders o  
WHERE EXISTS (  
  SELECT 1 FROM order_items oi  
  WHERE oi.order_id = o.id AND oi.price > 100  
);  

9. 使用ROW_NUMBER()实现分页

场景：MySQL 8.0+支持的窗口函数，通过行编号实现分页，逻辑清晰且性能稳定。

-- 查询第1-10条最新文章（按创建时间倒序）  
SELECT * FROM (  
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num  
  FROM articles  
) t  
WHERE row_num BETWEEN 1 AND 10;  

10. 使用WITH子句优化复杂查询

场景：将复杂查询拆解为多个CTE（公共表表达式），提升可读性和可维护性，类似“查询中的变量定义”。

-- 统计用户订单数和总金额，再关联用户表  
WITH user_stats AS (  
  SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount  
  FROM orders  
  GROUP BY user_id  
)  
SELECT u.name, us.order_count, us.total_amount  
FROM users u  
JOIN user_stats us ON u.id = us.user_id;  

结语

以上技巧均来自实际业务场景，兼具实用性和性能优化价值。MySQL的强大不仅在于基础功能，更体现在对复杂场景的灵活处理。建议在开发中多尝试这些特性，同时注意：

• JSON类型适用于非高频查询的动态数据，高频查询字段仍建议独立建表；
• 窗口函数、CTE等功能需MySQL 8.0+支持，注意版本兼容性；
• 复杂查询建议结合执行计划（EXPLAIN）分析性能。