- 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()
- 2.9 求二次方根 SQRT(x)
- 2.10 返回参数的符号 SIGN()
- 2.11 求次方 POW() 和 POWER()
- 2.12 正弦函数 SIN(x)
- 2.13 求反正弦值 ASIN()
- 2.14求余弦值 COS()
- 2.15 求反余弦值 ACOS()
- 2.16 求正切值 TAN()
- 2.17 求反正切值 ATAN()
- 2.18 求余切值 COT()
- 2.19 对数 LOG()、LOG2()、LOG10()
- 2.20 将E提升为指定数字的幂 EXP()
- 2.21 将十进制数转换为八进制 OCT()
- 2.22 完成整数除法时返回商(整数)值 DIV()
- 2.23 进制转换 CONV()
- 2.24 将弧度值转换为度 DEGREES()
- 2.25 返回Pi值 PI()
- 3. 聚合函数
- 4. 日期时间函数
- 5. 流程控制之条件判断函数
- 6. 类型转换函数
- 7. 加减密函数
- 8. 其它实用函数
1. 字符串函数
完整的内置字符串函数见《官方手册》。
1.1 字符串连接函数
有两个字符串连接函数：concat(s1,s2,s3,...,sN)和concat_ws(sep,s1,s2,s3,...,sN)。
concat()将多个字符串连接起来形成一个长字符串。它会尝试将字符全部转换为字符型，如果存在null，则直接返回null。
MariaDB [(none)]> 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 (0.003 sec)concat_ws(sep,s1,s2,...,sN)函数是concat()函数的特殊格式，它的第一个参数sep是用于连接s1,s2,...,sN的分隔符。分隔符可以是一个字符或一个字符串，只要合理即可。如果分隔符sep为null，则返回结果null，如果s1,s2,...,sN之间出现了null，则忽略null。
MariaDB [(none)]> select concat_ws(':','23','59','58'),concat_ws('-','1st','2nd');
+-------------------------------+----------------------------+
| concat_ws(':','23','59','58') | concat_ws('-','1st','2nd') |
+-------------------------------+----------------------------+
| 23:59:58 | 1st-2nd |
+-------------------------------+----------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> 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 (0.000 sec)由于concat()遇到null时总会返回null，这种处理方式可能并非所期望的结果，因此可以采用concat_ws()的方式忽略null或者采用ifnull()的方式将null转换为空字符串。
1.2 lower()、upper()、left()、right()
分别是变小写、变大写、从左取x长度字符、从右取x长度字符。
MariaDB [(none)]> select lower('NiHao'),upper('NiHao'),left('NiHao',3),right('NiHao',3);
+----------------+----------------+-----------------+------------------+
| lower('NiHao') | upper('NiHao') | left('NiHao',3) | right('NiHao',3) |
+----------------+----------------+-----------------+------------------+
| nihao | NIHAO | NiH | Hao |
+----------------+----------------+-----------------+------------------+
1 row in set (0.000 sec)1.3 填充函数lpad()和rpad()
有两种：lpad(string,n,pad)和rpad(string,n,pad)。
使用pad对string最左边和最右边进行填充，直到填充后总长度为n个字符。pad可以是一个字符串，如果是字符串则从左向右取直到符合长度为止。
MariaDB [(none)]> select lpad('NiHao',10,'x'),lpad('NiHao',10,'xy'),rpad('NiHao',10,'x');
+----------------------+-----------------------+----------------------+
| lpad('NiHao',10,'x') | lpad('NiHao',10,'xy') | rpad('NiHao',10,'x') |
+----------------------+-----------------------+----------------------+
| xxxxxNiHao | xyxyxNiHao | NiHaoxxxxx |
+----------------------+-----------------------+----------------------+
1 row in set (0.002 sec)长度n可以是小于或等于string字符串长度的值，此时lpad或者rpad的作用都是从左进行字符串截取而非填充，直到长度为n。也就是说lpad和rpad函数最强约束条件是长度参数n。
MariaDB [(none)]> select rpad('NiHao',3,'x'),lpad('NiHao',3,'x'),lpad('NiHao',0,'x');
+---------------------+---------------------+---------------------+
| rpad('NiHao',3,'x') | lpad('NiHao',3,'x') | lpad('NiHao',0,'x') |
+---------------------+---------------------+---------------------+
| NiH | NiH | |
+---------------------+---------------------+---------------------+
1 row in set (0.000 sec)1.4 trim()、ltrim()、rtrim()及trim()
分别用来消除string行首和行尾、行首、行尾的空格以及行首行尾指定的字符串。
函数 作用
----------------------- -------------------------------
ltrim(string) 删除行首空格
rtrim(string) 删除行尾空格
trim(string) 删除行首和行尾空格
trim(substring from string) 删除行首和行尾的字符串substring 例如：
MariaDB [(none)]> select length(trim(' NiHao ')) as A, length(ltrim('NiHao ')) as B, length(ltrim(' NiHao ')) as C, length(rtrim(' NiHao ')) as D;
+------+------+------+------+
| A | B | C | D |
+------+------+------+------+
| 5 | 6 | 6 | 6 |
+------+------+------+------+
1 row in set (0.002 sec)1.5 字符串重复函数repeat()
将string重复x次。
MariaDB [(none)]> 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 (0.001 sec)1.6 字符串替换函数replace()
使用字符串b替换字符串string中所有的字符串a。注意点是它们都可以是字符串。如果想要替换掉的字符串a不在string中，则不会进行替换。
MariaDB [(none)]> select replace('woshixiaotianshi','i','$'),replace('woshixiaotianshi','xi','##');
+-------------------------------------+---------------------------------------+
| replace('woshixiaotianshi','i','$') | replace('woshixiaotianshi','xi','##') |
+-------------------------------------+---------------------------------------+
| wosh$x$aot$ansh$ | woshi##aotianshi |
+-------------------------------------+---------------------------------------+
1 row in set (0.000 sec)1.7 字符串插入替换函数insert()
将string从位置p1开始,len个长度的字符替换为instead_string。
MariaDB [(none)]> select insert('woshixiaotianshi',6,2,'###');
+--------------------------------------+
| insert('woshixiaotianshi',6,2,'###') |
+--------------------------------------+
| woshi###aotianshi |
+--------------------------------------+
1 row in set (0.000 sec)1.8 字符串提取substring()
返回string中从x位置开始y个长度的字符串。如果给出的位置不存在，则无法提取所以返回空。如果给出的长度超出，则只提取允许范围内的字符串。
MariaDB [(none)]> select substring('NiHao',3,4) AS A,substring('NiHao',0,4) AS B,substring('NiHao',10,4) AS C,length(substring('NiHao',3,10)) AS D;
+------+------+------+------+
| A | B | C | D |
+------+------+------+------+
| Hao | | | 3 |
+------+------+------+------+
1 row in set (0.000 sec)1.9 字符串比较函数strcmp()
比较string1和string2的ascii码大小，从前向后依次比较。strcmp认为大小写字母是等价的，所以它们相等。且存在null时，直接返回null。
如果string1小于string2，返回-1。
如果string1等于string2，返回0。
如果string1大于string2，返回1。
MariaDB [(none)]> 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 (0.000 sec)关于字符串比较，另外两个函数least()和greatest()也能实现，这两个函数更多的用于取最值，特别是用于数值比较，所以在后文解释。
1.10 字符串长度函数length()和char_length()
length()返回字符串的字节数，注意不是字符数，char_length()返回的才是字符数。在SQL Server中长度函数是len(string)，且返回的是字符数。
MariaDB [(none)]> select length('woshixiaotianshi'),length('你好');
+----------------------------+------------------+
| length('woshixiaotianshi') | length('你好') |
+----------------------------+------------------+
| 16 | 6 |
+----------------------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> select char_length('woshixiaotianshi'),char_length('你好');
+---------------------------------+-----------------------+
| char_length('woshixiaotianshi') | char_length('你好') |
+---------------------------------+-----------------------+
| 16 | 2 |
+---------------------------------+-----------------------+
1 row in set (0.001 sec)1.11 字符串位置函数locate()、position()和instr()
这三个函数的作用相同，都是返回sub_str在string中的开始位置。和SQL Server中的charindex()函数功能类似。
MariaDB [(none)]> 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 (0.001 sec)1.12 字符串位置函数find_in_set()
返回子串sub_string在str_set中的位置，其中str_set是一个由逗号隔开的多个字符串集合。如果找不到位置(sub_str不在str_set中或者str_set为空串)则返回0，如果任意一个为null，则返回null。
MariaDB [(none)]> 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 (0.001 sec)1.13 字符串位置函数field()
返回字符串s在字符串集合str1,str2,...,strN中的位置。如果找不到或者字符串s为null，则返回0，因为null无法进行比较，也就是找不到。
MariaDB [(none)]> select field('ab','abc','1ab','ab','cd') as col1,field(null,'ab','cd') as col2;
+------+------+
| col1 | col2 |
+------+------+
| 3 | 0 |
+------+------+
1 row in set (0.001 sec)1.14 指定位置的字符串函数elt()
elt表示从(数据)仓库中提取需要的东西。n是位置，n=1则返回str1，n=2则返回str2，依次类推。当n<1或者大于字符串的数量，则返回null。
MariaDB [(none)]> 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 (0.001 sec)1.15 字符串反转函数reverse()
反转字符串str的字符顺序。
MariaDB [(none)]> select reverse('hello world');
+------------------------+
| reverse('hello world') |
+------------------------+
| dlrow olleh |
+------------------------+
1 row in set (0.000 sec)2. 数学函数
完整的内置数学函数见《官方手册》。
2.1 绝对值函数ABS()
MariaDB [(none)]> 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 (0.001 sec)2.2 取模函数MOD()
取x/y后的余数。支持小数和负数。如果除数为0或者除数被除数有一个为null，则返回null。
MariaDB [(none)]> select mod(37,8),mod(41.66,9),mod(-61.26,7),mod(21,0),mod(0,9);
+-----------+--------------+---------------+-----------+----------+
| mod(37,8) | mod(41.66,9) | mod(-61.26,7) | mod(21,0) | mod(0,9) |
+-----------+--------------+---------------+-----------+----------+
| 5 | 5.66 | -5.26 | NULL | 0 |
+-----------+--------------+---------------+-----------+----------+
1 row in set, 1 warning (0.000 sec)2.3 四舍五入函数ROUND()
返回值x含有y位小数的四舍五入后的结果，如果省略y，则默认y为0。
MariaDB [(none)]> 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 (0.001 sec)2.4 位数截断函数TRUNCATE()
截断x的小数位数使得最终保留y个小数位。它的用法和round(x,y)几乎一样，只不过truncate是用来截断而不用来四舍五入。不能省略y但可以等于0，且y不能为负数。
MariaDB [(none)]> select truncate(3.1415926,3),truncate(3.1415926,0);
+-----------------------+-----------------------+
| truncate(3.1415926,3) | truncate(3.1415926,0) |
+-----------------------+-----------------------+
| 3.141 | 3 |
+-----------------------+-----------------------+
1 row in set (0.000 sec)2.5 地板函数FLOOR()和天花板函数CEILING()
地板函数返回比x小的最大整数，天花板函数返回比x大的最小整数。
MariaDB [(none)]> select floor(3.14),floor(-3.14),ceiling(3.14),ceiling(-3.14);
+-------------+--------------+---------------+----------------+
| floor(3.14) | floor(-3.14) | ceiling(3.14) | ceiling(-3.14) |
+-------------+--------------+---------------+----------------+
| 3 | -4 | 4 | -3 |
+-------------+--------------+---------------+----------------+
1 row in set (0.000 sec)2.6 随机函数RAND()
每次随机返回一个0-1之间不包括0和1的数，且每次运行结果都不同。
MariaDB [(none)]> select rand(),rand(),rand();
+--------------------+--------------------+--------------------+
| rand() | rand() | rand() |
+--------------------+--------------------+--------------------+
| 0.4035214571315064 | 0.7428056567374669 | 0.5034639430264607 |
+--------------------+--------------------+--------------------+
1 row in set (0.000 sec)若要取得0-100之间的数，可以使用100去乘随机值，但这样获得的函数还是不包含0和100这两个边界的。
MariaDB [(none)]> select 100*rand(),100*rand(),100*rand();
+--------------------+---------------+-------------------+
| 100*rand() | 100*rand() | 100*rand() |
+--------------------+---------------+-------------------+
| 28.890277565354737 | 93.4122079922 | 80.39021033830029 |
+--------------------+---------------+-------------------+
1 row in set (0.002 sec)若要取整，则可以配合floor()或者ceiling()函数。但这样取得的是[0,99]或者[1,100]，而不能是[0,100]。
MariaDB [(none)]> select floor(100*rand()) as '[0,99]',ceiling(100*rand()) as '[1,100]';
+--------+---------+
| [0,99] | [1,100] |
+--------+---------+
| 55 | 93 |
+--------+---------+
1 row in set (0.000 sec)如果要想获得[0-100]这样包含边界的值，可以拓宽随机值。以下是两种方法：
MariaDB [(none)]> select ceiling(rand()*101-1),floor(rand()*101);
+-----------------------+-------------------+
| ceiling(rand()*101-1) | floor(rand()*101) |
+-----------------------+-------------------+
| 94 | 93 |
+-----------------------+-------------------+
1 row in set (0.000 sec)2.7 最值函数LEAST()
从v1,v2,v3,…,vn中取出最小值。有以下几种情况：
(1)当只有数值类型时，取数值最小的。且负数有效。
(2)当只有字符串时，从第一个字符开始向后比较ascii码，小写字母小于大写字母。
(3)数值和字符串比较，返回结果为0。若要比较，需要先将数字转换为字符串格式，且字符串类型的数字总是小于字母。
(4)当n个成员之间存在null的时候，总是返回null，因为无法比较。
MariaDB [(none)]> 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 warnings (0.001 sec)2.8 最值函数 GREASTEST()
和least()函数相反，它取的是最大值。包括以下几种情况：
(1)当只有数值类型时，取最大值。负值有效。
(2)当只有字符串时，比较ascii码，大写字母大于小写字母。
(3)当数字和字符串比较时，数字大于字符串，即返回数字中最大值。但是字符串类型的数字小于字母。这个least()不一样。
(4)当存在null值时，返回null。
MariaDB [(none)]> 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.000 sec)2.9 求二次方根 SQRT(x)
平方根函数 SQRT(x) 返回非负数 x 的二次方根。负数没有平方根，返回结果为 NULL。
MariaDB [test]> SELECT SQRT(25),SQRT(120),SQRT(-9);
+----------+--------------------+----------+
| SQRT(25) | SQRT(120) | SQRT(-9) |
+----------+--------------------+----------+
| 5 | 10.954451150103322 | NULL |
+----------+--------------------+----------+
1 row in set (0.001 sec)2.10 返回参数的符号 SIGN()
符号函数 SIGN(x) 返回参数的符号，x 的值为负、零和正时返回结果依次为 -1、0 和 1。
MariaDB [test]> SELECT SIGN(-6),SIGN(0),SIGN(34);
+----------+---------+----------+
| SIGN(-6) | SIGN(0) | SIGN(34) |
+----------+---------+----------+
| -1 | 0 | 1 |
+----------+---------+----------+
1 row in set (0.000 sec)2.11 求次方 POW() 和 POWER()
POW(x,y) 函数和 POWER(x,y) 函数用于计算 x 的 y 次方。
MariaDB [test]> SELECT POW(5,-2),POW(10,3),POW(100,0),POWER(4,3),POWER(6,-3);
+-----------+-----------+------------+------------+----------------------+
| POW(5,-2) | POW(10,3) | POW(100,0) | POWER(4,3) | POWER(6,-3) |
+-----------+-----------+------------+------------+----------------------+
| 0.04 | 1000 | 1 | 64 | 0.004629629629629629 |
+-----------+-----------+------------+------------+----------------------+
1 row in set (0.000 sec)2.12 正弦函数 SIN(x)
正弦函数 SIN(x) 返回 x 的正弦值，其中 x 为弧度值。
MariaDB [test]> SELECT SIN(1),SIN(0.5*PI());
+--------------------+---------------+
| SIN(1) | SIN(0.5*PI()) |
+--------------------+---------------+
| 0.8414709848078965 | 1 |
+--------------------+---------------+
1 row in set (0.001 sec)2.13 求反正弦值 ASIN()
反正弦函数 ASIN(x) 返回 x 的反正弦值，若 x 不在 -1 到 1 的范围之内，则返回 NULL。
MariaDB [test]> SELECT ASIN(0.8414709848078965),ASIN(2);
+--------------------------+---------+
| ASIN(0.8414709848078965) | ASIN(2) |
+--------------------------+---------+
| 1 | NULL |
+--------------------------+---------+
1 row in set (0.000 sec)2.14求余弦值 COS()
余弦函数 COS(x) 返回 x 的余弦值，x 为弧度值。
MariaDB [test]> SELECT COS(1),COS(0),COS(PI());
+--------------------+--------+-----------+
| COS(1) | COS(0) | COS(PI()) |
+--------------------+--------+-----------+
| 0.5403023058681398 | 1 | -1 |
+--------------------+--------+-----------+
1 row in set (0.000 sec)2.15 求反余弦值 ACOS()
MariaDB [test]> SELECT ACOS(2),ACOS(1),ACOS(-1);
+---------+---------+-------------------+
| ACOS(2) | ACOS(1) | ACOS(-1) |
+---------+---------+-------------------+
| NULL | 0 | 3.141592653589793 |
+---------+---------+-------------------+
1 row in set (0.000 sec)2.16 求正切值 TAN()
正切函数 TAN(x) 返回 x 的正切值，x 为给定的弧度值。
MariaDB [test]> SELECT TAN(1),TAN(0),TAN(PI());
+--------------------+--------+-------------------------+
| TAN(1) | TAN(0) | TAN(PI()) |
+--------------------+--------+-------------------------+
| 1.5574077246549023 | 0 | -1.2246467991473532e-16 |
+--------------------+--------+-------------------------+
1 row in set (0.000 sec)2.17 求反正切值 ATAN()
反正切 ATAN(x) 返回 x 的反正切值，正切为 x 的值。
MariaDB [test]> SELECT ATAN(1.5574077246549023),ATAN(0),ATAN(PI());
+--------------------------+---------+--------------------+
| ATAN(1.5574077246549023) | ATAN(0) | ATAN(PI()) |
+--------------------------+---------+--------------------+
| 1 | 0 | 1.2626272556789118 |
+--------------------------+---------+--------------------+
1 row in set (0.001 sec)2.18 求余切值 COT()
余切函数 COT(x) 返回 x 的余切值，x 是给定的弧度值。
MariaDB [test]> SELECT COT(1), COT(PI());
+--------------------+-----------------------+
| COT(1) | COT(PI()) |
+--------------------+-----------------------+
| 0.6420926159343306 | -8.165619676597685e15 |
+--------------------+-----------------------+
1 row in set (0.000 sec)2.19 对数 LOG()、LOG2()、LOG10()
MariaDB [test]> select log(2),log2(2),log10(2);
+--------------------+---------+--------------------+
| log(2) | log2(2) | log10(2) |
+--------------------+---------+--------------------+
| 0.6931471805599453 | 1 | 0.3010299956639812 |
+--------------------+---------+--------------------+
1 row in set (0.001 sec)2.20 将E提升为指定数字的幂 EXP()
EXP()函数用于将E提升为指定数字的幂。这里E(2.718281 ...)是自然对数的底数。
MariaDB [test]> SELECT EXP(1), EXP(-1), EXP(PI());
+-------------------+---------------------+--------------------+
| EXP(1) | EXP(-1) | EXP(PI()) |
+-------------------+---------------------+--------------------+
| 2.718281828459045 | 0.36787944117144233 | 23.140692632779267 |
+-------------------+---------------------+--------------------+
1 row in set (0.000 sec)2.21 将十进制数转换为八进制 OCT()
OCT()函数用于将十进制数转换为八进制。它返回十进制数的等效八进制值。
MariaDB [test]> SELECT OCT(2),OCT(5),OCT(18),OCT(186),OCT(1.5),OCT(3.6),OCT('A'),OCT('3A'),OCT(NULL);
+--------+--------+---------+----------+----------+----------+----------+-----------+-----------+
| OCT(2) | OCT(5) | OCT(18) | OCT(186) | OCT(1.5) | OCT(3.6) | OCT('A') | OCT('3A') | OCT(NULL) |
+--------+--------+---------+----------+----------+----------+----------+-----------+-----------+
| 2 | 5 | 22 | 272 | 1 | 3 | 0 | 3 | NULL |
+--------+--------+---------+----------+----------+----------+----------+-----------+-----------+
1 row in set (0.000 sec)2.22 完成整数除法时返回商(整数)值 DIV()
此函数用于返回商(整数)值。例如，当7除以3时，将返回2。
MariaDB [test]> SELECT 7 DIV 3, 4 DIV 4, 5 DIV 10, 6.2 DIV 2.6;
+---------+---------+----------+-------------+
| 7 DIV 3 | 4 DIV 4 | 5 DIV 10 | 6.2 DIV 2.6 |
+---------+---------+----------+-------------+
| 2 | 1 | 0 | 2 |
+---------+---------+----------+-------------+
1 row in set (0.000 sec)2.23 进制转换 CONV()
CONV() 函数将数字从一个进制转为另一个进制，进制一般是2-36之间，超出范围返回NULL。
MariaDB [test]> SELECT CONV(16, 10, 2),CONV(999999, 10, 16), CONV(999999, 16, 36), CONV(35, 10, 37);
+-----------------+----------------------+----------------------+------------------+
| CONV(16, 10, 2) | CONV(999999, 10, 16) | CONV(999999, 16, 36) | CONV(35, 10, 37) |
+-----------------+----------------------+----------------------+------------------+
| 10000 | F423F | 5ZR89 | NULL |
+-----------------+----------------------+----------------------+------------------+
1 row in set (0.000 sec)2.24 将弧度值转换为度 DEGREES()
DEGREES()函数用于将弧度值转换为度数。将弧度转换为度的公式为：
π radian = 180 degrees
MariaDB [test]> SELECT DEGREES(PI()*2), DEGREES(-1.5707963267948966);
+-----------------+------------------------------+
| DEGREES(PI()*2) | DEGREES(-1.5707963267948966) |
+-----------------+------------------------------+
| 360 | -90 |
+-----------------+------------------------------+
1 row in set (0.000 sec)2.25 返回Pi值 PI()
用于返回Pi值。默认显示的小数位数为7，但MySQL内部使用完整的双精度值。
MariaDB [test]> select PI(), PI()+0.000000000000000;
+----------+------------------------+
| PI() | PI()+0.000000000000000 |
+----------+------------------------+
| 3.141593 | 3.141592653589793 |
+----------+------------------------+
1 row in set (0.000 sec)3. 聚合函数
3.1 COUNT()、SUM()、AVG()、MAX()、MIN()函数
COUNT()函数，对于除“*”以外的任何参数，返回所选择集合中非NULL值的行的数目；对于参数“*”，则返回所选择集合中所有行的数目，包含NULL值的行。没有WHERE子句的COUNT(*)是经过内部优化的，能够快速地返回表中所有的记录总数。
MariaDB [test]> select * from test1;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 张三 | 17 |
| 2 | 李四 | 22 |
| 3 | 王五 | 9 |
| 4 | 赵六 | 46 |
| 5 | tun'er | 250 |
| 6 | woniu | 37 |
| 7 | wugui | 1 |
+----+--------+-----+
7 rows in set (0.000 sec)
MariaDB [test]> (select count(*) from test1) union (select sum(age) from test1)
-> union (select avg(age) from test1) union (select max(age) from test1)
-> union (select min(age) from test1);
+----------+
| count(*) |
+----------+
| 7.0000 |
| 382.0000 |
| 54.5714 |
| 250.0000 |
| 1.0000 |
+----------+
5 rows in set (0.000 sec)4. 日期时间函数
有很多很多，官方手册：日期时间函数。以下挑几个介绍。
4.1 当前日期时间
返回当前日期：curdate()、current_date()，它们是同义词；
返回当前时间：curtime()、current_time()，它们是同义词；
返回当前日期时间：now()、current_timestamp()、localtime()、localtimestamp、localtimestamp()、sysdate()，除了sysdate()，其余的都是now()的同义词。
MariaDB [(none)]> select curdate(),current_date(),current_timestamp(),curtime(),localtime(),now(),sysdate();
+------------+----------------+---------------------+-----------+---------------------+---------------------+---------------------+
| curdate() | current_date() | current_timestamp() | curtime() | localtime() | now() | sysdate() |
+------------+----------------+---------------------+-----------+---------------------+---------------------+---------------------+
| 2023-02-04 | 2023-02-04 | 2023-02-04 22:20:28 | 22:20:28 | 2023-02-04 22:20:28 | 2023-02-04 22:20:28 | 2023-02-04 22:20:28 |
+------------+----------------+---------------------+-----------+---------------------+---------------------+---------------------+
1 row in set (0.001 sec)注意，now()和sysdate()是不同的。now()返回的是执行SQL语句那一刻的时间(如果now()是在存储过程或函数或触发器中，则now()返回的是这些程序开始调用执行的时刻)，而sysdate()返回的是实时更新的当前时间，即操作系统当前的时间。通过下面的例子就知道了：
MariaDB [(none)]> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME(), SLEEP(2), NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME();
+---------------------+---------------------+---------------------+---------------------+----------+---------------------+---------------------+---------------------+---------------------+
| NOW() | CURRENT_TIMESTAMP() | SYSDATE() | LOCALTIME() | SLEEP(2) | NOW() | CURRENT_TIMESTAMP() | SYSDATE() | LOCALTIME() |
+---------------------+---------------------+---------------------+---------------------+----------+---------------------+---------------------+---------------------+---------------------+
| 2023-02-04 22:21:24 | 2023-02-04 22:21:24 | 2023-02-04 22:21:24 | 2023-02-04 22:21:24 | 0 | 2023-02-04 22:21:24 | 2023-02-04 22:21:24 | 2023-02-04 22:21:26 | 2023-02-04 22:21:24 |
+---------------------+---------------------+---------------------+---------------------+----------+---------------------+---------------------+---------------------+---------------------+
1 row in set (2.000 sec)可以看到，sleep(2)后，sysdate()返回的比其他的函数晚了两秒，而其他的函数返回的和sleep(2)之前的时间是一样的，且都是开始执行语句的时间。
4.2 week()
返回给定日期在当年是第几周。
MariaDB [(none)]> select week(now());
+-------------+
| week(now()) |
+-------------+
| 5 |
+-------------+
1 row in set (0.001 sec)4.3 year()、monthname()、quarter()
返回所给日期的年份、月份、月中天(所以day()的同义词是dayofmonth()函数)以及季度，不过返回的月份是英文全名。
MariaDB [(none)]> select year(now()),monthname(now());
+-------------+------------------+
| year(now()) | monthname(now()) |
+-------------+------------------+
| 2023 | February |
+-------------+------------------+
1 row in set (0.001 sec)4.4 hour()、minute()、second()
返回给定时间值的小时、分钟、秒部分。
MariaDB [(none)]> select now(),hour(now()),minute(now()),second(now());
+---------------------+-------------+---------------+---------------+
| now() | hour(now()) | minute(now()) | second(now()) |
+---------------------+-------------+---------------+---------------+
| 2023-02-04 22:23:25 | 22 | 23 | 25 |
+---------------------+-------------+---------------+---------------+
1 row in set (0.001 sec)4.5 extract()
从给定的DATETIME中提取秒(second)、分(minute)、时(hour)、日(day)、月(month)、周(week)、年(year)，还支持季度(quarter)提取。和SQL Server中的datepart()函数一样的功能。
MariaDB [(none)]> select extract(year from now()) as year_part, extract(month from now()) as month_part, extract(day from now()) as day_part, extract(week from now()) as week_part;
+-----------+------------+----------+-----------+
| year_part | month_part | day_part | week_part |
+-----------+------------+----------+-----------+
| 2023 | 2 | 4 | 5 |
+-----------+------------+----------+-----------+
1 row in set (0.001 sec)
MariaDB [(none)]> select now(),extract(hour from now()) as hour_part, extract(minute from now()) as minute_part, extract(second from now()) as second_part;
+---------------------+-----------+-------------+-------------+
| now() | hour_part | minute_part | second_part |
+---------------------+-----------+-------------+-------------+
| 2023-02-04 22:24:01 | 22 | 24 | 1 |
+---------------------+-----------+-------------+-------------+
1 row in set (0.000 sec)4.6 dayname()和dayofweek()
dayname返回给定日期是星期几，返回的周日期name的都是英文全名。而dayofweek返回的是数字代表的星期几，1表示周日，7表示周六。
MariaDB [(none)]> select dayname(20991231),dayofweek('20991231');
+-------------------+-----------------------+
| dayname(20991231) | dayofweek('20991231') |
+-------------------+-----------------------+
| Thursday | 5 |
+-------------------+-----------------------+
1 row in set (0.000 sec)4.7 日期时间格式化
日期格式化：date_format(date,fmt)
时间格式化：time_format(time,fmt)
其中fmt为日期时间的描述格式，使用%开头进行描述，例如%Y表示4位数字的年份，%m表示2位数字的月份等，更多的格式见官方手册fmt
MariaDB [(none)]> select date_format('20131012','%Y-%m-%d');
+------------------------------------+
| date_format('20131012','%Y-%m-%d') |
+------------------------------------+
| 2013-10-12 |
+------------------------------------+
1 row in set (0.000 sec)4.8 日期时间计算
增加日期：DATE_ADD(date,interval expr unit)，ADDDATE(date,interval expr unit)，它们是同义词；
减去日期：DATE_SUB(date,interval expr unit)，SUBDATE(date,interval expr unit)，它们是同义词；
在给定日期date基础上加或减去某种格式表达的日期时间。interval是关键字，expr是用来给定加减多少时间的表达式，unit是expr要表达的日期类型，见下图。其中expr的描述方式和unit是对应的。
Unit Description
MICROSECOND Microseconds
SECOND Seconds
MINUTE Minutes
HOUR Hours
DAY Days
WEEK Weeks
MONTH Months
QUARTER Quarters
YEAR Years
SECOND_MICROSECOND Seconds.Microseconds
MINUTE_MICROSECOND Minutes.Seconds.Microseconds
MINUTE_SECOND Minutes.Seconds
HOUR_MICROSECOND Hours.Minutes.Seconds.Microseconds
HOUR_SECOND Hours.Minutes.Seconds
HOUR_MINUTE Hours.Minutes
DAY_MICROSECOND Days Hours.Minutes.Seconds.Microseconds
DAY_SECOND Days Hours.Minutes.Seconds
DAY_MINUTE Days Hours.Minutes
DAY_HOUR Days Hours
YEAR_MONTH Years-Months例如year_month单元，从上表中得出它的格式是"years month"表示计算year部分和month部分的间隔。expr中year和month之间使用任意分隔符都可以，例如"1_2"、"1!2"、"1-2"和"1 2"都是允许的。如果使用day_minute单元，它的意义是"days hours.minutes"，那么expr中就需要给定3个值，这3个值从前向后分别代表日、时、分，中间可以用任意分隔符分隔，例如'3-2-1'表示3天2小时1分钟。
expr的前面可以加上"+"和"-"，分别表示加和减，不写时默认为"+"，所以date_add和date_sub之间通过正负符号是可以等价的。
以下是示例：
MariaDB [(none)]> select now(), date_add(now(),interval 31 day) as add31days, date_add(now(),interval '1_2' year_month) as add1year2month;
+---------------------+---------------------+---------------------+
| now() | add31days | add1year2month |
+---------------------+---------------------+---------------------+
| 2023-02-04 22:28:47 | 2023-03-07 22:28:47 | 2024-04-04 22:28:47 |
+---------------------+---------------------+---------------------+
1 row in set (0.000 sec)上述例子中使用了上面的第二列表示在当前日期内加上31天后的时间，第三列表示在当前日期基础上加上1年又2个月之后的时间。
如果date_add中expr使用的是负数，则表示减。
MariaDB [(none)]> select now(),
-> date_add(now(),interval '-31' day) as jian31days,
-> date_add(now(),interval '-1_2' year_month) as jian1year2month;
+---------------------+---------------------+---------------------+
| now() | jian31days | jian1year2month |
+---------------------+---------------------+---------------------+
| 2023-02-04 22:29:29 | 2023-01-04 22:29:29 | 2021-12-04 22:29:29 |
+---------------------+---------------------+---------------------+
1 row in set (0.000 sec)上面第二列表示在当前日期上减去31天后的时间，第三列表示在当前日期基础上减去1年又2个月之后的时间。
4.9 datediff()
expr1和expr2之间的天数差，是expr1减去expr2。
MariaDB [(none)]> select now(),datediff(now(),'2099-12-31');
+---------------------+------------------------------+
| now() | datediff(now(),'2099-12-31') |
+---------------------+------------------------------+
| 2023-02-04 22:30:19 | -28089 |
+---------------------+------------------------------+
1 row in set (0.001 sec)4.10 LAST_DAY()
返回给定日期所在月的最后一天。
MariaDB [(none)]> select last_day(now()),last_day('2099-12-31');
+-----------------+------------------------+
| last_day(now()) | last_day('2099-12-31') |
+-----------------+------------------------+
| 2023-02-28 | 2099-12-31 |
+-----------------+------------------------+
1 row in set (0.000 sec)5. 流程控制之条件判断函数
在MySQL/MariaDB中主要有if、ifnull和case语句进行条件判断。其中if语句和SQL Server中的if相差较大。
5.1 if()
函数用来判断expr是否为真，如果为真，则返回true_value，否则返回false_value。这和if语句是不一样的。
MariaDB [(none)]> select if(1>2,'a','b'),if(2>1,'a','b');
+-----------------+-----------------+
| if(1>2,'a','b') | if(2>1,'a','b') |
+-----------------+-----------------+
| b | a |
+-----------------+-----------------+
1 row in set (0.001 sec)expr判断是否为真的依据是expr的结果非0且非null。所以也可以直接使用数字表示真假，但不能使用字母表示真假。
MariaDB [(none)]> select if(99,'a','b'),if(0,'a','b'),if(null,'a','b'),if('2','a','b'),if('c','a','b');
+----------------+---------------+------------------+-----------------+-----------------+
| if(99,'a','b') | if(0,'a','b') | if(null,'a','b') | if('2','a','b') | if('c','a','b') |
+----------------+---------------+------------------+-----------------+-----------------+
| a | b | b | a | b |
+----------------+---------------+------------------+-----------------+-----------------+
1 row in set, 1 warning (0.000 sec)5.2 ifnull()
如果value1不为空则返回value1，否则返回value2。总之就是给定一个非null值。允许value2为null。
MariaDB [(none)]> select ifnull(1,'a'),ifnull('a','b'),ifnull(null,'a'),ifnull('a',null),ifnull(null,null);
+---------------+-----------------+------------------+------------------+-------------------+
| ifnull(1,'a') | ifnull('a','b') | ifnull(null,'a') | ifnull('a',null) | ifnull(null,null) |
+---------------+-----------------+------------------+------------------+-------------------+
| 1 | a | a | a | NULL |
+---------------+-----------------+------------------+------------------+-------------------+
1 row in set (0.001 sec)MySQL中的ifnull函数基本等价于SQL Server中的isnull()函数，跟SQL Server中的nullif函数相差非常大。且MySQL中的ifnull只能从两个参数中取一个非空值，而SQL Server中的coalesce()函数可以从多个参数中选第一个非空值。
5.3 nullif()
如果expr1等于expr2，则返回null，否则返回expr1。也就是说，两者不相等时取前者，否则取NULL。如果expr1和expr2任意一个为null，则直接返回null。这等价于：
case when expr1 = expr2 || expr1 is null || expr2 is null then null else expr1
end 例如：
MariaDB [(none)]> select nullif(1,1),nullif(1,2),nullif(null,1);
+-------------+-------------+----------------+
| nullif(1,1) | nullif(1,2) | nullif(null,1) |
+-------------+-------------+----------------+
| NULL | 1 | NULL |
+-------------+-------------+----------------+
1 row in set (0.001 sec)5.4 case语句
和SQL Server中的case语法差不多。也是两种格式:case when ...then...else...end和case ...when...then...else...end：
-- 格式一： CASE WHEN express_1 THEN value_1
WHEN express_2 THEN value_2
…ELSE value_n END; -- 格式二： CASE express WHEN value1 THEN value_1
WHEN value2 THEN value_2
...
ELSE value_n END;
注意，如果采用CASE...WHEN的写法格式，则express只能与value进行等同性检查。例如：
/*格式一示例*/ SELECT StudentID, CASE WHEN Mark < 60 THEN '不及格' WHEN Mark >= 60 AND Mark < 70 THEN '及格' WHEN Mark >= 70 AND Mark < 80 THEN '良好' ELSE '优秀' END FROM Tscore; /*格式二示例*/ SELECT StudentID , CASE FLOOR(Mark / 10) WHEN 5 THEN '不及格' WHEN 6 THEN '及格' WHEN 7 THEN '良好' ELSE '优秀' END FROM Tscore;
其中格式二为case ... when的格式，它的when部分的值都只能和floor(mark/10)做等于号比较，这是等同性检查。而格式一的写法就灵活的多，既可以做等号比较，也能做大于号或其他方式的比较。
6. 类型转换函数
类型转换函数用来转换数据类型。在MySQL/MariaDB中可以转换的类型有以下几种：
二进制: BINARY[(N)] 字符型: CHAR[(N)] 日期 : DATE 时间: TIME 日期时间型 : DATETIME 浮点数 : DECIMAL 整数 : SIGNED 无符号整数 : UNSIGNED
其中convert()有两种语法：
CONVERT(expr,type), CONVERT(expr USING transcoding_name)
后者用于不同字符集之间转换数据。
在转换数据类型时，cast和convert的功能基本是一样的，只是写法不同。
MariaDB [(none)]> SELECT CAST('3.1415' AS signed);
+--------------------------+
| CAST('3.1415' AS signed) |
+--------------------------+
| 3 |
+--------------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [(none)]> SELECT CAST(100 AS CHAR(2)),CONVERT('2099-12-31 23:59:59',TIME);
+----------------------+-------------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2099-12-31 23:59:59',TIME) |
+----------------------+-------------------------------------+
| 10 | 23:59:59 |
+----------------------+-------------------------------------+
1 row in set, 1 warning (0.000 sec)带有using的convert函数用来转换字符集。
MariaDB [(none)]> SELECT CHARSET('string'),CHARSET(CONVERT('string' USING latin1));
+-------------------+-----------------------------------------+
| CHARSET('string') | CHARSET(CONVERT('string' USING latin1)) |
+-------------------+-----------------------------------------+
| utf8mb4 | latin1 |
+-------------------+-----------------------------------------+
1 row in set (0.000 sec)7. 加减密函数
请移步：《MariaDB/MySQL加减密专题》
8. 其它实用函数
(1). sleep(N)
延迟N秒后执行后面的语句。特殊点在于sleep()函数可以用于select的选择列表。
select a,sleep(2),a from t; 注意上面的语句中，是先查询a，再阻塞2秒，之后再查询a，而不是先阻塞后再查询两次a或查询两次a后再阻塞。也就是说，对于mysql/mariadb来说，select的选择列表之间是有先后顺序的，不像sql server，选择列之间是完全平行等价的。可以通过下面的例子来验证：
MariaDB [(none)]> select sysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(2) | sysdate() |
+---------------------+----------+---------------------+
| 2023-02-04 22:41:15 | 0 | 2023-02-04 22:41:17 |
+---------------------+----------+---------------------+
1 row in set (2.000 sec)(2). 返回当前数据库名database()
(3). 返回当前数据库版本version()
(4). 返回当前登录用户名user()
MariaDB [mysql]> select database(),version(),user();
+------------+--------------------+----------------+
| database() | version() | user() |
+------------+--------------------+----------------+
| mysql | 10.8.6-MariaDB-log | root@localhost |
+------------+--------------------+----------------+
1 row in set (0.000 sec)(5). 返回加密字符串password(str), encrypt(str)
(6). 返回字符串的MD5值md5(str)
MariaDB [(none)]> select password('1234'),md5('1234'),encrypt('1234');
+-------------------------------------------+----------------------------------+-----------------+
| password('1234') | md5('1234') | encrypt('1234') |
+-------------------------------------------+----------------------------------+-----------------+
| *A4B6157319038724E3560894F7F932C8886EBFCF | 81dc9bdb52d04dc20036dbd8313ed055 | cJI9lgrTnq0m6 |
+-------------------------------------------+----------------------------------+-----------------+
1 row in set (0.000 sec)(7). last_insert_id()函数
MariaDB [test]> create table test1(id int primary key auto_increment,name varchar(16));
Query OK, 0 rows affected (0.020 sec)
MariaDB [test]> insert into test1 values(null, '张三');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into test1 values(null, '李四');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into test1 values(null, '王五');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into test1 values(null, '赵六');
Query OK, 1 row affected (0.007 sec)
MariaDB [test]> select * from test1;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
+----+--------+
4 rows in set (0.000 sec)
MariaDB [test]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 4 |
+------------------+
1 row in set (0.001 sec)一次插入多条记录，并查看last_insert_id()的值。
MariaDB [test]> insert into test1 values(null,'tun\'er'),(null,'woniu'),(null,'wugui');
Query OK, 3 rows affected (0.001 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> select *,last_insert_id() from test1;
+----+--------+------------------+
| id | name | last_insert_id() |
+----+--------+------------------+
| 1 | 张三 | 5 |
| 2 | 李四 | 5 |
| 3 | 王五 | 5 |
| 4 | 赵六 | 5 |
| 5 | tun'er | 5 |
| 6 | woniu | 5 |
| 7 | wugui | 5 |
+----+--------+------------------+
7 rows in set (0.000 sec)可以发现这里last_insert_id的值不是7而是5，这是因为批量插入的时候last_insert_id的值将会是批量中的第一条记录的自增列值。 且last_insert_id的值和表是无关的，只和会话环境有关。例如再向另外一个表插入后，last_insert_id的值将变为另一个值。
MariaDB [test]> create table test11(id int primary key auto_increment,name char(20));
Query OK, 0 rows affected (0.016 sec)
MariaDB [test]> insert into test11 values(null,'海绵宝宝');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into test11 values(null,'派大星');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into test11 values(null,'章鱼哥');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into test11 values(null,'蟹老板');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 4 |
+------------------+
1 row in set (0.000 sec)可以发现它又变回了4。
更多关于auto_increment计算相关内容见Mysql/MariaDB自增列。
文章评论