MySQL函数

MySQL常用函数说明

数学函数

绝对值
SELECT ABS(-1.1); => 1.1

圆周率
SELECT PI(); => 3.141593

平方根
SELECT SQRT(9); => 3
SELECT SQRT(30); => 5.477225575051661
SELECT SQRT(-6); => NULL

求余
SELECT MOD(30, 8); => 6
SELECT MOD(212, 17); => 8
SELECT MOD(33.2, 7); => 5.2

上取整
SELECT CEIL(-2.22); => -2
SELECT CEILING(2.22); => 3

下取整
SELECT FLOOR(-2.22); => -3
SELECT FLOOR(2.22); => 2

转换二进制
SELECT BIN(2); => 10

随机数
SELECT RAND(); => 0.3345055285231262（随机）
获取相同随机数
SELECT RAND(10); => 0.6570515219653505
SELECT RAND(20); => 0.15888261251047497

四舍五入
SELECT ROUND(1.4); => 1
SELECT ROUND(1.4251, 2); => 1.43
SELECT ROUND(1.4251, 3); => 1.425
SELECT ROUND(1335.4251, -1); => 1340

截取
SELECT TRUNCATE(1.4, 0); => 1
SELECT TRUNCATE(1.4251, 2); => 1.42
SELECT TRUNCATE(1.4251, 3); => 1.425
SELECT TRUNCATE(1.4251, -1); => 0

符号
SELECT SIGN(11); => 1
SELECT SIGN(0); => 0
SELECT SIGN(-11); => -1

幂运算
SELECT POW(2, 8); => 256
SELECT POW(256, 1/8); => 2
SELECT POWER(2, 8); => 256

自然常数的乘方计算
SELECT EXP(1); => 2.718281828459045

对数运算
返回 x 的自然对数
SELECT LOG(5); => 1.6094379124341003

返回 x 的基数为 10 的对数
SELECT LOG10(100); => 2

角度与弧度转换
SELECT RADIANS(90); => 1.5707963267948966
SELECT DEGREES(PI()); => 180

正弦和反正弦
SELECT SIN(1); => 0.8414709848078965
SELECT ROUND(SIN(PI())); => 0
SELECT ASIN(0.5); => 0.5235987755982988

余弦和反余弦
SELECT COS(1); => 0.5403023058681398
SELECT ACOS(1); => 0

正切、反正切和余切
SELECT TAN(1); => 1.557407724654902
SELECT ATAN(1); => 0.7853981633974483
SELECT COT(1); => 0.6420926159343308

字符串函数

计算字符数
SELECT CHAR_LENGTH('HongJing'); => 8
SELECT CHAR_LENGTH('泓景'); => 2

计算字节数
UTF8 编码时，一个汉字是 3 字节，一个 数字 或 字母 是一字节
SELECT LENGTH('HongJing'); => 8
SELECT LENGTH('泓景'); => 6

合并
SELECT CONCAT('My', 'SQL'); => MySQL
SELECT CONCAT('My', NULL, 'SQL'); => NULL

SELECT CONCAT_WS('-', 'My', 'SQL', 'Workbench'); => My-SQL-Workbench
SELECT CONCAT_WS('-', 'My', NULL, 'SQL', 'Workbench'); => My-SQL-Workbench

替换
SELECT INSERT('MySQL', 3, 4, 'Apple'); => MyApple
SELECT INSERT('MySQL', -1, 4, 'Apple'); => MySQL
SELECT REPLACE('xxx.git.com', 'x', 'w'); => www.git.com

截取
SELECT LEFT('landscape', 4); => land
SELECT RIGHT('landscape', 5); => scape

SELECT SUBSTRING('landscape', 3); => ndscape
SELECT SUBSTRING('landscape', 3, 2); => nd
SELECT SUBSTRING('landscape', -3); => ape
SELECT SUBSTRING('landscape', -3, 2); => ap

SELECT MID('landscape', 3); => ndscape
SELECT MID('landscape', 3, 2); => nd
SELECT MID('landscape', -3); => ape
SELECT MID('landscape', -3, 2); => ap

大小写转换
SELECT UPPER('landscape'); => LANDSCAPE
SELECT UCASE('landscape'); => LANDSCAPE

SELECT LOWER('LANDSCAPE'); => landscape
SELECT LCASE('LANDSCAPE'); => landscape

填充
SELECT LPAD('landscape', 10, '--'); => -landscape
SELECT LPAD('landscape', 5, '--'); => lands

SELECT RPAD('landscape', 10, '--'); => landscape-
SELECT RPAD('landscape', 5, '--'); => lands

删除空格
SELECT CONCAT('RED', LTRIM(' APPLE '), 'INC'); => REDAPPLE INC
SELECT CONCAT('RED', RTRIM(' APPLE '), 'INC'); => RED APPLEINC
SELECT CONCAT('RED', TRIM(' APPLE '), 'INC'); => REDAPPLEINC

删除指定字符串
SELECT TRIM('big' FROM 'bigApple'); => Apple

生成重复字符串
SELECT REPEAT('A', 3); => AAA

空格函数
SELECT CONCAT('(', SPACE(6), ')'); => ( )

字符串比较
SELECT STRCMP('txt', 'txt2'); => -1
SELECT STRCMP('txt', 'txt'); => 0
SELECT STRCMP('txt2', 'txt'); => 1

匹配子串开始位置
SELECT LOCATE('scape', 'landscape'); => 5
SELECT POSITION('scape' IN 'landscape'); => 5
SELECT INSTR('landscape', 'scape'); => 5

倒序
SELECT REVERSE('ABC'); => 'CBA'

返回指定位置的字符串
SELECT ELT(3, '3D', '2D', '1D'); => 1D

返回指定字符串位置
SELECT FIELD('3', '3D', '2D', '1D'); => 0

返回子串位置
SELECT FIND_IN_SET('Hi', 'io,Apple,Hi,TelHi'); => 3

选取字符串
SELECT MAKE_SET(1, 'a', 'b', 'c'); => a
SELECT MAKE_SET(1 | 4, 'a', 'b', 'c'); => a,c
SELECT MAKE_SET(1 | 4, 'a', 'b', NULL, 'c'); => a
SELECT MAKE_SET(0, 'a', 'b', 'c');

日期和时间函数

日期
SELECT CURDATE(); => 2017-11-22
SELECT CURDATE() + 0; => 20171122
SELECT CURRENT_DATE(); => 2017-11-22

时间
SELECT CURTIME(); => 22:18:36
SELECT CURRENT_TIME(); => 22:18:36
SELECT CURTIME() + 0; => 221836

日期和时间
SELECT CURRENT_TIMESTAMP(); => 2020-11-22 22:20:17
SELECT LOCALTIME(); => 2020-11-22 22:21:12
SELECT NOW(); => 2020-11-22 22:21:12
SELECT SYSDATE(); => 2020-11-22 22:21:12

UNIX
SELECT UNIX_TIMESTAMP(); => 1606060798
SELECT UNIX_TIMESTAMP(NOW()); => 1606060798
SELECT UNIX_TIMESTAMP('2000-01-01'); => 946656000
SELECT FROM_UNIXTIME(946656000); => 2000-01-01 00:00:00

UTC
SELECT UTC_DATE(); => 2020-11-22
SELECT UTC_DATE() + 0; => 20201122
SELECT UTC_TIME(); => 16:07:58
SELECT UTC_TIME() + 0; => 160818

星期
SELECT DAYNAME(NOW()); => Monday
SELECT DAYOFWEEK(NOW()); => 2 (周一)
SELECT WEEKDAY(NOW()); => 0 (周一)

第几周
WEEK(d, mode)
mode：范围 0-7，指定该星期是否起始于周日或周一，若省略，则使用 `default_week_format` 系统自变量的值
SELECT WEEK(NOW()); => 47
SELECT WEEK(NOW(), 0); => 47
SELECT WEEK(NOW(), 1); => 48
SELECT WEEK('2010-09-21', 3); => 38
SELECT WEEKOFYEAR('2010-09-21'); => 38

天数
SELECT DAYOFYEAR(NOW()); => 328
SELECT DAYOFMONTH(NOW()); => 23

年份
SELECT YEAR(NOW()); => 2020

季度
SELECT QUARTER(NOW()); => 4

月份
SELECT MONTH(NOW()); => 11
SELECT MONTH('2000-01-01'); => 1
SELECT MONTHNAME(NOW()); => November
SELECT MONTHNAME('2000-01-01'); => January

分钟
SELECT MINUTE(NOW()); => 13

秒钟
SELECT SECOND(NOW()); => 50

时间和秒钟转换
SELECT TIME_TO_SEC('10:20:15'); => 37215
SELECT SEC_TO_TIME(3600); => 01:00:00
SELECT SEC_TO_TIME(3600) + 0; => 10000

获取日期指定值
SELECT EXTRACT(MICROSECOND FROM '2020-01-01 11:20:13.123456'); => 123456
SELECT EXTRACT(YEAR FROM '2020-01-01 11:20:13.123456'); => 2020
SELECT EXTRACT(YEAR_MONTH FROM '2020-01-01 11:20:13.123456'); => 202001
SELECT EXTRACT(DAY_MINUTE FROM '2020-01-01 11:20:13.123456'); => 11120

时间运算
DATE_ADD(date, INTERVAL expr type)
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND); => 2011-01-01 00:00:00
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:01' MINUTE_SECOND); => 2011-01-01 00:01:00
SELECT ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND); => 2011-01-01 00:00:00

SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY); => 2010-12-02
SELECT DATE_SUB('2011-01-01 00:01:00', INTERVAL '0 0:1:1' DAY_SECOND); => 2010-12-31 23:59:59
SELECT SUBDATE('2011-01-02', INTERVAL 31 DAY); => 2010-12-02

SELECT ADDTIME('2000-12-31 23:59:59', '1:1:1'); => 2001-01-01 01:01:00
SELECT ADDTIME('02:02:02', '02:00:00'); => 04:02:02

SELECT SUBTIME('2000-12-31 23:59:59', '1:1:1'); => 2000-12-31 22:58:58
SELECT SUBTIME('02:02:02', '02:00:00'); => 00:02:02

SELECT DATEDIFF('2010-12-31 23:59:59', '2010-12-30'); => 1
SELECT DATEDIFF('2010-11-30 23:59:59', '2010-12-31'); => -31

时间比较
SELECT DATEDIFF('2010-12-31 23:59:59', '2010-12-30'); => 1
SELECT DATEDIFF('2010-11-30 23:59:59', '2010-12-31'); => -31

时间格式化
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); => Saturday October 1997
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%Y-%m-%d'); => 1997-10-04
SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l'); => 16 16 04 04 4

返回日期时间字符串的显示格式
SELECT GET_FORMAT(DATE, 'EUR'); => %d.%m.%Y
SELECT GET_FORMAT(DATE, 'USA'); => %m.%d.%Y

条件判断函数

IF
SELECT IF(1 > 2, 2, 3); => 3
SELECT IF(1 < 2, 'yes', 'no'); yes
SELECT IF(STRCMP('test', 'test1'), 'yes', 'no'); => yes

IFNULL
SELECT IFNULL(1, 2); => 1
SELECT IFNULL(NULL, 2); => 2
SELECT IFNULL(0, 2); => 0
SELECT IFNULL(1/0, 2); => 2

CASE

SELECT
CASE 2
　　　　WHEN 1 THEN 'one'
　　　　WHEN 2 THEN 'two'
ELSE 'more'
END; => two

SELECT CASE WHEN 1 < 0 THEN 'true' ELSE 'false' END; => false

信息函数

查看版本
SELECT VERSION();

查看连接 ID
SELECT CONNECTION_ID();

查看当前使用的数据库
SELECT DATABASE();
SELECT SCHEMA();

查看当前用户
SELECT USER();
SELECT CURRENT_USER();
SELECT SYSTEM_USER();
SELECT SESSION_USER();

返回字符串自变量的字符集
SELECT CHARSET('abc'); => utf8mb4
SELECT CHARSET(CONVERT('abc' USING latin1)); => latin1
SELECT CHARSET(VERSION()); => utf8

获取最后一个自动生成的ID值
SELECT LAST_INSERT_ID();

加密函数

SELECT PASSWORD('zeng'); => *6DA5E5918FDC9A206E55487F97B708E9BA13739C
SELECT MD5('zeng'); => e06c96c9ffae0154e7ade9e76f2667af

BASE64
SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));

加解密
SELECT LENGTH('secret'); => 6
SELECT ENCODE('secret', 'zeng'); => ?\P??
SELECT LENGTH(ENCODE('secret', 'zeng')); => 6
SELECT DECODE(ENCODE('secret', 'zeng'), 'zeng'); => secret