萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 13 個實用 SQL 技巧:CASE、去重、隨機、替換、自增重置等

MySQL 開發必備 13 個實用 SQL 技巧速查表

精選高頻實用 SQL 模式,涵蓋條件轉換、去重、隨機抽樣、字符串替換、自增控制、動態行號、字符串拼接、日期拆解、唯一鍵容錯、全文搜索、相對時間查詢、字符集設置、跨表插入,提升開發調試效率。

1. CASE WHEN 動態轉換顯示值


-- 日期為零值顯示空串
SELECT id, title, CASE date WHEN '0000-00-00' THEN '' ELSE date END AS date FROM t;
-- 狀態碼轉中文
SELECT id, title, CASE status WHEN 0 THEN 'open' WHEN 1 THEN 'close' ELSE 'standby' END AS status FROM t;

2. 查找重複記錄(如 Email)


SELECT email, COUNT(*) AS cnt FROM emails_table GROUP BY email HAVING cnt > 1 ORDER BY cnt DESC;

3. 隨機抽樣記錄


SELECT * FROM your_table ORDER BY RAND() LIMIT 10;
-- 大表慎用 ORDER BY RAND(),建議主鍵範圍隨機或 TABLESAMPLE(8.0+)

4. UPDATE 批量替換字符串


UPDATE your_table SET name = REPLACE(name, 'gbin1.com', 'gbtags.com') WHERE name LIKE '%John%';

5. 重置自增值


ALTER TABLE your_table AUTO_INCREMENT = 2;
-- 下一條插入 ID 為 2(需確保無主鍵衝突)

6. SELECT 動態生成行號


SET @N = 0;
SELECT @N := @N + 1 AS number, name, surname FROM gbtags_users;
-- 8.0+ 可用 ROW_NUMBER() OVER ()

7. CONCAT 字符串拼接


SELECT CONCAT(name, ' ', surname) AS complete_name FROM users;

8. 日期拆解與組合


SELECT id, title, YEAR(date_field) FROM your_table;
SELECT id, title, CONCAT(MONTH(date_field), '/', YEAR(date_field)) AS new_date FROM your_table;

9. INSERT IGNORE 唯一鍵衝突靜默跳過


INSERT IGNORE INTO tags (tag) VALUES ('good');
-- 重複執行不報錯,唯一鍵衝突自動忽略

10. 全文索引搜索


ALTER TABLE articles ADD FULLTEXT(content_column);
SELECT * FROM articles WHERE MATCH(content_column) AGAINST ('music');
-- 需空表建索引,已有數據需 REPAIR TABLE 或重建

11. 相對時間查詢(近一個月登錄統計)


SELECT user, COUNT(*) AS logins
FROM stat_log
WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
GROUP BY user;

12. 連接建立後立即設置字符集


SET NAMES 'utf8mb4';
-- 防止中文亂碼,建議連接池初始化時自動執行

13. 跨表插入指定列


INSERT INTO yourtable (field1, field2, field3)
SELECT newfield1, newfield2, 'fixed value' FROM yourtable2;
-- 高效遷移/歸檔數據,避免應用層逐行處理

避坑提醒

  • ORDER BY RAND() 大表全表掃描極慢,生產環境改用主鍵隨機範圍
  • INSERT IGNORE 會吞噬所有錯誤(不僅唯一鍵),調試時慎用
  • 全文索引需 MyISAM 或 InnoDB 5.6+,中文需配置 ngram 解析器
  • DATE_ADD(CURDATE(), INTERVAL -1 MONTH) 含當日 00:00:00,按需調整
copyright © 萬盛學電腦網 all rights reserved