转载：mysql的内置函数

傷城~ 2024-04-20 09:00 86阅读 0赞

**本文原创地址：博客园骏马金龙[https://www.cnblogs.com/f-ck-need-u/archive/2017/10/26/7740235.html][https_www.cnblogs.com_f-ck-need-u_archive_2017_10_26_7740235.html]**

我懒得又去跳转 直接复制全文过来的  原文是：

**博客园骏马金龙[https://www.cnblogs.com/f-ck-need-u/archive/2017/10/26/7740235.html][https_www.cnblogs.com_f-ck-need-u_archive_2017_10_26_7740235.html]**

**目录**

1. 字符串函数

1.1 字符串连接函数

1.2 lower(string)、upper(string)、left(string,x)、right(string,x)

1.3 填充函数

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

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

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

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

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

1.9 字符串比较函数strcmp(string1,string2)

1.10 字符串长度函数length(string)和char\_length(string)

1.11 字符串位置函数locate(sub\_str,string)、position(sub\_str in string)和instr(str,sub\_str)

1.12 字符串位置函数find\_in\_set(sub\_string,str\_set)

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

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

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

2. 数学函数

2.1 绝对值函数ABS(x)

2.2 取模函数mod(x,y)

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

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

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

2.6 随机函数rand()

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

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

3 日期时间函数

3.1 当前日期时间

3.2 week(DATE)

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

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

3.5 extract(part from DATE)

3.6 dayname(DATE)和dayofweek(DATE)

3.7 日期时间格式化

3.8 日期时间计算

3.9 datediff(expr1,expr2)

3.10 LAST\_DAY(datetime)

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

4.1 if(expr,true\_value,false\_value)

4.2 ifnull(value1,value2)

4.3 nullif(expr1,expr2)

4.4 case语句

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

6 其它实用函数

--------------------

MySQL/MariaDB的内置函数比较多，这里挑选一部分进行解释，完整的内置函数列表见[官方手册][Link 1]。

# 1. 字符串函数 #

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

## 1.1 字符串连接函数 ##

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

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

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

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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长度**字符**。

1
    2
    3
    4
    5
    6
    7
    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可以是一个字符串，如果是字符串则从左向右取直到符合长度为止。

1
    2
    3
    4
    5
    6
    7
    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。

1
    2
    3
    4
    5
    6
    7
    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行首和行尾、行首、行尾的空格以及行首行尾指定的字符串。

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

例如：

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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次。

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

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

1
    2
    3
    4
    5
    6
    7
    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。

1
    2
    3
    4
    5
    6
    7
    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个长度的字符串。如果给出的位置不存在，则无法提取所以返回空。如果给出的长度超出，则只提取允许范围内的字符串。

1
    2
    3
    4
    5
    6
    7
    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。

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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)，且返回的是字符数。

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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中：

![format_png][]

## 1.11 字符串位置函数locate(sub\_str,string)、position(sub\_str in string)和instr(str,sub\_str) ##

这三个函数的作用相同，都是返回sub\_str在string中的开始位置。和SQL Server中的charindex()函数功能类似。

1
    2
    3
    4
    5
    6
    7
    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。

1
    2
    3
    4
    5
    6
    7
    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无法进行比较，也就是找不到。

1
    2
    3
    4
    5
    6
    7
    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。

1
    2
    3
    4
    5
    6
    7
    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的字符顺序。

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

# 2. 数学函数 #

完整的内置数学函数见[官方手册][Link 3]。

## 2.1 绝对值函数ABS(x) ##

1
    2
    3
    4
    5
    6
    7
    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。

1
    2
    3
    4
    5
    6
    7
    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。

1
    2
    3
    4
    5
    6
    7
    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不能为负数。

1
    2
    3
    4
    5
    6
    7
    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大的最小整数。

1
    2
    3
    4
    5
    6
    7
    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的数，且每次运行结果都不同。

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

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

1
    2
    3
    4
    5
    6
    7
    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\]。

1
    2
    3
    4
    5
    6
    7
    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\]这样包含边界的值，可以拓宽随机值。以下是两种方法：

1
    2
    3
    4
    5
    6
    7
    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，因为无法比较。

1
    2
    3
    4
    5
    6
    7
    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 最值函数greatest(v1,v2,v3,…,vn) ##

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

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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 日期时间函数 #

有很多很多，[官方手册：日期时间函数][Link 4]。以下挑几个介绍。

## 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();

![format_png 1][]

注意，now()和sysdate()是不同的。now()返回的是执行SQL语句那一刻的时间(如果now()是在存储过程或函数或触发器中，则now()返回的是这些程序开始调用执行的时刻)，而sysdate()返回的是实时更新的当前时间，即操作系统当前的时间。通过下面的例子就知道了：

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

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

## 3.2 week(DATE) ##

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

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

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

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

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

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

1
    2
    3
    4
    5
    6
    7
    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()函数一样的功能。

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    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表示周六。

1
    2
    3
    4
    5
    6
    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][]

1
    2
    3
    4
    5
    6
    7
    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是对应的。

<table> 
 <thead> 
  <tr> 
   <th>Unit</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MICROSECOND</td> 
   <td>Microseconds</td> 
  </tr> 
  <tr> 
   <td>SECOND</td> 
   <td>Seconds</td> 
  </tr> 
  <tr> 
   <td>MINUTE</td> 
   <td>Minutes</td> 
  </tr> 
  <tr> 
   <td>HOUR</td> 
   <td>Hours</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>Days</td> 
  </tr> 
  <tr> 
   <td>WEEK</td> 
   <td>Weeks</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>Months</td> 
  </tr> 
  <tr> 
   <td>QUARTER</td> 
   <td>Quarters</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>Years</td> 
  </tr> 
  <tr> 
   <td>SECOND_MICROSECOND</td> 
   <td>Seconds.Microseconds</td> 
  </tr> 
  <tr> 
   <td>MINUTE_MICROSECOND</td> 
   <td>Minutes.Seconds.Microseconds</td> 
  </tr> 
  <tr> 
   <td>MINUTE_SECOND</td> 
   <td>Minutes.Seconds</td> 
  </tr> 
  <tr> 
   <td>HOUR_MICROSECOND</td> 
   <td>Hours.Minutes.Seconds.Microseconds</td> 
  </tr> 
  <tr> 
   <td>HOUR_SECOND</td> 
   <td>Hours.Minutes.Seconds</td> 
  </tr> 
  <tr> 
   <td>HOUR_MINUTE</td> 
   <td>Hours.Minutes</td> 
  </tr> 
  <tr> 
   <td>DAY_MICROSECOND</td> 
   <td>Days Hours.Minutes.Seconds.Microseconds</td> 
  </tr> 
  <tr> 
   <td>DAY_SECOND</td> 
   <td>Days Hours.Minutes.Seconds</td> 
  </tr> 
  <tr> 
   <td>DAY_MINUTE</td> 
   <td>Days Hours.Minutes</td> 
  </tr> 
  <tr> 
   <td>DAY_HOUR</td> 
   <td>Days Hours</td> 
  </tr> 
  <tr> 
   <td>YEAR_MONTH</td> 
   <td>Years-Months</td> 
  </tr> 
 </tbody> 
</table>

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

以下是示例：

1
    2
    3
    4
    5
    6
    7
    8
    9
    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使用的是负数，则表示减。

1
    2
    3
    4
    5
    6
    7
    8
    9
    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。

1
    2
    3
    4
    5
    6
    7
    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) ##

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

1
    2
    3
    4
    5
    6
    7
    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语句][if]是不一样的。

1
    2
    3
    4
    5
    6
    7
    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**。所以也可以直接使用数字表示真假，但不能使用字母表示真假。

1
    2
    3
    4
    5
    6
    7
    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中的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。这等价于：

1
    2
    3
    case when expr1 = expr2 || expr1 is null || expr2 is null then null 
    else expr1
    end

例如：

## 4.4 case语句 ##

和SQL Server中的case语法差不多。也是两种格式:`case when ...then...else...end`和`case ...when...then...else...end`：

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- 格式一：
    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进行等同性检查。例如：

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    /*格式一示例*/
    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中可以转换的类型有以下几种：

1
    2
    3
    4
    5
    6
    7
    8
    二进制: BINARY[(N)] 
    字符型: CHAR[(N)] 
    日期 : DATE 
    时间: TIME 
    日期时间型 : DATETIME
    浮点数 : DECIMAL 
    整数 : SIGNED 
    无符号整数 : UNSIGNED

其中convert()有两种语法：

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

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

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

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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函数用来转换字符集。

1
    2
    3
    4
    5
    6
    7
    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()
    
        1
        2
        3
        4
        5
        6
        7
        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)
    
        1
        2
        3
        4
        5
        6
        7
        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的值和一次插入一条记录还是一次批量插入的方式有关。
    
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        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。
    
        1
        2
        3
        4
        5
        6
        7
        mysql> select last_insert_id();
        +------------------+
        | last_insert_id() |
        +------------------+
        |                4 |
        +------------------+
        1 row in set
    
    一次插入多条记录，并查看last\_insert\_id()的值。
    
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        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的值将变为另一个值。
    
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        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自增列][Mysql_MariaDB]。

[https_www.cnblogs.com_f-ck-need-u_archive_2017_10_26_7740235.html]: https://www.cnblogs.com/f-ck-need-u/archive/2017/10/26/7740235.html
[Link 1]: https://mariadb.com/kb/en/library/function-and-operator-reference/
[Link 2]: https://mariadb.com/kb/en/string-functions/
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzMzMDEzLzIwMTcxMC83MzMwMTMtMjAxNzEwMjYyMTExMzE3NzMtMTg2Njc4NTU2NC5wbmc?x-oss-process=image/format,png
[Link 3]: https://mariadb.com/kb/en/numeric-functions/
[Link 4]: https://mariadb.com/kb/en/library/day/
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzMzMDEzLzIwMTcxMC83MzMwMTMtMjAxNzEwMjYyMTQ2MzI1NTUtMTc2MTg3ODEwMC5wbmc?x-oss-process=image/format,png
[fmt]: https://mariadb.com/kb/en/library/date_format/#description
[if]: https://mariadb.com/kb/en/library/if/
[Mysql_MariaDB]: http://www.cnblogs.com/f-ck-need-u/p/7592501.html#blog1.3