MySQL 常用函数

MySQL 提供了丰富的内置函数，用于执行各种操作，包括数据处理、数学运算、字符串处理、日期和时间操作、聚合函数等。

本文介绍一些常用的 MySQL 内置函数，更多更详细的函数介绍请参阅官方文档 Functions and Operators。

1.数学函数

ABS()

返回 X 的绝对值，如果 X 为 NULL 则返回 NULL。

ABS(X)
1

CEIL()

返回不小于X的最小整数值。如果X为NULL则返回NULL。等同于 CEILING()。

CEIL(X)
1

FLOOR()

返回不大于 X 的最大整数值。如果 X 为 NULL，则返回 NULL。

FLOOR(X)
1

CONV()

函数用于数值进制间的转化，如二进制与十进制之间的转换，十进制与十六进制转换等等。

CONV(N,from_base,to_base)
1

N 为待转换的整数，指定时可以是整数或字符串。from_base 表示初始的进制，to_base 表示转化后的进制，进制必须处于 [2, 36] 之间，否则返回 NULL。

任意参数为 NULL 则返回 NULL。

使用示例如下：

SELECT CONV('a',16,2);					-- 1010
SELECT CONV('6E',18,8);					-- 172
SELECT CONV(-17,10,-18);				-- -H
SELECT CONV(10+'10'+'10'+X'0a',10,10);	-- 40
1
2
3
4

注意：非数字的数值，比如十六进制的 0xff，传入函数时，以字符串的形传入：‘ff’。

2.字符串函数

CONCAT()

用于连接字符串或表字段。

可能有一个或多个参数。如果所有参数都是非二进制字符串，则结果为非二进制字符串。如果参数包含任何二进制字符串，则结果为二进制字符串。数值参数被转换为等效的非二进制字符串形式。

如果任何参数为NULL, CONCAT()返回NULL。

CONCAT(str1,str2,...)
1

（1）连接字符串。

SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'

SELECT CONCAT('My', NULL, 'QL');
-> NULL

SELECT CONCAT(14.3);
-> '14.3'
1
2
3
4
5
6
7
8

（2）连接数据表字段。

SELECT CONCAT(f_name, " ", l_name) AS name FROM employee WHERE level>3 LIMIT 1;
1

结果：

+---------------+ 
| name          | 
+---------------+ 
| Monica Sehgal |
1
2
3
4

注意：这里用到 CONCAT() 函数，用来把字符串串接起来。另外，AS 关键字给结果列CONCAT(f_name, " ", l_name)起了个别名。

CONCAT_WS()

用于连接字符串并指定分隔符，是 CONCAT() 的一种特殊形式。其中 WS 是 With Separator 的简写。

CONCAT_WS(separator,str1,str2,...)
1

用法示例：

SELECT CONCAT_WS(',','First name','Second name','Last Name');
-> 'First name,Second name,Last Name'

SELECT CONCAT_WS(',',f_name,l_name) AS Name from employee where level>3;
-> 'Monica,Sehgal'
1
2
3
4
5

GROUP_CONCAT()

使用 GROUP BY 子句时，可使用 GROUP_CONCAT() 聚集函数将分组中的某个字段进行拼接。如果没有非空值，则返回 NULL。

完整的语法如下所示：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
1
2
3
4

用法示例：

SELECT student_name, GROUP_CONCAT(test_score)
	FROM student
	GROUP BY student_name;

# 或者
SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
       FROM student
       GROUP BY student_name;
1
2
3
4
5
6
7
8

SUBSTR()

用于从字符串中提取子字符串，根据指定的起始位置和长度，从输入字符串中截取一部分子字符串。

SUBSTR() 等同于 SUBSTRING()。

主要用法有：

SUBSTR(str,pos)
SUBSTR(str FROM pos)
SUBSTR(str,pos,len)
SUBSTR(str FROM pos FOR len)
1
2
3
4

参数 str 为原字符串；pos为下标，从 1 开始，如果为负数表示从后往前；len 表示截取的字符数。

SELECT SUBSTRING('Quadratically',5); -- 'ratically'

SELECT SUBSTRING('foobarbar' FROM 4); -- 'barbar'

SELECT SUBSTRING('Quadratically',5,6); -- 'ratica'

SELECT SUBSTRING('Sakila', -3); -- 'ila'

SELECT SUBSTRING('Sakila', -5, 3); -- 'aki'

SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -- 'ki'
1
2
3
4
5
6
7
8
9
10
11

SUBSTRING_INDEX()

按分隔符截取字符串前 N 个或者后 N 个子串。函数原型如下：

SUBSTRING_INDEX(str,delim,count)
1

SUBSTRING_INDEX() 返回字符串按分隔符 delim 分隔的 count 个子串，对分隔符区分大小写。如果计数 count 为正，则返回从左到右 count 个子串。如果计数为负数，则返回从右边到左 count 个子串。

用法示例：

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'

SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
 -> 'mysql.com'
1
2
3
4
5

3.日期和时间函数

NOW()

返回当前日期时间。

如果 fsp 参数指定从0到6的秒精度，则返回值包含该位数的秒小数部分。

NOW([fsp])
1

示例：

SELECT NOW(); 	-- 2023-09-26 04:26:53
SELECT NOW(3);  -- 2023-09-26 07:04:39.211
1
2

CURDATE()

以“YYYY-MM-DD”或 YYYYMMDD 格式返回当前日期，具体取决于该函数是在字符串还是数字上下文中使用。

SELECT CURDATE(); -- 2023-09-26
1

CURTIME()

返回当前时间为 ‘hh:mm:ss’ 或 hhmmss 格式的值，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。以会话时区表示。

如果 fsp 参数指定从 0 到 6 的秒精度，则返回值包含该位数的秒小数部分。

CURTIME([fsp])
1

示例：

SELECT CURTIME();  -- 07:10:44
SELECT CURTIME(3); -- 07:10:57.339
1
2

时间选取函数

SET @dt = '2019-04-16 15:05:12.123456';

DATE(@dt)：返回日期，示例结果2019-04-16
TIME(@dt)：返回时间，示例结果15:05:12.123456
YEAR(@dt)：返回年 ，示例结果2019
MONTH(@dt)：返回月，示例结果4
MONTHNAME(@dt):返回月份的全名，示例结果 April
DAY(@dt)：返回月份中日，在1到31范围内。示例结果16。等同于 
DAYOFMONTH(@dt)：等同于 DAY()
DAYNAME(@dt) ：返回@dt的星期名字。示例结果 Tuesday
DAYOFYEAR(@dt) ：返回@dt在一年中的日数，范围在1到366。示例结果106
HOUR(@dt)：返回时，示例结果15
MINUTE(@dt)：返回分，示例结果05
SECOND(@dt)：返回秒，示例结果12
MICROSECOND(@dt)：返回微秒，示例结果123456
QUARTER(@dt)：返回季度，范围1到4。示例结果2
WEEK(@dt)：返回周，范围0到52。示例结果15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

时间戳与日期转换

Unix 时间戳转换为日期用函数： FROM_UNIXTIME()。

SELECT FROM_UNIXTIME(1156219870);
1

日期转换为 Unix 时间戳用函数： UNIX_TIMESTAMP()。

SELECT UNIX_TIMESTAMP(’2006-11-04 12:23:00′);
1

4.聚合函数

AVG()

返回 expr 的平均值。DISTINCT 选项可用于返回 expr 的不同值的平均值。

如果没有匹配的行或 expr 为NULL，AVG() 返回 NULL。

AVG([DISTINCT] expr) [over_clause]
1

COUNT()

用于统计指定条件的行数。

COUNT(expr) [over_clause]
1

主要有如下几种用法：

-- 返回表的总记录数
COUNT(*)

-- 返回指定列的值的数目（NULL 不计入）
COUNT(<column>)

-- 返回指定列的不同值的数目
COUNT(DISTINCT <column>)

-- 返回符合指定条件的记录数
COUNT(IF(<condition>,TRUE,NULL))

-- 返回符合指定条件的记录数
COUNT(IF(<condition>,TRUE,NULL))

-- 返回符合指定条件的记录中某列不同值的数目
COUNT(DISTINCT <column>, IF(<condition>,TRUE,NULL))
-- 或
COUNT(DISTINCT CASE WHERE <condition> THEN <column> END)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

尖括号中的内容表示实际使用时需要被替换为实际的值。

SUM()

返回 expr 的和。DISTINCT 关键字能用于对 expr 中不同的值求和。

如果没有匹配的行或 expr 为 NULL，SUM() 返回 NULL。

SUM([DISTINCT] expr) [over_clause]
1

5.比较函数

IN()

如果 expr 等于 IN() 列表中的任何值，则返回 1 (true)，否则返回 0 (false)。

expr IN (value,...)
1

示例：

SELECT 2 IN (0,3,5,7); 						-- 0
SELECT 'wefwf' IN ('wee','wefwf','weg'); 	-- 1
1
2

IN() 还可用于行构造器表达式。

SELECT (3,4) IN ((1,2), (3,4)); -- 1
SELECT (3,4) IN ((1,2), (3,5)); -- 0
1
2

ISNULL()

测试参数是否为 NULL。

SELECT ISNULL(1+1); -- 0
SELECT ISNULL(1/0); -- 1
1
2

LEAST()

返回最小的参数。

LEAST(value1,value2,...)
1

示例：

SELECT LEAST(2,0); 					-- 0
SELECT LEAST(34.0,3.0,5.0,767.0); 	-- 3.0
mysql> SELECT LEAST('B','A','C');	-- 'A'
1
2
3

6.其它函数

INET_ATON()

给定以字符串形式表示的点分十进制 IPv4 地址，返回一个整数，该整数以网络字节顺序（大端）表示该地址的数值。

如果 expr 非法或为 NULL，则返回 NULL。

INET_ATON(expr)
1

示例：

SELECT INET_ATON('10.0.5.9'); -- 167773449
1

对于本例，返回值计算方式为10×256^3 + 0×256^2 + 5×256 + 9。

INET_NTOA()

给定一个按网络字节顺序排列的数字，返回点分十进制 IPv4 地址。

如果 expr 非法或为 NULL，则返回 NULL。

INET_NTOA(expr)
1

示例：

SELECT INET_NTOA(167773449); -- '10.0.5.9'
1

---

参考文献

MySQL 8.0 Reference Manual :: 12 Functions and Operators
8.2.1.22 Row Constructor Expression Optimization