本文目錄：1. 字元串函數 1.1 字元串連接函數 1.2 lower()、upper()、left()、right() 1.3 填充函數lpad()和rpad() 1.4 trim()、ltrim()、rtrim()及trim() 1.5 字元串重覆函數repeat() 1.6 字元串替換函數re ...
本文目錄：
1. 字元串函數
1.1 字元串連接函數
1.2 lower()、upper()、left()、right()
1.3 填充函數lpad()和rpad()
1.4 trim()、ltrim()、rtrim()及trim()
1.5 字元串重覆函數repeat()
1.6 字元串替換函數replace()
1.7 字元串插入替換函數insert()
1.8 字元串提取substring()
1.9 字元串比較函數strcmp()
1.10 字元串長度函數length()和char_length()
1.11 字元串位置函數locate()、position()和instr()
1.12 字元串位置函數find_in_set()
1.13 字元串位置函數field()
1.14 指定位置的字元串函數elt()
1.15 字元串反轉函數reverse()
2. 數學函數
2.1 絕對值函數ABS()
2.2 取模函數mod()
2.3 四捨五入函數round()
2.4 位數截斷函數truncate()
2.5 地板函數floor()和天花板函數ceiling()
2.6 隨機函數rand()
2.7 最值函數least()
2.8 最值函數greastest()
3. 日期時間函數
3.1 當前日期時間
3.2 week()
3.3 year()、monthname()、quarter()
3.4 hour()、minute()、second()
3.5 extract()
3.6 dayname()和dayofweek()
3.7 日期時間格式化
3.8 日期時間計算
3.9 datediff()
3.10 LAST_DAY()
4. 流程式控制制之條件判斷函數
4.1 if()
4.2 ifnull()
4.3 nullif()
4.4 case語句
5. 類型轉換函數
6. 其它實用函數
MySQL/MariaDB的內置函數比較多，這裡挑選一部分進行解釋，完整的內置函數列表見官方手冊。
1. 字元串函數
完整的內置字元串函數見官方手冊。
1.1 字元串連接函數
有兩個字元串連接函數：concat(s1,s2,s3,...,sN)和concat_ws(sep,s1,s2,s3,...,sN)。
concat()將多個字元串連接起來形成一個長字元串。它會嘗試將字元全部轉換為字元型，如果存在null，則直接返回null。
mysql> select concat('a','b',1),concat(1,2,3),concat('a',null);
+-------------------+---------------+------------------+
| concat('a','b',1) | concat(1,2,3) | concat('a',null) |
+-------------------+---------------+------------------+
| ab1 | 123 | NULL |
+-------------------+---------------+------------------+
1 row in set
concat_ws(sep,s1,s2,...,sN)函數是concat()函數的特殊格式，它的第一個參數sep是用於連接s1,s2,...,sN的分隔符。分隔符可以是一個字元或一個字元串，只要合理即可。如果分隔符sep為null，則返回結果null，如果s1,s2,...,sN之間出現了null，則忽略null。
mysql> select concat_ws(':','23','59','58'),concat_ws('-','1st','2nd'),concat_ws('XXX','wo','shi');
+-------------------------------+----------------------------+-----------------------------+
| concat_ws(':','23','59','58') | concat_ws('-','1st','2nd') | concat_ws('XXX','wo','shi') |
+-------------------------------+----------------------------+-----------------------------+
| 23:59:58 | 1st-2nd | woXXXshi |
+-------------------------------+----------------------------+-----------------------------+
1 row in set
mysql> select concat_ws(':','23','59',null,'58'),concat_ws(null,'1st','2nd');
+------------------------------------+-----------------------------+
| concat_ws(':','23','59',null,'58') | concat_ws(null,'1st','2nd') |
+------------------------------------+-----------------------------+
| 23:59:58 | NULL |
+------------------------------------+-----------------------------+
1 row in set
由於concat()遇到null時總會返回null，這種處理方式可能並非所期望的結果，因此可以採用concat_ws()的方式忽略null或者採用ifnull()的方式將null轉換為空字元串。
1.2 lower(string)、upper(string)、left(string,x)、right(string,x)
分別是變小寫、變大寫、從左取x長度字元、從右取x長度字元。
mysql> select lower('MaLong'),upper('MaLong'),left('MaLong',3),right('Malong',3);
+-----------------+-----------------+------------------+-------------------+
| lower('MaLong') | upper('MaLong') | left('MaLong',3) | right('Malong',3) |
+-----------------+-----------------+------------------+-------------------+
| malong | MALONG | MaL | ong |
+-----------------+-----------------+------------------+-------------------+
1 row in set
1.3 填充函數
有兩種：lpad(string,n,pad)和rpad(string,n,pad)。
使用pad對string最左邊和最右邊進行填充，直到填充後總長度為n個字元。pad可以是一個字元串，如果是字元串則從左向右取直到符合長度為止。
mysql> select lpad('MaLong',10,'x'),lpad('MaLong',10,'xy'),rpad('MaLong',10,'x');
+-----------------------+------------------------+-----------------------+
| lpad('MaLong',10,'x') | lpad('MaLong',10,'xy') | rpad('MaLong',10,'x') |
+-----------------------+------------------------+-----------------------+
| xxxxMaLong | xyxyMaLong | MaLongxxxx |
+-----------------------+------------------------+-----------------------+
1 row in set
長度n可以是小於或等於string字元串長度的值，此時lpad或者rpad的作用都是從左進行字元串截取而非填充，直到長度為n。也就是說lpad和rpad函數最強約束條件是長度參數n。
mysql> select rpad('MaLong',3,'x'),lpad('MaLong',3,'x'),lpad('MaLong',0,'x');
+----------------------+----------------------+----------------------+
| rpad('MaLong',3,'x') | lpad('MaLong',3,'x') | lpad('MaLong',0,'x') |
+----------------------+----------------------+----------------------+
| MaL | MaL | |
+----------------------+----------------------+----------------------+
1 row in set
1.4 trim(string)、ltrim(string)、rtrim(sting)及trim(substring from string)
分別用來消除string行首和行尾、行首、行尾的空格以及行首行尾指定的字元串。
函數 作用
----------------------- -------------------------------
ltrim(string) 刪除行首空格
rtrim(string) 刪除行尾空格
trim(string) 刪除行首和行尾空格
trim(substring from string) 刪除行首和行尾的字元串substring
例如：
mysql> select length(trim(' MaLong ')) as A,
length(ltrim('MaLong ')) as B,
length(ltrim(' MaLong ')) as C,
length(rtrim(' MaLong ')) as D;
+---+---+---+---+
| A | B | C | D |
+---+---+---+---+
| 6 | 7 | 7 | 7 |
+---+---+---+---+
1 row in set (0.00 sec)
mysql> select trim('xy' from 'xyxabxycdxyxy');
+---------------------------------+
| trim('xy' from 'xyxabxycdxyxy') |
+---------------------------------+
| xabxycd |
+---------------------------------+
1 row in set
1.5 重覆字元串repeat(string,x)
將string重覆x次。
mysql> select repeat('xy',3),length(repeat(' ',3)),repeat('0',3);
+----------------+-----------------------+---------------+
| repeat('xy',3) | length(repeat(' ',3)) | repeat('0',3) |
+----------------+-----------------------+---------------+
| xyxyxy | 3 | 000 |
+----------------+-----------------------+---------------+
1 row in set
1.6 字元串替換函數replace(string,a,b)
使用字元串b替換字元串string中所有的字元串a。註意點是它們都可以是字元串。如果想要替換掉的字元串a不在string中，則不會進行替換。
mysql> select replace('woshiMaLongShuai','s','xxxx'),replace('woshiMaLongShuai','ob','xxxx');
+----------------------------------------+-----------------------------------------+
| replace('woshiMaLongShuai','s','xxxx') | replace('woshiMaLongShuai','ob','xxxx') |
+----------------------------------------+-----------------------------------------+
| woxxxxhiMaLongShuai | woshiMaLongShuai |
+----------------------------------------+-----------------------------------------+
1 row in set
1.7 字元串插入替換函數insert(string,p1,len,instead_string)
將string從位置p1開始,len個長度的字元替換為instead_string。
mysql> select insert('woshimalongshuai',6,2,'gao');
+--------------------------------------+
| insert('woshimalongshuai',6,2,'gao') |
+--------------------------------------+
| woshigaolongshuai |
+--------------------------------------+
1 row in set
1.8 字元串提取substring(string,x,y)
返回string中從x位置開始y個長度的字元串。如果給出的位置不存在，則無法提取所以返回空。如果給出的長度超出，則只提取允許範圍內的字元串。
mysql> select substring('MaLo',3,4) AS A,substring('MaLo',0,4) AS B,substring('MaLo',10,4) AS C,length(substring('MaLo',3,10)) AS D;
+----+---+---+---+
| A | B | C | D |
+----+---+---+---+
| Lo | | | 2 |
+----+---+---+---+
1 row in set (0.00 sec)
1.9 字元串比較函數strcmp(string1,string2)
比較string1和string2的ascii碼大小，從前向後依次比較。strcmp認為大小寫字母是等價的，所以它們相等。且存在null時，直接返回null。
- 如果string1小於string2，返回-1。
- 如果string1等於string2，返回0。
- 如果string1大於string2，返回1。
mysql> select strcmp('a','b'),strcmp('a','A'),strcmp('b','a');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','A') | strcmp('b','a') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set
mysql> select strcmp('ac','ab'),strcmp('ac','ac'),strcmp('a',null),strcmp(null,'a');
+-------------------+-------------------+------------------+------------------+
| strcmp('ac','ab') | strcmp('ac','ac') | strcmp('a',null) | strcmp(null,'a') |
+-------------------+-------------------+------------------+------------------+
| 1 | 0 | NULL | NULL |
+-------------------+-------------------+------------------+------------------+
1 row in set
關於字元串比較，另外兩個函數least()和greatest()也能實現，這兩個函數更多的用於取最值，特別是用於數值比較，所以在後文解釋。
1.10 字元串長度函數length(string)和char_length(string)
length()返回字元串的位元組數，註意不是字元數，char_length()返回的才是字元數。在SQL Server中長度函數是len(string)，且返回的是字元數。
mysql> select length('woshiyigeren'),length('我');
+------------------------+--------------+
| length('woshiyigeren') | length('我') |
+------------------------+--------------+
| 12 | 3 |
+------------------------+--------------+
1 row in set
mysql> select char_length('woshiyigeren'),char_length('我');
+-----------------------------+-------------------+
| char_length('woshiyigeren') | char_length('我') |
+-----------------------------+-------------------+
| 12 | 1 |
+-----------------------------+-------------------+
1 row in set
在SQL Server中：
1.11 字元串位置函數locate(sub_str,string)、position(sub_str in string)和instr(str,sub_str)
這三個函數的作用相同，都是返回sub_str在string中的開始位置。和SQL Server中的charindex()函數功能類似。
mysql> SELECT LOCATE('ball','football'),POSITION('ball' IN 'football') ,INSTR('football','ball');
+---------------------------+--------------------------------+--------------------------+
| LOCATE('ball','football') | POSITION('ball' IN 'football') | INSTR('football','ball') |
+---------------------------+--------------------------------+--------------------------+
| 5 | 5 | 5 |
+---------------------------+--------------------------------+--------------------------+
1 row in set
1.12 字元串位置函數find_in_set(sub_string,str_set)
返回子串sub_string在str_set中的位置，其中str_set是一個由逗號隔開的多個字元串集合。如果找不到位置(sub_str不在str_set中或者str_set為空串)則返回0，如果任意一個為null，則返回null。
mysql> select find_in_set('ab','cd,ab,dc'),find_in_set('ab',''),find_in_set(null,'ab,cd');
+------------------------------+----------------------+---------------------------+
| find_in_set('ab','cd,ab,dc') | find_in_set('ab','') | find_in_set(null,'ab,cd') |
+------------------------------+----------------------+---------------------------+
| 2 | 0 | NULL |
+------------------------------+----------------------+---------------------------+
1 row in set
1.13 字元串位置函數field(s,str1,str2,...,strN)
返回字元串s在字元串集合str1,str2,...,strN中的位置。如果找不到或者字元串s為null，則返回0，因為null無法進行比較，也就是找不到。
mysql> select field('ab','abc','1ab','ab','cd') as col1,field(null,'ab','cd') as col2;
+------+------+
| col1 | col2 |
+------+------+
| 3 | 0 |
+------+------+
1 row in set (0.00 sec)
1.14 指定位置的字元串函數elt(n,str1,str2,...,strN)
elt表示從(數據)倉庫中提取需要的東西。n是位置，n=1則返回str1，n=2則返回str2，依次類推。當n<1或者大於字元串的數量，則返回null。
mysql> select elt(1,'a','b','c'),elt(2,'a','b','c'),elt(0,'a','b'),elt(10,'a','b');
+--------------------+--------------------+----------------+-----------------+
| elt(1,'a','b','c') | elt(2,'a','b','c') | elt(0,'a','b') | elt(10,'a','b') |
+--------------------+--------------------+----------------+-----------------+
| a | b | NULL | NULL |
+--------------------+--------------------+----------------+-----------------+
1 row in set
1.15 字元串反轉函數reverse(str)
反轉字元串str的字元順序。
mysql> select reverse('hello');
+------------------+
| reverse('hello') |
+------------------+
| olleh |
+------------------+
1 row in set (0.00 sec)
2. 數學函數
完整的內置數學函數見官方手冊。
2.1 絕對值函數ABS(x)
mysql> select abs(0.9),abs(0),abs(-0.9);
+----------+--------+-----------+
| abs(0.9) | abs(0) | abs(-0.9) |
+----------+--------+-----------+
| 0.9 | 0 | 0.9 |
+----------+--------+-----------+
1 row in set
2.2 取模函數mod(x,y)
取x/y後的餘數。支持小數和負數。如果除數為0或者除數被除數有一個為null，則返回null。
mysql> select mod(31,8),mod(31.56,8),mod(-31.56,8),mod(31,0),mod(0,8);
+-----------+--------------+---------------+-----------+----------+
| mod(31,8) | mod(31.56,8) | mod(-31.56,8) | mod(31,0) | mod(0,8) |
+-----------+--------------+---------------+-----------+----------+
| 7 | 7.56 | -7.56 | NULL | 0 |
+-----------+--------------+---------------+-----------+----------+
1 row in set
2.3 四捨五入函數round(x,y)
返回值x含有y位小數的四捨五入後的結果，如果省略y，則預設y為0。
mysql> select round(3.15),round(3.15,1),round(-3.15),round(-3.15,1);
+-------------+---------------+--------------+----------------+
| round(3.15) | round(3.15,1) | round(-3.15) | round(-3.15,1) |
+-------------+---------------+--------------+----------------+
| 3 | 3.2 | -3 | -3.2 |
+-------------+---------------+--------------+----------------+
1 row in set
2.4 位數截斷函數truncate(x,y)
截斷x的小數位數使得最終保留y個小數位。它的用法和round(x,y)幾乎一樣，只不過truncate是用來截斷而不用來四捨五入。不能省略y但可以等於0，且y不能為負數。
mysql> select truncate(3.156,2),truncate(3.156,0);
+-------------------+-------------------+
| truncate(3.156,2) | truncate(3.156,0) |
+-------------------+-------------------+
| 3.15 | 3 |
+-------------------+-------------------+
1 row in set
2.5 地板函數floor(x)和天花板函數ceiling(x)
地板函數返回比x小的最大整數，天花板函數返回比x大的最小整數。
mysql> select floor(3.4),floor(-3.4),ceiling(3.4),ceiling(-3.4);
+------------+-------------+--------------+---------------+
| floor(3.4) | floor(-3.4) | ceiling(3.4) | ceiling(-3.4) |
+------------+-------------+--------------+---------------+
| 3 | -4 | 4 | -3 |
+------------+-------------+--------------+---------------+
1 row in set
2.6 隨機函數rand()
每次隨機返回一個0-1之間不包括0和1的數，且每次運行結果都不同。
mysql> select rand(),rand();
+--------------------+----------------------+
| rand() | rand() |
+--------------------+----------------------+
| 0.7380041170287915 | 0.055543343588284534 |
+--------------------+----------------------+
1 row in set
若要取得0-100之間的數，可以使用100去乘隨機值，但這樣獲得的函數還是不包含0和100這兩個邊界的。
mysql> select 100*rand(),100*rand(),100*rand();
+------------------+-------------------+--------------------+
| 100*rand() | 100*rand() | 100*rand() |
+------------------+-------------------+--------------------+
| 22.5249471352668 | 96.80735235736458 | 16.461923454387044 |
+------------------+-------------------+--------------------+
1 row in set
若要取整，則可以配合floor()或者ceiling()函數。但這樣取得的是[0,99]或者[1,100]，而不能是[0,100]。
mysql> select floor(100*rand()) as '[0,99]',ceiling(100*rand()) as '[1,100]';
+--------+---------+
| [0,99] | [1,100] |
+--------+---------+
| 90 | 24 |
+--------+---------+
1 row in set
如果要想獲得[0-100]這樣包含邊界的值，可以拓寬隨機值。以下是兩種方法：
mysql> select ceiling(rand()*101-1),floor(rand()*101);
+-----------------------+-------------------+
| ceiling(rand()*101-1) | floor(rand()*101) |
+-----------------------+-------------------+
| 92 | 55 |
+-----------------------+-------------------+
1 row in set
2.7 最值函數least(v1,v2,v3,…,vn)
從v1,v2,v3,…,vn中取出最小值。有以下幾種情況：
(1)當只有數值類型時，取數值最小的。且負數有效。
(2)當只有字元串時，從第一個字元開始向後比較ascii碼，小寫字母小於大寫字母。
(3)數值和字元串比較，返回結果為0。若要比較，需要先將數字轉換為字元串格式，且字元串類型的數字總是小於字母。
(4)當n個成員之間存在null的時候，總是返回null，因為無法比較。
mysql> select least(5,10,-1),least('ab','c','ac'),least('a',1),least('a','999'),least('a',1,null);
+----------------+----------------------+--------------+------------------+-------------------+
| least(5,10,-1) | least('ab','c','ac') | least('a',1) | least('a','999') | least('a',1,null) |
+----------------+----------------------+--------------+------------------+-------------------+
| -1 | ab | 0 | 999 | NULL |
+----------------+----------------------+--------------+------------------+-------------------+
1 row in set
2.8 最值函數greastest(v1,v2,v3,…,vn)
和least()函數相反，它取的是最大值。包括以下幾種情況：
(1)當只有數值類型時，取最大值。負值有效。
(2)當只有字元串時，比較ascii碼，大寫字母大於小寫字母。
(3)當數字和字元串比較時，數字大於字元串，即返回數字中最大值。但是字元串類型的數字小於字母。這個least()不一樣。
(4)當存在null值時，返回null。
mysql> select greatest(5,10,-1) as A,
greatest('ab','c','ac') as B,
greatest('a',1) as C,
greatest('a','999') as D,
greatest('a',1,null) as E;
+----+---+---+---+------+
| A | B | C | D | E |
+----+---+---+---+------+
| 10 | c | 1 | a | NULL |
+----+---+---+---+------+
1 row in set, 2 warnings (0.00 sec)
3 日期時間函數
有很多很多，官方手冊：日期時間函數。以下挑幾個介紹。
3.1 當前日期時間
返回當前日期：curdate()、current_date()，它們是同義詞；
返回當前時間：curtime()、current_time()，它們是同義詞；
返回當前日期時間：now()、current_timestamp()、localtime()、localtimestamp、localtimestamp()、sysdate()，除了sysdate()，其餘的都是now()的同義詞。
mysql> select curdate(),current_date(),current_timestamp(),curtime(),localtime(),now(),sysdate();
註意，now()和sysdate()是不同的。now()返回的是執行SQL語句那一刻的時間(如果now()是在存儲過程或函數或觸發器中，則now()返回的是這些程式開始調用執行的時刻)，而sysdate()返回的是實時更新的當前時間，即操作系統當前的時間。通過下麵的例子就知道了：
mysql> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME(),
SLEEP(2),
NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME()\G
*************************** 1. row ***************************
now(): 2017-03-24 13:30:09
current_timestamp(): 2017-03-24 13:30:09
sysdate(): 2017-03-24 13:30:09
localtime(): 2017-03-24 13:30:09
sleep(2): 0
now(): 2017-03-24 13:30:09
current_timestamp(): 2017-03-24 13:30:09
sysdate(): 2017-03-24 13:30:11 # 註意此處sleep 2秒後的時間
localtime(): 2017-03-24 13:30:09
1 row in set (2.00 sec)
可以看到，sleep(2)後，sysdate()返回的比其他的函數晚了兩秒，而其他的函數返回的和sleep(2)之前的時間是一樣的，且都是開始執行語句的時間。
3.2 week(DATE)
返回給定日期在當年是第幾周。
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 12 |
+-------------+
1 row in set
3.3 year(DATE)、monthname(DATE)、day(DATE)、quarter(DATE)
返回所給日期的年份、月份、月中天(所以day()的同義詞是dayofmonth()函數)以及季度，不過返回的月份是英文全名。
mysql> select year(now()),monthname(now());
+-------------+------------------+
| year(now()) | monthname(now()) |
+-------------+------------------+
| 2017 | March |
+-------------+------------------+
1 row in set
3.4 hour(TIME)、minute(TIME)、second(TIME)
返回給定時間值的小時、分鐘、秒部分。
mysql> select now(),hour(now()),minute(now()),second(now());
+---------------------+-------------+---------------+---------------+
| now() | hour(now()) | minute(now()) | second(now()) |
+---------------------+-------------+---------------+---------------+
| 2017-03-23 14:21:57 | 14 |
