前言

作為高頻操作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）分析性能。