函數分類導航
MySQL 內置函數超過 200 個，按功能域歸類為九大類，掌握核心高頻函數可顯著提升 SQL 表達力與性能。
1. 數學函數
| 函數 | 功能 | 示例與結果 |
|---|
GREATEST/LEAST | 最大/最小值 | GREATEST(10,9,128,1)=128 |
FLOOR/CEILING | 向下/向上取整 | FLOOR(-1.2)=-2, CEILING(-1.2)=-1 |
ROUND/TRUNCATE | 四捨五入/截斷 | ROUND(25.501)=26, TRUNCATE(1.54578,2)=1.54 |
ABS/SIGN | 絕對值/符號 | SIGN(-2)=-1 |
SQRT/POW | 平方根/冪運算 | POW(2,2)=4, POW(10,-2)=0.01 |
SIN/COS/TAN/ASIN/ACOS/ATAN | 三角函數 (弧度制) | 配合 RADIANS()/DEGREES() 角度轉換 |
BIN/OCT/HEX | 進制轉字符串 | HEX(255)='FF' |
注意： 函數名與括號間不能有空格，否則報錯；錯誤參數返回 NULL。
2. 聚合函數
COUNT/SUM/AVG/MIN/MAX：基礎聚合，配合 GROUP BY 分組統計。GROUP_CONCAT(DISTINCT col ORDER BY col SEPARATOR ';')：行轉列神器，默認 1024 字節上限，可調 group_concat_max_len。STDDEV/VARIANCE：統計分析輔助。
3. 字符串函數 (高頻)
| 函數 | 核心用法 | 典型場景 |
|---|
ASCII/CHAR | 字符與碼值互轉 | 字符編碼檢查 |
LEFT/RIGHT/SUBSTRING | 定長/定位截取 | 手機號掩碼 CONCAT(LEFT(phone,3),'****',RIGHT(phone,4)) |
TRIM/LTRIM/RTRIM | 去首尾/左/右空格 | 數據清洗 ETL |
LPAD/RPAD | 左/右填充至指定長 | 流水號補零 LPAD(id,8,'0') |
REPLACE | 全量替換子串 | 敏感詞過濾、路徑分隔符統一 |
CONCAT/CONCAT_WS | 拼接 (WS 帶分隔符) | 生成全名、拼接查詢條件 |
STRCMP | 字典序比較 | 排序邏輯判斷 |
4. 日期時間函數
- 當前值：
NOW() (日期時間)、CURDATE()、CURTIME()。
- 分量提取：
YEAR/MONTH/DAY/HOUR/MINUTE/SECOND、MONTHNAME/DAYNAME、WEEK/YEARWEEK、DAYOFWEEK/DAYOFMONTH/DAYOFYEAR。
- 運算：
DATE_ADD(date, INTERVAL 1 MONTH)、DATE_SUB、DATEDIFF、TIMESTAMPDIFF(UNIT, dt1, dt2) (精確單位差)。
- 格式化：
DATE_FORMAT(dt, '%Y-%m-%d %H:%i:%S')、TIME_FORMAT，區分大小寫，格式符見文檔。
5. 加密函數
| 函數 | 算法/特點 | 適用場景 |
|---|
AES_ENCRYPT/AES_DECRYPT | AES 對稱加密，輸出 BLOB | 敏感字段加密存儲 (需密鑰管理) |
ENCODE/DECODE | 弱加密，MySQL 8.0 棄用 | 不推薦新項目使用 |
ENCRYPT | Unix crypt()，Windows 不支援 | 舊系統兼容 |
PASSWORD | MySQL 內部認證專用，不可逆 | 僅用於 mysql.user 表 |
MD5/SHA1/SHA2 | 哈希摘要，無解密 | 密碼存儲 (建議加鹽)、文件校驗 |
6. 流程控制函數 (SQL 中實現邏輯判斷)
IFNULL(expr1, expr2)：expr1 為 NULL 返回 expr2，常用於空值默認值。NULLIF(expr1, expr2)：相等返回 NULL，否則返回 expr1，用於避免除零 10/NULLIF(cnt,0)。IF(cond, true_val, false_val)：三元運算，IF(score>=60,'及格','不及格')。CASE WHEN ... THEN ... ELSE ... END：多條件分支，可嵌套，支援搜索條件與簡單值兩種語法。
7. 格式化函數
FORMAT(num, decimals)：千分位逗號 + 固定小數位，FORMAT(12345.6,2)='12,345.60'。DATE_FORMAT/TIME_FORMAT：見日期函數。INET_ATON('192.168.1.1')=3232235777、INET_NTOA(3232235777)='192.168.1.1'：IP 與整數互轉，利於範圍查詢與索引。
8. 類型轉換函數
CAST(expr AS TYPE)、CONVERT(expr, TYPE)：目標類型 BINARY/CHAR/DATE/TIME/DATETIME/SIGNED/UNSIGNED。- 隱式轉換：數值上下文自動將字符串轉數字
'99'+1=100；比較上下文字符串轉數字 5='5ab' → TRUE (警告)。
BINARY 'str' 或 CAST('str' AS BINARY)：開啟二進制比較，區分大小寫。CAST(CURDATE() AS SIGNED)：日期轉數值 20240120。
9. 系統信息函數
| 函數 | 返回值 | 典型用途 |
|---|
DATABASE() | 當前默認庫 | 多租戶 Schema 校驗 |
USER()/CURRENT_USER() | 登入用戶/認證用戶 | 審計觸發器記錄操作者 |
VERSION() | 服務器版本 | 兼容性判斷 |
CONNECTION_ID() | 線程 ID | 關聯 SHOW PROCESSLIST 排查 |
LAST_INSERT_ID() | 自增 ID | 主從同步、批量插入獲取 ID |
FOUND_ROWS() | 無 LIMIT 總行數 | 分頁查詢優化，配合 SQL_CALC_FOUND_ROWS |
BENCHMARK(n, expr) | 0 (耗時見客戶端) | 表達式性能基準測試 |
GET_LOCK/RELEASE_LOCK/IS_FREE_LOCK | 命名鎖操作 | 分布式應用層鎖協調 |
MASTER_POS_WAIT(log, pos, timeout) | 主從同步等待 | 半同步複制應用層確認 |
性能與避坑提示
- WHERE 子句對列使用函數導致索引失效：
WHERE DATE(created_at)='2024-01-20' → 改為 WHERE created_at >= '2024-01-20' AND created_at < '2024-01-21'。
GROUP_CONCAT 結果截斷靜默發生，必須顯式設置 SET SESSION group_concat_max_len=1000000;。RAND() 在 ORDER BY 中導致全表掃描排序，大表抽樣用 WHERE RAND() < 0.01 或系統表抽樣。- 加密函數輸出為二進制，存儲列類型需為
VARBINARY/BLOB，字符集列會導致數據損壞。
