MySQL中的常用函数

在MySQL中，函数不仅可以出现在select语句及其子句中，而且还可以出现在update、delete语句中。

常用的函数有：

1. 字符串函数；主要用于处理字符串。

2. 数值函数；主要用于处理数字。

3. 日期和时间函数；主要用于处理日期和事件。

4. 系统信息函数；获取系统信息。

1. 使用字符串函数：

虽然每种数据库都支持SQL，但是每种数据库拥有各自所支持的函数。

1.1 合并字符串函数concat() 和 concat_ws()：

在MySQL中可以通过函数concat()和concat_ws()将传入的参数连接成为一个字符串。

语法定义为：

?

1 2	concat(s1, s2,...sn) //该函数会将传入的参数连接起来返回合并的字符串类型的数据。如果其中一个参数为 null ，则返回值为 null .

示例：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> select concat( 'my' , 's' , 'ql' ); + -----------------------+ | concat( 'my' , 's' , 'ql' ) | + -----------------------+ | mysql | + -----------------------+ 1 row in set (0.00 sec) mysql> select concat( 'my' , 's' , 'ql' , null ); + ----------------------------+ | concat( 'my' , 's' , 'ql' , null ) | + ----------------------------+ | NULL | + ----------------------------+ 1 row in set (0.00 sec) mysql> select concat(curdate(), 12.2); + -------------------------+ | concat(curdate(), 12.2) | + -------------------------+ | 2016-08-2512.2 | + -------------------------+ 1 row in set (0.00 sec) //说明：将当前时间和数值12.2合并。即concat()函数不仅可以接受字符串参数，而且还可以接受其他类型参数。

concat_ws()的定义：

?

1 2 3

concat_ws(sep,s1,s2,...sn) //该函数与concat()相比，多了一个表示分隔符的seq参数，不仅将传入的其他参数连接起来，而且还会通过分隔符将各个字符串分割开来。 //分隔符可以是一个字符串，也可以是其他参数。如果分割符为 null ，则返回结果为 null 。函数会忽略任何分割符后的参数 null .

示例：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> select concat_ws( '-' , '020' , '87658907' ); + ---------------------------------+ | concat_ws( '-' , '020' , '87658907' ) | + ---------------------------------+ | 020-87658907 | + ---------------------------------+ 1 row in set (0.00 sec) mysql> select concat_ws( null , '020' , '87658907' ); + ----------------------------------+ | concat_ws( null , '020' , '87658907' ) | + ----------------------------------+ | NULL | + ----------------------------------+ 1 row in set (0.00 sec) //当分隔符为 null 时，则返回结果为 null mysql> select concat_ws( '-' , '020' , null , '87658907' ); + --------------------------------------+ | concat_ws( '-' , '020' , null , '87658907' ) | + --------------------------------------+ | 020-87658907 | + --------------------------------------+ 1 row in set (0.00 sec) //不是第一个参数的 null 将被忽略

1.2 比较字符串大小函数strcmp()：

strcmp()定义为：

?

1 2	strcmp(str1,str2); //如果参数str1大于str2，返回1；如果str1小于str2,则返回-1；如果str1等于str2，则返回0；

示例：

?

1 2 3 4 5 6 7

mysql> select strcmp( 'abc' , 'abd' ),strcmp( 'abc' , 'abc' ),strcmp( 'abc' , 'abb' ); + ---------------------+---------------------+---------------------+ | strcmp( 'abc' , 'abd' ) | strcmp( 'abc' , 'abc' ) | strcmp( 'abc' , 'abb' ) | + ---------------------+---------------------+---------------------+ | -1 | 0 | 1 | + ---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)

1.3 获取字符串长度函数length()和字符数函数char_length()：

length()的定义如下：

length(str)

char_length(str)的定义如下：

char_length(str)

示例：

1 2 3 4 5 6 7 8

mysql> select length( 'mysql' ),length( '汉字' ),char_length( 'mysql' ),char_length( '汉字' ); + -----------------+----------------+----------------------+---------------------+ | length( 'mysql' ) | length( '汉字' ) | char_length( 'mysql' ) | char_length( '汉字' ) | + -----------------+----------------+----------------------+---------------------+ | 5 | 4 | 5 | 4 | + -----------------+----------------+----------------------+---------------------+ 1 row in set , 2 warnings (0.00 sec) //字符串‘MySQL'共有5个字符，但是占6个字节空间。这是因为每个字符串都是以\0结束。两个函数都是获取字符串的字符数而不是所占空间大小。

1.4 字母的大小写转换upper()和lower()：

字母大小转换函数：upper(s); ucase(s);
字母小写转换函数：lower(s); lcase(s);

示例：

1 2 3 4 5 6 7

mysql> select upper ( 'mysql' ),ucase( 'mYsql' ), lower ( 'MYSQL' ),lcase( 'MYsql' ); + ----------------+----------------+----------------+----------------+ | upper ( 'mysql' ) | ucase( 'mYsql' ) | lower ( 'MYSQL' ) | lcase( 'MYsql' ) | + ----------------+----------------+----------------+----------------+ | MYSQL | MYSQL | mysql | mysql | + ----------------+----------------+----------------+----------------+ 1 row in set (0.00 sec)

1.5 查找字符串：

mysql中提供了丰富的函数去查找字符串的位置。分别有find_in_set()函数、field()函数、locate()函数、position()函数和instr()函数。同时还提供了查找指定位置的字符串的函数elt()。

1.5.1 返回字符串位置的find_in_set()函数：

函数定义为：

1 2	find_in_set(str1,str2) //会返回在字符串str2中与str1相匹配的字符串的位置，参数str2字符串中将包含若干个用逗号隔开的字符串。

示例：

1 2 3 4 5 6 7

mysql> select find_in_set( 'mysql' , 'oracle,mysql,db2' ); + -----------------------------------------+ | find_in_set( 'mysql' , 'oracle,mysql,db2' ) | + -----------------------------------------+ | 2 | + -----------------------------------------+ 1 row in set (0.00 sec)

1.5.2 返回指定字符串位置的field()函数：

函数定义为：

1 2	filed(str,str1,str2...) //返回第一个与字符串str匹配的字符串的位置。

示例：

1 2 3 4 5 6 7

mysql> select field( 'mysql' , 'oracle' , 'db2' , 'redis' , 'mysql' ); + -----------------------------------------------+ | field( 'mysql' , 'oracle' , 'db2' , 'redis' , 'mysql' ) | + -----------------------------------------------+ | 4 | + -----------------------------------------------+ 1 row in set (0.00 sec)

1.5.3 返回子字符串相匹配的开始位置：

mysql中有三个函数可以获取子字符串相匹配的开始位置，分别是locate()、position()、instr()函数。

locate(str1,str) //返回参数str中字符串str1的开始位置

position(str1 in str) 和 instr(str,str1)

示例：

1 2 3 4 5 6 7

mysql> select locate( 'sql' , 'mysql' ),position( 'sql' in 'mysql' ),instr( 'mysql' , 'sql' ); + -----------------------+----------------------------+----------------------+ | locate( 'sql' , 'mysql' ) | position( 'sql' in 'mysql' ) | instr( 'mysql' , 'sql' ) | + -----------------------+----------------------------+----------------------+ | 3 | 3 | 3 | + -----------------------+----------------------------+----------------------+ 1 row in set (0.00 sec)

1.5.4 返回指定位置的字符串的elt()函数：

函数语法为：

elt(n,str1,str2...);

示例：

1 2 3 4 5 6 7

mysql> select elt(1, 'mysql' , 'db2' , 'oracle' ); + -------------------------------+ | elt(1, 'mysql' , 'db2' , 'oracle' ) | + -------------------------------+ | mysql | + -------------------------------+ 1 row in set (0.00 sec)

1.5.5 选择字符串的make_set()函数：

函数定义为：

make_set(num,str1,str2...strn)

示例：

1 2 3 4 5 6 7 8

mysql> select bin(5),make_set(5, 'mysql' , 'db2' , 'oracle' , 'redus' ); + --------+--------------------------------------------+ | bin(5) | make_set(5, 'mysql' , 'db2' , 'oracle' , 'redus' ) | + --------+--------------------------------------------+ | 101 | mysql,oracle | + --------+--------------------------------------------+ 1 row in set (0.00 sec) //make_set()首先会将数值num转换成二进制数，然后按照二进制从参数str1,str2,...,strn中选取相应的字符串。再通过二进制从右到左的顺序读取该值，如果值为1选择该字符串，否则将不选择该字符串。

1.6 从现有字符串中截取子字符串：

截取子字符串的函数有：left(),right(),substring(),mid();

1.6.1 从左边或右边截取子字符串：

函数定义为：

1 2 3 4

left (str,num) //返回字符串str中包含前num个字母（从左边数）的字符串。 right (str,num) //返回字符串str中包含后num个字母（从右边数）的字符串。

示例：

1 2 3 4 5 6 7

mysql> select left ( 'mysql' ,2), right ( 'mysql' ,3); + -----------------+------------------+ | left ( 'mysql' ,2) | right ( 'mysql' ,3) | + -----------------+------------------+ | my | sql | + -----------------+------------------+ 1 row in set (0.00 sec)

1.6.2 截取指定位置和长度的字符串：

可以通过substring()和mid()函数截取指定位置和长度的字符串。

函数语法为：

1 2	substring (str,num,len) //返回字符串str中的第num个位置开始长度为len的子字符串。 mid(str,num,len)

示例：

1 2 3 4 5 6 7

mysql> select substring ( 'zhaojd' ,2,3),mid( 'zhaojd' ,2,4); + -------------------------+-------------------+ | substring ( 'zhaojd' ,2,3) | mid( 'zhaojd' ,2,4) | + -------------------------+-------------------+ | hao | haoj | + -------------------------+-------------------+ 1 row in set (0.00 sec)

1.7 去除字符串的首尾空格：

去除字符串首尾空格的函数有：ltrim()、rtrim()、trim()

1.7.1 去除字符串开始处的空格：

函数定义如下：

ltrim(str) //返回去掉开始处空格的字符串

示例：

1 2 3 4 5 6 7

mysql> select length(concat( '-' , ' mysql ' , '-' )),length(concat( '-' ,ltrim( ' mysql ' ), '-' )); + -----------------------------------+------------------------------------------+ | length(concat( '-' , ' mysql ' , '-' )) | length(concat( '-' ,ltrim( ' mysql ' ), '-' )) | + -----------------------------------+------------------------------------------+ | 9 | 8 | + -----------------------------------+------------------------------------------+ 1 row in set (0.00 sec)

1.7.2 去除字符串结束处的空格：

rtrim(str) //返回去掉结束处空格的字符串。

示例：

1 2 3 4 5 6 7

mysql> select length(concat( '-' , ' mysql ' , '-' )) ,length(concat( '-' ,rtrim( ' mysql ' ), '-' )); + -----------------------------------+------------------------------------------+ | length(concat( '-' , ' mysql ' , '-' )) | length(concat( '-' ,rtrim( ' mysql ' ), '-' )) | + -----------------------------------+------------------------------------------+ | 9 | 8 | + -----------------------------------+------------------------------------------+ 1 row in set (0.00 sec)

1.7.3 去除字符串首尾空格：

trim(str) //返回去掉首尾空格的字符串

示例：

1 2 3 4 5 6 7

mysql> select concat( ' mysql ' ) origi,length(concat( ' mysql ' )) orilen, concat(trim( ' mysql ' )) after , length(concat(trim( ' mysql ' ))) afterlen; + ---------+--------+-------+----------+ | origi | orilen | after | afterlen | + ---------+--------+-------+----------+ | mysql | 7 | mysql | 5 | + ---------+--------+-------+----------+ 1 row in set (0.00 sec)

1.8 替换字符串：

实现替换字符串的功能，分别为insert()和replace()

1.8.1 使用insert()函数：

函数定义为：

insert(str,pos,len,newstr)
//insert()函数会将字符串str中的pos位置开始长度为len的字符串用字符串newstr来替换。
//如果参数pos的值超过字符串长度，则返回值为原始字符串str。
//如果len的长度大于原来str中所剩字符串的长度，则从位置pos开始进行全部替换。若任何一个参数为null,则返回值为null.

示例：

1 2 3 4 5 6 7

mysql> select insert ( '这是mysql数据库系统' ,3,5, 'oracle' ) bieming; + ----------------------+ | bieming | + ----------------------+ | 这oracleql数据库系统 | + ----------------------+ 1 row in set , 1 warning (0.00 sec)

1.8.1 使用replace()函数：

函数的定义为：

replace(str,substr,newstr) //将字符串str中的子字符串substr用字符串newstr来替换。

示例：

1 2 3 4 5 6 7

mysql> select replace ( '这是mysql数据库' , 'mysql' , 'db2' ) bieming; + ---------------+ | bieming | + ---------------+ | 这是db2数据库 | + ---------------+ 1 row in set , 1 warning (0.00 sec)

2. 使用数值函数：

2.1 获取随机数：

通过rand()和rand(x)函数来获取随机数。这两个函数都会返回0-1之间的随机数，其中rand()函数返回的数是完全随机的，而rand(x)函数返回的随机数值是完全相同的。

示例：

1 2 3 4 5 6 7

mysql> select rand(),rand(),rand(3),rand(3); + --------------------+--------------------+--------------------+--------------------+ | rand() | rand() | rand(3) | rand(3) | + --------------------+--------------------+--------------------+--------------------+ | 0.9600886758045188 | 0.7006410161970565 | 0.9057697559760601 | 0.9057697559760601 | + --------------------+--------------------+--------------------+--------------------+ 1 row in set (0.00 sec)

2.2 获取整数的函数：

在具体应用中，如果想要获取整数，可以通过ceil()和floor()函数来实现。

ceil()函数的定义为：

ceil(x) //函数返回大于或等于数值x的最小整数。
floor() //函数返回小于或等于数值x的最大整数。

示例：

1 2 3 4 5 6 7

mysql> select ceil(4.3),ceil(-2.5),floor(4.3),floor(-2.5); + -----------+------------+------------+-------------+ | ceil(4.3) | ceil(-2.5) | floor(4.3) | floor(-2.5) | + -----------+------------+------------+-------------+ | 5 | -2 | 4 | -3 | + -----------+------------+------------+-------------+ 1 row in set (0.00 sec)

2.3 截取数值函数：

可以通过truncate()对数值的小数位进行截取：

函数定义为：

truncate(x,y) //返回数值x,保留小数点后y位

示例：

1 2 3 4 5 6 7

mysql> select truncate (903.343434,2), truncate (903.343,-1); + ------------------------+----------------------+ | truncate (903.343434,2) | truncate (903.343,-1) | + ------------------------+----------------------+ | 903.34 | 900 | + ------------------------+----------------------+ 1 row in set (0.00 sec)

2.4 四舍五入函数：

对数值进行四舍五入可以通过round()函数实现:
round(x)
//函数返回值x经过四舍五入操作后的数值。
round(x,y)
//返回数值x保留到小数点后y位的值。在具体截取数据时需要进行四舍五入的操作。

示例：

1 2 3 4 5 6 7

mysql> select round(903.53567),round(-903.53567),round(903.53567,2),round(903.53567,-1); + ------------------+-------------------+--------------------+---------------------+ | round(903.53567) | round(-903.53567) | round(903.53567,2) | round(903.53567,-1) | + ------------------+-------------------+--------------------+---------------------+ | 904 | -904 | 903.54 | 900 | + ------------------+-------------------+--------------------+---------------------+ 1 row in set (0.00 sec)

3. 使用日期和时间函数：

3.1 获取当前日期和时间的函数：

3.1.1 获取当前日期和时间（日期 + 时间）：

MySQL中可以通过四个函数获取当前日期和时间，分别是now(),current_timestamp(),localtime(),sysdate()，这四个函数不仅可以获取当前日期和时间，而且显示的格式也一样。推荐使用now()

示例：

1 2 3 4 5 6 7

mysql> select now(), current_timestamp (),localtime(),sysdate(); + ---------------------+---------------------+---------------------+---------------------+ | now() | current_timestamp () | localtime() | sysdate() | + ---------------------+---------------------+---------------------+---------------------+ | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | + ---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)

3.1.2 获取当前日期：

获取当前日期的函数curdate()和current_date()函数。

示例：

1 2 3 4 5 6 7

mysql> select curdate(), current_date (); + ------------+----------------+ | curdate() | current_date () | + ------------+----------------+ | 2016-08-25 | 2016-08-25 | + ------------+----------------+ 1 row in set (0.00 sec)

3.1.3 获取当前时间：

获取当前时间的函数，curtime()或者current_time();推荐使用curtime();

示例：

1 2 3 4 5 6 7

mysql> select curtime(), current_time (); + -----------+----------------+ | curtime() | current_time () | + -----------+----------------+ | 16:15:04 | 16:15:04 | + -----------+----------------+ 1 row in set (0.00 sec)

3.2 获取日期和时间各部分值：

在MySQL中，可以通过各种函数来获取当前日期和时间的各部分值，其中year()函数返回日期中的年份，quarter()函数返回日期属于第几个季度，month()函数返回日期属于第几个月，week()函数返回日期属于第几个星期，dayofmonth()函数返回日期属于当前月的第几天,hour()函数返回时间的小时，minute()函数返回时间的分钟，second()函数返回时间的秒。

示例：

1 2 3 4 5 6 7

mysql> select now(), year (now()),quarter(now()), month (now()),week(now()),dayofmonth(now()), hour (now()), minute (now()), second (now()); + ---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+ | now() | year (now()) | quarter(now()) | month (now()) | week(now()) | dayofmonth(now()) | hour (now()) | minute (now()) | second (now()) | + ---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+ | 2016-08-25 16:27:37 | 2016 | 3 | 8 | 34 | 25 | 16 | 27 | 37 | + ---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+ 1 row in set (0.00 sec)

3.2.1 关于月的函数：

示例：

?

1 2 3 4 5 6 7 8

mysql> select now(), month (now()),monthname(now()); + ---------------------+--------------+------------------+ | now() | month (now()) | monthname(now()) | + ---------------------+--------------+------------------+ | 2016-08-25 16:29:37 | 8 | August | + ---------------------+--------------+------------------+ 1 row in set (0.00 sec) // month ()函数返回数字表示的月份，monthname()函数返回了英文表示的月份。

3.2.2 关于星期的函数：

示例：

1 2 3 4 5 6 7

mysql> select now(),week(now()),weekofyear(now()),dayname(now()),dayofweek(now()),weekday(now()); + ---------------------+-------------+-------------------+----------------+------------------+----------------+ | now() | week(now()) | weekofyear(now()) | dayname(now()) | dayofweek(now()) | weekday(now()) | + ---------------------+-------------+-------------------+----------------+------------------+----------------+ | 2016-08-25 16:34:35 | 34 | 34 | Thursday | 5 | 3 | + ---------------------+-------------+-------------------+----------------+------------------+----------------+ 1 row in set (0.00 sec)

3.2.3 关于天的函数：

示例：

1 2 3 4 5 6 7

mysql> select now(),dayofyear(now()),dayofmonth(now()); + ---------------------+------------------+-------------------+ | now() | dayofyear(now()) | dayofmonth(now()) | + ---------------------+------------------+-------------------+ | 2016-08-25 16:37:12 | 238 | 25 | + ---------------------+------------------+-------------------+ 1 row in set (0.00 sec)

3.2.4 获取指定值的extract()：

函数定义为：

1 2	extract(type from date ) //上述函数会从日期和时间参数 date 中获取指定类型参数type的值。type的取值可以是： year ， month ， day ， hour ， minute 和 second

示例：

1 2 3 4 5 6 7 8

mysql> select now(),extract( year from now()) year ,extract( month from now()) month ,extract( day from now()) day ,extract( hour from now()) hour ,extract(mi nute from now()) minute ,extract( second from now()) second ; + ---------------------+------+-------+------+------+--------+--------+ | now() | year | month | day | hour | minute | second | + ---------------------+------+-------+------+------+--------+--------+ | 2016-08-25 16:43:45 | 2016 | 8 | 25 | 16 | 43 | 45 | + ---------------------+------+-------+------+------+--------+--------+ 1 row in set (0.00 sec)

3.3 计算日期和时间的函数：

3.3.1 与默认日期和时间操作：

两个函数来实现与默认日期和时间的操作，分别为to_days()和from_days()

to_days(date):该函数计算日期参数date与默认日期和时间（0000年1月1日）之间的想个天数。

from_days(number)：该函数计算从默认日期和时间（0000年1月1日）开始经历number天后的日期和时间。

示例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select now(),to_days(now()),from_days(to_days(now())); + ---------------------+----------------+---------------------------+ | now() | to_days(now()) | from_days(to_days(now())) | + ---------------------+----------------+---------------------------+ | 2016-08-25 16:50:30 | 736566 | 2016-08-25 | + ---------------------+----------------+---------------------------+ 1 row in set (0.00 sec) //指定两个日期之间相隔的天数； mysql> select now(),datediff(now(), '2000-12-01' ); + ---------------------+------------------------------+ | now() | datediff(now(), '2000-12-01' ) | + ---------------------+------------------------------+ | 2016-08-25 16:52:16 | 5746 | + ---------------------+------------------------------+ 1 row in set (0.00 sec)

3.3.2 与指定日期和时间操作：

adddate(date,n)函数：该函数计算日期参数date加上n天后的日期。

subdate(date,n)函数：该函数计算日期参数date减去n天后的日期。

adddate(d,interval expr type)：返回日期参数d加上一段时间后的日期，表达式参数expr决定了时间的长度，参数type决定了所操作的对象。

subdate(d,interval expr type)：返回日期参数d减去一段时间后的日期，表达式expr决定了时间的长度。参数type决定了所操作的对象。

addtime(time,n):计算时间参数time加上n秒后的时间。

subtime(time,n):计算时间参数time减去n秒后的时间。

示例一：

1 2 3 4 5 6 7

mysql> select curdate(),adddate(curdate(),5),subdate(curdate(),5); + ------------+----------------------+----------------------+ | curdate() | adddate(curdate(),5) | subdate(curdate(),5) | + ------------+----------------------+----------------------+ | 2016-08-25 | 2016-08-30 | 2016-08-20 | + ------------+----------------------+----------------------+ 1 row in set (0.00 sec)

示例二：

1 2 3 4 5 6 7

mysql> select curdate(),adddate(curdate(),interval '2,3' year_month),subdate(curdate(),interval '2,3' year_month); + ------------+----------------------------------------------+----------------------------------------------+ | curdate() | adddate(curdate(),interval '2,3' year_month) | subdate(curdate(),interval '2,3' year_month) | + ------------+----------------------------------------------+----------------------------------------------+ | 2016-08-25 | 2018-11-25 | 2014-05-25 | + ------------+----------------------------------------------+----------------------------------------------+ 1 row in set (0.00 sec)

示例三：

1 2 3 4 5 6 7

mysql> select curtime(),addtime(curtime(),5),subtime(curtime(),5); + -----------+----------------------+----------------------+ | curtime() | addtime(curtime(),5) | subtime(curtime(),5) | + -----------+----------------------+----------------------+ | 17:12:21 | 17:12:26 | 17:12:16 | + -----------+----------------------+----------------------+ 1 row in set (0.00 sec)

4. 使用系统信息函数：

1	select version(), database (), user ();

示例：

1 2 3 4 5 6 7 8 9

mysql> select version(), database (), user (); + ------------+------------+----------------+ | version() | database () | user () | + ------------+------------+----------------+ | 5.5.51-log | NULL | root@localhost | + ------------+------------+----------------+ 1 row in set (0.00 sec) //获取 auto_increment约束的最后ID select last_insert_id();