MYSQL常用函数总结
一、数学函数
数学函数主要用于处理数字，包括整型、浮点数等。
计算绝对值
SELECT ABS(-1) AS '求绝对值';
小数取整
SELECT
CEIL( 1.5 ) AS '向上取整1',
CEILING( 1.5 ) AS '向上取整2',
FLOOR( 1.5 ) AS '向下取整';
数字精度处理
SELECT
ROUND(1.23456, 3) AS '四舍五入',
TRUNCATE(1.23456, 3) AS '保留3位不舍入',
ROUND(1.23456, 3) AS '保留3位舍入',
FORMAT(1.23456,3) AS '保留3位舍入'
随机数(0~1)
SELECT
RAND() ,
RAND() AS '每次随机',
RAND(10),
RAND(10) AS '根据随机种子随机'
计算数字符号
SELECT
SIGN(0) AS '零值',
SIGN(-10) AS '负数',
SIGN(10) AS '正数';
获取圆周率
SELECT PI()
计算次方
SELECT
POW( 2, 2 ),
POWER( 2, 3 ),
EXP(4) as '指数e的4次方'
计算开平方
SELECT SQRT(25)
计算除法取余
SELECT MOD(5,2);
计算对数
SELECT
LOG(20.085536923188) AS '自然指数e为底的对数',
LOG10(100) AS '10为底的对数'
角度<=>弧度
SELECT
RADIANS( 180 ) AS '角度转弧度',
DEGREES( 3.1415926535898 ) AS '弧度转角度'
三角函数计算
SELECT
SIN(-3.15) AS '求正弦(以下参数均是弧度)',
ASIN(0.15) AS '求反正弦',
COS(30) AS '求余弦',
ACOS(0.15) AS '求反余弦',
TAN(- 15) AS '求正切',
ATAN(30) AS '求反正切',
ATAN(30, 15) AS '求反正切',
ATAN2(30) AS '求反正切',
ATAN2(30, 15) AS '求反正切',
COT(15) AS '求余切值'
进制转换
SELECT
ASCII('abc') AS '求第一个字符的ASCII码',
BIN(2) AS '10进制转换为二进制',
OCT(7) AS '10进制转换为八进制',
HEX(15) AS '10进制转换为十六进制',
CONV(10, 8, 10) AS '指定进制转换'
二、字符串函数
字符串长度
SELECT
CHAR_LENGTH( '你好123' ) AS '字符数',
LENGTH( '你好123' ) AS '字符长度'
字符拼接
SELECT
CONCAT('12', '34') AS '字符串普通拼接',
CONCAT_WS('连接符', 'a', 'b', 'c') AS '用连接符拼接'
字符串大小写转换
SELECT
UPPER( 'abc' ) AS '小转大',
UCASE( 'abc' ) AS '小转大',
LOWER( 'A' ) AS '大转小',
LCASE( 'A' ) AS '大转小'
字符串截取
SELECT
LEFT('123456789', 2) AS '截取从左边开始到第2位',
RIGHT('123456789', 2) AS '截取从右边开始到第2位',
SUBSTRING('123456789',3,6) AS '截取从左边开始第3-6位',
MID('123456789',3,6) AS '截取从左边开始第3-6位'
复杂截取
SUBSTRING_INDEX(str,x,index)：用x截取str，并在截取的数组中取第index个
SELECT SUBSTRING_INDEX('a*b','*',1); -- a
SELECT SUBSTRING_INDEX('a*b','*',-1); -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1); -- c
指定位置与长度的字符替换
INSERT(str,pos,len,newstr)
str：字符串
pos：str开始被替换的位置
len：被替换的字符串长度
newstr：新字符串
SELECT INSERT('12345',2,1,'a')
字符串替换
REPLACE(s,x,y)：在字符串s中将x替换成y
SELECT REPLACE('abaca','a','0')
字符串填充
- LPAD(s,len,x)：从左边开始用x填充s到长度为len
- RPAD(s,len,x)：规则同上
SELECT
LPAD( 'abc', 5, 'xz' ) AS '左填充',
RPAD( 'abc', 5, 'xx' ) AS '右填充';
去除空格(首尾)
SELECT
LTRIM( ' 123456 ' ) AS '去除左边空白字符',
RTRIM( ' 123456 ' ) AS '去除右边空白字符',
TRIM( ' 123456 ' ) AS '去除两边空白字符'
去除指定字符(首尾)
SELECT TRIM('@' FROM '@@abc@@') AS '剔除除指定字符'
字符串重复
SELECT REPEAT('123',3)
获得n个空格字符
SELECT SPACE(100) AS '获得n个空格字符'
翻转字符串
SELECT REVERSE('abc')
比较两个字符串(ASCII码值)大小
SELECT
ASCII('2'),
ASCII('1'),
STRCMP('2', '1')
找字符在字符串中的位置
LOCATE(x,s)、POSITION(x IN s)：x在s中的位置
SELECT LOCATE('4', '123456789'),POSITION('5' IN '123456789')
INSTR(s,y)：y在s中的位置
SELECT INSTR('123456789','2')
返回字符串第n项
ELT(n,s1,s2,...sn)
SELECT ELT(2,'a','b','c')
找位置
FIELD(x,s1,s2...,sn)：找到x在s1-sn字符串列表中匹配的位置
SELECT FIELD('3','1','2','3','4','5','6','7','8','9')
FIND_IN_SET(x,strlist) ：在strlist字符串列表(注意：必须是"1,2,3,4"这种以逗号分隔的字符串)中找到x的位置
SELECT FIND_IN_SET('4','1,2,3,4,5,6,7,8,9')
计算二进制数真假位
EXPORT_SET(二进制数, 真位, 假位, 连接符, 二进制数中需要的位数)：https://vimsky.com/examples/usage/export_set-function-in-mysql.html
SELECT BIN(5),EXPORT_SET(5,'真位也即二进制1','假位也即二进制0','-连接符-',5)
SELECT BIN(5),EXPORT_SET(5,'y','n','——',5)
计算二进制数真假位返回对应结果
MAKE_SET(x,str1,str2,…)：将x转换为二进制数，根据位置一一对应，返回真位对应的字符串,https://blog.csdn.net/qq_41725312/article/details/83039525
SELECT
BIN(2 | 1),
MAKE_SET(2 | 1, 'a', 'b', 'c');
三、日期时间函数
返回当前时间相关
SELECT
NOW() AS '当前日期时间1',
CURRENT_TIMESTAMP () AS '当前日期时间2',
LOCALTIME () AS '当前日期时间3',
SYSDATE() AS '当前日期时间4',
LOCALTIMESTAMP () AS '当前日期时间5',
CURDATE() AS '当前日期1',
CURRENT_DATE () AS '当前日期2',
CURTIME() AS '当前时间1',
CURRENT_TIME () AS '当前时间2'
UNIX时间戳相关
SELECT
UNIX_TIMESTAMP() AS 'UNIX时间戳1',
UNIX_TIMESTAMP( '2023-06-09 13:16:58' ) AS '日期时间转UNIX时间戳',
FROM_UNIXTIME(1686287851) AS 'UNIX时间戳转日期时间'
返回UTC格式的日期时间
SELECT UTC_DATE(),UTC_TIME()
在日期时间中取月值
SELECT
MONTH('2023-06-09 13:22:44') AS '取月值',
MONTHNAME('2023-06-09 13:22:44') AS '取月名'
在日期时间中取日值
SELECT
DAYOFYEAR( '2023-06-09 13:22:44' ) AS '年第几天',
DAY ( '2023-06-09 13:22:44' ) AS '月第几天',
DAYOFMONTH( '2023-06-09 13:22:44' ) AS '月第几天',
DAYNAME( '2023-06-09 13:22:44' ) AS '星期几';
在日期时间中取周值
SELECT
DAYOFWEEK( '2023-06-09 13:22:44' ) AS '周内的第几天',
WEEKDAY( '2023-06-09 13:22:44' ) AS '星期几',
WEEK ( '2023-06-09 13:22:44' ) AS '今年的第几周',
WEEKOFYEAR( '2023-06-09 13:22:44' ) AS '今年的第几周';
在日期时间中取季度值
SELECT QUARTER('2023-06-09 13:22:44') AS '取季度'
在日期时间中取时间分部
SELECT
HOUR('2023-06-09 13:22:44') AS '取小时',
MINUTE('2023-06-09 13:22:44') AS '取分钟',
SECOND('2023-06-09 13:22:44') AS '取秒'
在日期时间中提取
SELECT EXTRACT(MICROSECOND FROM '2023-06-09 13:22:44') AS '取微妙';
SELECT EXTRACT(SECOND FROM '2023-06-09 13:22:44') AS '取秒';
SELECT EXTRACT(MINUTE FROM '2023-06-09 13:22:44') AS '取分钟';
SELECT EXTRACT(HOUR FROM '2023-06-09 13:22:44') AS '取小时';
SELECT EXTRACT(DAY FROM '2023-06-09 13:22:44') AS '取月中第几天';
SELECT EXTRACT(WEEK FROM '2023-06-09 13:22:44') AS '取年中第几周';
SELECT EXTRACT(MONTH FROM '2023-06-09 13:22:44') AS '取月';
SELECT EXTRACT(QUARTER FROM '2023-06-09 13:22:44') AS '取季度';
SELECT EXTRACT(YEAR FROM '2023-06-09 13:22:44') AS '取年';
SELECT EXTRACT(SECOND_MICROSECOND FROM '2023-06-09 13:22:44') AS '取秒微妙';
SELECT EXTRACT(MINUTE_MICROSECOND FROM '2023-06-09 13:22:44') AS '取分钟秒微妙';
SELECT EXTRACT(MINUTE_SECOND FROM '2023-06-09 13:22:44') AS '取分钟秒';
SELECT EXTRACT(HOUR_MICROSECOND FROM '2023-06-09 13:22:44') AS '取小时分钟秒微妙';
SELECT EXTRACT(HOUR_SECOND FROM '2023-06-09 13:22:44') AS '取小时分钟秒';
SELECT EXTRACT(HOUR_MINUTE FROM '2023-06-09 13:22:44') AS '取小时分钟';
SELECT EXTRACT(DAY_MICROSECOND FROM '2023-06-09 13:22:44') AS '取日小时分钟秒微妙';
SELECT EXTRACT(DAY_SECOND FROM '2023-06-09 13:22:44') AS '取日小时分钟秒';
SELECT EXTRACT(DAY_MINUTE FROM '2023-06-09 13:22:44') AS '取日小时分钟';
SELECT EXTRACT(DAY_HOUR FROM '2023-06-09 13:22:44') AS '取日小时';
SELECT EXTRACT(YEAR_MONTH FROM '2023-06-09 13:22:44') AS '取年月';
将时间与秒数互转
SELECT
TIME_TO_SEC( '2023-06-09 00:01:00' ) AS '时间转秒数1',
TIME_TO_SEC( '00:01:00' ) AS '时间转秒数2',
SEC_TO_TIME(120) AS '秒数转时间'
计算0000年1月1日相关
SELECT
TO_DAYS( '0000-01-01' ) AS '计算到0000-01-01的天数1',
TO_DAYS( '2023-06-09 13:58:50') AS '计算到0000-01-01的天数2',
FROM_DAYS(739045) AS '计算0000-01-01往后偏移的日期'
计算天数差值(忽略时间分部)
SELECT DATEDIFF('2023-06-09 00:00:00','2023-06-08 23:59:59')
加减指定天数(忽略时间分部)
SELECT
'2023-06-09 14:05:30' AS '原始值',
ADDDATE('2023-06-09 14:05:30', 1) AS '加1天',
SUBDATE('2023-06-09 14:05:30', 1) AS '减1天',
ADDTIME('2023-06-09 14:05:30', 1) AS '加1秒',
SUBTIME('2023-06-09 14:05:30', 1) AS '减1秒'
日期时间通用偏移
正值向未来偏移，负值向过去偏移
- ADDDATE(d,INTERVAL 数值 type)
- DATE_ADD(d,INTERVAL 数值 type)
负值向过去偏移，正值向未来偏移
- SUBDATE(d,INTERVAL 数值 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
时间格式化
1、常用格式
SELECT
DATE_FORMAT('2011-09-20 08:30:45', '%Y-%m-%d %H:%i:%S') AS '指定时间',
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S') AS '现在'
2、日期格式化函数
DATE_FORMAT(d,f)
TIME_FORMAT(t,f)
3、获取格式化字符串函数
GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
SELECT
GET_FORMAT(DATETIME,'EUR') AS '欧盟标准',
GET_FORMAT(DATETIME,'USA') AS '美国标准',
GET_FORMAT(DATETIME,'JIS') AS '日本工业标准',
GET_FORMAT(DATETIME,'ISO') AS '国际标准化组织制订的标准',
GET_FORMAT(DATETIME,'INTERNAL') AS '国际标准',
GET_FORMAT(DATE,'EUR'),
GET_FORMAT(DATE,'USA'),
GET_FORMAT(DATE,'JIS'),
GET_FORMAT(DATE,'ISO'),
GET_FORMAT(DATE,'INTERNAL'),
GET_FORMAT(TIME,'EUR'),
GET_FORMAT(TIME,'USA'),
GET_FORMAT(TIME,'JIS'),
GET_FORMAT(TIME,'ISO'),
GET_FORMAT(TIME,'INTERNAL')
4、格式字符串表示
%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 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59) //这个最TM坑人，以前一直记得是m，错了半天才弄明白咋回事
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字"%"
四、条件判断函数
IF
IF(expr,v1,v2)：如果表达式expr成立，返回结果v1；否则，返回结果v2
SELECT IF('1>2','正确','错误')
IFNULL
IFNULL(v1,v2)：如果v1的值不为NULL，则返回v1，否则返回v2
SELECT IFNULL(NULL,'Hello Word')
CASE
CASE表示函数开始，END表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。
- 语法1
CASE
WHEN e1
THEN v1
WHEN e2
THEN e2
...
ELSE vn
END
SELECT
CASE
WHEN 1 > 0 THEN '1 > 0'
WHEN 2 > 0 THEN '2 > 0'
ELSE '3 > 0'
END
- 语法2
CASE expr
WHEN e1 THEN v1
WHEN e1 THEN v1
...
ELSE vn
END
SELECT
CASE 1
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
ELSE 'C'
END
五、系统信息函数
系统相关
SELECT
VERSION() AS 'mysql版本',
CONNECTION_ID() AS 'mysql当前链接数',
DATABASE() AS '当前使用的数据库1',
SCHEMA() AS '当前使用的数据库2'
用户相关
SELECT
USER(),
SYSTEM_USER(),
SESSION_USER(),
CURRENT_USER(),
CURRENT_USER
字符集相关
SELECT
CHARSET( "你好" ) AS '返回字符串str的字符集',
COLLATION ( "你好" ) AS '返回字符串str的字符排列方式',
LAST_INSERT_ID() AS '返回最近生成的AUTO_INCREMENT(自增)值'
六、加密函数
SELECT MD5('计算摘要')
8.0.16废弃：PASSWORD(str)、ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str)
七、其他函数
mysql中的get_lock锁机制解析
IP地址与数字相互转换的函数
SELECT
INET_ATON( '192.168.0.1' ) AS 'IP转数字',
INET_NTOA( 3232235521 ) AS '数字转IP'
加锁函数和解锁函数
GET_LOCK(name,time)函数定义一个名称为name、持续时间长度为time秒的锁。如果锁定成功，则返回1；如果尝试超时，则返回0；如果遇到错误，返回NULL。IS_USED_LOCK(name)函数判断名称为name的锁定是否存在，存在返回1，不存在返回NULLIS_FREE_LOCK(name)函数判断是否已使用名为name的锁定。如果使用，返回0，否则，返回1；RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，则返回1；如果尝试超时，返回0了如果解锁失败，返回NULL；
SELECT GET_LOCK('MySQL1',10);
SELECT IS_USED_LOCK('MySQL1');
SELECT IS_FREE_LOCK('MySQL1');
SELECT RELEASE_LOCK('MySQL1');
重复执行指定操作的函数
BENCHMARK(count,expr)函数将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。
SELECT BENCHMARK(10000,NOW())
改变字符集的函数
CONVERT(s USING cs)函数将字符串s的字符集变成cs。
SELECT
CHARSET('ABC') AS '查看ABC的字符集',
CONVERT('ABC' USING gbk) AS 'ABC使用gbk字符集',
CHARSET(CONVERT('ABC' USING gbk)) AS 'ABC从原来字符集转换为gbk字符集'
转换数据类型
- CAST(x AS type)
- CONVERT(x,type)
这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER有效
-- 字符串 转 整数
SELECT CAST('123' AS UNSIGNED INTEGER) + 1
-- mysql的隐式转换
SELECT '123' + 1
-- DATETIME 转换为 DATE
SELECT CAST(NOW() AS DATE)
参考：
https://vimsky.com/examples/usage/export_set-function-in-mysql.html
