https://www.flickr.com/photos/8058098@N07/2718571627/
本文整理出一些 MySQL 的內建函數，能夠在查詢時，對結果中的欄位值做調整。比方說四捨五入、計算相差天數、轉大小寫等，都能讓資料看起來更有意義。
此篇內容轉載自本人在 iThome 的文章。
一、函數的用法
假設我們有以下的資料表。
| id | first_name | last_name |
|---|---|---|
| 1 | Vincent | Zheng |
在查詢的 SELECT 語法後方，可呼叫函數，並將欄位名稱作為參數傳入，進行運算。
以下的語法，是將「first_name」與「last_name」的欄位值，傳遞給叫做 CONCAT 的函數串接起來：
SELECT CONCAT(`first_name`, `last_name`) AS `full_name` FROM `employee`;
本文接下來的例子，參數值都是寫死的，實際使用時，替換為資料表的欄位名稱即可。
二、數值函數
（一）進位與捨去
CEIL 能讓數值往正方向無條件進位。
SELECT CEIL(2.1); -- 3 SELECT CEIL(-2.1); -- -2
FLOOR 能讓數值往負方向無條件進捨去。
SELECT FLOOR(2.8); -- 2 SELECT FLOOR(-2.8); -- -3
（二）四捨五入
ROUND 能讓數值四捨五入到指定的小數位。
SELECT ROUND(30.2368, 3); -- 30.237 SELECT ROUND(30.2361, 3); -- 30.236 SELECT ROUND(-4.52, 1); -- -4.5
（三）取餘數
MOD 能取餘數。
SELECT MOD(24, 6); -- 0 SELECT MOD(24, 7); -- 3
（四）產生亂數
RAND 能產生大於等於 0，而且小於 1 的亂數。
SELECT CEIL(RAND() * 10);
以下的使用方式能產生範圍為 1 ~ 10 的亂數。
SELECT CEIL(RAND() * 10);
三、字串函數
（一）連接
CONCAT 可以串接字串。例如將姓氏與名字組合成全名。
SELECT CONCAT("Vincent", " ", "van", " ", "Gogh");
（二）轉大小寫
UPPER 可將英文轉為大寫。
SELECT UPPER("mssql 資料庫"); -- MSSQL 資料庫
LOWER 可將英文轉為小寫。
SELECT LOWER("Mongo DB"); -- mongo db
（三）填充
LPAD 可針對未達指定長度的字串，將指定的字串填充在左方，直到滿足該長度。
SELECT LPAD("493", 5, "0"); -- 00493
SELECT LPAD(10, 2, 0); -- 10
SELECT LPAD("7468", 3, "0"); -- 746
至於 RPAD 則是將字串填充在右方。
這兩個函數的參數，可以傳入數值或字串型態，而回傳的結果固定為字串。
（四）去除首尾空白
TRIM 可以去除字串首尾的半形空白。
SELECT TRIM(" Hello World "); -- Hello World
（五）長度
LENGTH 可以取得字串的長度。
SELECT LENGTH("Hello World"); -- 11
（六）擷取
SUBSTRING 可以擷取字串的一部份。
SELECT SUBSTRING("Hello World", 1, 7); -- Hello W
四、日期函數
（一）現在日期時間
CURDATE 可取得現在日期（UTC+0）。
SELECT CURDATE(); -- 2024-01-22
CURTIME 可取得現在時間（UTC+0）。
SELECT CURTIME(); -- 03:07:18
NOW 可取得現在日期與時間（UTC+0）。
SELECT NOW(); -- 2024-01-22 03:07:18
（二）取出年月日和時分秒
YEAR、MONTH、DAY 分別可取得日期的年、月、日部份。
SELECT YEAR("2024-01-22"); -- 2024
SELECT MONTH("2024-01-22 03:07:18"); -- 1
SELECT DAY(NOW()); -- 22
HOUR、MINUTE、SECOND 分別可取得時間的時、分、秒部份。
SELECT HOUR("03:07:18"); -- 3
SELECT MINUTE("2024-01-22 03:07:18"); -- 7
SELECT SECOND(NOW()); -- 18
（三）增減
DATE_ADD 可對日期時間進行增減。
SELECT DATE_ADD("2024-01-22", INTERVAL 180 DAY); -- 2024-07-20
SELECT DATE_ADD(NOW(), INTERVAL -3 HOUR); -- 2024-01-21 00:07:18
其中 INTERVAL 是固定要寫上的關鍵字。而 DAY 與 HOUR 為單位。
（四）相差天數
DATEDIFF 可計算相差的天數。
SELECT DATEDIFF("2024-01-22", "2024-01-05"); -- 17
SELECT DATEDIFF("2024-01-05", "2024-01-22"); -- -17
SELECT DATEDIFF("2024-01-22 00:00:01", "2024-01-21 23:59:59"); -- 1
此函數的計算方式，是第一個參數減去第二個。且只看日期部份，忽略時間。
五、條件判斷函數
（一）選擇
IF 能夠傳入判斷式，當結果為 true 或 false，傳回各自指定的值，
SELECT IF(1 > 0, "大於", "小於"); -- 大於 SELECT IF(NOW() < "2024-01-01", "未到期", "已到期"); -- 已到期
（二）如果是 null
IFNULL 可在傳入的值為 null 時，回傳另一個指定的值。
SELECT IFNULL(null, "Unknown");
（三）多重選擇
最後要介紹的不算是函數，但也是一種條件判斷語法。
CASE ... WHEN ... THEN ... ELSE ... END 語法可實現多重選擇，有兩種寫法。
第一種是提供多個條件依序判斷，遇到第一個符合的條件，就回傳值。
SELECT
CASE
WHEN NOW() < "2024-07-01" THEN "短期"
WHEN NOW() < "2025-01-01" THEN "中期"
ELSE "長期"
END;
第二種是給定一個值，根據它的值為何，來決定回傳值。
SELECT
CASE YEAR("2026-01-15")
WHEN 2024 THEN "一年內"
WHEN 2025 THEN "兩年內"
ELSE "兩年以上"
END;
下一篇：【MySQL】使用分組查詢並搭配聚合函數來統計資料
留言
張貼留言