MySQL 實用小技巧持續更新：隨機抽樣、分組統計、常用函數、去重、大量匯入優化

1. 巧用 RAND() 隨機抽樣

-- 隨機抽取 10 筆
SELECT * FROM `table` ORDER BY RAND() LIMIT 10;

-- 大表優化：先取隨機 ID 再 JOIN (避免全表排序)
SELECT t.* FROM `table` t
JOIN (SELECT id FROM `table` ORDER BY RAND() LIMIT 10) r USING (id);

注意：ORDER BY RAND() 會觸發全表掃描與臨時表排序，百萬級以上表必須用優化寫法。

2. GROUP BY WITH ROLLUP 多層級彙總

函數語法說明/範例
CONCATCONCAT(str1,str2,...)任一參數為 NULL 則結果為 NULL。CONCAT('My','SQL')='MySQL'
CONCAT_WSCONCAT_WS(sep,str1,str2,...)忽略 NULL，用分隔符連接。CONCAT_WS(',','a',NULL,'c')='a,c'
GROUP_CONCATGROUP_CONCAT(expr [ORDER BY ...] [SEPARATOR ','])群組內字串聚合。SELECT GROUP_CONCAT(name) FROM t GROUP BY dept
CASECASE WHEN cond THEN val ... ELSE val END條件分支。CASE WHEN score>=60 THEN 'Pass' ELSE 'Fail' END
IFIF(cond, true_val, false_val)三元運算。IF(1>2,'yes','no')='no'
IFNULLIFNULL(val1, val2)val1 非 NULL 回傳 val1，否則 val2。IFNULL(NULL,0)=0
NULLIFNULLIF(val1, val2)相等回傳 NULL，否則 val1。NULLIF(1,1)=NULL
COALESCECOALESCE(v1,v2,v3,...)回傳第一個非 NULL 值。COALESCE(a,b,c)
GREATESTGREATEST(v1,v2,...)最大值。GREATEST(3,1,5)=5
LEASTLEAST(v1,v2,...)最小值
UNIX_TIMESTAMPUNIX_TIMESTAMP([date])日期轉秒數。UNIX_TIMESTAMP('2024-01-01')
FROM_UNIXTIMEFROM_UNIXTIME(ts[,format])秒數轉日期。FROM_UNIXTIME(1704067200,'%Y-%m-%d')
TO_DAYSTO_DAYS(date)日期轉天數 (從 Year 0)。TO_DAYS('2024-01-01')
MD5MD5(str)32 位十六進制雜湊。常用於密碼/唯一鍵生成
INET_ATONINET_ATON('192.168.1.1')IPv4 轉無符號整數。3232235777
INET_NTOAINET_NTOA(num)整數轉 IPv4。INET_NTOA(3232235777)='192.168.1.1'


4. 高效刪除重複資料 (保留一筆)
單欄位去重
CREATE TEMPORARY TABLE tmp_ids 
SELECT MIN(id) AS id FROM `table` GROUP BY col1, col2 HAVING COUNT(*) > 1;

DELETE t FROM `table` t
LEFT JOIN tmp_ids tmp ON t.id = tmp.id
WHERE tmp.id IS NULL 
  AND (t.col1, t.col2) IN (SELECT col1, col2 FROM `table` GROUP BY col1, col2 HAVING COUNT(*) > 1);

存儲過程通用去重 (游標逐組刪除)
ALTER TABLE tbl_name DISABLE KEYS;
-- 大量 INSERT / LOAD DATA
ALTER TABLE tbl_name ENABLE KEYS;
DISABLE KEYS 關閉非唯一索引更新，匯入完成後 ENABLE KEYS 重建，速度比逐筆維護索引快數倍。

InnoDB：借助 MyISAM 中轉

關閉 innodb_buffer_pool_size (或設極小) 釋放記憶體
建立無索引 MyISAM 表：CREATE TABLE tmp_m ENGINE=MyISAM SELECT * FROM src LIMIT 0
SET SESSION concurrent_insert=2 (尾部並發插入)
大量 INSERT INTO tmp_m ... 或 LOAD DATA
匯入完成：ALTER TABLE tmp_m ENGINE=InnoDB, ADD PRIMARY KEY (id), ADD INDEX ...
重命名替換原表

原理：MyISAM 尾部插入極快，再轉 InnoDB 重建聚簇索引，避免 InnoDB 亂序插入導致的頁分裂與 B+ 樹重組。

總結
MySQL 小技巧核心價值：語法糖簡化邏輯 (WITH ROLLUP/COALESCE)、效能捷徑 (DISABLE KEYS/MyISAM 中轉)、資料品質 (去重模板)、隨機抽樣優化。實際專案中，將常用片段封裝為程式碼片段/儲存過程/工具腳本，可大幅提升開發與維運效率。