(MariaDB)MySQL内置函数大全

本文目录：
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 最值函数greatest()
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 最值函数greatest(v1,v2,v3,…,vn)

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

mysql> select greatest(5,10,-1) as A,
              greatest('ab','c','ac') as B,
              greatest('a',1) as C,
              greatest('a','999') as D,
              greatest('a',1,null) as E;
+----+---+---+---+------+
| A  | B | C | D | E    |
+----+---+---+---+------+
| 10 | c | 1 | a | NULL |
+----+---+---+---+------+
1 row in set, 2 warnings (0.00 sec)

3 日期时间函数

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

3.1 当前日期时间

返回当前日期：curdate()、current_date()，它们是同义词；
返回当前时间：curtime()、current_time()，它们是同义词；
返回当前日期时间：now()、current_timestamp()、localtime()、localtimestamp、localtimestamp()、sysdate()，除了sysdate()，其余的都是now()的同义词。

mysql> select curdate(),current_date(),current_timestamp(),curtime(),localtime(),now(),sysdate();

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

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

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

3.2 week(DATE)

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

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          12 |
+-------------+
1 row in set

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

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

mysql> select year(now()),monthname(now());
+-------------+------------------+
| year(now()) | monthname(now()) |
+-------------+------------------+
|        2017 | March            |
+-------------+------------------+
1 row in set

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

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

mysql> select now(),hour(now()),minute(now()),second(now());
+---------------------+-------------+---------------+---------------+
| now()               | hour(now()) | minute(now()) | second(now()) |
+---------------------+-------------+---------------+---------------+
| 2017-03-23 14:21:57 |          14 |            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自增列。

posted @ 2017-10-26 23:52 骏马金龙阅读(11058) 评论(3) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部