mysql进阶查询

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

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

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

-- 创建用户偏好表（JSON字段存储动态配置）  
CREATETABLE user_preferences (  
idINT PRIMARY KEYCOMMENT'主键ID',  
  user_id INTCOMMENT'用户ID',  
  preferences JSONCOMMENT'用户偏好设置（JSON格式）'
) COMMENT'用户偏好表';  

-- 插入测试数据  
INSERTINTO 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  
GROUPBY department WITHROLLUP;  

-- 结果示例：  
-- 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(CASEWHENstatus = 'active'THEN1ELSE0END) AS active_users,  
SUM(CASEWHENstatus = 'inactive'THEN1ELSE0END) AS inactive_users  
FROMusers;  

-- 结果示例：  
-- 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实现upsert操作

场景：需要根据唯一键（如用户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;