发表更新MySQL系列27 分钟读完 (大约3999个字)

MySQL系列(4)： MySQL 常用函数

　　MySQL 提供了丰富的函数，可以帮助编写简单快捷的 SQL 语句。比如字符串处理、数值运算、日期运算等。

　　常用的函数有字符串函数、日期函数、数值函数、聚合函数，还有 全文检索函数、转换函数、XML函数、位操作函数、加密解密函数、锁函数、MySQL信息函数、几何函数、空间函数、JSON 函数、MySQL 企业加密函数、其它函数。

　　MySQL 8.0 函数和操作官方文档

　　此篇文章目的是作为字典，看过一遍先了让自己有个映像有些函数可以实现某些功能，具体需要使用再查询，多用几次就记住了。

字符串函数

MySQL 字符串函数-官方文档，下面例举常用的函数。

函数名 描述 备注
ASCII(str) 返回最左边字符的 ASCII 码值 单个字符,ASCII 码表
BIN(n) 返回十进制数字的二进制表示
CHAR(n1,n2,…) 将数字转换为字符并拼接 可传多个数字,逗号分隔
BIT_LENGTH(str) 返回字符所占的位长 中文占3个字节,24位(utf8)
LENGTH(str) 返回字符所占字节长度
CHAR_LENGTH(str) 返回字符个数，多字节字符计为 1 个 同 CHARACTER_LENGTH()
CONCAT(str1,str2,…) 返回拼接字符串 可传多个字符，豆号分隔
CONCAT_WS(separator,str1,str2,…) 使用分隔符拼接str1,str2…
TO_BASE64(str) 字符 转 BASE 64
FROM_BASE64(base64) BASE 64 转 字符
HEX(str) 返回十进制数或字符串的十六进制表示形式 对每个字符的每个字节
转换为两个十六进制数字
UNHEX(x) 将十六进制表示反转为字符
INSERT(str,pos,len,newstr) 从 pos 位开始,len 个字符长替换为 newstr
INSTR(str,substr) 返回 str 中第一次出现 substr 的位置 LOCATE(substr,str)
LOWER(str) 全部转为小写
UPPER(str) 全部转为大写
LEFT(str,len) 返回左边 len 个字符
RIGHT(str,len) 返回右边 len 个字符
LTRIM(str) 去除字符串行头空格
RTRIM(str) 去除字符串行尾空格
TRIM(str) 去除字符串行头和行尾空格 默认是去空格
TRIM(LEADING str FROM str1) 去除 str1 头部字符 str
TRIM(BOTH str FROM str1) 去除 str1 两头的字符 str
TRIM(TRAILING str FROM str1) 去除 str1 尾部字符 str
REVERSE(str) 字符串反转
REPEAT(str,count) 返回重复 str 重复 count 次的结果
REPLACE(str,from_str,to_str) 将 str 中的 from_str 替换为 to_str
STRCMP(str1,str2) 两个字符串比较,相等返回 0
FORMAT(X,D[,locale]) 大数字格式化为：#,##,##.## 字符串 FORMAT(12332.123456,4);
-> ‘12,332.1235’
LPAD(str,len,padstr) 用 padstr 对 str 左边进行填充到 len 长度
RPAD(str,len,padstr) 用 padstr 对 str 右边进行填充到 len 长度
ELT(N,str1,str2,str3,…) 返回第 N 个 str
FIELD(str,str1,str2,str3,…) 返回 str 出现在 str1,str2,str3…元素集中的索引位
FIND_IN_SET(str,strlist) 返回 str 出现在 strlist 字符串集中的索引位 strlist=’a,b,c,d’
SPACE(N) 返回 N 个空字符
SUBSTRING(str,pos) 从 pos 位置返回 str 字符串的子串
SUBSTRING(str,pos,len) 从 pos 位置返回 str 字符串 len 个字符子串
SUBSTRING_INDEX(str,delim,count) count 为正，返回第 count 个 delim 左边字符串(从左侧开始计算)；
count 为负，返回第 count 个 delim 右边字符串(从右侧开始计算)		 delim：分隔符

在建表时设置的字段长度是用于指定字符的个数，但千万要注意区分的是字符个数不等于字段长度；如果指定的字符个数超出了字段类型的范围，则是会报错或者写入的数据超出范围时报错。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT LENGTH('A'),CHAR_LENGTH('A'),CHARACTER_LENGTH('A'),BIT_LENGTH('A');
+-------------+------------------+-----------------------+-----------------+
| LENGTH('A') | CHAR_LENGTH('A') | CHARACTER_LENGTH('A') | BIT_LENGTH('A') |
+-------------+------------------+-----------------------+-----------------+
|           1 |                1 |                     1 |               8 |
+-------------+------------------+-----------------------+-----------------+
1 row in set

mysql> SELECT LENGTH('中'),CHAR_LENGTH('国'),CHARACTER_LENGTH('地'),BIT_LENGTH('球');
+--------------+-------------------+------------------------+------------------+
| LENGTH('中') | CHAR_LENGTH('国') | CHARACTER_LENGTH('地') | BIT_LENGTH('球') |
+--------------+-------------------+------------------------+------------------+
|            3 |                 1 |                      1 |               24 |
+--------------+-------------------+------------------------+------------------+

从上例可以看出，一个字母字符占 1 个字节，8位，计 1 个字符；一个汉字占 3 个字节，24位，计 1 个字符。

数值函数

MySQL 数值函数-官方文档，下面例举常用的数值函数。

ABS(X) 描述 备注
ABS(X) 返回 X 的绝对值
CEIL(X) 返回大于 X 的最小整数值 同：CEILING(X)
FLOOR(X) 返回小于 X 的最大整数值
CRC32(str) 计算 str 的循环冗余校验值并返回 返回的是32位无符号值
DEGREES(X) 将弧度 X 转度数并返回
RADIANS(X) 将度数 X 转弧度并返回
MOD(N,M) 返回 N 被 M 除的模(整数商的余数) 等同于 N % M
PI() 返回 π (pi) 值 3.141593
POW(X,Y) 返回 X 的 Y 幂的值 同：POWER(X,Y)
RAND() 默认返回 0 到 1 的随机数，
也可获得指定范围的随机数		 i <= *R* < j
R=FLOOR(i + RAND() * (j − i))
ROUND(X,D) 返回 X 四舍五入并保留 D位小数的值
在没指定 D 时，默认为 0 位小数。		 如果 X 没有小数位，
则指定的 D 无效
TRUNCATE(X,D) 数字 X 截断小数位，保留 D 位小数
SQRT(X) 返回非负数 X 的平方根 X 为负数时，返回 NULL

日期函数

MySQL 日期和时间函数-官方文档，下面例举常用的日期和时间函数。

函数名 描述 备注
ADDDATE(expr,days) 为日期 expr 添加整数天数 days 例：expr=’2008-01-02’
ADDTIME(expr1,expr2) 将 expr2 添加到 expr1 并返回结果 expr1 是时间或日期时间表达式，
expr2 是时间表达式。
DATE_ADD(date,INTERVAL expr unit) 为日期 date 添加 unit 单位的整数 expr INTERVAL=间隔
unit=[SECOND,MINUTE,HOUR,
DAY,MONTH,YEAR,
YEAR_MONTH,
DAY_MINUTE,
MICROSECOND]
DATE_SUB(date,INTERVAL expr unit) 为日期 date 减去 unit 单位的整数 expr 等同于
SUBDATE(date,INTERVAL expr unit)
SUBDATE(expr,days) 返回日期时间表达式 expr 减去天数 days 的值
SUBTIME(expr1,expr2) 返回 expr1 - expr2 并与 expr1 格式相同的值 expr1 是日期时间表达式，
expr2 是时间表达式
PERIOD_ADD(P,N) 将 N 个月添加到周期 P，以YYYYMM格式返回值 周期 P 的格式：
YYMM 或 YYYYMM
PERIOD_DIFF(P1,P2) 返回周期 P1 - P2 的月数
CONVERT_TZ(dt,from_tz,to_tz) 将日期时间 dt 从时区 from_tz 转换为 to_tz 时区 时区表示：’+8:00’
CURDATE() 获取当前日期，
默认格式：’YYYY-MM-DD’		 等同于：CURRENT_DATE()
若要’YYYYMMDD’ 表示，
使用 CURDATE() + 0
CURTIME() 获取当前时间
默认格式：’HH:MM:SS’		 等同于：CURRENT_TIME()
若要’HHMMSS’ 表示，
使用 CURTIME() + 0
NOW() 返回当前本地时间
(具体指语句开始执行的时间)		 等同于 LOCALTIME(),
LOCALTIMESTAMP()
若语句执行过程中有延迟或睡眠，
这个时间在一开始就确定不变的
SYSDATE() 返回当前本地时间
(具体指语句执行的时间，是实时动态的时间)		 最新的执行时间，是非确定的时间
DATE(expr) 返回日期或时间表示式 expr 的日期部分 DATE(‘2003-12-31 01:02:03’)
> ‘2003-12-31’
YEAR(date) 获取日期中的年
TIME(expr) 返回日期或时间表达式 expr 的日期部分 TIME(‘2003-12-31 01:02:03’)
> ‘01:02:03’
HOUR(time) 返回时间的小时值 ‘10:05:03’ 返回 10
MINUTE(time) 返回时间的分钟值
SECOND(time) 返回时间的秒值
MONTH(date) 返回日期的月份值
DAYOFMONTH(date); 返回月份的日期值 等同于：DAY(date)
LAST_DAY(date) 返回月份最后一天的日期
DAYNAME(date) 返回日期的星期名称 Monday, Tuesday….
MONTHNAME(date) 返回日期的月份名称 January, February….
DAYOFWEEK(date) 返回日期在一周星期的索引 注：西方周六是星期最后一天，
索引是 7
周日是星期第一天，索引是 1
DAYOFYEAR(date) 返回日期在一年中的第几天
DATEDIFF(expr1,expr2) 返回日期时间 expr1 − expr2 的天数 计算仅使用日期部分
TIMEDIFF(expr1,expr2) 返回时间 expr1 − expr2 的时间 expr1 和 expr2 格式必须相同，
返回的是同样的时间格式
QUARTER(date) 返回日期所在的季度(1-4)
DATE_FORMAT(date,format) 将日期 date 按 format 格式化 例：’%Y-%m-%d %H:%i:%s’
结果：YYYY-MM-DD HH:MM:SS
format,多种表示格式见官方文档
TIME_FORMAT(time,format) 时间 time 按 format 格式化 (‘22:11:34’, ‘%H-%i-%s’)
> ‘22-11-34’
TIME_TO_SEC(time) 将时间 time 转为秒值
STR_TO_DATE(str,format) 将字符串按格式转换成日期
返回格式：YYYY-MM-DD		 DATE_FORMAT(date,format)的反转
(‘01,5,2013’,’%d,%m,%Y’)
> ‘2013-05-01’
TO_DAYS(date) 计算 0 年到 date 日期的天数
TO_SECONDS(expr) 计算 0 年到日期表大式 expr 的秒数
MAKEDATE(year,dayofyear) 根据给定的年和天数，返回日期 MAKEDATE(2019,10)
> ‘2019-01-10’
MAKETIME(hour,minute,second) 根据给定的时分秒，返回时间 MAKETIME(20,35,55)
> ‘20:35:55’
MICROSECOND(expr) 获取时间表示式 expr 的微秒
EXTRACT(unit FROM date) 返回日期中指定单位 unit 的值
FROM_DAYS(N) 将天数 N 转换为日期
SEC_TO_TIME(seconds) 将秒数转换为时间 默认是：HH:MM:SS
UNIX_TIMESTAMP() 获取当前时间的时间戳 获取指定日期的时间戳，使用 >
UNIX_TIMESTAMP(date)
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)		 将Unix 时间戳转换为本地时间(本地时区)
或按指定格式转换		 默认返回格式：
YYYY-MM-DD HH:MM:SS
GET_FORMAT(unit,area) 获取地区默认的时间格式 GET_FORMAT官方文档
例：GET_FORMAT(DATETIME,’ISO’)
返回：’%Y-%m-%d %H:%i:%s’
TIMESTAMP(expr), TIMESTAMP(expr1,expr2) 使用 expr 参数，将 expr 作为日期时间返回；
使用两个参数，将 expr2 添加到表达式 expr1，并返回日期时间		 expr,expr1,expr2
指日期或时间表达式
TIMESTAMPADD
(unit,interval,datetime_expr)		 日期时间添加单位为 unit 的 interval 个值 (MINUTE,1,’2003-01-02’)
> ‘2003-01-02 00:01:00’
TIMESTAMPDIFF
(unit,datetime_expr1,datetime_expr2)		 计算两个日期时间的单位差
expr2 - expr1		 (MINUTE,’2003-02-01’,’2003-02-01 12:05:55’)
> 725
TIMESTAMPDIFF
(unit,datetime_expr1,datetime_expr2)
UTC_DATE() 返回 UTC 日期 UTC > 协调世界时(标准时间)
本地时间= UTC + 时区
UTC_TIME() 返回 UTC 时间 UTC > 又称为格林尼治时间(GMT)
通常理解为伦敦时区(0时区)时间
UTC_TIMESTAMP 返回 UTC 日期时间 格式：YYYY-MM-DD HH:MM:SS
WEEK(date[,mode]) 返回日期的周数 mode 指定 一周是从 0 或 1 开始算。
不指定使用默认日星期格式,
0 表示星期天为一周第一天
WEEKDAY(date) 返回日期在一周中的索引 (0 = Monday, 1 = Tuesday, … 6 = Sunday)
WEEKOFYEAR(date) 返回日期在一年中的第几周
YEARWEEK(date), YEARWEEK(date,mode) 返回日期的年和第几周 如果日期是

注意 YEARWEEK(date,mode) 与 WEEK(date[,mode]) 的区别：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MySQL [(none)]> SELECT YEARWEEK('2019-01-01',0), YEARWEEK('2019-01-01',1);
+--------------------------+--------------------------+
| YEARWEEK('2019-01-01',0) | YEARWEEK('2019-01-01',1) |
+--------------------------+--------------------------+
|                   201852 |                   201901 |
+--------------------------+--------------------------+
1 row in set (0.00 sec)

MySQL [(none)]>  SELECT WEEK('2019-01-01',0), WEEK('2019-01-01',1);
+----------------------+----------------------+
| WEEK('2019-01-01',0) | WEEK('2019-01-01',1) |
+----------------------+----------------------+
|                    0 |                    1 |
+----------------------+----------------------+
1 row in set (0.00 sec)

聚合函数

MySQL 聚合函数-官方文档，聚合函数很多情况下会和分组(GROUP BY)配合使用。

函数名 描述 备注
AVG([DISTINCT] expr) 取表达式 expr 的平均值，
加上 DISTINCT，取非重复的平均值
COUNT([DISTINCT] expr) 统计表达式 expr 的计数(行数)
COUNT（*）返回检索所有行的计数，
不管是否包含 NULL 的值		 expr 非 null 的值的计数
InnoDB以相同的方式处理
SELECT COUNT（*）和
SELECT COUNT（1）操作。
没有性能差异。
MAX([DISTINCT] expr) 取最大值
MIN([DISTINCT] expr) 取最小值
SUM([DISTINCT] expr) 取总和 如果没有行，返回 NULL
GROUP_CONTACT(col_name) 返回字段值的连接字符串 默认使用逗号分隔
JSON_ARRAYAGG(col_or_expr) 返回 JSON 数组 Added in MySQL 5.7.22.
JSON_OBJECTAGG(key, value) 返回 JSON 对象 Added in MySQL 5.7.22.

  1. **GROUP_CONCAT(expr)**：返回连接列非 NULL 值的字符串。 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    -- 语法
    GROUP_CONCAT([DISTINCT] expr [,expr ...]
               [ORDER BY {unsigned_integer | col_name | expr}
                   [ASC | DESC] [,col_name ...]]
               [SEPARATOR str_val])
    -- 示例
    mysql> SELECT student_name, GROUP_CONCAT(test_score) FROM student GROUP BY student_name;
    mysql> SELECT sys_user_id,GROUP_CONCAT(id ORDER BY id ASC), CHAR_LENGTH(GROUP_CONCAT(id ORDER BY id DESC)),COUNT(sys_user_id) 
    		FROM tbu_user GROUP BY sys_user_id;	
    +-------------+----------------------------------+------------------------------------------------+--------------------+
    | sys_user_id | GROUP_CONCAT(id ORDER BY id ASC) | CHAR_LENGTH(GROUP_CONCAT(id ORDER BY id DESC)) | COUNT(sys_user_id) |
    +-------------+----------------------------------+------------------------------------------------+--------------------+
    |           1 | 530,531,532,534,536,540,544,546, |                                             32 |                246 |
    |          57 | 1,2,3,4,5,6,812,815,819,822,825, |                                             32 |                 13 |
    |         123 | 8629                             |                                              4 |                  1 |
    +-------------+----------------------------------+------------------------------------------------+--------------------+

    注意： GROUP_CONCAT(expr) 返回的字符串长度受 group_concat_max_len 参数的限制，超出会被截断，默认是 1024 字节，可以设置更高的值来显示更多的内容，修改语法如下，值 val 是无符号整型。

    1
    set [GLOBAL | SESSION] group_concat_max_len = val;

  2. JSON_ARRAYAGG(col_or_expr)：将多行的指定列值组装成数组返回。—-来自官网

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes 
     		FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)
  1. **JSON_OBJECTAGG(key, value)**：将多行的指定列值组装成 JSON 对象返回。—-来自官网 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value) FROM t3 GROUP BY o_id;
+------+----------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, name)        |
+------+----------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}     |
|    3 | {"color": "green", "shape": "square"}  |
+------+----------------------------------------+
1 row in set (0.00 sec)

流程函数

MySQL 控制流函数-官方文档

MySQL 提供流程控制函数主要是执行条件判断来返回相应的值。

函数名 描述
CASE value
WHEN [ ] THEN result
[WHEN [compare_value] THEN result …]
[ELSE else_result] END		 value 等于 compare_value，返回 result
都不相等，返回 else_result
CASE WHEN [condition] THEN result
[WHEN [condition] THEN result …]
[ELSE result] END		 condition 为 true，返回 result
IF(expr1,expr2,expr3) 如果 expr1 为真，返回 expr2，否则返回 expr3
注：expr1 <> 0 and expr1 <> NULL
IFNULL(expr1,expr2) 如果 expr1 不为 NULL，返回 expr1，否则返回 expr2
NULLIF(expr1,expr2) 如果 expr1 = expr2 为 true，返回 NULL，否则返回 expr1

使用示例：

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 CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL > SELECT CASE 3 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; 
+------------------------------------------------------------+
| CASE 3 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| more                                                       |
+------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL > SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

其它常用函数

其它常用函数包括 **查看信息函数、转换函数、位操作函数、加密解密函数、锁操作函数、全文检索函数、JSON 函数、其它函数**。下面例举几个觉得中用的函数。

函数名 描述 备注
DATABASE() 返回当前使用的数据库名
CONNECTION_ID() 返回连接线程 ID
USER() 返回当前客户端登录的用户和主机
VERSION() 返回数据库版本
LAST_INSERT_ID() 返回自增列最后插入的 ID
MD5(str) 返回字符串的 MD5 值
SHA1(str) 计算字符串 SHA-1 160 位校验合
返回 40 个十六进制数字的字符串		 等同于 SHA(str)
其中一种用途是作为哈希键
SHA2(str, hash_length) 计算SHA-2系列散列函数
（SHA-224，SHA-256，SHA-384和SHA-512）
INET_ATON(ip) 返回 IP 地址的数字表示 默认是 IPv4 的函数，
还有 IPv6的函数
INET_NTOA(num) 返回数字代表的 IP 地址
DEFAULT() 返回表列的默认值
SLEEP(seconds) 当前语句休眠 seconds 秒后返回
UUID() 返回 36位 UUID
UUID_SHORT() 返回短 17位 UUID

MySQL系列(4)： MySQL 常用函数

http://blog.gxitsky.com/2019/01/16/MySQL-04-common-function/

作者

光星

发布于

2019-01-16

更新于

2023-03-06

许可协议

喜欢这篇文章？打赏一下作者吧

评论

关注我
×