萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 常用函數速查手冊:字串、日期、聚合、流程控制與 JSON

函數分類速查索引

本文整理 MySQL 開發最高頻函數,按功能分類,附語法與實戰範例。

1. 字串函數

函數語法說明範例
CONCATCONCAT(str1,str2,...)串聯,任一 NULL 結果 NULLCONCAT('My','SQL')='MySQL'
CONCAT_WSCONCAT_WS(sep,str1,str2...)帶分隔符串聯,忽略 NULLCONCAT_WS(',','A',NULL,'C')='A,C'
SUBSTRINGSUBSTRING(str,pos[,len])擷取子字串,pos 可負SUBSTRING('abcdef',2,3)='bcd'
LEFT/RIGHTLEFT(str,len)左/右側擷取LEFT('abc',2)='ab'
TRIMTRIM([BOTH|LEADING|TRAILING] [c] FROM str)去除空白/指定字元TRIM(' a ')='a'
REPLACEREPLACE(str,from,to)全部替換REPLACE('a b',' ','_')='a_b'
REVERSEREVERSE(str)反轉字串REVERSE('abc')='cba'
UPPER/LOWERUPPER(str)大小寫轉換UPPER('mysql')='MYSQL'
LENGTH/CHAR_LENGTHLENGTH(str)位元組/字元長度LENGTH('中')=3, CHAR_LENGTH=1
LOCATE/FIND_IN_SETLOCATE(sub,str[,pos])搜尋位置/集合中位置LOCATE('b','abc')=2
LPAD/RPADLPAD(str,len,pad)左/右填充LPAD('5',3,'0')='005'
HEX/UNHEXHEX(str)十六進制互轉HEX('MySQL')='4D7953514C'
BASE64_ENCODE/DECODETO_BASE64(str)Base64 編解碼 (8.0+)TO_BASE64('abc')='YWJj'

2. 日期時間函數

函數語法說明範例
NOW/SYSDATENOW()當前日期時間,NOW() 查詢固定NOW()='2024-03-15 14:30:00'
CURDATE/CURTIMECURDATE()當前日期/時間CURDATE()='2024-03-15'
DATE_ADD/SUBDATE_ADD(date,INTERVAL expr unit)日期加減DATE_ADD(NOW(),INTERVAL 1 DAY)
DATEDIFFDATEDIFF(d1,d2)日期差天數DATEDIFF('2024-03-15','2024-03-10')=5
TIMESTAMPDIFFTIMESTAMPDIFF(unit,dt1,dt2)指定單位差值TIMESTAMPDIFF(MONTH,d1,d2)
DATE_FORMATDATE_FORMAT(date,format)格式化輸出DATE_FORMAT(NOW(),'%Y-%m-%d')
STR_TO_DATESTR_TO_DATE(str,format)字串解析為日期STR_TO_DATE('15/03/2024','%d/%m/%Y')
LAST_DAYLAST_DAY(date)月最後一天LAST_DAY('2024-02-01')='2024-02-29'
EXTRACTEXTRACT(unit FROM date)擷取元件EXTRACT(MONTH FROM NOW())
MAKEDATE/MAKETIMEMAKEDATE(year,dayofyear)組裝日期/時間MAKEDATE(2024,60)='2024-02-29'
UNIX_TIMESTAMP/FROM_UNIXTIMEUNIX_TIMESTAMP([date])Unix 時間戳互轉FROM_UNIXTIME(1710484200)
CONVERT_TZCONVERT_TZ(dt,from_tz,to_tz)時區轉換CONVERT_TZ(NOW(),'+00:00','+08:00')

3. 聚合函數

函數語法說明範例
COUNTCOUNT(*) / COUNT(col)行數/非 NULL 計數COUNT(DISTINCT col) 去重計數
SUM/AVGSUM(col)總和/平均,忽略 NULLSUM(price)
MIN/MAXMIN(col)最小/大值MAX(created_at)
GROUP_CONCATGROUP_CONCAT([DISTINCT] expr [ORDER BY...] [SEPARATOR str])群組串聯GROUP_CONCAT(name ORDER BY id SEPARATOR ',')
STDDEV/VARIANCESTDDEV(expr)標準差/變異數統計分析
JSON_ARRAYAGG/JSON_OBJECTAGGJSON_ARRAYAGG(col)JSON 陣列/物件聚合 (8.0+)輸出 JSON 格式
BIT_AND/OR/XORBIT_AND(expr)位元聚合旗標運算

4. 流程控制函數

函數語法說明範例
CASECASE WHEN cond THEN val ... ELSE val END多條件分支CASE WHEN score>=90 THEN 'A' ELSE 'B' END
IFIF(cond, true_val, false_val)三元運算,條件以整數判斷IF(stock>0,'有貨','缺貨')
IFNULLIFNULL(expr1,expr2)expr1 非 NULL 回傳 expr1IFNULL(nickname,username)
COALESCECOALESCE(v1,v2,...)回傳第一個非 NULL(標準 SQL)COALESCE(mobile,phone,email)
NULLIFNULLIF(expr1,expr2)相等回 NULLNULLIF(divisor,0) 防除零
GREATEST/LEASTGREATEST(v1,v2,...)最大/最小值GREATEST(1,5,3)=5

5. JSON 函數

函數語法說明
JSON_EXTRACTJSON_EXTRACT(doc, path) 或 doc->>path擷取值(文字)
JSON_UNQUOTEJSON_UNQUOTE(val)移除引號
JSON_SET/INSERT/REPLACEJSON_SET(doc, path, val)修改/新增/替換
JSON_REMOVEJSON_REMOVE(doc, path)移除路徑
JSON_CONTAINSJSON_CONTAINS(doc, val, path)包含判斷
JSON_KEYSJSON_KEYS(doc[,path])取得鍵名陣列
JSON_LENGTHJSON_LENGTH(doc[,path])長度/元素數
JSON_TABLEJSON_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/512SHA2(str, 256)
PASSWORD()MySQL 密碼雜湊8.0 移除,勿用於應用
AES_ENCRYPT/DECRYPTAES 加解密需金鑰管理
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 時隨查隨用,效率倍增!

copyright © 萬盛學電腦網 all rights reserved