MySQL全面瓦解13：系统函数相关

提到MySQL的系统函数，我们前面有使用过聚合函数，其实只是其中一小部分。MySQL提供很多功能强大、方便易用的函数，使用这些函数，可以极大地提高用户对于数据库的管理效率，并更加灵活地满足不同用户的需求。

本文将MySQL的函数分类并汇总，以便提供后面使用中的参考。

数值类型函数

说明

abs

返回绝对值

sqrt

返回二次方根

mod

返回余数

ceil/ceiling

两个函数功能一致，返回不小于参数的最小整数，即向上取整

floor

向下取整，返回值转化为bigint

rand

生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列

round

对所传参数进行四舍五入

sign

返回参数的符号（正、负）

pow/power

两个函数的功能相同，返回所传参数的次方的结果值

sin

求正弦值

asin

求反正弦值，与函数 SIN 互为反函数

cos

求余弦值

acos

求反余弦值，与函数 COS 互为反函数

tan

求正切值

atan

求反正切值，与函数 TAN 互为反函数

cot

求余切值

使用ABS(number)的目的是返回 number的绝对值，如果是正值则返回是是它本身，负值则返回相反的正值，0则返回0。

使用SQRT(number)函数，返回的是number的开方根。这边需要注意，负数无法开方，所以返回的是null，如下所示。

MOD(number1,number2) 返回 number1 除以number2的余数，包含小数的数值同样有效，如下，9%4.5=0，18.3%9=0.3：

使用CEIL(number)和CEILING(number)一个意思，返回大于等于number的最小整数值。

这边需要注意，返回的类型是bigint，做存储或计算的时候需要注意数据类型匹配。

与上面ceil正好相反，floor(number) 返回的是小于 number 的最大整数值。

生成0~1之间的随机数。如果传入整数参数，则会产生重复序列，再次调用还是这个随机数，如下图，第3、5、7个是重复序列。

函数round(number1,number2)，指的是对给定的值number1进行四舍五入的取值过程，number2是指定保留小数后的位数，为负数时，则是指定保留小数前的位数。

round(78.78,-1)按照个位数取整，为80；round(78.78,-2)按照百位数取整，为100。

这样需要注意，当你的值为负数时，返回的是-1，当你的值为正数时候，返回的是1，当为0时，返回0。

函数pow/power(number1,number2),用于计算 number1 的 number2 次方，number2可以为负数，为负数时，在次方基础上要再取倒数。

如上，pow(10,-2) = 1 / pow(10,2)=0.01;

sin为正弦值，cos为余弦值，这个我们数学学过了，我们也学过sin(x+y)=sin(x)*cos(y)+ cos(x)*sin(y),一起验证下，如下：

其他三角函数可以如法炮制测试下，都是我们学过的数学知识。

字符类型函数

length

计算字符串长度函数，返回字符串的字节长度

concat

合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个

insert

替换字符串函数

lower

将字符串中的字母转换为小写

upper

将字符串中的字母转换为大写

left

从左侧字截取符串，返回字符串左边的n个字符

right

从右侧字截取符串，返回字符串右边的n个字符

trim

删除字符串左右两侧的空格

replace

字符串替换函数，返回替换后的新字符串

substr/substring

截取字符串，返回从指定位置开始的指定长度的字符换

reverse

字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

统计字符串的字节长度，一这边需要注意，单个数字或者字符是一个字节，汉字（使用utf-8编码格式）是三个字节，字符串中的空格也占据一个字节。

CONCAT(str1,str1,…) 函数，参数1个或者n个，返回值会将参数合并的结果返回回来，这边需要注意的是，如果有一个值是null的，正整个结果都是null值。

INSERT(str1,index,len,str2) 指str1字符串的index位置开始的len长度的字符用str2来替换。

从上面的语句可以总结以下几点：

1、index指的并非是索引位置而是实际位置，他是从1开始计算的，所以是字符串的索引+1，如第一个。

2、如果index超过字符串最大位置，则返回原值，如第二个。

3、如果len超过字符串长度，是允许的，并且index之后的内容都会被替换。

4、四个参数中只要有一个值为null，系统认为函数调用有问题，同样返回null给你。

upper指的是把字符串转换成大写，lower指的是把字符串转为小写。

LEFT(str,num)、RIGHT(str,num) 函数返回字符串 str 最左边或者最右边的 num 个字符，num小于等于0的时候返回空。

TRIM(str)：删除str左右空格；LTRIM(str)：只删除字符串左边的空格；RTRIM(Str)：删除字符串右边的空格。下面的例子简单明了：

REPLACE(str,a1,a2) ，对于字符串 str ，出现的所有a1都使用a2来替换。

一种方式是：substr(str1,index,len)，截取字符串str1从位置 index 开始的len长度的子字符串。

从上面的4个语句可以总结以下几点：

2、如果是index是负数，则从右开始算，即倒数，如substr('Brand',-4,2)，则从右数第四个字符，即r，然后取之后的2个字符，即ra。

另一种方式是：substr(str from index for len)，同理，是截取字符串str从位置 index 开始的len长度的子字符串。

REVERSE(str) 指的是将原字符串 str 直接反序显示，比如abc，反序为cba：

日期类型函数

curdate/current_date

两个函数作用相同，返回当前系统的日期值

curtime/current_time

两个函数作用相同，返回当前系统的时间值

now/sysdate

两个函数作用相同，返回当前系统的日期和时间值

unix_timestamp

获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数

from_unixtime

将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数

month

获取指定日期中的月份

monthname

获取指定日期中的月份英文名称

dayname

获取指定曰期对应的星期几的英文名称

dayofweek

获取指定日期是一周中是第几天，返回值范围是1~7,1=周日

week

获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53

dayofyear

获取指定曰期是一年中的第几天，返回值范围是1~366

dayofmonth

获取指定日期是一个月中是第几天，返回值范围是1~31

year

获取年份，返回值范围是 1970〜2069

time_to_sec

将时间参数转换为秒数

sec_to_time

将秒数转换为时间，与TIME_TO_SEC 互为相反

date_add/adddate

两个函数功能相同，都是向日期添加指定的时间间隔

date_sub/subdate

两个函数功能相同，都是向日期减去指定的时间间隔

addtime

时间加法运算，在原始时间上添加指定的时间

subtime

时间减法运算，在原始时间上减去指定的时间

datediff

获取两个日期之间间隔，返回参数 1 减去参数 2 的值

date_format

格式化指定的日期，根据参数返回指定格式的值

weekday

获取指定日期在一周内的对应的工作日索引

返回当前所在服务器的系统日期，当以字符串方式返回的时候，格式为"YYYY-MM-DD"，当以数值方式返回的时候，格式为"YYYYMMDD"，如下面+0后得到 20201128：

返回当前所在服务器的系统时间，当以字符串方式返回的时候，格式为"HH:MM:SS"，当以数值方式返回的时候，格式为"HHMMSS"，如下面+0后得到 103002：

同理返回系统日期+时间，格式为"YYYY-MM-DD HH:MM:SS" 或者 "YYYYMMDDHHMMSS"，根据不同场景返回对应格式。

unix_timestamp(date)，里面的date是可选参数，无参的时候等同于获得当前系统时间的时间戳：

FROM_UNIXTIME(timestamp[,format])  与上面正好相反，把时间戳数据进行处理，并返回日期时间的格式，

参数timestamp是时间戳，参数format是格式，有%Y %m  %d %H之类分别来代表年月日时分秒等，如下

格式

%M

月名字(January～December)

%W

星期名字(Sunday～Saturday)

%D

有英语前缀的月份的日期(1st, 2nd, 3rd, 等等）

%Y

年, 数字, 4 位

%y

年, 数字, 2 位

%a

缩写的星期名字(Sun～Sat)

%d

月份中的天数, 数字(00～31)

%e

月份中的天数, 数字(0～31)

%m

月, 数字(01～12)

%c

月, 数字(1～12)

%b

缩写的月份名字(Jan～Dec)

%j

一年中的天数(001～366)

%H

小时(00～23)

%k

小时(0～23)

%h

小时(01～12)

%I

%l（小写的L）

小时(1～12)

%i

分钟, 数字(00～59)

%r

时间,12 小时(hh:mm:ss [AP]M)

%T

时间,24 小时(hh:mm:ss)

%S

秒(00～59)

%s

%p

AM或PM

一个星期中的天数英文名称(Sunday~Saturday）

%w

一个星期中的天数(0=Sunday ～6=Saturday）

%U

星期(0～52), 这里星期天是星期的第一天

%u

星期(0～52), 这里星期一是星期的第一天

%%

输出%

MONTH(date) 函数：data为必填参数，返回date对应的月份，范围为 1～12。

MONTHNAME(date) 函数：date为必填参数，返回对应的月份名称。

DAYNAME(date)：返回的是指定日期的对应星期名称，比如今天周六就是Saturday.

DAYWEEK(date)：返回date对应的数值，这边可以看到周六返回的是7，这个是正确的，因为是从周日开始算的，周日是1，周一是2，...  ，周六是7。如下图：

星期几？

对应数值

星期日

星期一

1

星期二

2

星期三

3

星期四

4

星期五

5

星期六

6

7

WEEK(date[,mode]) 函数：返回给定date 属于一年中的第几周。它包含两个参数：

data是指定时间，在它所在年的第几周。

mode为可选参数，如下面这个表，用于确定周数计算的逻辑。指定本周是从星期一还是星期日开始，返回的周数应在<code>0</code>到<code>52</code>之间或<code>0</code>到<code>53</code>之间。 

模式

星期的第一天

范围

星期 1 是第一天

Sunday

0-53

一年中多一个星期天

Monday

一年多3天

1-53

一年中多一个星期一

因为是可选参数，所以如果默认情况下WEEK函数将使用default_week_format系统变量的值。不同人的系统参数配置可能不一样，可以看看自己的配置是什么：这边查出是0，则代表从星期的第一天为sunday。

我们做个测试：

TIME_TO_SEC(time) 函数将参数 time 转换为秒数的时间值，公式：" h×3600+ m ×60+ s"。

SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为时、分、秒时间值。

日期时间加法函数：DATE_ADD(date,INTERVAL expr type)，包含两个参数：

date：参数是日期格式。expr 参数是时间间隔。

type：时间间隔类型，参数如下

type字段的类型

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

测试一下：分别输出间隔一天、一小时、一分钟的时间：

也可以为负数，负数则为相反的意思：

DATE_SUB(date,INTERVAL expr type)，参数与上面日期加法一致，测试一下，分别减去1年、1时、1分：

ADDTIME(time,expr)、SUBTIME(time,expr) 函数用于执行时间的加减法运算。

参数time： 是一个时间或日期时间表达式

参数expr： 是一个时间表达式

测试一下：

获取两个日期的间隔，因为只计算日期部分，所以实际是第一个日期减去第二个日期的差额天数，测试一下：

DATE_FORMAT(date，format) 函数：将我们的日期进行格式化显示。

包含两个参数：

date参数：要进行格式化的日期值

format参数：格式符号，这个可以参考上面那个时间戳格式化的那个表格。

注意与dayofweek的区别，dayofweek是周天为1，周一到周六为2~7。而WEEKDAY(date) 返回date的周索引(0=周一，1=周二, ……6= 周天)。

今天是周六，测试一下：

这个在分组函数那一章学习过了，大家可以参考下：MySQL全面瓦解10：分组查询和聚合函数

后续会有专门的章节进行详解。

mysql的系统函数还是比较强大的，一个个验证写了快一天，泪崩，如果能熟练使用到我们开发中会事半功倍。这篇分类清晰，可以当作参考工具使用。

架构与思维·公众号：撰稿者为bat、字节的几位高阶研发/架构。不做广告、不卖课、不要打赏，只分享优质技术

码字不易，欢迎关注，欢迎转载

作者：翁智华

出处：https://www.cnblogs.com/wzh2010/

本文采用「CC BY 4.0」知识共享协议进行许可，转载请注明作者及出处。