1. 字符串函数

完整的内置字符串函数见官方手册。

1.1 字符串连接函数

有两个字符串连接函数：concat(s1,s2,s3,...,sN)和concat_ws(sep,s1,s2,s3,...,sN)。

concat()将多个字符串连接起来形成一个长字符串。它会尝试将字符全部转换为字符型，如果存在null，则直接返回null。

1mysql> select concat('a','b',1),concat(1,2,3),concat('a',null);
2+-------------------+---------------+------------------+
3| concat('a','b',1) | concat(1,2,3) | concat('a',null) |
4+-------------------+---------------+------------------+
5| ab1               | 123           | NULL             |
6+-------------------+---------------+------------------+
71 row in set 

concat_ws(sep,s1,s2,...,sN)函数是concat()函数的特殊格式，它的第一个参数sep是用于连接s1,s2,...,sN的分隔符。分隔符可以是一个字符或一个字符串，只要合理即可。如果分隔符sep为null，则返回结果null，如果s1,s2,...,sN之间出现了null，则忽略null。

1mysql> select concat_ws(':','23','59','58'),concat_ws('-','1st','2nd'),concat_ws('XXX','wo','shi');
2+-------------------------------+----------------------------+-----------------------------+
3| concat_ws(':','23','59','58') | concat_ws('-','1st','2nd') | concat_ws('XXX','wo','shi') |
4+-------------------------------+----------------------------+-----------------------------+
5| 23:59:58                      | 1st-2nd                    | woXXXshi                    |
6+-------------------------------+----------------------------+-----------------------------+
71 row in set
8
9mysql> select concat_ws(':','23','59',null,'58'),concat_ws(null,'1st','2nd');
10+------------------------------------+-----------------------------+
11| concat_ws(':','23','59',null,'58') | concat_ws(null,'1st','2nd') |
12+------------------------------------+-----------------------------+
13| 23:59:58                           | NULL                        |
14+------------------------------------+-----------------------------+
151 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长度字符。

1mysql> select lower('MaLong'),upper('MaLong'),left('MaLong',3),right('Malong',3);
2+-----------------+-----------------+------------------+-------------------+
3| lower('MaLong') | upper('MaLong') | left('MaLong',3) | right('Malong',3) |
4+-----------------+-----------------+------------------+-------------------+
5| malong          | MALONG          | MaL              | ong               |
6+-----------------+-----------------+------------------+-------------------+
71 row in set 

1.3 填充函数

有两种：lpad(string,n,pad)和rpad(string,n,pad)。

使用pad对string最左边和最右边进行填充，直到填充后总长度为n个字符。pad可以是一个字符串，如果是字符串则从左向右取直到符合长度为止。

1mysql> select lpad('MaLong',10,'x'),lpad('MaLong',10,'xy'),rpad('MaLong',10,'x');
2+-----------------------+------------------------+-----------------------+
3| lpad('MaLong',10,'x') | lpad('MaLong',10,'xy') | rpad('MaLong',10,'x') |
4+-----------------------+------------------------+-----------------------+
5| xxxxMaLong            | xyxyMaLong             | MaLongxxxx            |
6+-----------------------+------------------------+-----------------------+
71 row in set 

长度n可以是小于或等于string字符串长度的值，此时lpad或者rpad的作用都是从左进行字符串截取而非填充，直到长度为n。也就是说lpad和rpad函数最强约束条件是长度参数n。

1mysql> select rpad('MaLong',3,'x'),lpad('MaLong',3,'x'),lpad('MaLong',0,'x');
2+----------------------+----------------------+----------------------+
3| rpad('MaLong',3,'x') | lpad('MaLong',3,'x') | lpad('MaLong',0,'x') |
4+----------------------+----------------------+----------------------+
5| MaL                  | MaL                  |                      |
6+----------------------+----------------------+----------------------+
71 row in set 

1.4 trim(string)、ltrim(string)、rtrim(sting)及trim(substring from string)

分别用来消除string行首和行尾、行首、行尾的空格以及行首行尾指定的字符串。

1 函数                    作用
2-----------------------         -------------------------------
3      ltrim(string)             删除行首空格
4      rtrim(string)             删除行尾空格
5      trim(string)              删除行首和行尾空格
6trim(substring from string)     删除行首和行尾的字符串substring 

例如：

1mysql> select length(trim(' MaLong ')) as A,
2              length(ltrim('MaLong ')) as B,
3              length(ltrim(' MaLong ')) as C,
4              length(rtrim(' MaLong ')) as D;
5+---+---+---+---+
6| A | B | C | D |
7+---+---+---+---+
8| 6 | 7 | 7 | 7 |
9+---+---+---+---+
101 row in set (0.00 sec)
11
12mysql> select trim('xy' from 'xyxabxycdxyxy');
13+---------------------------------+
14| trim('xy' from 'xyxabxycdxyxy') |
15+---------------------------------+
16| xabxycd                         |
17+---------------------------------+
181 row in set 

1.5 重复字符串repeat(string,x)

将string重复x次。

1mysql> select repeat('xy',3),length(repeat(' ',3)),repeat('0',3);
2+----------------+-----------------------+---------------+
3| repeat('xy',3) | length(repeat(' ',3)) | repeat('0',3) |
4+----------------+-----------------------+---------------+
5| xyxyxy         |                     3 | 000           |
6+----------------+-----------------------+---------------+
71 row in set 

1.6 字符串替换函数replace(string,a,b)

使用字符串b替换字符串string中所有的字符串a。注意点是它们都可以是字符串。如果想要替换掉的字符串a不在string中，则不会进行替换。

1mysql> select replace('woshiMaLongShuai','s','xxxx'),replace('woshiMaLongShuai','ob','xxxx');
2+----------------------------------------+-----------------------------------------+
3| replace('woshiMaLongShuai','s','xxxx') | replace('woshiMaLongShuai','ob','xxxx') |
4+----------------------------------------+-----------------------------------------+
5| woxxxxhiMaLongShuai                    | woshiMaLongShuai                        |
6+----------------------------------------+-----------------------------------------+
71 row in set 

1.7 字符串插入替换函数insert(string,p1,len,instead_string)

将string从位置p1开始,len个长度的字符替换为instead_string。

1mysql> select insert('woshimalongshuai',6,2,'gao');
2+--------------------------------------+
3| insert('woshimalongshuai',6,2,'gao') |
4+--------------------------------------+
5| woshigaolongshuai                    |
6+--------------------------------------+
71 row in set 

1.8 字符串提取substring(string,x,y)

返回string中从x位置开始y个长度的字符串。如果给出的位置不存在，则无法提取所以返回空。如果给出的长度超出，则只提取允许范围内的字符串。

1mysql> 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;
2+----+---+---+---+
3| A  | B | C | D |
4+----+---+---+---+
5| Lo |   |   | 2 |
6+----+---+---+---+
71 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。

1mysql> select strcmp('a','b'),strcmp('a','A'),strcmp('b','a');
2+-----------------+-----------------+-----------------+
3| strcmp('a','b') | strcmp('a','A') | strcmp('b','a') |
4+-----------------+-----------------+-----------------+
5|              -1 |               0 |               1 |
6+-----------------+-----------------+-----------------+
71 row in set
8mysql> select strcmp('ac','ab'),strcmp('ac','ac'),strcmp('a',null),strcmp(null,'a');
9+-------------------+-------------------+------------------+------------------+
10| strcmp('ac','ab') | strcmp('ac','ac') | strcmp('a',null) | strcmp(null,'a') |
11+-------------------+-------------------+------------------+------------------+
12|                 1 |                 0 | NULL             | NULL             |
13+-------------------+-------------------+------------------+------------------+
141 row in set 

关于字符串比较，另外两个函数least()和greatest()也能实现，这两个函数更多的用于取最值，特别是用于数值比较，所以在后文解释。

1.10 字符串长度函数length(string)和char_length(string)

length()返回字符串的字节数，注意不是字符数，char_length()返回的才是字符数。在SQL Server中长度函数是len(string)，且返回的是字符数。

1mysql> select length('woshiyigeren'),length('我');
2+------------------------+--------------+
3| length('woshiyigeren') | length('我') |
4+------------------------+--------------+
5|                     12 |            3 |
6+------------------------+--------------+
71 row in set
8
9mysql> select char_length('woshiyigeren'),char_length('我');
10+-----------------------------+-------------------+
11| char_length('woshiyigeren') | char_length('我') |
12+-----------------------------+-------------------+
13|                          12 |                 1 |
14+-----------------------------+-------------------+
151 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()函数功能类似。

1mysql> SELECT LOCATE('ball','football'),POSITION('ball' IN 'football') ,INSTR('football','ball');
2+---------------------------+--------------------------------+--------------------------+
3| LOCATE('ball','football') | POSITION('ball' IN 'football') | INSTR('football','ball') |
4+---------------------------+--------------------------------+--------------------------+
5|                         5 |                              5 |                        5 |
6+---------------------------+--------------------------------+--------------------------+
71 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。

1mysql> select find_in_set('ab','cd,ab,dc'),find_in_set('ab',''),find_in_set(null,'ab,cd');
2+------------------------------+----------------------+---------------------------+
3| find_in_set('ab','cd,ab,dc') | find_in_set('ab','') | find_in_set(null,'ab,cd') |
4+------------------------------+----------------------+---------------------------+
5|                            2 |                    0 | NULL                      |
6+------------------------------+----------------------+---------------------------+
71 row in set 

1.13 字符串位置函数field(s,str1,str2,...,strN)

返回字符串s在字符串集合str1,str2,...,strN中的位置。如果找不到或者字符串s为null，则返回0，因为null无法进行比较，也就是找不到。

1mysql> select field('ab','abc','1ab','ab','cd') as col1,field(null,'ab','cd') as col2;
2+------+------+
3| col1 | col2 |
4+------+------+
5|    3 |    0 |
6+------+------+
71 row in set (0.00 sec) 

1.14 指定位置的字符串函数elt(n,str1,str2,...,strN)

elt表示从(数据)仓库中提取需要的东西。n是位置，n=1则返回str1，n=2则返回str2，依次类推。当n<1或者大于字符串的数量，则返回null。

1mysql> select elt(1,'a','b','c'),elt(2,'a','b','c'),elt(0,'a','b'),elt(10,'a','b');
2+--------------------+--------------------+----------------+-----------------+
3| elt(1,'a','b','c') | elt(2,'a','b','c') | elt(0,'a','b') | elt(10,'a','b') |
4+--------------------+--------------------+----------------+-----------------+
5| a                  | b                  | NULL           | NULL            |
6+--------------------+--------------------+----------------+-----------------+
71 row in set 

1.15 字符串反转函数reverse(str)

反转字符串str的字符顺序。

1mysql> select reverse('hello');
2+------------------+
3| reverse('hello') |
4+------------------+
5| olleh            |
6+------------------+
71 row in set (0.00 sec) 

2. 数学函数

完整的内置数学函数见官方手册。

2.1 绝对值函数ABS(x)

1mysql> select abs(0.9),abs(0),abs(-0.9);
2+----------+--------+-----------+
3| abs(0.9) | abs(0) | abs(-0.9) |
4+----------+--------+-----------+
5| 0.9      |      0 | 0.9       |
6+----------+--------+-----------+
71 row in set 

2.2 取模函数mod(x,y)

取x/y后的余数。支持小数和负数。如果除数为0或者除数被除数有一个为null，则返回null。

1mysql> select mod(31,8),mod(31.56,8),mod(-31.56,8),mod(31,0),mod(0,8);
2+-----------+--------------+---------------+-----------+----------+
3| mod(31,8) | mod(31.56,8) | mod(-31.56,8) | mod(31,0) | mod(0,8) |
4+-----------+--------------+---------------+-----------+----------+
5|         7 | 7.56         | -7.56         | NULL      |        0 |
6+-----------+--------------+---------------+-----------+----------+
71 row in set 

2.3 四舍五入函数round(x,y)

返回值x含有y位小数的四舍五入后的结果，如果省略y，则默认y为0。

1mysql> select round(3.15),round(3.15,1),round(-3.15),round(-3.15,1);
2+-------------+---------------+--------------+----------------+
3| round(3.15) | round(3.15,1) | round(-3.15) | round(-3.15,1) |
4+-------------+---------------+--------------+----------------+
5| 3           | 3.2           | -3           | -3.2           |
6+-------------+---------------+--------------+----------------+
71 row in set 

2.4 位数截断函数truncate(x,y)

截断x的小数位数使得最终保留y个小数位。它的用法和round(x,y)几乎一样，只不过truncate是用来截断而不用来四舍五入。不能省略y但可以等于0，且y不能为负数。

1mysql> select truncate(3.156,2),truncate(3.156,0);
2+-------------------+-------------------+
3| truncate(3.156,2) | truncate(3.156,0) |
4+-------------------+-------------------+
5| 3.15              | 3                 |
6+-------------------+-------------------+
71 row in set 

2.5 地板函数floor(x)和天花板函数ceiling(x)

地板函数返回比x小的最大整数，天花板函数返回比x大的最小整数。

1mysql> select floor(3.4),floor(-3.4),ceiling(3.4),ceiling(-3.4);
2+------------+-------------+--------------+---------------+
3| floor(3.4) | floor(-3.4) | ceiling(3.4) | ceiling(-3.4) |
4+------------+-------------+--------------+---------------+
5|          3 |          -4 |            4 |            -3 |
6+------------+-------------+--------------+---------------+
71 row in set 

2.6 随机函数rand()

每次随机返回一个0-1之间不包括0和1的数，且每次运行结果都不同。

1mysql> select rand(),rand();
2+--------------------+----------------------+
3| rand()             | rand()               |
4+--------------------+----------------------+
5| 0.7380041170287915 | 0.055543343588284534 |
6+--------------------+----------------------+
71 row in set 

若要取得0-100之间的数，可以使用100去乘随机值，但这样获得的函数还是不包含0和100这两个边界的。

1mysql> select 100*rand(),100*rand(),100*rand();
2+------------------+-------------------+--------------------+
3| 100*rand()       | 100*rand()        | 100*rand()         |
4+------------------+-------------------+--------------------+
5| 22.5249471352668 | 96.80735235736458 | 16.461923454387044 |
6+------------------+-------------------+--------------------+
71 row in set 

若要取整，则可以配合floor()或者ceiling()函数。但这样取得的是[0,99]或者[1,100]，而不能是[0,100]。

1mysql> select floor(100*rand()) as '[0,99]',ceiling(100*rand()) as '[1,100]';
2+--------+---------+
3| [0,99] | [1,100] |
4+--------+---------+
5|     90 |      24 |
6+--------+---------+
71 row in set 

如果要想获得[0-100]这样包含边界的值，可以拓宽随机值。以下是两种方法：

1mysql> select ceiling(rand()*101-1),floor(rand()*101);
2+-----------------------+-------------------+
3| ceiling(rand()*101-1) | floor(rand()*101) |
4+-----------------------+-------------------+
5|                    92 |                55 |
6+-----------------------+-------------------+
71 row in set 

2.7 最值函数least(v1,v2,v3,…,vn)

从v1,v2,v3,…,vn中取出最小值。有以下几种情况：
(1)当只有数值类型时，取数值最小的。且负数有效。
(2)当只有字符串时，从第一个字符开始向后比较ascii码，小写字母小于大写字母。
(3)数值和字符串比较，返回结果为0。若要比较，需要先将数字转换为字符串格式，且字符串类型的数字总是小于字母。
(4)当n个成员之间存在null的时候，总是返回null，因为无法比较。

1mysql> select least(5,10,-1),least('ab','c','ac'),least('a',1),least('a','999'),least('a',1,null);
2+----------------+----------------------+--------------+------------------+-------------------+
3| least(5,10,-1) | least('ab','c','ac') | least('a',1) | least('a','999') | least('a',1,null) |
4+----------------+----------------------+--------------+------------------+-------------------+
5|             -1 | ab                   | 0            | 999              | NULL              |
6+----------------+----------------------+--------------+------------------+-------------------+
71 row in set 

2.8 最值函数greatest(v1,v2,v3,…,vn)

和least()函数相反，它取的是最大值。包括以下几种情况：
(1)当只有数值类型时，取最大值。负值有效。
(2)当只有字符串时，比较ascii码，大写字母大于小写字母。
(3)当数字和字符串比较时，数字大于字符串，即返回数字中最大值。但是字符串类型的数字小于字母。这个least()不一样。
(4)当存在null值时，返回null。

1mysql> select greatest(5,10,-1) as A,
2              greatest('ab','c','ac') as B,
3              greatest('a',1) as C,
4              greatest('a','999') as D,
5              greatest('a',1,null) as E;
6+----+---+---+---+------+
7| A  | B | C | D | E    |
8+----+---+---+---+------+
9| 10 | c | 1 | a | NULL |
10+----+---+---+---+------+
111 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()返回的是实时更新的当前时间，即操作系统当前的时间。通过下面的例子就知道了：

1mysql> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME(),
2       SLEEP(2),
3       NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME()G
4*************************** 1. row ***************************
5              now(): 2017-03-24 13:30:09
6current_timestamp(): 2017-03-24 13:30:09
7          sysdate(): 2017-03-24 13:30:09
8        localtime(): 2017-03-24 13:30:09
9           sleep(2): 0
10              now(): 2017-03-24 13:30:09
11current_timestamp(): 2017-03-24 13:30:09
12          sysdate(): 2017-03-24 13:30:11    # 注意此处sleep 2秒后的时间
13        localtime(): 2017-03-24 13:30:09
141 row in set (2.00 sec) 

可以看到，sleep(2)后，sysdate()返回的比其他的函数晚了两秒，而其他的函数返回的和sleep(2)之前的时间是一样的，且都是开始执行语句的时间。

3.2 week(DATE)

返回给定日期在当年是第几周。

1mysql> select week(now());
2+-------------+
3| week(now()) |
4+-------------+
5|          12 |
6+-------------+
71 row in set 

3.3 year(DATE)、monthname(DATE)、day(DATE)、quarter(DATE)

返回所给日期的年份、月份、月中天(所以day()的同义词是dayofmonth()函数)以及季度，不过返回的月份是英文全名。

1mysql> select year(now()),monthname(now());
2+-------------+------------------+
3| year(now()) | monthname(now()) |
4+-------------+------------------+
5|        2017 | March            |
6+-------------+------------------+
71 row in set 

3.4 hour(TIME)、minute(TIME)、second(TIME)

返回给定时间值的小时、分钟、秒部分。

1mysql> select now(),hour(now()),minute(now()),second(now());
2+---------------------+-------------+---------------+---------------+
3| now()               | hour(now()) | minute(now()) | second(now()) |
4+---------------------+-------------+---------------+---------------+
5| 2017-03-23 14:21:57 |          14 |            21 |            57 |
6+---------------------+-------------+---------------+---------------+
71 row in set 

3.5 extract(part from DATE)

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

1mysql> select extract(year from now()) as year_part,
2              extract(month from now()) as month_part,
3              extract(day from now()) as day_part,
4              extract(week from now()) as week_part;
5+-----------+------------+----------+-----------+
6| year_part | month_part | day_part | week_part |
7+-----------+------------+----------+-----------+
8|      2017 |         10 |       18 |        42 |
9+-----------+------------+----------+-----------+
101 row in set (0.00 sec)
11
12mysql> select now(),extract(hour from now()) as hour_part,
13              extract(minute from now()) as minute_part,
14              extract(second from now()) as second_part;
15+---------------------+-----------+-------------+-------------+
16| now()               | hour_part | minute_part | second_part |
17+---------------------+-----------+-------------+-------------+
18| 2017-10-18 04:34:12 |         4 |          34 |          12 |
19+---------------------+-----------+-------------+-------------+
201 row in set (0.00 sec) 

3.6 dayname(DATE)和dayofweek(DATE)

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

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

3.7 日期时间格式化

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

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

1mysql> select date_format('20131012','%Y-%m-%d');
2+------------------------------------+
3| date_format('20131012','%Y-%m-%d') |
4+------------------------------------+
5| 2013-10-12                         |
6+------------------------------------+
71 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是对应的。

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

以下是示例：

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