MySQL系列(4): MySQL 常用函数
MySQL 提供了丰富的函数,可以帮助编写简单快捷的 SQL 语句。比如字符串处理、数值运算、日期运算等。
常用的函数有字符串函数、日期函数、数值函数、聚合函数,还有 全文检索函数、转换函数、XML函数、位操作函数、加密解密函数、锁函数、MySQL信息函数、几何函数、空间函数、JSON 函数、MySQL 企业加密函数、其它函数。
此篇文章目的是作为字典,看过一遍先了让自己有个映像有些函数可以实现某些功能,具体需要使用再查询,多用几次就记住了。
字符串函数
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 | mysql> SELECT LENGTH('A'),CHAR_LENGTH('A'),CHARACTER_LENGTH('A'),BIT_LENGTH('A'); |
从上例可以看出,一个字母字符占 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 | MySQL [(none)]> SELECT YEARWEEK('2019-01-01',0), YEARWEEK('2019-01-01',1); |
聚合函数
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. |
**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;
JSON_ARRAYAGG(col_or_expr):将多行的指定列值组装成数组返回。—-来自官网
1 | mysql> SELECT o_id, attribute, value FROM t3; |
- **JSON_OBJECTAGG(key, value)**:将多行的指定列值组装成 JSON 对象返回。—-来自官网
1 | mysql> SELECT o_id, attribute, value FROM t3; |
流程函数
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 | MySQL > SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; |
其它常用函数
其它常用函数包括 **查看信息函数、转换函数、位操作函数、加密解密函数、锁操作函数、全文检索函数、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/