(MariaDB)MySQL内置函数大全

共计 41784 个字符，预计需要花费 105 分钟才能阅读完成。

本文目录：
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)之前的时间是一样的，且都是开始执行语句的时间。

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-10/148021p2.htm

 

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 |            21 |            57 |
+---------------------+-------------+---------------+---------------+
1 row in set

 

3.5 extract(part from DATE)

从给定的 DATETIME 中提取秒 (second)、分(minute)、时(hour)、日(day)、月(month)、周(week)、年(year)，还支持季度(quarter) 提取。和 SQL Server 中的 datepart()函数一样的功能。

mysql> 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 |
+-----------+------------+----------+-----------+
|      2017 |         10 |       18 |        42 |
+-----------+------------+----------+-----------+
1 row in set (0.00 sec)

mysql> 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 |
+---------------------+-----------+-------------+-------------+
| 2017-10-18 04:34:12 |         4 |          34 |          12 |
+---------------------+-----------+-------------+-------------+
1 row in set (0.00 sec)

 

3.6 dayname(DATE)和 dayofweek(DATE)

dayname 返回给定日期是星期几，返回的周日期 name 的都是英文全名。而 dayofweek 返回的是数字代表的星期几，1 表示周日，7 表示周六。

mysql> select dayname(20131111),dayofweek('20131111');
+-------------------+-----------------------+
| dayname(20131111) | dayofweek('20131111') |
+-------------------+-----------------------+
| Monday            |                     2 |
+-------------------+-----------------------+
1 row in set (0.00 sec)

 

3.7 日期时间格式化

日期格式化：date_format(date,fmt)
时间格式化：time_format(time,fmt)

其中 fmt 为日期时间的描述格式，使用 % 开头进行描述，例如 %Y 表示 4 位数字的年份，%m 表示 2 位数字的月份等，更多的格式见官方手册fmt

mysql> select date_format('20131012','%Y-%m-%d');
+------------------------------------+
| date_format('20131012','%Y-%m-%d') |
+------------------------------------+
| 2013-10-12                         |
+------------------------------------+
1 row in set (0.00 sec)

 

3.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 之间通过正负符号是可以等价的。

以下是示例：

mysql> select now(),
              date_add(now(),interval 31 day) as add31days,
              date_add(now(),interval '1_2' year_month) as add1year2month;
+---------------------+---------------------+---------------------+
| now()               | add31days           | add1year2month      |
+---------------------+---------------------+---------------------+
| 2017-10-18 05:00:11 | 2017-11-18 05:00:11 | 2018-12-18 05:00:11 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

上述例子中使用了上面的第二列表示在当前日期内加上 31 天后的时间，第三列表示在当前日期基础上加上 1 年又 2 个月之后的时间。

如果 date_add 中 expr 使用的是负数，则表示减。

mysql> select now(),
              date_add(now(),interval '-31' day) as jian31days,
              date_add(now(),interval '-1_2' year_month) as jian1year2month;
+---------------------+---------------------+---------------------+
| now()               | jian31days          | jian1year2month     |
+---------------------+---------------------+---------------------+
| 2017-10-18 05:00:58 | 2017-09-17 05:00:58 | 2016-08-18 05:00:58 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

上面第二列表示在当前日期上减去 31 天后的时间，第三列表示在当前日期基础上减去 1 年又 2 个月之后的时间。

 

3.9 datediff(expr1,expr2)

expr1 和 expr2 之间的天数差，是 expr1 减去 expr2。

mysql> select now(),datediff(now(),'2018-01-01');
+---------------------+------------------------------+
| now()               | datediff(now(),'2018-01-01') |
+---------------------+------------------------------+
| 2017-03-23 14:57:06 |                         -284 |
+---------------------+------------------------------+
1 row in set

 

3.10 LAST_DAY(datetime)

返回给定日期所在月的最后一天。

mysql> select last_day(now()),last_day('2016-02-03');
+-----------------+------------------------+
| last_day(now()) | last_day('2016-02-03') |
+-----------------+------------------------+
| 2017-03-31      | 2016-02-29             |
+-----------------+------------------------+
1 row in set

 

4 流程控制之条件判断函数

在 MySQL/MariaDB 中主要有 if、ifnull 和 case 语句进行条件判断。其中 if 语句和 SQL Server 中的 if 相差较大。

 

4.1 if(expr,true_value,false_value)

if 函数用来判断 expr 是否为真，如果为真，则返回 true_value，否则返回 false_value。这和 if 语句 是不一样的。

mysql> 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

expr 判断是否为真的依据是 expr 的结果 非 0 且非 null。所以也可以直接使用数字表示真假，但不能使用字母表示真假。

mysql> 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.00 sec)

 

4.2 ifnull(value1,value2)

如果 value1 不为空则返回 value1，否则返回 value2。总之就是给定一个非 null 值。允许 value2 为 null。

mysql> 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

MySQL 中的 ifnull 函数基本等价于 SQL Server 中的 isnull()函数，跟 SQL Server 中的 nullif 函数相差非常大。且 MySQL 中的 ifnull 只能从两个参数中取一个非空值，而 SQL Server 中的 coalesce()函数可以从多个参数中选第一个非空值。

 

4.3 nullif(expr1,expr2)

如果 expr1 等于 expr2，则返回 null，否则返回 expr1。也就是说，两者不相等时取前者，否则取 NULL。如果 expr1 和 expr2 任意一个为 null，则直接返回 null。这等价于：

case when expr1 = expr2 || expr1 is null || expr2 is null then null 
else expr1
end

例如：

mysql> 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.00 sec)

 

4.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)做等于号比较，这是等同性检查。而格式一的写法就灵活的多，既可以做等号比较，也能做大于号或其他方式的比较。

 

5 类型转换函数 cast()和 convert()

类型转换函数用来转换数据类型。在 MySQL/MariaDB 中可以转换的类型有以下几种：

二进制: BINARY[(N)] 
字符型: CHAR[(N)] 
日期 : DATE 
时间: TIME 
日期时间型 : DATETIME
浮点数 : DECIMAL 
整数 : SIGNED 
无符号整数 : UNSIGNED

其中 convert()有两种语法：

CONVERT(expr,type), CONVERT(expr USING transcoding_name)

后者用于不同字符集之间转换数据。

在转换数据类型时，cast 和 convert 的功能基本是一样的，只是写法不同。

mysql> SELECT CAST('3.35' AS signed);
+------------------------+
| CAST('3.35' AS signed) |
+------------------------+
|                      3 |
+------------------------+
1 row in set

mysql> SELECT  CAST(100 AS CHAR(2)),CONVERT('2013-8-9 12:12:12',TIME);
+----------------------+-----------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2013-8-9 12:12:12',TIME) |
+----------------------+-----------------------------------+
| 10                   | 12:12:12                          |
+----------------------+-----------------------------------+
1 row in set

带有 using 的 convert 函数用来转换字符集。

mysql> SELECT  CHARSET('string'),CHARSET(CONVERT('string' USING latin1));
+-------------------+-----------------------------------------+
| CHARSET('string') | CHARSET(CONVERT('string' USING latin1)) |
+-------------------+-----------------------------------------+
| utf8mb4           | latin1                                  |
+-------------------+-----------------------------------------+
1 row in set

 

6 其它实用函数

• (1). sleep(N)
  延迟 N 秒后执行后面的语句。特殊点在于 sleep()函数可以用于 select 的选择列表。

  select a,sleep(2),a from t;
  

  注意上面的语句中，是先查询 a，再阻塞 2 秒，之后再查询 a，而不是先阻塞后再查询两次 a 或查询两次 a 后再阻塞。也就是说，对于 mysql/mariadb 来说，select 的选择列表之间是有先后顺序的，不像 sql server，选择列之间是完全平行等价的。可以通过下面的例子来验证：

  select sysdate(),sleep(1),sysdate();
  

• (2). 返回当前数据库名 database()
• (3). 返回当前数据库版本 version()

• (4). 返回当前登录用户名 user()

  mysql> select database(),version(),user();
  +------------+-----------+--------------------+
  | database() | version() | user()             |
  +------------+-----------+--------------------+
  | test       | 5.6.35    | root@192.168.100.1 |
  +------------+-----------+--------------------+
  1 row in set
  

• (5). 返回加密字符串 password(str)

• (6). 返回字符串的 MD5 值 md5(str)

  mysql> select password('abc'),md5('abc');
  +-------------------------------------------+----------------------------------+
  | password('abc')                           | md5('abc')                       |
  +-------------------------------------------+----------------------------------+
  | *0D3CED9BEC10A777AEC23CCC353A8C08A633045E | 900150983cd24fb0d6963f7d28e17f72 |
  +-------------------------------------------+----------------------------------+
  1 row in set
  

• (7). last_insert_id()函数
  LAST_INSERT_ID() 返回最后一个 INSERT 或 UPDATE 为 AUTO_INCREMENT 列设置的第一个发生的值。该函数值不是基于表的，这一点和 SQL Server 是不同的，也就是说，对 a 表插入的最后一个值是 10，再对 b 表插入的最后一个值是 15，那么函数返回的将是 15。并且 last_insert_id 的值和一次插入一条记录还是一次批量插入的方式有关。

  mysql> create table test10(id int primary key auto_increment,name char(20));
  # 一次插入一条记录。
  mysql> insert into test10 values(null,'gaoxiaofang');
  mysql> insert into test10 values(null,'malongshuai');
  mysql> insert into test10 values(null,'longshuai');
  mysql> insert into test10 values(null,'xiaofang');
  mysql> select * from test10;
  +----+-------------+
  | id | name        |
  +----+-------------+
  |  1 | gaoxiaofang |
  |  2 | malongshuai |
  |  3 | longshuai   |
  |  4 | xiaofang    |
  +----+-------------+
  4 rows in set
  

  查看 last_insert_id 的值，结果将是 4。

  mysql> select last_insert_id();
  +------------------+
  | last_insert_id() |
  +------------------+
  |                4 |
  +------------------+
  1 row in set
  

  一次插入多条记录，并查看 last_insert_id()的值。

  mysql> insert into test10 values(null,'tun\'er'),(null,'woniu'),(null,'wugui');
  mysql> select *,last_insert_id() from test10;
  +----+-------------+------------------+
  | id | name        | last_insert_id() |
  +----+-------------+------------------+
  |  1 | gaoxiaofang |                5 |
  |  2 | malongshuai |                5 |
  |  3 | longshuai   |                5 |
  |  4 | xiaofang    |                5 |
  |  5 | tun'er      |                5 |
  |  6 | woniu       |                5 |
  |  7 | wugui       |                5 |
  +----+-------------+------------------+
  7 rows in set
  

  可以发现这里 last_insert_id 的值不是 7 而是 5，这是因为批量插入的时候 last_insert_id 的值将会是批量中的第一条记录的自增列值。且 last_insert_id 的值和表是无关的，只和会话环境有关。例如再向另外一个表插入后，last_insert_id 的值将变为另一个值。

  mysql> create table test11(id int primary key auto_increment,name char(20));
  mysql> insert into test11 values(null,'gaoxiaofang');
  mysql> insert into test11 values(null,'malongshuai');
  mysql> insert into test11 values(null,'longshuai');
  mysql> insert into test11 values(null,'xiaofang');
  mysql> select last_insert_id();
  +------------------+
  | last_insert_id() |
  +------------------+
  |                4 |
  +------------------+
  1 row in set
  

  可以发现它又变回了 4。

更多关于 auto_increment 计算相关内容见Mysql/MariaDB 自增列。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-10/148021.htm

本文目录：
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)之前的时间是一样的，且都是开始执行语句的时间。

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-10/148021p2.htm