MySQL中的常用函数有哪些

2025-03-29 技术教程

小编给大家分享一下MySQL中的常用函数有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

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

常用的函数有：

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

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

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

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

1. 使用字符串函数：

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

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

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

语法定义为：

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

示例：

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

concat_ws()的定义：

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

示例：

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

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

strcmp()定义为：

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

示例：

mysql>selectstrcmp('abc','abd'),strcmp('abc','abc'),strcmp('abc','abb');+---------------------+---------------------+---------------------+|strcmp('abc','abd')|strcmp('abc','abc')|strcmp('abc','abb')|+---------------------+---------------------+---------------------+|-1|0|1|+---------------------+---------------------+---------------------+1rowinset(0.00sec)

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

length()的定义如下：

length(str)

char_length(str)的定义如下：

char_length(str)

示例：

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

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

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

示例：

1.5 查找字符串：

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

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

函数定义为：

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

示例：

mysql>selectfind_in_set('mysql','oracle,mysql,db2');+-----------------------------------------+|find_in_set('mysql','oracle,mysql,db2')|+-----------------------------------------+|2|+-----------------------------------------+1rowinset(0.00sec)

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

函数定义为：

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

示例：

mysql>selectfield('mysql','oracle','db2','redis','mysql');+-----------------------------------------------+|field('mysql','oracle','db2','redis','mysql')|+-----------------------------------------------+|4|+-----------------------------------------------+1rowinset(0.00sec)

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

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

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

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

示例：

mysql>selectlocate('sql','mysql'),position('sql'in'mysql'),instr('mysql','sql');+-----------------------+----------------------------+----------------------+|locate('sql','mysql')|position('sql'in'mysql')|instr('mysql','sql')|+-----------------------+----------------------------+----------------------+|3|3|3|+-----------------------+----------------------------+----------------------+1rowinset(0.00sec)

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

函数语法为：

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

示例：

mysql>selectelt(1,'mysql','db2','oracle');+-------------------------------+|elt(1,'mysql','db2','oracle')|+-------------------------------+|mysql|+-------------------------------+1rowinset(0.00sec)

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

函数定义为：

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

示例：

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

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

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

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

函数定义为：

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

示例：

mysql>selectleft('mysql',2),right('mysql',3);+-----------------+------------------+|left('mysql',2)|right('mysql',3)|+-----------------+------------------+|my|sql|+-----------------+------------------+1rowinset(0.00sec)

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

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

函数语法为：

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

示例：

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

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

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

函数定义如下：

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

示例：

mysql>selectlength(concat('-','mysql','-')),length(concat('-',ltrim('mysql'),'-'));+-----------------------------------+------------------------------------------+|length(concat('-','mysql','-'))|length(concat('-',ltrim('mysql'),'-'))|+-----------------------------------+------------------------------------------+|9|8|+-----------------------------------+------------------------------------------+1rowinset(0.00sec)

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

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

示例：

mysql>selectlength(concat('-','mysql','-')),length(concat('-',rtrim('mysql'),'-'));+-----------------------------------+------------------------------------------+|length(concat('-','mysql','-'))|length(concat('-',rtrim('mysql'),'-'))|+-----------------------------------+------------------------------------------+|9|8|+-----------------------------------+------------------------------------------+1rowinset(0.00sec)

1.7.3 去除字符串首尾空格：

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

示例：

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.

示例：

mysql>selectinsert('这是mysql数据库系统',3,5,'oracle')bieming;+----------------------+|bieming|+----------------------+|这oracleql数据库系统|+----------------------+1rowinset,1warning(0.00sec)

1.8.1 使用replace()函数：

函数的定义为：

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

示例：

mysql>selectreplace('这是mysql数据库','mysql','db2')bieming;+---------------+|bieming|+---------------+|这是db2数据库|+---------------+1rowinset,1warning(0.00sec)

2. 使用数值函数：

2.1 获取随机数：

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

示例：

mysql>selectrand(),rand(),rand(3),rand(3);+--------------------+--------------------+--------------------+--------------------+|rand()|rand()|rand(3)|rand(3)|+--------------------+--------------------+--------------------+--------------------+|0.9600886758045188|0.7006410161970565|0.9057697559760601|0.9057697559760601|+--------------------+--------------------+--------------------+--------------------+1rowinset(0.00sec)

2.2 获取整数的函数：

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

ceil()函数的定义为：

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

示例：

mysql>selectceil(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|+-----------+------------+------------+-------------+1rowinset(0.00sec)

2.3 截取数值函数：

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

函数定义为：

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

示例：

mysql>selecttruncate(903.343434,2),truncate(903.343,-1);+------------------------+----------------------+|truncate(903.343434,2)|truncate(903.343,-1)|+------------------------+----------------------+|903.34|900|+------------------------+----------------------+1rowinset(0.00sec)

2.4 四舍五入函数：

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

示例：

mysql>selectround(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|+------------------+-------------------+--------------------+---------------------+1rowinset(0.00sec)

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

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

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

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

示例：

mysql>selectnow(),current_timestamp(),localtime(),sysdate();+---------------------+---------------------+---------------------+---------------------+|now()|current_timestamp()|localtime()|sysdate()|+---------------------+---------------------+---------------------+---------------------+|2016-08-2516:09:20|2016-08-2516:09:20|2016-08-2516:09:20|2016-08-2516:09:20|+---------------------+---------------------+---------------------+---------------------+1rowinset(0.00sec)

3.1.2 获取当前日期：

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

示例：

mysql>selectcurdate(),current_date();+------------+----------------+|curdate()|current_date()|+------------+----------------+|2016-08-25|2016-08-25|+------------+----------------+1rowinset(0.00sec)

3.1.3 获取当前时间：

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

示例：

mysql>selectcurtime(),current_time();+-----------+----------------+|curtime()|current_time()|+-----------+----------------+|16:15:04|16:15:04|+-----------+----------------+1rowinset(0.00sec)

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

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

示例：

mysql>selectnow(),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-2516:27:37|2016|3|8|34|25|16|27|37|+---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+1rowinset(0.00sec)

3.2.1 关于月的函数：

示例：

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

3.2.2 关于星期的函数：

示例：

mysql>selectnow(),week(now()),weekofyear(now()),dayname(now()),dayofweek(now()),weekday(now());+---------------------+-------------+-------------------+----------------+------------------+----------------+|now()|week(now())|weekofyear(now())|dayname(now())|dayofweek(now())|weekday(now())|+---------------------+-------------+-------------------+----------------+------------------+----------------+|2016-08-2516:34:35|34|34|Thursday|5|3|+---------------------+-------------+-------------------+----------------+------------------+----------------+1rowinset(0.00sec)

3.2.3 关于天的函数：

示例：

mysql>selectnow(),dayofyear(now()),dayofmonth(now());+---------------------+------------------+-------------------+|now()|dayofyear(now())|dayofmonth(now())|+---------------------+------------------+-------------------+|2016-08-2516:37:12|238|25|+---------------------+------------------+-------------------+1rowinset(0.00sec)

3.2.4 获取指定值的extract()：

函数定义为：

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

示例：

mysql>selectnow(),extract(yearfromnow())year,extract(monthfromnow())month,extract(dayfromnow())day,extract(hourfromnow())hour,extract(minutefromnow())minute,extract(secondfromnow())second;+---------------------+------+-------+------+------+--------+--------+|now()|year|month|day|hour|minute|second|+---------------------+------+-------+------+------+--------+--------+|2016-08-2516:43:45|2016|8|25|16|43|45|+---------------------+------+-------+------+------+--------+--------+1rowinset(0.00sec)

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

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

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

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

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

示例：

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

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秒后的时间。

示例一：

mysql>selectcurdate(),adddate(curdate(),5),subdate(curdate(),5);+------------+----------------------+----------------------+|curdate()|adddate(curdate(),5)|subdate(curdate(),5)|+------------+----------------------+----------------------+|2016-08-25|2016-08-30|2016-08-20|+------------+----------------------+----------------------+1rowinset(0.00sec)

示例二：

mysql>selectcurdate(),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|+------------+----------------------------------------------+----------------------------------------------+1rowinset(0.00sec)

示例三：

mysql>selectcurtime(),addtime(curtime(),5),subtime(curtime(),5);+-----------+----------------------+----------------------+|curtime()|addtime(curtime(),5)|subtime(curtime(),5)|+-----------+----------------------+----------------------+|17:12:21|17:12:26|17:12:16|+-----------+----------------------+----------------------+1rowinset(0.00sec)

4. 使用系统信息函数：

selectversion(),database(),user();

示例：

以上是“MySQL中的常用函数有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！