盘点MySQL中常用的函数

一、介绍

在平常使用MySQL的过程中，我们常常会使用到其中的函数。有些函数常用，就会非常熟悉，但有些不经常使用就会十分生疏。

避免使用到的时候，到处去翻博客，干脆自己总结，记录一下，顺便也加深一下自己的印象。

二、函数

1）聚合函数

聚合函数又称为组函数，一般结合分组进行使用，用来统计和计算。

聚合函数在平常的sql中十分常用，不需要查阅文档就应该保证熟练使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- avg 计算平均值
select avg(age) from tb_user;

-- count，计算行数
select count(1) from tb_user;
select count(*) from tb_user;
select count(name) from tb_user;

-- max，最大数
select max(age) from tb_user;

-- min，最小数
select min(age) from tb_user;

-- sum，求和
select sum(age) from tb_user;

-- group_concat 分组聚合，以逗号分隔
select sex, group_concat(name) from tb_user group by sex;

需要注意的是，聚合函数不会对null值进行计算。由于它会略过null值，具体业务情况需要酌情分析。

2）字符串函数

在处理字符串上，MySQL也有一套自己的处理函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- concat 合并字符串
select concat('000', '111', '222');-- 000111222
select concat('000', null, '222');-- null

-- 对于合并字符串，还可以这样写，在字符串中间用空格隔开。不推荐，可读性不是很好
select '000' '111' '222';-- 000111222

-- concat_ws，合并字符串，与上面不同的是，此处第一个参数是分隔符
select concat_ws('-', '000', '111', '222');-- 000-111-222
select concat_ws(null, '000', '111', '222');-- null
select concat_ws('-', '000', null, '222');-- 000-222

-- length，返回字符串的字节长度
select length('0123456');-- 7
select length('半月无霜');-- 12

-- reverse，反转字符串
select reverse('0123456');-- 6543210
select reverse('半月无霜');-- 霜无月半

-- lower，将字符串中的所有字母都变成小写
select upper('ABCD');-- abcd

-- upper，将字符串中的所有字母都变成大写
select upper('abcd');-- ABCD

-- left，从左到右进行截取指定数量的字符
select left('0123456', 2);-- 01

-- right，从右到左进行截取指定数量的字符
select right('0123456', 2);-- 56

-- lpad，从字符串左边进行填充，直到长度到达指定字符长度
select lpad('1', 5, '0');-- 00001

-- rpad，从字符串右边进行填充，直到长度到达指定字符长度
select rpad('1', 5, '0');-- 10000

-- ltrim，去除字符串右边的空格
select ltrim('   1000');-- 1000

-- rtrim，去除字符串右边的空格
select rtrim('1000   ');-- 1000

-- trim，去除字符串左右两边的空格
select trim('  1000  ');-- 1000

-- repeat，将字符串重复指定的次数
select repeat('1000', 3);-- 100010001000

-- replace，将字符串替换其中指定的字符
select replace('我喜欢你', '喜欢', '讨厌');-- 我讨厌你

-- strcmp，字符串比较
select strcmp('abc', 'abc');-- 0，相等返回0
select strcmp('abc', 'abcd');-- -1，前字符串<后字符串 返回1
select strcmp('abcd', 'abc');-- 1，前字符串>后字符串 返回-1
select strcmp('abc', null);-- null，有null值计算，返回null

-- substring，截取字符串，从指定的位置开始，截取指定数量的字符
select substring('0123456', 2, 1);-- 1
select substring('0123456', 2, 4);-- 1234

-- insert，替换字符串，将从指定的位置开始，到指定的数量的字符串，替换为新字符串
select insert('0123456', 2, 1, 'A');-- 0A23456
select insert('0123456', 2, 4, 'ABCD');-- 0ABCD56

3）数字计算函数

除去加减乘除的符号外，数字计算上，MySQL也提供了一些常用的数学计算函数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- abs，绝对值
select abs(-1);-- 1
select abs(1);-- 1

-- sqrt, 开平方
select sqrt(4);-- 2
select sqrt(9);-- 3

-- mod，求余数
select MOD(9, 2);-- 1

-- ceil、ceiling，返回整数，向上取整
select ceil(3.14);-- 4
select ceiling(3.14);-- 4

-- floor，返回整数，向下取整
select floor(3.14);-- 3

-- round，四舍五入
select round(3.14);-- 3
select round(2.76);-- 3
select round(2.8828, 2);-- 2.88
select round(2.8828, 3);-- 2.883

-- rand，返回一个0-1之间的随机数
select rand();-- 0.5440051923915792

-- sign，返回参数的符号，负整数返回-1，正整数返回1，0返回0
select sign(0);-- 0
select sign(88);-- 1
select sign(-7);-- -1

-- pow、power，幂次方
select pow(2, 3);-- 8
select power(2, 3);-- 8

-- pi，圆周率π
select pi();-- 3.141593

-- sin，正弦值
select sin(0.5*pi());-- 1
select sin(1);-- 0.8414709848078965

-- asin，反正弦值
select asin(0.8414709848078965)-- 1
select asin(2);-- null

-- cos，余弦值
select cos(0);-- 1
select cos(pi());-- -1
select cos(1);-- 0.5403023058681398

-- acos，反余弦值
select acos(1);-- 0
select acos(-1);-- 3.141592653589793
select acos(2);-- null

-- tan，正切值
select tan(0);-- 0
select tan(1);-- 1.5574077246549023

-- atan，反正切值
select atan(0);-- 0
select atan(1, 1);-- 0.7853981633974483

-- cot，余切值
select cot(1);-- 0.6420926159343306

4）流程控制函数

就是判断啦

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- if，判断函数
select if(88>60, '合格', '不合格');-- 合格
select if(59>60, '合格', '不合格');-- 不合格

-- ifnull，判断是否为空
select ifnull(null, 0);-- null
select ifnull(55, 0);-- 55

-- case when，也是个流程控制的，但不属于函数
select case 
	when 80>90 then '优秀' 
	when 80>70 then '良好' 
	when 80>60 then '一般' 
	else '差劲' end as rate;-- 良好

5）日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- curdate、current_date，返回当前日期
select curdate();-- 2022-05-01
select current_date();-- 2022-05-01

-- curtime、current_time，返回当前时间
select curtime();-- 22:04:30
select 、();-- 22:04:30

-- now、sysdate，返回当前日期时间
select now();-- 2022-05-01 22:05:25
select sysdate();-- 2022-05-01 22:05:25

-- unix_timestamp，返回当前unix时间戳
select unix_timestamp();-- 1651414009

-- from_unixtime，格式化unix时间戳
select from_unixtime(1651414009);-- 2022-05-01 22:06:49
select from_unixtime(1651414009, '%Y-%m-%d');-- 2022-05-01

-- year，返回指定日期的年份
select year(now());-- 2022

-- month，返回指定日期的月份
select month(now());-- 5

-- monthname，返回指定日期的月份英文名
select monthname(now());-- May

-- day、dayofmonth，返回指定日期是一月中的第几天
select day(now());-- 1
select dayofmonth(now());-- 1

-- dayofyear，返回指定日期是一年中的第几天
select dayofyear(now());-- 121

-- dayofweek，返回指定日期在一周的第几天，星期天是第一天，星期一是第二天
select dayofweek('2022-05-01 10:00:00');-- 1
select dayofweek('2022-05-02 10:00:00');-- 2

-- weekday，返回指定日期在一周内的索引值，星期一是0，星期日是6
select weekday('2022-05-01 10:00:00');-- 6
select weekday('2022-05-02 10:00:00');-- 0

-- week，返回指定日期是一年中的第几周
select week(now());-- 18

-- dayname，返回指定日期是星期几英文名
select dayname(now());-- Sunday

-- time_to_sec，返回指定时间的秒数
select time_to_sec('00:00:11');-- 11

-- sec_to_time，返回指定秒数的时间
select sec_to_time(11);-- 00:00:11

-- date_add、adddate，将指定日期添加指定的时间间隔
select date_add('2022-05-01 10:00:00', INTERVAL 30 SECOND);-- 2022-05-01 10:00:30
select date_add('2022-05-01 10:00:00', INTERVAL '30:30' MINUTE_SECOND);-- 2022-05-01 10:30:30
select date_add('2022-05-01 10:00:00', INTERVAL 30 MINUTE);-- 2022-05-01 10:30:00
select date_add('2022-05-01 10:00:00', INTERVAL 2 HOUR);-- 2022-05-01 12:00:00
select date_add('2022-05-01 10:00:00', INTERVAL 2 DAY);-- 2022-05-03 10:00:00
select date_add('2022-05-01 10:00:00', INTERVAL 1 MONTH);-- 2022-06-01 10:00:00
select date_add('2022-05-01 10:00:00', INTERVAL 1 YEAR);-- 2023-05-01 10:00:00

-- date_sub、subdate，将指定日期减去指定的时间间隔
select date_sub('2022-05-01 10:00:00', INTERVAL 30 SECOND);-- 2022-05-01 09:59:30
select date_sub('2022-05-01 10:00:00', INTERVAL '30:30' MINUTE_SECOND);-- 2022-05-01 09:29:30
select date_sub('2022-05-01 10:00:00', INTERVAL 30 MINUTE);-- 2022-05-01 09:30:00
select date_sub('2022-05-01 10:00:00', INTERVAL 2 HOUR);-- 2022-05-01 08:00:00
select date_sub('2022-05-01 10:00:00', INTERVAL 2 DAY);-- 2022-04-29 10:00:00
select date_sub('2022-05-01 10:00:00', INTERVAL 1 MONTH);-- 2022-04-01 10:00:00
select date_sub('2022-05-01 10:00:00', INTERVAL 1 YEAR);-- 2021-05-01 10:00:00

-- addtime，将指定的日期添加指定的时间
select addtime('2022-05-01 10:00:00', '01:30:30');-- 2022-05-01 11:30:30

-- subtime，将指定的日期添加指定的时间
select subtime('2022-05-01 10:00:00', '01:30:30');-- 2022-05-01 08:29:30

-- datediff，返回两个日期间相差的天数，参数1-参数2的日期
select datediff('2022-05-01 10:00:00', '2022-05-02 10:00:00');-- -1
select datediff('2022-05-01 23:59:59', '2022-05-02 00:00:00');-- -1

-- date_format，返回日期格式化
select date_format(now(), '%Y-%m-%d');-- 2022-05-01
select date_format(now(), '%Y-%m-%d %T');-- 2022-05-01 23:27:51
select date_format(now(), '%Y-%m-%d %H:%i:%s');-- 2022-05-01 23:27:51

格式化参数有

参数 说明
%a 工作日的缩写名称（Sun~Sat)
%b 月份的缩写名称（Jan…Dec)
%c 月份，数字形式（0~12)
%D 带有英语后缀的该月日期(0th, 2st, 3nd,…）
%d 该月日期，数字形式（00~31)
%e 该月日期，数字形式（(0~31)
%f 微秒（000000 …999999)
%H 以 2 位数表示 24 小时（00~23)
%h, %I 以 2 位数表示 12 小时（01~12)
%i 分钟，数字形式(00~59)
%j —年中的天数（001~366)
%k 以 24 小时（0~23)表示
%l 以12小时（1~12)表示
%M 月份名称（January~December)
%m 月份，数字形式（00~12)
%p 上午（AM) 或下午（PM)
%r 时间，12小时制（小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM)
%S, %s 以 2 位数形式表示秒（00~59)
%T 时间，24 小时制（小时 (hh): 分钟 (mm): 秒数 (ss))
%U 周（00~53)，其中周日为每周的第一天
%u 周（00~53)，其中周一为每周的第一天
%V 周（01~53)，其中周日为每周的第一天，和％X同时使用
%v 周（01~53)，其中周一为每周的第一天，和%x同时使用
%W 星期标识（周日、周一、周二…周六）
%w —周中的每日（0= 周日…6= 周六）
%X 该周的年份，其中周日为每周的第一天，数字形式，4 位数，和％V同时使用
%x 该周的年份，其中周一为每周的第一天，数字形式，4位数，和%v同时使用
%Y 4 位数形式表示年份
%y 2 位数形式表示年份
%% %一个文字字符

6）类型转换函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- cast、convert，类型转换函数
-- 转换为浮点
select cast('2022.87' as DECIMAL);-- 2023
select cast('2022.87' as DECIMAL(11, 2));-- 2022.87
-- 转换为整数
select cast('2022.87' as SIGNED);-- 2022
select cast('-2022.87' as SIGNED);-- -2022
select cast('2022.87' as UNSIGNED);-- 2022
select cast('-2022.87' as UNSIGNED);-- 18446744073709549594，无符号整数类型不支持负数
-- 字符串转为日期、时间、日期时间，格式必须是yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss
select cast('2022-05-01 10:00:00' as DATE);-- 2022-05-01
select cast('2022-05-01 10:00:00' as TIME);-- 10:00:00
select cast('2022-05-01 10:00:00' as DATETIME);-- 2022-05-01 10:00:00
-- 数字转为字符串
select cast(123 as CHAR);-- 123
select cast(123 as BINARY);-- 123

-- convert，字符集转换
select convert('abc' USING utf8);-- abc
select convert('你好' USING latin1);-- 乱码

-- 不是函数，但可以使用加号类解决
select '123' + 0;-- 123，数字类型

-- concat、可以使数字合并为字符串
select concat(0, '');-- 0，字符串类型

-- date_format、可以使日期时间格式化为字符串

7）系统函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- version，版本号
select version();-- 5.7.32-log

-- connection_id，连接id
select connection_id();-- 119394

-- user、system_user、session_user，连接的用户
select user();-- banmoon@61.140.236.57
select system_user();-- banmoon@61.140.236.57
select session_user();-- banmoon@61.140.236.57

-- current_user，连接允许的主机
select current_user();-- banmoon@%

-- database、schema，
select database();-- test
select schema();-- test

-- inet_ntoa，将IPv4地址的dotted-quad表示形式作为字符串，并以整数形式返回给定IP地址的数值
select inet_aton('192.168.0.101');-- 3232235621
select inet_aton('-111');-- null

-- inet_ntoa，以网络字节顺序获取IPv4地址，然后以dotted-quad字符串表示形式返回该地址
select inet_ntoa('3232235621');-- 192.168.0.101
select inet_ntoa('-222');-- null

-- last_insert_id，获取上次插入的主键
select last_insert_id();-- 0

-- md5，md5加密
select md5('123456');-- e10adc3949ba59abbe56e057f20f883e

-- password，加密
select password('123456');-- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

-- encode，加密
select encode('123456', 'abc');

-- decode，解密
select decode(encode('123456', 'abc'), 'abc')-- 123456

三、最后

本来是想将函数的定义也放在这里的，但想了想还是分开吧，自定义函数可以和存储过程一起出。

我是半月，祝你幸福！！！