【收藏夹吃灰系列】MySQL常用函数细致总结

前言

这是我参与11月更文挑战的第1天，活动详情查看：2021最后一次更文挑战。MySQL 常用函数不熟练？看我这篇就够了！！

▶ MySQL 常用函数总览

MySQL 常用函数.png

★ 字符串函数

LENGTH(str)

掌握指数：★★★★

函数说明：

返回 str 字符串字节长度

注意：

英文的一个字符为 1 个字节
GBK 编码的为 2 个字节
UTF-8 编码的汉字为 3 个字节

SQL 语句示例：


# 英文字符串字节长度
select length('HUALEI'); # 6

select length('大家好'); # 9
复制代码

INSERT(str,pos,len,newstr)

掌握指数：★★★

函数说明：

向 str 中第 pos 位置开始插入长度为 len 的 newStr 字符串

SQL 语句示例：

如果我想给 str = 'HUALEI' ，想用 insert() 函数将其拼接成 "HUALEI is a hansome boy."，那我该怎么做呢？


select insert('HUALEI', length('HUALEI')+1, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI

or

select insert('HUALEI', length('HUALEI')+2, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI

but answer:

select insert('HUALEI  ', length('HUALEI')+2, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI is a hansome boy.
复制代码

注意： MySQL 中从 1 开始算下标，插入要满足 pos <= length(str) 。

那如果是中文汉字字符串呢，如何让 “青花瓷” 变成 “青花烤瓷”，“苏格拉底广场” 变成 “苏格拉底广场舞” ？

    
select insert('青花瓷', 3, length('烤瓷'), '烤瓷'); # 青花烤瓷

select insert('苏格拉底广场', 7, length('舞'), '舞'); # 苏格拉底广场舞
复制代码

第一个 SQL 中 pos => 3 为汉字字符串的下标索引，从第三个汉字字符开始插入长度为 length('烤瓷') 的 '烤瓷' ；再看第二个 SQL， 7 大于字符个数，但是并不大于 length('苏格拉底广场') => 18 ，所以新字符串在最后一个位置上插入成功。

LEFT(str,len)

掌握指数：★★★

函数说明：

从字符串 str 左边开始截取长度为 len 的字符串

SQL 语句示例：


select left('HUALEI', 3); # HUA
复制代码

那 “大家好” 这个汉字字符串我想左截取出 “大家”，该如何做呢？


select left('大家好', length('大家')); # 大家好
复制代码

奇怪，截取的结果和预期不一样，为什么呢？

注意： 这里的 len 还是并不是通过 length() 而是通过 char_length() 得到的，从 1 开始到 len 即为截取的目标字符串。

所以，对于中文字符串来说，正确的写法应该是：

select left('大家好', 2); # 大家

RIGHT(str,len)

掌握指数：★★★

函数说明：

从字符串 str 右边开始截取长度为 len 的字符串

SQL 语句示例：


select right('HUALEI', 3); # LEI

select right('大家好', 1); # 好
复制代码

不赘述，和 LEFT() 一个道理。

SUBSTR(str FROM pos) <=> SUBSTR(str,pos) SUBSTR(str FROM pos FOR len) <=> SUBSTR(str,pos,len)

掌握指数：★★★★

函数说明：

从 str 的第 pos 个位置开始截取长度为 len 的子串，如果没有 len 参数则截取到末尾

SQL 语句示例：


select substr('abcdefg' from 2); # bcdefg
# 等价于
select substr('abcdefg', 2); # bcdefg

select substr('abcd' from 1 for 3); # abc
# 等价于
select substr('abcd', 1, 3); # abc

# 这里的 len 理解为从 pos 开始截取几个汉字
select substr('大家好', 2, 1); # 家
复制代码

STRCMP(expr1,expr2)

掌握指数：★★★

函数说明：

expr1 > expr2 => 返回 1；expr1 = expr2 => 返回 0；expr1 < expr2 => 返回 -1

SQL 语句示例：


select strcmp('bbcd','bacd'); # 对应位置上的字符对应相比，返回 1

select strcmp('你好', '你好'); # 两个字符串一模一样，返回 0

select strcmp('ABC','abc'); # 不区分大小写，返回 0

select strcmp('abadf','abadfe'); # 后面大，返回-1
复制代码

CONCAT(str1,str2,...)

掌握指数：★★★★

函数说明：

将 str1、str2 ... 等字符串连接成一个新的字符串

SQL 语句示例：


select concat('hel','llo'); # hello

select concat('大家','好'); # 大家好
复制代码

注意： 只要连接的字符串中存在一个 null 值，最终结果也将是 null 。


select concat(null, 'abc'); # null

select concat('abc',null);  # null
复制代码

LOCATE(substr,str) | POSITION(substr IN str) | INSTR(str,substr)

掌握指数：★★★★

函数说明：

返回子串 substr 在父串 str 中的开始位置，如果父串中压根就不包含子串那么返回 0

SQL 语句示例：


select locate('LEI', 'HUALEI'); # 子串 'LEI' 在父串 'HUALEI' 中的开始位置是 4
select locate('LEI ', 'HUALEI'); # 子串不存在，返回 0

select position('LEI' in 'HUALEI'); # 子串 'LEI' 在父串 'HUALEI' 中的开始位置是 4
select position('LEI ' in 'HUALEI'); # 子串不存在，返回 0

select instr('HUALEI', 'LEI'); # 4

select instr('HUALEI', 'LEI '); # 0
复制代码

小结：

三个函数都能拿到子串在父串中的开始位置
locate() 和 position() 函数基本类似，就是参数列表不同，后者使用 in 表示子串在父串里面中的 position 位置，写起来比较好理解，更推荐使用
instr() 和 locate() 唯一不同的就是参数位置交换了，注意别搞混淆写反了

LOWER(str) | UPPER(str)

掌握指数：★★★

函数说明：

将 str 字符串全部小写（LOWER）/大写（UPPER）

SQL 语句示例：


# 小写化
select lower('HUALEI'); # hualei

# 大写话
select upper('hualei'); # HUALEI
复制代码

注意： 仅对英文字符串有效，中文字符串无效。


select lower('大家好，我是 HUALEI'); # 大家好，我是 hualei
复制代码

LTRIM(str) | RTRIM(str) | TRIM([remstr FROM] str)

掌握指数：★★★★

函数说明：

去除 str 字符串中的空格

SQL 语句示例：


select ltrim('   HUALEI'); # HUALEI

select rtim('大家好！  '); # 大家好！

select trim('  HUALEI  '); # HUALEI
复制代码

注意： trim() 函数只会去除 str 字符串前后的空格并不会去除所有空格！


select trim('   HUA LEI   '); # HUA LEI
复制代码

REPEAT(str,count)

掌握指数：★★

函数说明：

返回 str 重复 count 遍后的结果

SQL 语句示例：


select repeat('HUALEI ', 5); # HUALEI HUALEI HUALEI HUALEI HUALEI 

select repeat('雷猴啊', 2); # 雷猴啊雷猴啊
复制代码

REVERSE(str)

掌握指数：★★

函数说明：

将字符串 str 按倒序反过来

SQL 语句示例：


select reverse('我被反过来了'); # 了来过反被我

select reverse('HUALEI'); # IELAUH
复制代码

RPAD(str,len,padstr) | LPAD(str,len,padstr)

掌握指数：★★★

函数说明：

指定 str 字符串长度 len， len > length(str) 不足用 padstr 向右/左填充；len < length(str) 充足则根据指定长度进行截取。

SQL 语句示例：


select rpad('大家', 3, '好'); # 大家好

select rpad('HUALEI', length('HUALEI')+length(' NB'), ' NB'); # HUALEI NB

select length('大家好'); # 9

select lpad('SQL', 3, 'My'); # SQL

select lpad('SQL', length('MySQL'), 'My'); # MySQL
复制代码

★ 数学函数

FORMAT(X,D) | ROUND(X) | ROUND(X,D)

掌握指数：★★★★

函数说明：

对 X 四舍五入保留小数点后 D 位

SQL 语句示例：


select format(3.1415926,3); # 3.142
# 等价于
select ROUND(3.1415926, 3); # 3.142

# 无保留位数时，取整
select round(3.1415926); # 3
复制代码

CEIL(X) | FLOOR(X)

掌握指数：★★★★

函数说明：

ceil 天花板（向更大值方向）取整；floor 地板（向更小值方向）取整

SQL 语句示例：


# 向上取整
select ceil(3.5); # 4
select ceil(-3.5); # -3

# 向下取整
select floor(3.5); # 3
select floor(-3.5); # -4
复制代码

MOD(N,M)

掌握指数：★★★

函数说明：

取 N / M 的余数，等价于 N % M

SQL 语句示例：


select mod(10, 3); # 1
# 等价于
select 10 % 3; # 1
复制代码

POW(X,Y) | POWER(X,Y)

掌握指数：★★★

函数说明：

返回 X 的 Y 次方

SQL 语句示例：


select pow(2, 10); # 2^10 = 1024
# 也可以写作
select power(2, 10); # 2^10 = 1024
复制代码

SQRT(X)

掌握指数：★★★

函数说明：

返回 X 的平方根，也就是对 X 开平方

SQL 语句示例：


select sqrt(100); # 10

select sqrt(2); # 根号 2 => 1.4142135623730951
复制代码

GREATEST(expr1, expr2, expr3, …) | LEAST(expr1, expr2, expr3, …)

掌握指数：★★★

函数说明：

返回参数列表中最大/最小值

注意： 参数列表可以是字符序列。

SQL 语句示例：


select greatest(1,2,3,4,51,6,7,8); # 51

select greatest('Java', 'MySQL', 'JavaScript'); # MySQL

select least(-1,2,3,4,5,6,7,8); # -1

select least('Java', 'MySQL', 'JavaScript'); # Java
复制代码

RAND()

掌握指数：★★★★★

函数说明：

返回 (0, 1) 之间的随机数

SQL 语句示例：


select rand(CURRENT_TIMESTAMP); # 给定种子值，这里给当前时间戳作为值，保证相对随机

select format(rand()*100, 0); # 产生 (0, 100) 之间的随机整数
select round(rand()*100); # 产生 (0, 100) 之间的随机整数

select ... order by rand() limit N; # 随机查询取 N 条记录
复制代码

聚合函数 MAX(expr) | MIN(expr) | SUM(expr) | COUNT(expr) | AVG([DISTINCT] expr)

掌握指数：★★★★★

函数说明：

聚合函数，配合 group by 使用，用来求最大、小值/求和/计数/求平均值

SQL 语句示例：


# 查询男同事里薪水最高的人
select max(salary) from emp where sex = '男';

# 查询女同事里薪水最少的人
select min(salary) from emp where sex = '女';

# 查询所有姓王的学生的个人总成绩 
select sum(sscore) 姓王的学生的个人总成绩 from score where sid in (select sid from student stu where stu.sname like '王%') group by sid; 

# 查询女同事的人数
select count(id) 女同事的人数 from emp group by sex having sex = '女';

# 查询平均薪水
select avg(salary) 平均薪水 from emp;
复制代码

注意：

除非另有说明，否则聚合函数会忽略 null 值
如果在不包含 group by 子句的语句中使用聚合函数，就等效于对所有行进行分组，结果总是有一行
时间类型的值对 sum() 和 avg() 无效！它们会将其换成数字，丢弃第一个非数字字符后的所有信息

另外，聚合函数可以传入独立的表达式作为参数：


# 查询分数在 80 分及以上人数
select count(1) from score where sscore >= 80;
# 等价于
select count(if(sscore >= 80, 1, null)) from score;

# 查询分数在 80 分及以上总分
select sum(sscore) from score where sscore >= 80;
# 等价于
select sum(if(sscore>=80, sscore, null)) from score;

# 查询分数在 80 分及以上平均分
select avg(sscore) from score where sscore >= 80;
# 等价于
select avg(if(sscore>=80, sscore, null)) from score;
复制代码

★ 日期函数

CURDATE() <=> CURRENT_DATE

掌握指数：★★★★

函数说明：

返回当前日期，格式为 YYYY-MM-dd

SQL 语句示例：


select curdate(); # 2021-11-07

select current_date; # 2021-11-07
复制代码

CURTIME() <=> CURRENT_TIME

掌握指数：★★★★

函数说明：

返回当前时间，格式为 HH:mm:ss

SQL 语句示例：


select curtime(); # 10:31:23

select current_time; # 10:31:23
复制代码

NOW() <=> CURRENT_TIMESTAMP

掌握指数：★★★★

函数说明：

返回当前日期时间，格式为 YYYY-MM-dd HH:mm:ss

SQL 语句示例：


select now(); # 2021-11-07 10:31:46

select current_timestamp; # 2021-11-07 10:31:46
复制代码

DAY(date) | DAYOFWEEK(date) | DAYOFMONTH(date) | DAYOFYEAR(date)

掌握指数：★★★★

函数说明：

返回 date 中 dd / 这一天是这一周/月/年中的第几天

SQL 语句示例：


# 取出 date 中的天
select day('2021-11-07'); # 7

# 一周中的第几天
select dayofweek(now()); # 星期天（Sunday） -> 返回 1

# 一个月中的第几天
select dayofmonth(now()); # 7

# 一年中的第几天
select dayofyear(now()); # 311
复制代码

WEEK(date[,mode]) | WEEKOFYEAR(date)

掌握指数：★★★

函数说明：

Mode	First day of week
0	`Sunday => 1`
1	`Monday => 1`

使用 mode 指定星期天是一周中的第一天还是星期一，然后根据这个标准判断 date 是一年的第几周，返回结果 (0, 52) ；WEEKOFYEAR() 总是以星期一作为一周的开始，即 mode 固定就是 1

SQL 语句示例：


# 默认，mode => 0，星期天（Sunday）=> 1
select week('2021-11-07'); # 45
select week('2021-11-07', 0); # 45

# 星期一（Monday）=> 1
select week('2021-11-07', 1); # 44
# 等价于
select weekofyear('2021-11-07'); # 44
复制代码

MONTH(date) | QUARTER(date)

掌握指数：★★★

函数说明：

返回 date 中的月份/所属季度

SQL 语句示例：


select month(now()); # 11

# 第一季度（1, 2, 3）第二季度（4, 5, 6）第三季度（7, 8, 9）第四季度（10, 11, 12）
select quarter(curdate()); # 4
复制代码

YEAR(date) | YEARWEEK(date,mode)

掌握指数：★★★★

函数说明：

返回 date 中的年份/年份+第几周

SQL 语句示例：


select year(curdate()); # 2021

# 默认 mode = 0，星期天（Sunday）=> 1
select yearweek('2021-11-07'); # 202145
# 等价于
select YEARWEEK('2021-11-07', 0); # 202145

# 星期一（Monday） => 1
select YEARWEEK('2021-11-07', 1); # 202144
复制代码

DAYNAME(date) | MONTHNAME(date)

掌握指数：★★

函数说明：

返回该天/月英文名

SQL 语句示例：


select dayname('2021-11-07'); # Sunday 星期天

select monthname('2021-11-07'); # November 11 月
复制代码

STR_TO_DATE(str,format) | DATE_FORMAT(date,format)

掌握指数：★★★★★

函数说明：

根据 date 字符串的格式，转换成日期，相反地，可以将 date 转换成指定格式的字符串

SQL 语句示例：


# 字符串转 date 类型
select str_to_date('2021年11月07日', '%Y年%m月%d日'); # 2021-11-07
select str_to_date('2021年11月07日 12点28分34秒', '%Y年%m月%d日 %H点%i分%s秒'); # 2021-11-07 12:28:34

# date 转指定格式的字符串
select date_format(now(), '%Y年%m月%d日 %H点%i分%s秒'); # 2021年11月07日 11点29分56秒
复制代码

DATEDIFF(expr1,expr2)

掌握指数：★★★★★

函数说明：

返回两个 date 相隔的天数

SQL 语句示例：


select concat(datediff(curdate(), '2021-01-01'), '天') as '距离开年以来已经过了'; # 312天

select concat(datediff(str_to_date(concat(year(now()), '/12/31'), '%Y/%m/%d'), now()), '天') as '距离年末仅剩'; # 52天
复制代码

DATE_ADD(date,INTERVAL expr unit) | DATE_SUB(date,INTERVAL expr unit)

掌握指数：★★★★★

函数说明：

对 date 做加减法

SQL 语句示例：


select ceil(rand()*31); # (0, 31]
# 将当前时间戳加上 (0, 31] 区间中的随机 DAY 天数
select date_add(CURRENT_TIMESTAMP, interval ( ceil(rand()*31) ) DAY );

select ceil(rand()*4); # [1, 4]
# 将当前时间戳加上 [1, 4] 区间中的随机 WEEK 周数
select date_add(now(), interval ( ceil(rand()*4) ) WEEK );

# 相反地，对指定 date 做减法，将当前时间戳减去 [0, 10] 区间中的随机 YEAR 年数
select date_sub(CURRENT_TIMESTAMP, interval ( round(rand(CURRENT_TIME)*11) ) YEAR );
复制代码

TO_DAYS(date) | FROM_DAYS(N)

掌握指数：★★

函数说明：

给定一个 date ，返回从公元 0 年到 date 的天数

小知识：历史并不存在公元 0 年，但 0 年是公元位数对齐的基础

SQL 语句示例：


select to_days('2021-11-07'); # 738466
# 并不完全等价，to_days() 包含当天，而 datediff() 不然
select datediff('2021-11-07','0-01-01'); # 738465

# to_days() 的逆运用，给定一个从公元 0 年开始的天数，返回一个 date
select from_days(737515); # 2019-4-1
select from_days(to_days('2021-11-07')); # 2021-11-07
复制代码

☛ 流程控制函数

IF(expr1,expr2,expr3)

掌握指数：★★★★

函数说明：

判断 expr1 表达式真假，真返回 expr2，否则返回 expr3

SQL 语句示例：


select if(10 > 5, '10 更大', '5 更大'); # 10 更大
复制代码

IFNULL(expr1,expr2) | NULLIF(expr1,expr2)

掌握指数：★★★

函数说明：

IFNULL 用来判断 expr1 是否为 null，如果不是则返回 expr2 ，否则返回 expr1 ；NULLIF 则是用来判断 expr1、2 是否相等，相等则返回 null，否则返回 expr1

SQL 语句示例：


# expr1 is not null.return expr1
select ifnull('exp1 is not null', null); # exp1 is not null
# expr1 is null.return expr2
select ifnull(null, 'exp1 is null'); # exp1 is null

select nullif('HUALEI', 'hualei'); # 相等返回 null
# 等价于（不区分大小写）
select nullif('HUALEI', 'HUALEI'); # 相等返回 null

# expr1 != expr2
select nullif('HUALEI', null); # HUALEI
复制代码

注意： expr1 != null ，否则返回 null 。

select nullif(null, 'HUALEI'); # null
复制代码

IF ... ELSE 语句

掌握指数：★★★★

函数说明：

写法不同于 IF() 函数

语法：


IF search_condition THEN
    statement_list
ELSE
    statement_list
END IF;
复制代码

SWITCH ... CASE 语句

掌握指数：★★★★

函数说明：

开关语句

语法：


CASE case_value
WHEN when_value THEN
  statement_list
ELSE
  statement_list
END;
复制代码

☛ 消息摘要函数

PASSWORD(str)

掌握指数：★★★

函数说明：

计算并返回密码字符串

SQL 语句示例：


select PASSWORD('abc') # *0D3CED9BEC10A777AEC23CCC353A8C08A633045E

select PASSWORD('ABC') # *71B101096C51D03995285042443F5C44D59C8A31
复制代码

注意： 该函数在 MySQL8.0.11 版本中被移除了。

MD5(str)

掌握指数：★★★★

函数说明：

计算 MD5 总和校验码

SQL 语句示例：


select MD5("abc"); # 900150983cd24fb0d6963f7d28e17f72
复制代码

SHA(str) | SHA1(str)

掌握指数：★★★★

函数说明：

计算 SHA/SHA1 总和校验码

SQL 语句示例：


select SHA('abc')  # a9993e364706816aba3e25717850c26c9cd0d89d
# 等价于
select SHA1('abc') # a9993e364706816aba3e25717850c26c9cd0d89d
复制代码

☛ 对称加密函数

ENCODE(str,pass_str) | DECODE(crypt_str,pass_str)

掌握指数：★★★★

函数说明：

通过公共密钥加密（Encode 编码）/解密（Decode 解码）

SQL 语句示例：


# 'password' 作为公钥加密字符串信息 'HUALEI'
select encode('HUALEI', 'password'); # ���e

# 将加密后的密文通过公钥 'password' 进行解密，从而得到加密前的明文
select decode(encode('HUALEI', 'password'), 'password'); # HUALEI
复制代码

AES_ENCRYPT(str,key_str) | AES_DECRYPT(crypt_str,key_str) | DES_ENCRYPT(str[,{key_num|key_str}]) | DES_DECRYPT(crypt_str[,key_str])

掌握指数：★★★★

函数说明：

通过额 AES(Advanced Encryption Standard 高级加密标准，作为 DES 算法的替代品) / DES(Data Encryption Standard 数据加密标准) 算法对称加密信息

SQL 语句示例：


# AES 算法加密，公钥为 'salt'
select aes_encrypt('HUALEI', 'salt'); # �ɫD�b*�ճ�ϐe�
# AES 算法解密
select aes_decrypt(aes_encrypt('HUALEI', 'salt'), 'salt'); # abc

# DES 算法加密，公钥为 'password'
select des_encrypt('HUALEI', 'password'); # ��o�#�
# DES 算法解密
select des_decrypt(des_encrypt('HUALEI', 'password'), 'password'); # HUALEI
复制代码

☛ 系统信息函数

VERSION()

掌握指数：★★★★

函数说明：

返回当前 MySQL 版本号

SQL 语句示例：


select version(); # 5.7.31-log
复制代码

USER() | CURRENT_USER

掌握指数：★★★★

函数说明：

返回当前用户角色

SQL 语句示例：


select user(); # root@localhost
# 等价于
select current_user; # root@localhost
复制代码

DATABASE()

掌握指数：★★★★

函数说明：

返回当前所在数据库名

SQL 语句示例：


select database(); # mysql
复制代码

CONNECTION_ID()

掌握指数：★★★

函数说明：

返回当前用户连接数

SQL 语句示例：


select connect_id(); # 38
复制代码

函数全概述

以上全部函数，我将它们归纳成一张思维导图，非常直观，供大家学习参考：

MySQL 常用函数.jpg

结尾

撰文不易，欢迎大家点赞、评论，你的关注、点赞是我坚持的不懈动力，感谢大家能够看到这里！Peace & Love。