精選高頻實用 SQL 模式,涵蓋條件轉換、去重、隨機抽樣、字符串替換、自增控制、動態行號、字符串拼接、日期拆解、唯一鍵容錯、全文搜索、相對時間查詢、字符集設置、跨表插入,提升開發調試效率。
-- 日期為零值顯示空串
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;
SELECT email, COUNT(*) AS cnt FROM emails_table GROUP BY email HAVING cnt > 1 ORDER BY cnt DESC;
SELECT * FROM your_table ORDER BY RAND() LIMIT 10;
-- 大表慎用 ORDER BY RAND(),建議主鍵範圍隨機或 TABLESAMPLE(8.0+)
UPDATE your_table SET name = REPLACE(name, 'gbin1.com', 'gbtags.com') WHERE name LIKE '%John%';
ALTER TABLE your_table AUTO_INCREMENT = 2;
-- 下一條插入 ID 為 2(需確保無主鍵衝突)
SET @N = 0;
SELECT @N := @N + 1 AS number, name, surname FROM gbtags_users;
-- 8.0+ 可用 ROW_NUMBER() OVER ()
SELECT CONCAT(name, ' ', surname) AS complete_name FROM users;
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;
INSERT IGNORE INTO tags (tag) VALUES ('good');
-- 重複執行不報錯,唯一鍵衝突自動忽略
ALTER TABLE articles ADD FULLTEXT(content_column);
SELECT * FROM articles WHERE MATCH(content_column) AGAINST ('music');
-- 需空表建索引,已有數據需 REPAIR TABLE 或重建
SELECT user, COUNT(*) AS logins
FROM stat_log
WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
GROUP BY user;
SET NAMES 'utf8mb4';
-- 防止中文亂碼,建議連接池初始化時自動執行
INSERT INTO yourtable (field1, field2, field3)
SELECT newfield1, newfield2, 'fixed value' FROM yourtable2;
-- 高效遷移/歸檔數據,避免應用層逐行處理
ORDER BY RAND() 大表全表掃描極慢,生產環境改用主鍵隨機範圍INSERT IGNORE 會吞噬所有錯誤(不僅唯一鍵),調試時慎用DATE_ADD(CURDATE(), INTERVAL -1 MONTH) 含當日 00:00:00,按需調整