MySQL 常用函数的使用

文章目录

前言
一、概述
二、常用函数
三、相关文档

前言

经常写 sql 时会忘记一些函数的使用，遂即整理一下~~

一、概述

如果想要详细的了解 mysql 中的函数，可去查看 MySQL 官方参考手册：https://dev.mysql.com/doc/refman/5.7/en/preface.html

在这里插入图片描述

以下我整理的是比较常用的一些函数使用，供大家参考。

二、常用函数

1. 字符串函数

目录：

函数名	作用
CHAR_LENGTH()	返回参数中的字符数
CONCAT()	返回连接的字符串
CONCAT_WS()	返回与分隔符连接
FORMAT()	返回格式化为指定小数位数的数字
LIKE	简单的匹配模糊
NOT LIKE	简单匹配模糊的否定
REGEXP 或 RLIKE	字符串是否匹配正则表达式
NOT REGEXP 或 NOT RLIKE	否定正则表达式
REPEAT()	重复一个字符串指定的次数
REPLACE()	替换指定字符串的出现
REVERSE()	反转字符串中的字符
RTRIM()	删除尾随空格
TRIM()	删除前导和尾随空格
UPPER()	转换为大写
LOWER()	转换为写小
FIELD()	后续参数中第一个参数的索引（位置），`支持自定义排序`
FIND_IN_SET()	第一个参数在第二个参数中的索引（位置）
LEFT()	返回具有指定长度的字符串的左边部分，可用于字符串截取
RIGHT()	返回具有指定长度的字符串的右边部分，可用于字符串截取
SUBSTR()	字符串截取
LOCATE()	指定字符的坐标
INSTR()	指定字符的坐标
CONVERT()	类型转换

CHAR_LENGTH( str )

返回 str 字符串的长度，以代码点为单位，多字节字符计为单个代码点。这意味着，对于包含两个 3 字节字符的字符串， LENGTH() 返回 6，而 CHAR_LENGTH() 返回 2，如下所示：

mysql> SET @dolphin:='海豚';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);
+------------------+-----------------------+
| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |
+------------------+-----------------------+
|                6 |                     2 |
+------------------+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

通常可用与统计某个字段中的字符数，例如：查询 user 表中 id 为 1 的用户姓名字数。

mysql> SELECT name , CHAR_LENGTH(name) FROM user WHERE id = 1;
+-----------+-------------------+
| name      | CHAR_LENGTH(name) |
+-----------+-------------------+
| 米大傻    |                 3 |
+-----------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

CONCAT( str1.str2,… )

返回由连接参数产生的字符串。可能有一个或多个参数。如果所有参数都是非二进制字符串，则结果是非二进制字符串。如果参数包含任何二进制字符串，则结果为二进制字符串。一个数字参数被转换成它的等价的非二进制字符串形式。

CONCAT() 的参数中有任意一个为 NULL ，则返回 NULL。

mysql> SELECT CONCAT('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

-- -------------------------------------

mysql> SELECT CONCAT('My', NULL, 'QL');
+--------------------------+
| CONCAT('My', NULL, 'QL') |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set (0.00 sec)

-- -------------------------------------

mysql> SELECT CONCAT(13.8,14.9);
+-------------------+
| CONCAT(13.8,14.9) |
+-------------------+
| 13.814.9          |
+-------------------+
1 row in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CONCAT 函数一般用在 SELECT 查询语法中，用于修改返回字段内容，例如我有一张用户表，信息如下

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
+----+-----------+-----------+------+--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

倘若我现在需要一列：省份 - 姓名的数据，就可以使用 CONCAT 函数，如下

mysql> SELECT CONCAT(province,'-',name) AS fullName, age FROM user;
+---------------------+------+
| fullName            | age  |
+---------------------+------+
| 湖南省-米大傻       |   18 |
| 湖南省-曹大力       |   17 |
| NULL               |   19 |
| 湖北省-张大仙       |   20 |
| 四川省-李大花       |   22 |
+---------------------+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

其中用户名为 薛大鹏 的用户，由于字段 province 对应的值为 NULL ，使用 CONCAT 进行拼接的结果也就是 NULL。

CONCAT_WS( separator,str1.str2,… )

CONCAT_WS() 代表 Concatenate With Separator 是 CONCAT()函数的特殊形式，第一个参数（separator）是其余参数的分隔符。分隔符的位置在要连接的两个字符串之间。分隔符可以是字符串，其余参数也可以。如果分隔符为 NULL，则结果为 NULL；但如果分隔符后面的参数为NULL，只会被直接忽略掉，而不会导致结果为 NULL。

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
+-------------------------------------------------------+
| CONCAT_WS(',','First name','Second name','Last Name') |
+-------------------------------------------------------+
| First name,Second name,Last Name                      |
+-------------------------------------------------------+
1 row in set (0.01 sec)

-- -----------------------------------------------------------------

mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
+----------------------------------------------+
| CONCAT_WS(',','First name',NULL,'Last Name') |
+----------------------------------------------+
| First name,Last Name                         |
+----------------------------------------------+
1 row in set (0.00 sec)

-- -----------------------------------------------------------------

mysql> SELECT CONCAT_WS(NULL,'First name','Second name','Last Name');
+--------------------------------------------------------+
| CONCAT_WS(NULL,'First name','Second name','Last Name') |
+--------------------------------------------------------+
| NULL                                                   |
+--------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

例如我有一张用户表，信息如下

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
+----+-----------+-----------+------+--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

倘若我现在需要一列：省份 - 姓名的数据，就可以使用 CONCAT_WS 函数，如下

mysql> SELECT CONCAT_WS('-',province,name) AS fullName, age FROM user;
+---------------------+------+
| fullName            | age  |
+---------------------+------+
| 湖南省-米大傻       |   18 |
| 湖南省-曹大力       |   17 |
| 薛大鹏              |   19 |
| 湖北省-张大仙       |   20 |
| 四川省-李大花       |   22 |
+---------------------+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

其中用户名为 薛大鹏 的用户，即使字段 province 对应的值为 NULL ，使用 CONCAT_WS 进行拼接会跳过为 NULL 的值，从而使输出结果不为 NULL，这是不同于 CONCAT 函数的一点。

FORMAT( X,D[,locale] )

将数字 X 格式化为类似的格式 #,###,###.##，四舍五入到 D 小数位，并将结果作为字符串返回。如果 D是 0，则结果没有小数点或小数部分。可选的第三个参数允许指定区域设置以用于结果数字的小数点、千位分隔符和分隔符之间的分组。lc_time_names允许的区域设置值与系统变量的合法值相同。如果未指定语言环境，则默认为 en_US。

mysql> SELECT FORMAT(12332.123456, 4);
+-------------------------+
| FORMAT(12332.123456, 4) |
+-------------------------+
| 12,332.1235             |
+-------------------------+
1 row in set (0.00 sec)

-- ----------------------------------

mysql> SELECT FORMAT(12332.1,4);
+-------------------+
| FORMAT(12332.1,4) |
+-------------------+
| 12,332.1000       |
+-------------------+
1 row in set (0.00 sec)

-- ----------------------------------

mysql> SELECT FORMAT(12332.2,0);
+-------------------+
| FORMAT(12332.2,0) |
+-------------------+
| 12,332            |
+-------------------+
1 row in set (0.00 sec)

-- ----------------------------------

mysql> SELECT FORMAT(12332.2,2,'de_DE');
+---------------------------+
| FORMAT(12332.2,2,'de_DE') |
+---------------------------+
| 12.332,20                 |
+---------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

de_DE语言环境使用 点(.) 来分隔千位和 逗号(,) 来分隔小数点。

这个函数可以用于将金额转换成常见带 千位分隔符 的形式展示。例如我有一张产品表

mysql> SELECT * FROM product;
+--------+---------------------+----------+-------+
| code   | title               | price    | stock |
+--------+---------------------+----------+-------+
| P00001 | 外星人X14           | 11969.00 |   600 |
| P00002 | 外星人M15           |  8969.00 |   800 |
| P00003 | 外星人X15           | 24999.00 |   550 |
| P00004 | 苹果MacBook         |  7198.00 |   752 |
| P00005 | 华硕（ASUS）ROG     | 44239.00 |   480 |
+--------+---------------------+----------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

要计算每个产品的总价值，可以使用库存数量乘以价格，如下查询语句：

mysql> SELECT code,title,CONCAT('￥',FORMAT(stock*price,2)) AS totalPrice FROM product;
+--------+---------------------+------------------+
| code   | title               | totalPrice       |
+--------+---------------------+------------------+
| P00001 | 外星人X14           | ￥7,181,400.00   |
| P00002 | 外星人M15           | ￥7,175,200.00   |
| P00003 | 外星人X15           | ￥13,749,450.00  |
| P00004 | 苹果MacBook         | ￥5,412,896.00   |
| P00005 | 华硕（ASUS）ROG     | ￥21,234,720.00  |
+--------+---------------------+------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

expr LIKE pat

使用 SQL 模式进行模糊匹配。返回 1( TRUE) 或 0( FALSE)。模糊匹配的内容不必是文字字符串。例如，它可以指定为字符串表达式或表格列。在后一种情况下，列必须定义为 MySQL 字符串类型之一。

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a%';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a%' |
+------------+---------------+
|          0 |             1 |
+------------+---------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

LIKE 函数通常用于 WHERE 表达式中，以搜索匹配字段中的指定内容，语法如下：

... WHERE `column` LIKE pattern ...
1

LIKE 函数通常跟通配符搭配使用：

% 匹配任意数量的字符，甚至是零个字符。
_ 只匹配一个字符。

例如我有一张用户表，信息如下

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

查询姓 米 的用户

mysql> SELECT * FROM user where name LIKE '米%';
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
+----+-----------+-----------+------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

通配符还可以使用多个，比如查询姓名中带 小 的用户

mysql> SELECT * FROM user where name LIKE '%小%';
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

expr NOT LIKE pat

在 LIKE 前面加上 NOT 时，表示与 LIKE 相反的意思，即选择 expr 不包含 pat 的数据记录

NOT LIKE 函数和 LIKE 函数一样，通常用于 WHERE 表达式中，以排除搜索匹配字段中的指定内容，语法如下：

... WHERE `column` NOT LIKE pattern ...
1

NOT LIKE 函数通常跟通配符搭配使用：

% 匹配任意数量的字符，甚至是零个字符。
_ 只匹配一个字符。

例如我有一张用户表，信息如下

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

查询不姓 米 的用户

mysql> SELECT * FROM user where name NOT LIKE '米%';
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

查询身份信息中不包括 南 的用户

mysql> SELECT * FROM user where province NOT LIKE '%南%' OR province is NULL;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
+----+-----------+-----------+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

值得注意得是当选择的 expr 为 NULL 时，不管 pat 为什么条件都不会排除掉。

mysql> SELECT '湖南省' NOT LIKE '%南%','湖北省' NOT LIKE '%南%',NULL NOT LIKE '%南%';
+------------------------------+------------------------------+-----------------------+
| '湖南省' NOT LIKE '%南%'     | '湖北省' NOT LIKE '%南%'     | NULL NOT LIKE '%南%'  |
+------------------------------+------------------------------+-----------------------+
|                            0 |                            1 |                  NULL |
+------------------------------+------------------------------+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

expr REGEXP pat

字符串 expr 与指定的正则表达式 pattern 匹配，满足条件则返回 1(TRUE)，否则返回 0(FALSE)。该模式可以是扩展的正则表达式，模式不必是文字字符串。例如，它可以指定为字符串表达式或表格列。

RLIKE 是 REGEXP 的同义词，所以也可以写成 expr RLIKE pat

PS：如果对正则表达式的语法不太熟悉的，可借鉴正则表达式-基础教程

mysql> SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

-- -------------------------------------------------

mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

-- -------------------------------------------------

mysql> SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

expr NOT REGEXP pat

字符串 expr 与指定的正则表达式 pattern 不匹配，满足条件则返回 1(TRUE)，否则返回 0(FALSE)。该模式可以是扩展的正则表达式，模式不必是文字字符串。例如，它可以指定为字符串表达式或表格列。

RLIKE 是 REGEXP 的同义词，所以也可以写成 expr NOT RLIKE pat

PS：如果对正则表达式的语法不太熟悉的，可借鉴正则表达式-基础教程

mysql> SELECT '20220922' NOT REGEXP '^\d{4}-\d{1,2}-\d{1,2}';
+------------------------------------------------+
| '20220922' NOT REGEXP '^\d{4}-\d{1,2}-\d{1,2}' |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

REPEAT(str,count)

返回由字符串 str 重复 count 次数组成的字符串。如果 count 小于 1，则返回一个空字符串。

mysql> SELECT REPEAT('MySQL',3);
+-------------------+
| REPEAT('MySQL',3) |
+-------------------+
| MySQLMySQLMySQL   |
+-------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT REPEAT('MySQL',-1);
+--------------------+
| REPEAT('MySQL',-1) |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

REPLACE(str,from_str,to_str)

返回 str 所有出现的字符串都被字符串 from_str 替换的字符串 to_str。 REPLACE() 搜索时执行区分大小写的匹配 from_str。

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+-------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww') |
+-------------------------------------+
| WwWwWw.mysql.com                    |
+-------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

REVERSE(str)

返回 str 字符顺序颠倒的字符串。

mysql> SELECT REVERSE('abc');
+----------------+
| REVERSE('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

这个函数是多字节安全的。

RTRIM(str)

返回 str 删除了尾随空格字符的字符串。

mysql> SELECT RTRIM('barbar   ');
+--------------------+
| RTRIM('barbar   ') |
+--------------------+
| barbar             |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

这个函数是多字节安全的。

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

返回删除 str 了所有前缀或后缀的字符串。remstr如果没有给出任何说明符BOTH, LEADING, or TRAILING，BOTH则假定。 remstr是可选的，如果未指定，则删除空格。

mysql> SELECT TRIM('  bar   ');
+------------------+
| TRIM('  bar   ') |
+------------------+
| bar              |
+------------------+
1 row in set (0.00 sec)

-------------------------------------------------

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
+------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxbarxxx') |
+------------------------------------+
| barxxx                             |
+------------------------------------+
1 row in set (0.00 sec)


-----------------------------------------------
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
+---------------------------------+
| TRIM(BOTH 'x' FROM 'xxxbarxxx') |
+---------------------------------+
| bar                             |
+---------------------------------+
1 row in set (0.00 sec)

----------------------------------------------

mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
+-------------------------------------+
| TRIM(TRAILING 'xyz' FROM 'barxxyz') |
+-------------------------------------+
| barx                                |
+-------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

UPPER(str)

根据当前字符集映射返回 str 所有字符都变为大写的字符串。默认值为 latin1(cp1252 西欧)。

mysql> SELECT UPPER('hello');
+----------------+
| UPPER('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

LOWER(str)

返回 str 根据当前字符集映射将所有字符更改为小写的字符串。默认值为 latin1(cp1252 西欧)。

mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

FIELD( separator,str1.str2,… )

返回 , , , 列表str 中的索引（位置）。如果没有找到则返回。str1str2str3...0str

如果所有参数 FIELD() 都是字符串，则所有参数都作为字符串进行比较。如果所有参数都是数字，则将它们作为数字进行比较。否则，参数将作为双精度进行比较。

如果 str 是NULL，则返回值是 0 因为 NULL与任何值的相等比较失败。FIELD() 是的补码ELT()。

mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
+-------------------------------------------+
| FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff') |
+-------------------------------------------+
|                                         2 |
+-------------------------------------------+
1 row in set (0.02 sec)

--------------------------------------------------------

mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
+-------------------------------------------+
| FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

--------------------------------------------------------

mysql> SELECT FIELD(null, 'Aa', 'Bb', null, 'Dd', 'Ff');
+-------------------------------------------+
| FIELD(null, 'Aa', 'Bb', null, 'Dd', 'Ff') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

这个函数还有一个比较常用的作用就是自定义排序，比如以下用户表：

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12

如果想要对这些用户的按照指定年龄进行排序，则可以使用 FIELD() 函数

mysql> SELECT * FROM user order by FIELD(age,20,18,17,19,21,22);
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL      |   19 |      1 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

FIND_IN_SET(str,strlist)

str 要查询的字符串

strlist 字段名参数以 , 分隔如 (1,2,6,8,10,22)

假如字符串 str 在由 N 子链组成的字符串列表 strlist 中，则返回值的范围在 1 到 N 之间。如果 str 不在 strlist 或 strlist 为空字符串，则返回值为 0 。如任意一个参数为 NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号 , 时将无法正常运行。

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
+----------------------------+
| FIND_IN_SET('b','a,b,c,d') |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

因为 b 在 strlist 集合中放在 2 的位置，所以返回 2。

比如以下产品表，type 字段表示产品的类型，一个产品可有多个类型：

mysql> SELECT * FROM product;
+--------+---------------------+----------+-------+---------+
| code   | title               | price    | stock | type    |
+--------+---------------------+----------+-------+---------+
| P00001 | 外星人X14           | 11969.00 |   600 | 1,2,4,6 |
| P00002 | 外星人M15           |  8969.00 |   800 | 2,4,5   |
| P00003 | 外星人X15           | 24999.00 |   550 | 1,4,6   |
| P00004 | 苹果MacBook         |  7198.00 |   752 | 1,6,14  |
| P00005 | 华硕（ASUS）ROG     | 44239.00 |   480 | 3,5     |
+--------+---------------------+----------+-------+---------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

如果我要查询产品类型中有 4 的产品，可以这么写

mysql> SELECT * FROM product where FIND_IN_SET('4',type);
+--------+--------------+----------+-------+---------+
| code   | title        | price    | stock | type    |
+--------+--------------+----------+-------+---------+
| P00001 | 外星人X14    | 11969.00 |   600 | 1,2,4,6 |
| P00002 | 外星人M15    |  8969.00 |   800 | 2,4,5   |
| P00003 | 外星人X15    | 24999.00 |   550 | 1,4,6   |
+--------+--------------+----------+-------+---------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

LOCATE(substr,str), LOCATE(substr,str,pos)

主要用于不区分大小写的字符串查找，可以返回字符串中第一次出现的子字符串的位置。

substr：必须项，要在字符串中搜索的子字符串
str：必须项，要搜索的字符串
pos：可选的，搜索的起始位置，位置 1 是默认值

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7
1
2
3
4
5
6

INSTR(str,substr)

作用是返回 str 字符串在 substr字符串的第一次出现的位置，如果没有找到，则返回 0。

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0
1
2
3
4

2. 时间和日期函数

目录：

函数名	作用
ADDDATE()	将时间值（间隔）添加到日期值
ADDTIME()	添加时间
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE()	提取日期或日期时间表达式的日期部分
DATE_ADD()	将时间值（间隔）添加到日期值
DATE_SUB()	从日期中减去时间值（间隔）
DATE_FORMAT()	按指定格式日期
DAY()	返回月份中的第几天 (0-31)
DAYNAME()	返回工作日的名称
DAYOFMONTH()	返回月份中的第几天 (0-31)
DAYOFWEEK()	返回参数的工作日索引
DAYOFYEAR()	返回一年中的某一天 (1-366)
EXTRACT()	提取日期的一部分
FROM_DAYS()	将天数转换为日期
GET_FORMAT()	返回日期格式字符串
LAST_DAY()	返回参数的月份的最后一天
MICROSECOND()	从参数返回微秒
SECOND()	从参数返回秒
MINUTE()	从参数返回分钟
HOUR()	提取小时
MONTH()	从过去的日期返回月份
MONTHNAME()	返回月份的名称
NOW()	返回当前日期和时间
PERIOD_ADD()	为年月添加期间
PERIOD_DIFF()	返回期间之间的月数
DATEDIFF()	返回期间之间的数天
QUARTER()	从日期参数返回季度
SEC_TO_TIME()	将秒转换为 ‘hh:mm:ss’ 格式
STR_TO_DATE()	将字符串转换为日期
SUBTIME()	减去时间
WEEK()	返回周数
WEEKDAY()	返回工作日索引
WEEKOFYEAR()	返回日期的日历周 (1-53)
YEAR()	返回年份
YEARWEEK()	返回年份和星期

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

ADDDATE() 函数用于将指定的时间间隔添加到给定的日期和时间。添加间隔后，它将返回日期或 DateTime。

date 参数指定开始日期或日期时间值。 expr 是一个表达式，指定要从开始日期添加或减去的间隔值。expr 被评估为字符串；它可能 - 以负间隔开头。unit是一个关键字，表示应该解释表达式的单位。

有关 INTERVAL unit 的参数信息，可参阅 Temporal Intervals

mysql> SELECT ADDDATE('2022-09-23 10:00:00',INTERVAL 90 SECOND);
+---------------------------------------------------+
| ADDDATE('2022-09-23 10:00:00',INTERVAL 90 SECOND) |
+---------------------------------------------------+
| 2022-09-23 10:01:30                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

----------------------------------------------------------------

mysql> SELECT ADDDATE('2022-09-23 10:00:00',INTERVAL 25 DAY);
+------------------------------------------------+
| ADDDATE('2022-09-23 10:00:00',INTERVAL 25 DAY) |
+------------------------------------------------+
| 2022-10-18 10:00:00                            |
+------------------------------------------------+
1 row in set (0.00 sec)

----------------------------------------------------------------

mysql> SELECT ADDDATE('2022-09-23 10:00:00',INTERVAL '1:1' MINUTE_SECOND);
+-------------------------------------------------------------+
| ADDDATE('2022-09-23 10:00:00',INTERVAL '1:1' MINUTE_SECOND) |
+-------------------------------------------------------------+
| 2022-09-23 10:01:01                                         |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

----------------------------------------------------------------

mysql> SELECT ADDDATE('2022-09-23 10:00:00',INTERVAL '-1:30:15' HOUR_SECOND);
+----------------------------------------------------------------+
| ADDDATE('2022-09-23 10:00:00',INTERVAL '-1:30:15' HOUR_SECOND) |
+----------------------------------------------------------------+
| 2022-09-23 08:29:45                                            |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

----------------------------------------------------------------

mysql> SELECT ADDDATE('2022-09-23 10:00:00',-10);
+------------------------------------+
| ADDDATE('2022-09-23 10:00:00',-10) |
+------------------------------------+
| 2022-09-13 10:00:00                |
+------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

ADDTIME(expr1,expr2)

将一个日期或日期时间表达式同一个时间表达式相加，返回一个由两者相加生成新的日期时间表达式。

mysql> SELECT ADDTIME('2022-09-23 10:00:00',-10);
+------------------------------------+
| ADDTIME('2022-09-23 10:00:00',-10) |
+------------------------------------+
| 2022-09-23 09:59:50                |
+------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------------

mysql> SELECT ADDTIME('2022-09-23 10:00:00','3 10:00:00');
+---------------------------------------------+
| ADDTIME('2022-09-23 10:00:00','3 10:00:00') |
+---------------------------------------------+
| 2022-09-26 20:00:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------------

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997                               |
+-----------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CURDATE()

根据函数是在字符串还是数字上下文中使用，将当前日期作为值或格式返回。YYYY-MM-DD 或 YYYYMMDD

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2022-09-24 |
+------------+
1 row in set (0.00 sec)

------------------------

mysql> SELECT CURDATE() + 0;
+---------------+
| CURDATE() + 0 |
+---------------+
|      20220924 |
+---------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CURTIME([fsp])

根据函数是在字符串还是数字上下文中使用，以值 hh:mm:ss 或格式返回当前时间。hhmmss 该值以会话时区表示。

如果 fsp 给定参数以指定从 0 到 6 的小数秒精度，则返回值包括那么多数字的小数秒部分。

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 11:01:13  |
+-----------+
1 row in set (0.00 sec)

------------------------

mysql> SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
|        110156 |
+---------------+
1 row in set (0.00 sec)

------------------------

mysql> SELECT CURTIME(6);
+-----------------+
| CURTIME(6)      |
+-----------------+
| 11:03:32.520346 |
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

DATE(expr)

提取日期或日期时间表达式的日期部分 expr。

mysql> SELECT DATE('2022-09-24 11:06:58');
+-----------------------------+
| DATE('2022-09-24 11:06:58') |
+-----------------------------+
| 2022-09-24                  |
+-----------------------------+
1 row in set (0.00 sec)

-----------------------------------------

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2022-09-24  |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DATE_ADD(date,INTERVAL expr unit)

将时间值（间隔）添加到日期值。该 date 参数指定开始日期或日期时间值。expr 是一个表达式，指定要从开始日期添加或减去的间隔值。expr 被评估为字符串；它可能-以负间隔开头。unit 是一个关键字，表示应该解释表达式的单位。

有关时间间隔语法的更多信息，包括说明符的完整列表、每个值 unit 的参数的预期形式以及时间算术中操作数解释的规则，请参阅 Temporal Intervals。

返回值取决于参数：

DATE 如果 date参数是一个 DATE 值并且您的计算仅涉及 YEAR、 MONTH 和 DAY 部分（即，没有时间部分）。
DATETIME 如果第一个参数是 DATETIME（或 TIMESTAMP）值，或者如果第一个参数是 aDATE 并且 unit 值使用 HOURS、MINUTES 或 SECONDS。
否则字符串。

为确保结果为 DATETIME，您可以使用 CAST() 将第一个参数转换为 DATETIME 。

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
+---------------------------------------+
| DATE_ADD('2018-05-01',INTERVAL 1 DAY) |
+---------------------------------------+
| 2018-05-02                            |
+---------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------

mysql> SELECT DATE_ADD('2020-12-31 23:59:59',INTERVAL 1 SECOND);
+---------------------------------------------------+
| DATE_ADD('2020-12-31 23:59:59',INTERVAL 1 SECOND) |
+---------------------------------------------------+
| 2021-01-01 00:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------

mysql> SELECT DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND);
+--------------------------------------------------------------+
| DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) |
+--------------------------------------------------------------+
| 2101-01-01 00:01:00                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------

mysql> SELECT DATE_ADD('1900-01-01 00:00:00',INTERVAL '-1 10' DAY_HOUR);
+-----------------------------------------------------------+
| DATE_ADD('1900-01-01 00:00:00',INTERVAL '-1 10' DAY_HOUR) |
+-----------------------------------------------------------+
| 1899-12-30 14:00:00                                       |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------

mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',INTERVAL '1.999999' SECOND_MICROSECOND);
+-------------------------------------------------------------------------------+
| DATE_ADD('1992-12-31 23:59:59.000002',INTERVAL '1.999999' SECOND_MICROSECOND) |
+-------------------------------------------------------------------------------+
| 1993-01-01 00:00:01.000001                                                    |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

DATE_SUB(date,INTERVAL expr unit)

从日期中减去时间值（间隔）。该 date 参数指定开始日期或日期时间值。expr 是一个表达式，指定要从开始日期添加或减去的间隔值。expr 被评估为字符串；它可能-以负间隔开头。unit 是一个关键字，表示应该解释表达式的单位。

有关时间间隔语法的更多信息，包括说明符的完整列表、每个值 unit 的参数的预期形式以及时间算术中操作数解释的规则，请参阅 Temporal Intervals。

返回值取决于参数：

DATE 如果 date参数是一个 DATE 值并且您的计算仅涉及 YEAR、 MONTH 和 DAY 部分（即，没有时间部分）。
DATETIME 如果第一个参数是 DATETIME（或 TIMESTAMP）值，或者如果第一个参数是 aDATE 并且 unit 值使用 HOURS、MINUTES 或 SECONDS。
否则字符串。

为确保结果为 DATETIME，您可以使用 CAST() 将第一个参数转换为 DATETIME 。

mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
+----------------------------------------+
| DATE_SUB('2018-05-01',INTERVAL 1 YEAR) |
+----------------------------------------+
| 2017-05-01                             |
+----------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------

mysql> SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);
+---------------------------------------------------------------+
| DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND) |
+---------------------------------------------------------------+
| 2024-12-30 22:58:59                                           |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

---------------------------------------------------

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02                              |
+-----------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

DATE_FORMAT(date,format)

date 根据 format 字符串格式化值。

说明符可以在 format 字符串中使用。该 % 字符在格式说明符字符之前是必需的。说明符的详细介绍可参见 specifier 。

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+
1 row in set (0.00 sec)

-----------------------------------------------------------

mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+
1 row in set (0.00 sec)

-----------------------------------------------------------

mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j');
+-----------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j') |
+-----------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277                                  |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

-----------------------------------------------------------

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');
+-----------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w') |
+-----------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6                        |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

-----------------------------------------------------------

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52                            |
+------------------------------------+
1 row in set (0.00 sec)

-----------------------------------------------------------

mysql> SELECT DATE_FORMAT('2006-06-01', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-01', '%d') |
+---------------------------------+
| 01                              |
+---------------------------------+
1 row in set (0.00 sec)

-----------------------------------------------------------

mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
+-----------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2022-09-24 12:00:34                     |
+-----------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

DAY(date)

DAY() 是的同义词 DAYOFMONTH()。

DAYNAME(date)

返回的工作日名称 date。

mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Saturday       |
+----------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYNAME('2022-09-22 10:12:00');
+--------------------------------+
| DAYNAME('2022-09-22 10:12:00') |
+--------------------------------+
| Thursday                       |
+--------------------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYNAME('2022-09-22');
+-----------------------+
| DAYNAME('2022-09-22') |
+-----------------------+
| Thursday              |
+-----------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYNAME('20220922');
+---------------------+
| DAYNAME('20220922') |
+---------------------+
| Thursday            |
+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

DAYOFMONTH(date)

返回 date、到范围内 1 的 31、或具有零日部分 0 的日期（例如 0000-00-00 或）的月份中的某天。2008-00-00

mysql> SELECT DAYOFMONTH(NOW());
+-------------------+
| DAYOFMONTH(NOW()) |
+-------------------+
|                24 |
+-------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFMONTH('2022-09-22 10:12:00');
+-----------------------------------+
| DAYOFMONTH('2022-09-22 10:12:00') |
+-----------------------------------+
|                                22 |
+-----------------------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFMONTH('2022-09-22');
+--------------------------+
| DAYOFMONTH('2022-09-22') |
+--------------------------+
|                       22 |
+--------------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFMONTH('20220922');
+------------------------+
| DAYOFMONTH('20220922') |
+------------------------+
|                     22 |
+------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

DAYOFWEEK(date)

返回 date ( 1= Sunday, 2= Monday, …, 7= Saturday) 的工作日索引。这些索引值对应于 ODBC 标准。

mysql> SELECT DAYOFWEEK(NOW());
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFWEEK('2022-09-24 10:12:00');
+----------------------------------+
| DAYOFWEEK('2022-09-24 10:12:00') |
+----------------------------------+
|                                7 |
+----------------------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFWEEK('2022-09-24');
+-------------------------+
| DAYOFWEEK('2022-09-24') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFWEEK('20220924');
+-----------------------+
| DAYOFWEEK('20220924') |
+-----------------------+
|                     7 |
+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

DAYOFYEAR(date)

返回一年中的某一天， date 范围 1 为 366 。

mysql> SELECT DAYOFYEAR(NOW());
+------------------+
| DAYOFYEAR(NOW()) |
+------------------+
|              267 |
+------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFYEAR('2022-09-24 10:12:00');
+----------------------------------+
| DAYOFYEAR('2022-09-24 10:12:00') |
+----------------------------------+
|                              267 |
+----------------------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFYEAR('2022-09-24');
+-------------------------+
| DAYOFYEAR('2022-09-24') |
+-------------------------+
|                     267 |
+-------------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT DAYOFYEAR('20220924');
+-----------------------+
| DAYOFYEAR('20220924') |
+-----------------------+
|                   267 |
+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

EXTRACT(unit FROM date)

用于从一个 date 或者 interval 类型中截取到特定的部分。有关 unit 参数的信息，请参阅 Temporal Intervals

mysql> SELECT EXTRACT(YEAR FROM NOW()) year,EXTRACT(MONTH FROM NOW()) month,EXTRACT(DAY FROM NOW()) day;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2022 |     9 |   24 |
+------+-------+------+
1 row in set (0.00 sec)

--------------------------------------------------

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2022-09-24 10:12:00');
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2022-09-24 10:12:00') |
+------------------------------------------------+
|                                         202209 |
+------------------------------------------------+
1 row in set (0.00 sec)

--------------------------------------------------

mysql> SELECT EXTRACT(DAY_MINUTE FROM '2022-09-24 10:12:00');
+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM '2022-09-24 10:12:00') |
+------------------------------------------------+
|                                         241012 |
+------------------------------------------------+
1 row in set (0.00 sec)

--------------------------------------------------

mysql> SELECT EXTRACT(MICROSECOND FROM '2022-09-24 10:30:00.000123');
+--------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2022-09-24 10:30:00.000123') |
+--------------------------------------------------------+
|                                                    123 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

FROM_DAYS(N)

给定天数 N，返回一个 DATE 值。

mysql> SELECT FROM_DAYS(730669);
+-------------------+
| FROM_DAYS(730669) |
+-------------------+
| 2000-07-03        |
+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

GET_FORMAT({DATE|TIME|DATETIME}, {‘EUR’|‘USA’|‘JIS’|‘ISO’|‘INTERNAL’})

该函数将为指定的参数返回格式化的字符串。

参数：

DATE |时间|约会时间 -日期或时间或DateTime。
“ EUR” | “美国” | ‘JIS’| “ ISO” | “内部” - 使用了不同的格式。

此函数有助于将日期或时间或 DateTime 转换为指定参数的格式化字符串。如果与 DATE_FORMAT() 函数结合使用，则 GET_FORMAT() 函数将更有用。

函数调用	结果
GET_FORMAT(DATE,‘USA’)	‘%m.%d.%Y’
GET_FORMAT(DATE,‘JIS’)	‘%Y-%m-%d’
GET_FORMAT(DATE,‘ISO’)	‘%Y-%m-%d’
GET_FORMAT(DATE,‘EUR’)	‘%d.%m.%Y’
GET_FORMAT(DATE,‘INTERNAL’)	‘%Y%m%d’
GET_FORMAT(DATETIME,‘USA’)	‘%Y-%m-%d %H.%i.%s’
GET_FORMAT(DATETIME,‘JIS’)	‘%Y-%m-%d %H:%i:%s’
GET_FORMAT(DATETIME,‘ISO’)	‘%Y-%m-%d %H:%i:%s’
GET_FORMAT(DATETIME,‘EUR’)	‘%Y-%m-%d %H.%i.%s’
GET_FORMAT(DATETIME,‘INTERNAL’)	‘%Y%m%d%H%i%s’
GET_FORMAT(TIME,‘USA’)	‘%h:%i:%s %p’
GET_FORMAT(TIME,‘JIS’)	‘%H:%i:%s’
GET_FORMAT(TIME,‘ISO’)	‘%H:%i:%s’
GET_FORMAT(TIME,‘EUR’)	‘%H.%i.%s’
GET_FORMAT(TIME,‘INTERNAL’)	‘%H%i%s’

mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'EUR'));
+-------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(DATE,'EUR')) |
+-------------------------------------------+
| 24.09.2022                                |
+-------------------------------------------+
1 row in set (0.01 sec)

-------------------------------------------------------

mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'JIS'));
+-------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(DATE,'JIS')) |
+-------------------------------------------+
| 2022-09-24                                |
+-------------------------------------------+
1 row in set (0.00 sec)

-------------------------------------------------------

mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
+--------------------------------------------------+
| STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')) |
+--------------------------------------------------+
| 2003-10-31                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

LAST_DAY(date)

获取日期或日期时间值并返回该月最后一天的相应值。如果参数无效则返回 NULL 。

mysql> SELECT LAST_DAY(NOW());
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2022-09-30      |
+-----------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT LAST_DAY(20220924);
+--------------------+
| LAST_DAY(20220924) |
+--------------------+
| 2022-09-30         |
+--------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT LAST_DAY('2022-09-24');
+------------------------+
| LAST_DAY('2022-09-24') |
+------------------------+
| 2022-09-30             |
+------------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT LAST_DAY('2022-09-24 10:10:00');
+---------------------------------+
| LAST_DAY('2022-09-24 10:10:00') |
+---------------------------------+
| 2022-09-30                      |
+---------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

MICROSECOND(expr)

将时间或日期时间表达式的微秒作为从到 expr 范围内的数字返回 0~999999

mysql> SELECT MICROSECOND('12:00:00.123456');
+--------------------------------+
| MICROSECOND('12:00:00.123456') |
+--------------------------------+
|                         123456 |
+--------------------------------+
1 row in set (0.00 sec)

--------------------------------------------

mysql> SELECT MICROSECOND('2019-12-31 23:59:59.000010');
+-------------------------------------------+
| MICROSECOND('2019-12-31 23:59:59.000010') |
+-------------------------------------------+
|                                        10 |
+-------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SECOND(expr)

将时间或日期时间表达式的秒作为从到 expr 范围内的数字返回 0~60

mysql> SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

--------------------------------------------

mysql> SELECT SECOND('2019-12-31 23:59:59');
+-------------------------------+
| SECOND('2019-12-31 23:59:59') |
+-------------------------------+
|                            59 |
+-------------------------------+
1 row in set (0.00 sec)

--------------------------------------------

mysql> SELECT NOW(),SECOND(NOW());
+---------------------+---------------+
| NOW()               | SECOND(NOW()) |
+---------------------+---------------+
| 2022-09-24 17:36:45 |            45 |
+---------------------+---------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

MINUTE(expr)

将时间或日期时间表达式的分钟作为从到 expr 范围内的数字返回 0~60

mysql> SELECT MINUTE('2008-02-03 10:05:03');
+-------------------------------+
| MINUTE('2008-02-03 10:05:03') |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set (0.01 sec)

-------------------------------------------

mysql> SELECT MINUTE('10:05:03');
+--------------------+
| MINUTE('10:05:03') |
+--------------------+
|                  5 |
+--------------------+
1 row in set (0.00 sec)

-------------------------------------------

mysql> SELECT NOW(),MINUTE(NOW());
+---------------------+---------------+
| NOW()               | MINUTE(NOW()) |
+---------------------+---------------+
| 2022-09-24 17:37:29 |            37 |
+---------------------+---------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

HOUR(time)

返回小时 time。返回值的范围是 0 时间 23值。但是，TIME 值的范围实际上要大得多，因此 HOUR 可以返回大于的值 23。

mysql> SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
|               10 |
+------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT HOUR('272:59:59');
+-------------------+
| HOUR('272:59:59') |
+-------------------+
|               272 |
+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

MONTH(expr)

返回月份，在 1 到 12 月date 的范围内，

mysql> SELECT MONTH('2008-02-03');
+---------------------+
| MONTH('2008-02-03') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

-------------------------------------

mysql> SELECT MONTH('20220902');
+-------------------+
| MONTH('20220902') |
+-------------------+
|                 9 |
+-------------------+
1 row in set (0.00 sec)

-------------------------------------

mysql> SELECT NOW(),MONTH(NOW());
+---------------------+--------------+
| NOW()               | MONTH(NOW()) |
+---------------------+--------------+
| 2022-09-24 17:41:12 |            9 |
+---------------------+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

MONTHNAME(date)

返回一个字符串，指示指定的月份。

mysql> SELECT MONTHNAME('2008-02-03');
+-------------------------+
| MONTHNAME('2008-02-03') |
+-------------------------+
| February                |
+-------------------------+
1 row in set (0.00 sec)

-------------------------------------

mysql> SELECT MONTHNAME('20220927');
+-----------------------+
| MONTHNAME('20220927') |
+-----------------------+
| September             |
+-----------------------+
1 row in set (0.00 sec)

-------------------------------------

mysql> SELECT MONTHNAME('2022-09-27 10:12:58');
+----------------------------------+
| MONTHNAME('2022-09-27 10:12:58') |
+----------------------------------+
| September                        |
+----------------------------------+
1 row in set (0.00 sec)

-------------------------------------

mysql> SELECT NOW(),MONTHNAME(NOW());
+---------------------+------------------+
| NOW()               | MONTHNAME(NOW()) |
+---------------------+------------------+
| 2022-09-27 16:17:37 | September        |
+---------------------+------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

NOW([fsp])

根据函数是在字符串还是数字上下文中使用，将当前日期和时间以 YYYY-MM-DD hh:mm:ss 或 YYYYMMDDhhmmss 的格式返回。

如果fsp给定参数以指定从 0 到 6 的小数秒精度，则返回值包括那么多数字的小数秒部分。

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-09-27 16:27:03 |
+---------------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT NOW() + 0;
+----------------+
| NOW() + 0      |
+----------------+
| 20220927162736 |
+----------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT NOW(6) + 0;
+-----------------------+
| NOW(6) + 0            |
+-----------------------+
| 20220927162806.087253 |
+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

PERIOD_ADD(P,N)

将 N 月份添加到 P（格式为 YYMM 或 YYYYMM）。返回格式为的值 YYYYMM。

mysql> SELECT PERIOD_ADD(200801,2);
+----------------------+
| PERIOD_ADD(200801,2) |
+----------------------+
|               200803 |
+----------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT PERIOD_ADD(0802,2);
+--------------------+
| PERIOD_ADD(0802,2) |
+--------------------+
|             200804 |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

PERIOD_DIFF(P1,P2)

返回期间 P1 和 P2 之间的月数。P1 和 P2 应该是格式 YYMM 或 YYYYMM。

注意：期间参数 P1 和 P2 不是日期值。

mysql> SELECT PERIOD_DIFF(200802,200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
|                         11 |
+----------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

DATEDIFF(P1,P2)
返回期间 P1 和 P2 之间的天数。 P1 和 P2参数是合法的日期或日期/时间表达式。

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

--------------------------------------------------------

mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31 15:35:06');
+-------------------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31 15:35:06') |
+-------------------------------------------------------+
|                                                   -31 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

QUARTER(date)

返回指定时间一年中所在的季度， date 范围 1 为 4。

mysql> SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT QUARTER('20080401');
+---------------------+
| QUARTER('20080401') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT QUARTER('2008-04-01 10:12:58');
+--------------------------------+
| QUARTER('2008-04-01 10:12:58') |
+--------------------------------+
|                              2 |
+--------------------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT QUARTER(NOW());
+----------------+
| QUARTER(NOW()) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SEC_TO_TIME(seconds)

将 seconds 转换为小时、分钟和秒的参数作为 TIME 值返回。结果的范围受限于 TIME 数据类型的范围。如果参数对应的值超出该范围，则会出现警告。

mysql> SELECT SEC_TO_TIME(2378);
+-------------------+
| SEC_TO_TIME(2378) |
+-------------------+
| 00:39:38          |
+-------------------+
1 row in set (0.00 sec)

--------------------------------

mysql> SELECT SEC_TO_TIME(2378) + 0;
+-----------------------+
| SEC_TO_TIME(2378) + 0 |
+-----------------------+
|                  3938 |
+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

STR_TO_DATE(str,format)

该函数是 DATE_FORMAT() 的反函数，它需要一个字符串 str 和一个格式字符串 format 。如果格式字符串同时包含日期和时间部分，则 STR_TO_DATE() 返回一个值；如果从中提取的日期、时间或日期时间值非法，就会返回警告；如果不能按照 format 解析 str，则将返回 NULL；如果其中任何一个参数为 NULL ，则都将返回 NULL。

格式字符串可以包含文字字符和以 . 开头的格式说明符 %。文字字符 format 必须与 str 匹配。

说明符可以在 format 字符串中使用。该 % 字符在格式说明符字符之前是必需的。说明符的详细介绍可参见 specifier 。

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
+-------------------------------------+
| STR_TO_DATE('01,5,2013','%d,%m,%Y') |
+-------------------------------------+
| 2013-05-01                          |
+-------------------------------------+
1 row in set (0.00 sec)

------------------------------------------------

mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
+---------------------------------------+
| STR_TO_DATE('May 1, 2013','%M %d,%Y') |
+---------------------------------------+
| 2013-05-01                            |
+---------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

扫描从开头开始， str 如果 format 发现不匹配则失败。末尾的多余字符将 str 被忽略。

mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
        -> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
        -> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
        -> '09:30:17'
1
2
3
4
5
6

未指定的日期或时间部分的值为 0，因此未完全指定的值会 str产生部分或所有部分设置为 0 的结果：

mysql> SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'
1
2
3
4
5
6

允许“零”日期或部分值为 0 的日期，除非 SQL 模式设置为不允许此类值。

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'
1
2
3
4

如果 NO_ZERO_DATE 启用 SQL 模式，则不允许零日期。在这种情况下， STR_TO_DATE() 返回 NULL 并生成警告：

mysql> SET sql_mode = '';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| 0000-00-00                            |
+---------------------------------------+

mysql> SET sql_mode = 'NO_ZERO_DATE';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '00/00/0000' for function str_to_date
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

不能使用 format %X%V 将年-周字符串转换为日期，因为如果周跨越月边界，年和周的组合不能唯一标识年和月。要将年周转换为日期，还应该指定工作日：

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
        -> '2004-10-18'
1
2

SUBTIME(expr1,expr2)

该函数用于执行时间的减法运算。expr1 表示要减去间隔的时间/日期时间，expr2 表示要减去间隔的时间。

mysql> SELECT SUBTIME('2022-10-24 14:34:00','10:22:10');
+-------------------------------------------+
| SUBTIME('2022-10-24 14:34:00','10:22:10') |
+-------------------------------------------+
| 2022-10-24 04:11:50                       |
+-------------------------------------------+
1 row in set (0.00 sec)

-------------------------------------------------------

mysql> SELECT SUBTIME('14:34:00','10:22:10');
+--------------------------------+
| SUBTIME('14:34:00','10:22:10') |
+--------------------------------+
| 04:11:50                       |
+--------------------------------+
1 row in set (0.00 sec)

-------------------------------------------------------

mysql> SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
+--------------------------------------------------------+
| SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002') |
+--------------------------------------------------------+
| 2007-12-30 22:58:58.999997                             |
+--------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

WEEK(date[,mode])

该函数是用来做周的统计和计算，返回日期的周数。

参数：

date：要获取周数的日期
mode：是一个可选参数，用于确定周数计算的逻辑

下表描述了参数 mode 的含义。

Mode	一周的第一天	范围	说明
0	星期日	0-53	遇到本年的第一个星期日开始，是第1周。前面的计算为第0周
1	星期一	0-53	假如第一周超过3天，那么计算为本年的第1周，否则为第0周
2	星期日	1-53	遇到本年的第一个星期日开始，是第1周
3	星期一	1-53	假如第一周超过3天，那么计算为本年的第1周，否则为上一年的第5x周
4	星期日	0-53	假如第一周能超过3天，那么计算为本年的第一周。否则为第0周
5	星期一	0-53	遇到本年的第一个星期一开始，是第一周
6	星期日	1-53	假如第一周能超过3天，那么计算为本年的第一周。否则为上年度的第5x周
7	星期一	1-53	遇到本年的第一个星期一开始，是第一周

mysql> SELECT WEEK(NOW());
+-------------+
| WEEK(NOW()) |
+-------------+
|          43 |
+-------------+
1 row in set (0.00 sec)

----------------------------

mysql> SELECT WEEK('2022-01-31');
+--------------------+
| WEEK('2022-01-31') |
+--------------------+
|                  5 |
+--------------------+
1 row in set (0.00 sec)

----------------------------

mysql> SELECT WEEK('2021-01-31',1);
+----------------------+
| WEEK('2021-01-31',1) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

查询 1 月份每周入职人员数量

SELECT 
	WEEK( create_time, 1 ) AS weeks,
	count( id ) AS count 
FROM
user 
WHERE
	create_time > '2022-01-01 00:00:00' 
	AND create_time < '2022-01-31 23:59:59' 
GROUP BY
	weeks;
1
2
3
4
5
6
7
8
9
10

WEEKDAY(date)

该函数用于查找给定日期的工作日值。如果日期为 NULL ，则 WEEKDAY() 函数将返回 NULL。否则，它将返回日期的索引，即星期一为 0，星期二为 1，星期日为 6。

mysql> SELECT WEEKDAY(NOW());
+----------------+
| WEEKDAY(NOW()) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

---------------------------

mysql> SELECT WEEKDAY('2021-01-31');
+-----------------------+
| WEEKDAY('2021-01-31') |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

WEEKOFYEAR(date)

该函数用于查找给定日期的星期数。如果日期为 NULL，则 WEEKOFYEAR() 函数将返回 NULL。否则,它将返回 Week的值，范围在 1到53 之间。此方法仅接受一个参数。

mysql> SELECT WEEKOFYEAR(NOW());
+-------------------+
| WEEKOFYEAR(NOW()) |
+-------------------+
|                43 |
+-------------------+
1 row in set (0.00 sec)

---------------------------------------

mysql> SELECT WEEKOFYEAR('2008-02-20');
+--------------------------+
| WEEKOFYEAR('2008-02-20') |
+--------------------------+
|                        8 |
+--------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

YEAR(date)

该函数用于返回date中的年份值。

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2022 |
+-------------+
1 row in set (0.00 sec)

---------------------------------------

mysql> SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
|               1987 |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

YEARWEEK(date), YEARWEEK(date,mode)

函数返回给定日期的年和周数 (从 0 到 53 的数字)

下表描述了参数 mode 的含义。

Mode	一周的第一天	范围	说明
0	星期日	0-53	遇到本年的第一个星期日开始，是第1周。前面的计算为第0周
1	星期一	0-53	假如第一周超过3天，那么计算为本年的第1周，否则为第0周
2	星期日	1-53	遇到本年的第一个星期日开始，是第1周
3	星期一	1-53	假如第一周超过3天，那么计算为本年的第1周，否则为上一年的第5x周
4	星期日	0-53	假如第一周能超过3天，那么计算为本年的第一周。否则为第0周
5	星期一	0-53	遇到本年的第一个星期一开始，是第一周
6	星期日	1-53	假如第一周能超过3天，那么计算为本年的第一周。否则为上年度的第5x周
7	星期一	1-53	遇到本年的第一个星期一开始，是第一周

mysql> SELECT YEARWEEK(NOW());
+-----------------+
| YEARWEEK(NOW()) |
+-----------------+
|          202243 |
+-----------------+
1 row in set (0.00 sec)

-----------------------------------

mysql> SELECT YEARWEEK('1987-01-01');
+------------------------+
| YEARWEEK('1987-01-01') |
+------------------------+
|                 198652 |
+------------------------+
1 row in set (0.00 sec)

-----------------------------------

mysql> SELECT YEARWEEK('1987-01-01',1);
+--------------------------+
| YEARWEEK('1987-01-01',1) |
+--------------------------+
|                   198701 |
+--------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

3. 运算符和数学函数

以下关于运算符的使用主要参考于菜鸟教程：https://m.runoob.com/mysql/mysql-operator.html

算术运算符

目录：

运算符	作用
%, MOD	模运算符，取余
*	乘法运算符，乘法
+	加法运算符，加法
-	减运算符，减法
/	除运算符，除法
DIV	整数除法，整除

模运算符：取余

mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

乘法运算符，乘法

mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

加法运算符，加法

mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

减运算符，减法

mysql> select 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

除运算符，除法

mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

整数除法，整除

mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

运算符	作用
=	等于
<=>	严格比较两个NULL值是否相等，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN … AND …	在两值之间，>=min&&<=max
NOT BETWEEN … AND …	不在两值之间
IN (…)	在集合中
NOT IN (…)	不在集合中
IS NULL	为空
IS NOT NULL	不为空

等于

mysql> select 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

--------------------------

mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

不等于

mysql> select 2<>3, 2!=3;
+------+------+
| 2<>3 | 2!=3 |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

--------------------------

mysql> select null<=>null , null=null;
+-------------+------------+
| null<=>null | null<>null |
+-------------+------------+
|           1 |       NULL |
+-------------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

大于

mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

小于

mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

小于等于

mysql> select 2<=3;
+------+
| 2<=3 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

大于等于

mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

BETWEEN … AND …

mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

NOT BETWEEN … AND …

mysql> select 5 not between 1 and 10;
+------------------------+
| 5 not between 1 and 10 |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

IN (…)

mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

NOT IN (…)

mysql> select 5 not in (1,2,3,4,5);
+----------------------+
| 5 not in (1,2,3,4,5) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

IS NULL

mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

--------------------------

mysql> select 'a' is NULL;
+-------------+
| 'a' is NULL |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

IS NOT NULL

mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

--------------------------

mysql> select 'a' IS NOT NULL;
+-----------------+
| 'a' IS NOT NULL |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

逻辑非

mysql> select not 1, !0;
+-------+----+
| not 1 | !0 |
+-------+----+
|     0 |  1 |
+-------+----+
1 row in set, 1 (0.00 sec)
1
2
3
4
5
6
7

逻辑与

mysql> select 2 and 0,2 and 1;
+---------+---------+
| 2 and 0 | 2 and 1 |
+---------+---------+
|       0 |       1 |
+---------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

逻辑或

mysql> select 2 or 0, 2 or 1, 0 or 0, 1 || 0;
+--------+--------+--------+--------+
| 2 or 0 | 2 or 1 | 0 or 0 | 1 || 0 |
+--------+--------+--------+--------+
|      1 |      1 |      0 |      1 |
+--------+--------+--------+--------+
1 row in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7

逻辑异或

mysql> select 1 xor 1, 0 xor 0, 1 xor 0, null or 1, 1 ^ 0;
+---------+---------+---------+-----------+-------+
| 1 xor 1 | 0 xor 0 | 1 xor 0 | null or 1 | 1 ^ 0 |
+---------+---------+---------+-----------+-------+
|       0 |       0 |       1 |         1 |     1 |
+---------+---------+---------+-----------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符	作用
&	按位与
\|	按位或
^	按位异或
~	按位取反
<<	按位左移
>>	按位右移

按位与

mysql> select 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

按位或

mysql> select 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

按位异或

mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

按位取反

mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

按位左移

mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

按位左移

mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

数学函数

函数	作用
ABS()	返回绝对值

ABS(X)

该函数用于返回 X 数值的绝对值，如果 X 为 NULL 则返回 NULL。

mysql> SELECT ABS(2022);
+-----------+
| ABS(2022) |
+-----------+
|      2022 |
+-----------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT ABS(-2022);
+------------+
| ABS(-2022) |
+------------+
|       2022 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4. 聚合函数

聚合函数通常是对值集进行操作，它们通常与 GROUP BY 子句一起使用，将值分组为子集。

目录：

函数	作用
AVG()	返回参数的平均值
COUNT()	统计返回的行数
GROUP_CONCAT()	返回一个连接的字符串
JSON_ARRAYAGG()	将结果集作为单个 JSON 数组返回
JSON_OBJECTAGG()	将结果集作为单个 JSON 对象返回
MAX()	返回最大值
MIN()	返回最小值
STDDEV_POP()	返回总体标准差
STDDEV_SAMP()	返回样本标准差
SUM()	返回总和
VAR_POP()	返回总体标准方差
VAR_SAMP()	返回样本方差

AVG()

该函数是一个聚合函数，用于返回各种记录中表达式的平均值。

例如：我这有一张用户表

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL     |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

计算用户表中男女的平均年纪各为多少（1 表示男，0 表示女）

mysql> SELECT gender, AVG(age) FROM user GROUP BY gender;
+--------+----------+
| gender | AVG(age) |
+--------+----------+
|      1 |  18.0000 |
|      0 |  21.0000 |
+--------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

COUNT()

该函数是用来统计表中记录的一个函数，返回匹配条件的行数。

基本用法：

count(*)：包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，不会忽略列值为NULL的记录。
count(1：忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替，在统计结果的时候，不会忽略列值为NULL的记录。
count(列名)：只包括列名指定列，返回指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。
count(distinct 列名)：只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

count(*)、count(1)、count(列名) 执行效率比较：

如果列为主键，count(列名)效率优于count(1)
如果列不为主键，count(1)效率优于count(列名)
如果表中存在主键，count(主键列名)效率最优
如果表中只有一列，则count(*)效率最优
如果表有多列，且不存在主键，则count(1)效率优于count(*)

例如：统计学生表中的数量

mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
|    10770 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

统计学生表中男女的数量各是多少

mysql> SELECT sex, COUNT(id) FROM student GROUP BY sex;
+-----+-----------+
| sex | COUNT(id) |
+-----+-----------+
|   1 |      5381 |
|   0 |      5389 |
+-----+-----------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8

GROUP_CONCAT()

该函数用于将GROUP BY产生的同一个分组中的值用连接起来，返回一个字符串结果。

可以使用 DISTINCT 去除重复值
如果需要对结果中的值进行排序，可以使用ORDER BY子句
可以使用 SEPARATOR 关键字设置 分隔符，默认为英文逗号

例如：我这有一张用户表

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   18 |      1 |
|  3 | 薛大鹏    | NULL     |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

查看用户表中男女年龄的情况

mysql> SELECT gender , group_concat(age) FROM user GROUP BY gender;
+--------+-------------------+
| gender | group_concat(age) |
+--------+-------------------+
|      0 | 20,22,21          |
|      1 | 18,18,19          |
+--------+-------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

查看用户表中男女年龄的情况，去除重复值

mysql> SELECT gender , group_concat(DISTINCT age) FROM user GROUP BY gender;
+--------+-------------------+
| gender | group_concat(age) |
+--------+-------------------+
|      0 | 20,22,21          |
|      1 | 18,19             |
+--------+-------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

查看用户表中男女年龄的情况，并且年龄安装从大到小排序

mysql> SELECT gender , group_concat(age ORDER BY age DESC) FROM user GROUP BY gender;
+--------+-------------------------------------+
| gender | group_concat(age ORDER BY age DESC) |
+--------+-------------------------------------+
|      0 | 22,21,20                            |
|      1 | 19,18,18                            |
+--------+-------------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

查看用户表中男女年龄的情况，通过 - 拼接

mysql> SELECT gender , GROUP_CONCAT(age SEPARATOR '-') FROM user GROUP BY gender;
+--------+---------------------------------+
| gender | GROUP_CONCAT(age SEPARATOR '-') |
+--------+---------------------------------+
|      0 | 20-22-21                        |
|      1 | 18-18-19                        |
+--------+---------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

MAX()

该函数的作用是查询指定列的最大值。

例如：我这有一张用户表

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL     |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

查询用户表中最大年龄是多少

mysql> SELECT MAX(age) FROM user;
+----------+
| MAX(age) |
+----------+
|       22 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

查询用户表中男女最大年龄分别是多少

mysql> SELECT gender, MAX(age) FROM user GROUP BY gender;
+--------+----------+
| gender | MAX(age) |
+--------+----------+
|      1 |       19 |
|      0 |       22 |
+--------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

MIN()

该函数的作用是查询指定列的最大值。

例如：我这有一张用户表

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL     |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

查询用户表中最小年龄是多少

mysql> SELECT MIN(age) FROM user;
+----------+
| MIN(age) |
+----------+
|       17 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

查询用户表中男女最小年龄分别是多少

mysql> SELECT gender, MIN(age) FROM user GROUP BY gender;
+--------+----------+
| gender | MIN(age) |
+--------+----------+
|      1 |       17 |
|      0 |       20 |
+--------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

SUM()

该函数用于计算一组值或表达式的总和。

例如：我这有一张用户表

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL     |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

计算出用户表中所有人年龄的总和

mysql> SELECT SUM(age) FROM user;
+----------+
| SUM(age) |
+----------+
|      117 |
+----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

5. 流程控制运算符

目录：

函数	作用
CASE	多分支的函数
IF()	if/else 构造
IFNULL()	空 if/else 构造
NULLIF()	如果 expr1 = expr2 返回 NULL

CASE()

该函数是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。

可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。

语法：

CASE 测试表达式
	WHEN 简单表达式1 THEN 结果表达式1
	WHEN 简单表达式2 THEN 结果表达式2
	WHEN 简单表达式n THEN 结果表达式n
	ELSE 结果表达式n+1
END
1
2
3
4
5
6

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。如果没有取值为TRUE的布尔表达式，则当指定了ELSE子句时,返回ELSE子句中指定的结果；如果没有指定ELSE子句，则返回NULL。

例如：我这有一张用户表

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL     |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

可以看到表中性别（gender）是用 0 和 1 表示的，可以使用 CASE 将 0 => 女，1 => 男 返回

mysql> SELECT
    -> id,name,province,age,
    -> (CASE WHEN gender = 0 THEN '女' WHEN gender = 1 THEN '男' END) AS gender
    -> FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 | 男     |
|  2 | 曹大力    | 湖南省    |   17 | 男     |
|  3 | 薛大鹏    | NULL      |   19 | 男     |
|  4 | 张大仙    | 湖北省    |   20 | 女     |
|  5 | 李大花    | 四川省    |   22 | 女     |
|  6 | 杨小羊    | 河南省    |   21 | 女     |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

IF(expr1,expr2,expr3)

该函数根据条件的结果为 true 或 false，返回第一个值，或第二个值。

例如：我这有一张用户表

mysql> SELECT * FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 |      1 |
|  2 | 曹大力    | 湖南省    |   17 |      1 |
|  3 | 薛大鹏    | NULL     |   19 |      1 |
|  4 | 张大仙    | 湖北省    |   20 |      0 |
|  5 | 李大花    | 四川省    |   22 |      0 |
|  6 | 杨小羊    | 河南省    |   21 |      0 |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

可以看到表中性别（gender）是用 0 和 1 表示的，可以使用 IF 将 0 => 女，1 => 男 返回

mysql> SELECT
    -> id,name,province,age,
    -> IF(gender = 0,'女','男') AS gender
    -> FROM user;
+----+-----------+-----------+------+--------+
| id | name      | province  | age  | gender |
+----+-----------+-----------+------+--------+
|  1 | 米大傻    | 湖南省    |   18 | 男     |
|  2 | 曹大力    | 湖南省    |   17 | 男     |
|  3 | 薛大鹏    | NULL      |   19 | 男     |
|  4 | 张大仙    | 湖北省    |   20 | 女     |
|  5 | 李大花    | 四川省    |   22 | 女     |
|  6 | 杨小羊    | 河南省    |   21 | 女     |
+----+-----------+-----------+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

IFNULL(expression, alt_value)

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

mysql> SELECT IFNULL(0,1);
+-------------+
| IFNULL(0,1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

----------------------------

mysql> SELECT IFNULL(NULL,1);
+----------------+
| IFNULL(NULL,1) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

NULLIF(expr1, expr2)

如果 expr1=expr2 成立，那么返回值为null，否则返回值为 expr1 的值。

mysql> SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

-------------------------

mysql> SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

三、相关文档

Temporal Intervals

INTERVAL expr unit 中的 expr 是一个表达式，指定要从开始日期添加或减去的间隔值。expr 被评估为字符串；它可能 - 以负间隔开头。unit是一个关键字，表示应该解释表达式的单位；它是一个说明符，例如 HOUR、DAY 或 WEEK。INTERVAL 关键字和说明符 unit 不区分大小写。下表显示了 expr 每个 unit 值的参数的预期形式。

unit	预期 expr 格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’

specifier

说明符	描述
%a	工作日的缩写名称 ( Sun… Sat)
%b	缩写月份名称 ( Jan… Dec)
%c	月份，数字 ( 0… 12)
%D	0th带有英文后缀 ( , 1st, 2nd, 3rd, …)的月份中的某天
%d	月份中的日期，数字 ( 00… 31)
%e	月份中的日期，数字 ( 0… 31)
%f	微秒 ( 000000… 999999)
%H	小时（00… 23）
%h	小时（01… 12）
%I	小时（01… 12）
%i	分钟，数字 ( 00… 59)
%j	一年中的某一天 ( 001… 366)
%k	小时（0… 23）
%l	小时（1… 12）
%M	月份名称 ( January… December)
%m	月份，数字 ( 00… 12)
%p	AM 或者 PM
%r	时间，12 小时（hh:mm:ss 后跟 AM or PM）
%S	秒 ( 00… 59)
%s	秒 ( 00… 59)
%T	时间，24 小时制 ( hh:mm:ss)
%U	周 ( 00… 53)，其中星期日是一周的第一天； WEEK()模式 0
%u	周 ( 00… 53)，其中星期一是一周的第一天； WEEK()模式一
%V	周 ( 01… 53)，其中星期日是一周的第一天； WEEK()模式二；与 %X
%v	周 ( 01… 53)，其中星期一是一周的第一天； WEEK()模式3；与 %x
%W	工作日名称 ( Sunday… Saturday)
%w	星期几（0=星期日… 6=星期六）
%X	星期天是一周的第一天的一周年，数字，四位数字；与%V
%x	一周的年份，其中星期一是一周的第一天，数字，四位数字；与%v
%Y	年份，数字，四位数
%y	年份，数字（两位数）
%%	文字%字符
%x	x, 对于上面未列出的任何 “ x”

…待完善

相关阅读:
【MySQL】索引与事务
 c语言结构体中的一个char数组怎么赋值？
低代码开发如何助力数字化企业管理系统平台构建
 分布式协调系统ZooKeeper实践与原理剖析
 day15--使用postman, newman和jenkins进行接口自动化测试
 关于阿里云 ACK ingress部分补充
 JVM之垃圾判断的详细解析
 基于antd实现动态修改节点的Tree组件
 【云原生】Kubernetes----Metrics-Server组件与HPA资源
 golang 使用 make 创建 map 是否需要指定长度
原文地址：https://blog.csdn.net/xhmico/article/details/125805711