函數分類總覽
| 類別 | 核心函數 | 典型場景 |
|---|
| 字符串 | CONCAT, SUBSTRING, REPLACE, TRIM, UPPER/LOWER, LOCATE, LENGTH/CHAR_LENGTH | 數據清洗、報表拼接、模糊匹配 |
| 數值 | ROUND, CEIL/FLOOR, TRUNCATE, RAND, ABS, MOD | 金融計算、分頁偏移、隨機抽樣 |
| 日期時間 | NOW, CURDATE, DATE_ADD/SUB, DATEDIFF, DATE_FORMAT, EXTRACT, UNIX_TIMESTAMP | 業務時間窗口、同比環比、定時任務 |
| 系統信息 | VERSION, DATABASE, USER, LAST_INSERT_ID, CONNECTION_ID | 審計日志、多租戶隔離、調試排查 |
一、字符串函數精講
1.1 拼接：CONCAT vs CONCAT_WS
-- CONCAT：任意參數為 NULL 則整體返回 NULL
SELECT CONCAT('a','b',NULL); -- NULL
-- CONCAT_WS(sep, str1, str2...)：首參為分隔符，自動跳過 NULL
SELECT CONCAT_WS('-', '020', NULL, '87654321'); -- '020-87654321'
-- 實戰：拼接地址、電話、標籤
SELECT CONCAT_WS(',', province, city, district) AS full_addr FROM users;
1.2 截取：LEFT/RIGHT/SUBSTRING/MID
SELECT LEFT('mysql', 2); -- 'my'
SELECT RIGHT('mysql', 3); -- 'sql'
SELECT SUBSTRING('zhaojd', 2, 3); -- 'hao' (從第2位取3字符)
SELECT MID('zhaojd', 2, 4); -- 'haoj' (MID 別名)
1.3 去空格：TRIM/LTRIM/RTRIM
SELECT TRIM(' mysql '); -- 'mysql'
SELECT LTRIM(' mysql'); -- 'mysql'
SELECT RTRIM('mysql '); -- 'mysql'
-- 進階：去除指定字符
SELECT TRIM(BOTH 'x' FROM 'xxxmysqlxxx'); -- 'mysql'
1.4 替換：REPLACE vs INSERT
-- REPLACE：全量替換所有匹配子串
SELECT REPLACE('abcabc', 'ab', 'XY'); -- 'XYcXYc'
-- INSERT：按位置長度替換（類似數組 splice）
SELECT INSERT('這是mysql', 3, 5, 'oracle'); -- '這oracle' (從第3位長度5替換)
1.5 大小寫轉換
SELECT UPPER('mysql'), LOWER('MYSQL'), UCASE('MySql'), LCASE('MySql');
1.6 長度與字符數
SELECT LENGTH('漢字'), CHAR_LENGTH('漢字'); -- 6, 2 (utf8mb4 下漢字佔3字節)
SELECT LENGTH('mysql'), CHAR_LENGTH('mysql'); -- 5, 5
1.7 定位與查找
SELECT LOCATE('sql', 'mysql'); -- 3
SELECT POSITION('sql' IN 'mysql'); -- 3
SELECT INSTR('mysql', 'sql'); -- 3
SELECT FIND_IN_SET('mysql', 'oracle,mysql,db2'); -- 2 (按逗號分隔)
SELECT FIELD('mysql', 'oracle','db2','mysql'); -- 3 (按參數列表位置)
SELECT ELT(2, 'a','b','c'); -- 'b' (按索引取值)
SELECT MAKE_SET(5, 'a','b','c','d'); -- 'a,c' (5=0101b 取第1、3個)
1.8 比較：STRCMP
SELECT STRCMP('abc','abd'), STRCMP('abc','abc'), STRCMP('abc','abb'); -- -1, 0, 1
二、數值函數精講
-- 隨機數
SELECT RAND(), RAND(42); -- 無種子每次不同，有種子固定序列
-- 取整
SELECT CEIL(4.3), CEIL(-2.5), FLOOR(4.3), FLOOR(-2.5); -- 5, -2, 4, -3
-- 截取小數（不四捨五入）
SELECT TRUNCATE(903.343434, 2), TRUNCATE(903.343, -1); -- 903.34, 900
-- 四捨五入
SELECT ROUND(903.53567), ROUND(903.53567, 2), ROUND(903.53567, -1); -- 904, 903.54, 900
三、日期時間函數精講
3.1 獲取當前時間
SELECT NOW(), CURRENT_TIMESTAMP(), SYSDATE(); -- 精度到秒
SELECT CURDATE(), CURRENT_DATE(); -- 僅日期
SELECT CURTIME(), CURRENT_TIME(); -- 僅時間
3.2 日期時間拆解
SELECT
YEAR(NOW()), QUARTER(NOW()), MONTH(NOW()), MONTHNAME(NOW()),
WEEK(NOW()), WEEKOFYEAR(NOW()),
DAY(NOW()), DAYOFMONTH(NOW()), DAYOFYEAR(NOW()),
DAYNAME(NOW()), DAYOFWEEK(NOW()), WEEKDAY(NOW()),
HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
-- 統一入口：EXTRACT
SELECT EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(DAY_HOUR FROM NOW());
3.3 日期運算
-- 天級
SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY), DATE_SUB(CURDATE(), INTERVAL 5 DAY);
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365
-- 月/年級（自動處理月末邊界）
SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
SELECT DATE_ADD(CURDATE(), INTERVAL '1-2' YEAR_MONTH); -- +1年2月
-- 秒級
SELECT ADDTIME(CURTIME(), '00:05:00'), SUBTIME(CURTIME(), 300);
3.4 格式化輸出
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'); -- 2024-01-15 14:30:45
SELECT STR_TO_DATE('2024/01/15', '%Y/%m/%d'); -- 字符串轉日期
四、系統信息函數
SELECT VERSION(), DATABASE(), USER(), CONNECTION_ID(), LAST_INSERT_ID();
五、實戰最佳實踐
- 字符集統一 utf8mb4：所有字符串操作前確保連接、表、列均為 utf8mb4，避免截取亂碼。
- 日期運算優先用 INTERVAL：自動處理閏年、月末、DST，勿手動拼接天數。
- 金額計算用 DECIMAL + ROUND/TRUNCATE：嚴禁 FLOAT/DOUBLE 參與財務運算。
- 隨機抽樣大表用 RAND() 配合索引：
WHERE RAND() < 0.01 會全表掃描，改用 WHERE id >= (SELECT FLOOR(MAX(id)*RAND()) FROM t) 走主鍵。
- 系統函數用於審計觸發器：
USER() 記錄操作人，LAST_INSERT_ID() 獲取自增 ID 避免併發衝突。
