函數分類速查索引
本文整理 MySQL 開發最高頻函數,按功能分類,附語法與實戰範例。
1. 字串函數
| 函數 | 語法 | 說明 | 範例 |
CONCAT | CONCAT(str1,str2,...) | 串聯,任一 NULL 結果 NULL | CONCAT('My','SQL')='MySQL' |
CONCAT_WS | CONCAT_WS(sep,str1,str2...) | 帶分隔符串聯,忽略 NULL | CONCAT_WS(',','A',NULL,'C')='A,C' |
SUBSTRING | SUBSTRING(str,pos[,len]) | 擷取子字串,pos 可負 | SUBSTRING('abcdef',2,3)='bcd' |
LEFT/RIGHT | LEFT(str,len) | 左/右側擷取 | LEFT('abc',2)='ab' |
TRIM | TRIM([BOTH|LEADING|TRAILING] [c] FROM str) | 去除空白/指定字元 | TRIM(' a ')='a' |
REPLACE | REPLACE(str,from,to) | 全部替換 | REPLACE('a b',' ','_')='a_b' |
REVERSE | REVERSE(str) | 反轉字串 | REVERSE('abc')='cba' |
UPPER/LOWER | UPPER(str) | 大小寫轉換 | UPPER('mysql')='MYSQL' |
LENGTH/CHAR_LENGTH | LENGTH(str) | 位元組/字元長度 | LENGTH('中')=3, CHAR_LENGTH=1 |
LOCATE/FIND_IN_SET | LOCATE(sub,str[,pos]) | 搜尋位置/集合中位置 | LOCATE('b','abc')=2 |
LPAD/RPAD | LPAD(str,len,pad) | 左/右填充 | LPAD('5',3,'0')='005' |
HEX/UNHEX | HEX(str) | 十六進制互轉 | HEX('MySQL')='4D7953514C' |
BASE64_ENCODE/DECODE | TO_BASE64(str) | Base64 編解碼 (8.0+) | TO_BASE64('abc')='YWJj' |
2. 日期時間函數
| 函數 | 語法 | 說明 | 範例 |
NOW/SYSDATE | NOW() | 當前日期時間,NOW() 查詢固定 | NOW()='2024-03-15 14:30:00' |
CURDATE/CURTIME | CURDATE() | 當前日期/時間 | CURDATE()='2024-03-15' |
DATE_ADD/SUB | DATE_ADD(date,INTERVAL expr unit) | 日期加減 | DATE_ADD(NOW(),INTERVAL 1 DAY) |
DATEDIFF | DATEDIFF(d1,d2) | 日期差天數 | DATEDIFF('2024-03-15','2024-03-10')=5 |
TIMESTAMPDIFF | TIMESTAMPDIFF(unit,dt1,dt2) | 指定單位差值 | TIMESTAMPDIFF(MONTH,d1,d2) |
DATE_FORMAT | DATE_FORMAT(date,format) | 格式化輸出 | DATE_FORMAT(NOW(),'%Y-%m-%d') |
STR_TO_DATE | STR_TO_DATE(str,format) | 字串解析為日期 | STR_TO_DATE('15/03/2024','%d/%m/%Y') |
LAST_DAY | LAST_DAY(date) | 月最後一天 | LAST_DAY('2024-02-01')='2024-02-29' |
EXTRACT | EXTRACT(unit FROM date) | 擷取元件 | EXTRACT(MONTH FROM NOW()) |
MAKEDATE/MAKETIME | MAKEDATE(year,dayofyear) | 組裝日期/時間 | MAKEDATE(2024,60)='2024-02-29' |
UNIX_TIMESTAMP/FROM_UNIXTIME | UNIX_TIMESTAMP([date]) | Unix 時間戳互轉 | FROM_UNIXTIME(1710484200) |
CONVERT_TZ | CONVERT_TZ(dt,from_tz,to_tz) | 時區轉換 | CONVERT_TZ(NOW(),'+00:00','+08:00') |
3. 聚合函數
| 函數 | 語法 | 說明 | 範例 |
COUNT | COUNT(*) / COUNT(col) | 行數/非 NULL 計數 | COUNT(DISTINCT col) 去重計數 |
SUM/AVG | SUM(col) | 總和/平均,忽略 NULL | SUM(price) |
MIN/MAX | MIN(col) | 最小/大值 | MAX(created_at) |
GROUP_CONCAT | GROUP_CONCAT([DISTINCT] expr [ORDER BY...] [SEPARATOR str]) | 群組串聯 | GROUP_CONCAT(name ORDER BY id SEPARATOR ',') |
STDDEV/VARIANCE | STDDEV(expr) | 標準差/變異數 | 統計分析 |
JSON_ARRAYAGG/JSON_OBJECTAGG | JSON_ARRAYAGG(col) | JSON 陣列/物件聚合 (8.0+) | 輸出 JSON 格式 |
BIT_AND/OR/XOR | BIT_AND(expr) | 位元聚合 | 旗標運算 |
4. 流程控制函數
| 函數 | 語法 | 說明 | 範例 |
CASE | CASE WHEN cond THEN val ... ELSE val END | 多條件分支 | CASE WHEN score>=90 THEN 'A' ELSE 'B' END |
IF | IF(cond, true_val, false_val) | 三元運算,條件以整數判斷 | IF(stock>0,'有貨','缺貨') |
IFNULL | IFNULL(expr1,expr2) | expr1 非 NULL 回傳 expr1 | IFNULL(nickname,username) |
COALESCE | COALESCE(v1,v2,...) | 回傳第一個非 NULL(標準 SQL) | COALESCE(mobile,phone,email) |
NULLIF | NULLIF(expr1,expr2) | 相等回 NULL | NULLIF(divisor,0) 防除零 |
GREATEST/LEAST | GREATEST(v1,v2,...) | 最大/最小值 | GREATEST(1,5,3)=5 |
5. JSON 函數
| 函數 | 語法 | 說明 |
JSON_EXTRACT | JSON_EXTRACT(doc, path) 或 doc->>path | 擷取值(文字) |
JSON_UNQUOTE | JSON_UNQUOTE(val) | 移除引號 |
JSON_SET/INSERT/REPLACE | JSON_SET(doc, path, val) | 修改/新增/替換 |
JSON_REMOVE | JSON_REMOVE(doc, path) | 移除路徑 |
JSON_CONTAINS | JSON_CONTAINS(doc, val, path) | 包含判斷 |
JSON_KEYS | JSON_KEYS(doc[,path]) | 取得鍵名陣列 |
JSON_LENGTH | JSON_LENGTH(doc[,path]) | 長度/元素數 |
JSON_TABLE | JSON_TABLE(doc, path COLUMNS ...) | JSON 轉關聯表 (8.0+) |
6. 資訊與型別轉換
-- 系統資訊
VERSION(), DATABASE(), USER(), CURRENT_USER(), CONNECTION_ID(), LAST_INSERT_ID()
-- 型別轉換
CAST(expr AS TYPE), CONVERT(expr, TYPE)
-- TYPE: CHAR, DATE, DATETIME, DECIMAL, SIGNED, UNSIGNED, TIME, BINARY, JSON
-- 例
SELECT CAST('123' AS UNSIGNED), CONVERT('2024-01-01', DATE);
7. 加密與雜湊
| 函數 | 說明 | 備註 |
MD5() | MD5 雜湊 (32 hex) | 不可逆、不安全 |
SHA1(), SHA2() | SHA-1, SHA-224/256/384/512 | SHA2(str, 256) |
PASSWORD() | MySQL 密碼雜湊 | 8.0 移除,勿用於應用 |
AES_ENCRYPT/DECRYPT | AES 加解密 | 需金鑰管理 |
RANDOM_BYTES() | 密碼學安全隨機位元組 | 8.0+,生成鹽值/Token |
實戰組合拳範例
動態搜尋關鍵字高亮
SET @kw = 'MySQL';
SELECT
id,
title,
REPLACE(content, @kw, CONCAT('', @kw, '')) AS highlighted
FROM articles
WHERE MATCH(title, content) AGAINST(@kw IN BOOLEAN MODE);
產生報表代碼:年月日流水號
SELECT CONCAT(
DATE_FORMAT(NOW(), '%Y%m%d'),
LPAD(COALESCE(MAX(CAST(SUBSTRING(order_no, 9) AS UNSIGNED)), 0) + 1, 4, '0')
) AS new_order_no
FROM orders
WHERE order_no LIKE CONCAT(DATE_FORMAT(NOW(), '%Y%m%d'), '%');
計算連續登入天數
SELECT
user_id,
COUNT(*) AS streak_days
FROM (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
FROM user_logins
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 7; -- 連續 7 天以上
效能提醒
- 函數在 WHERE 欄位上會導致索引失效:
WHERE DATE(created_at) = '2024-03-15' → 改用範圍查詢
- GROUP_CONCAT 長度限制:
SET SESSION group_concat_max_len = 1000000;
- JSON 函數需 8.0+,5.7 僅支援基本擷取
- 避免 SELECT * 配合函數運算,明確列出欄位
將此速查表加入書籤,寫 SQL 時隨查隨用,效率倍增!