MySQL常用函数

函数	用法
CHAR(num1, [num2, ...])	将每个参数对应的整数转为对应的字符，并将这些字符组成一个字符串返回
CHAR_LENGTH(string) CHARACTER_LENGTH(string)	返回给定字符串的长度
CONCAT(string1, string2, ..., stringN)	用来按顺序拼接指定的 2 个或多个字符串，并返回拼接后的字符串。如果您需要使用分隔符连接多个字符串，请您使用 CONCAT_WS() 函数。
CONCAT_WS(separator, string1, string2, ..., stringN)	用来通过指定的分隔符按顺序拼接指定的 2 个或多个字符串，并返回拼接后的字符串
INSERT(str, pos, len, newstr)	在一个字符串中插入一个新的字符串，该新的字符串会替换源字符串中指定位置开始的指定长度的子字符串
LCASE(str) LOWER(str)	将指定字符串转为小写并返回
UCASE(str) UPPER(str)	将指定字符串转为大写并返回
LEFT(string, length)	从指定字符串的左侧返回指定数量的字符组成的字符串
LENGTH(string)	返回指定字符串的以字节为单位的长度
LPAD(str, len, padstr)	在字符串的左侧填充指定的字符串，使其达到指定的长度
RIGHT(string, length)	从指定字符串的左侧返回指定数量的字符组成的字符串
RPAD(str, len, padstr)	在一个字符串的右侧填充指定的字符串，使其达到指定的长度
REPEAT(str, count)	将指定的字符串重复指定的次数并返回
REPLACE(str, from_str, to_str)	将字符串中出现的所有子字符串替换为新的子字串。 `REPLACE()` 函数是基于字符的替换，并且替换字符串时是区分大小写的
REVERSE(str)	返回反转后的字符串
STRCMP(str1, str2)	比较两个字符串并返回比较结果。`STRCMP()` 函数基于字符串采用的字符排列规则（collation）比较两个字符串
SUBSTR() SUBSTRING()	返回一个字符串中从指定位置开始的指定长度的子字符串

1.1 CHAR_LENGTH()/CHARACTER_LENGTH()

CHAR_LENGTH(string)
CHARACTER_LENGTH(string)

参数：

string

必需的。需要计算长度的字符串。

返回值：

CHAR_LENGTH()/CHARACTER_LENGTH() 函数返回给定字符串的长度。如果参数 string 为 NULL，它将返回 NULL。CHAR_LENGTH()/CHARACTER_LENGTH() 函数返回的字符的个数，而不是字节的个数。如果要返回字符串的字节长度，请使用 LENGTH() 函数。

示例

SELECT CHAR_LENGTH('Hello');
SELECT CHARACTER_LENGTH('Hello');

+----------------------+
| CHAR_LENGTH('Hello') |
+----------------------+
|                    5 |
+----------------------+

1.2 CONCAT()

CONCAT(string1, string2, ..., stringN)

参数：

string1, string2, ..., stringN

必需的，至少应指定一个字符串；如果拼接的参数中一个为 NULL, 则返回 NULL；当不指定字符串时，MySQL 将会报错： ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT'。

返回值：

CONCAT() 函数返回拼接后的字符串。
如果其中一个参数为 NULL，CONCAT() 函数将返回 NULL。
如果只有一个参数，CONCAT() 函数将返回参数本身。

CONCAT() 示例

SELECT CONCAT('Hello', 'World');

+--------------------------+
| CONCAT('Hello', 'World') |
+--------------------------+
| HelloWorld               |
+--------------------------+

如果拼接的参数中一个为 NULL, 则返回 NULL。请看如下的示例：

SELECT CONCAT('Hello', NULL);

+-----------------------+
| CONCAT('Hello', NULL) |
+-----------------------+
| NULL                  |
+-----------------------+

1.3 CONCAT_WS()

CONCAT_WS(separator, string1, string2, ..., stringN)

参数：

separator

分隔符，必须的。您可以使用单个字符，也可以使用字符串。

string1, string2, ..., stringN

必需的，至少应指定一个字符串；如果拼接的参数为 NULL, 则会被忽略；当不指定字符串时，MySQL 将会报错： ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT_WS'。

返回值：

CONCAT_WS() 函数返回使用分隔符连接后的多个字符串。
如果 separator 为 NULL，CONCAT_WS() 函数将返回 NULL。
如果 string1, string2, ..., stringN 中有 NULL，则会被 CONCAT_WS() 函数忽略。

CONCAT_WS() 示例

要使用 - 拼接字符串 'Hello' 和 'World'，请使用如下的语句：

SELECT CONCAT_WS('-', 'Hello', 'World');

+----------------------------------+
| CONCAT_WS('-', 'Hello', 'World') |
+----------------------------------+
| Hello-World                      |
+----------------------------------+

分隔符可以使用一个字符串：

SELECT CONCAT_WS('----', 'Hello', NULL, 'World');

+-------------------------------------------+
| CONCAT_WS('----', 'Hello', NULL, 'World') |
+-------------------------------------------+
| Hello----World                            |
+-------------------------------------------+

CONCAT_WS() 函数会忽略 NULL 值的参数。请看如下的示例：

SELECT CONCAT_WS('-', 'Hello', NULL, 'World');

+----------------------------------------+
| CONCAT_WS('-', 'Hello', NULL, 'World') |
+----------------------------------------+
| Hello-World                            |
+----------------------------------------+

如果只有一个参与拼接的字符串参数，CONCAT_WS() 函数会原样返回此字符串。

SELECT CONCAT_WS('-', 'Hello');

+-------------------------+
| CONCAT_WS('-', 'Hello') |
+-------------------------+
| Hello                   |
+-------------------------+

1.4 INSERT()

INSERT(str, pos, len, newstr)

参数：

str

必需的。需要处理的字符串。

pos

必需的。开始插入新字符串 newstr 的位置。pos 的值从 1 开始。

len

必需的。要被替换的字符的数量。

newstr

必需的。要插入的新字符串。

返回值：

INSERT(str, pos, len, newstr) 函数返回被处理后的原字符串 str，其中 str 中从 pos 开始长度为 len 的子字符串被 newstr 替换掉。

如果 len 为 0，则直接在 pos 插入字符串，而不进行替换。
如果 pos 不在字符串长度范围内，INSERT() 函数将返回原始字符串。
如果 len 不在字符串从位置 pos 的剩余部分的长度内，INSERT() 函数将从位置 pos 替换字符串的剩余部分。
如果任何参数是 NULL，INSERT() 函数将返回 NULL。

INSERT() 示例

插入一个字符

SELECT INSERT('Hello_World', 6, 1, ' ');

+----------------------------------+
| INSERT('Hello_World', 6, 1, ' ') |
+----------------------------------+
| Hello World                      |
+----------------------------------+

INSERT() 参数为 NULL

如果 INSERT() 任何参数为 NULL，INSERT() 函数将返回 NULL。

SELECT
    INSERT(NULL, 6, 1, ' ') null_1,
    INSERT('Hello_World', NULL, 1, ' ') null_2,
    INSERT('Hello_World', 6, NULL, ' ') null_3,
    INSERT('Hello_World', 6, 1, NULL) null_4;

+--------+--------+--------+--------+
| null_1 | null_2 | null_3 | null_4 |
+--------+--------+--------+--------+
| NULL   | NULL   | NULL   | NULL   |
+--------+--------+--------+--------+

1.5 LEFT()

LEFT(string, length)

参数：

string

必需的。需要从中提取字符的字符串。

length

必需的。需要从字符串中提取字符的数量。

返回值：

LEFT(string, length) 从指定字符串的左侧返回指定数量的字符组成的字符串。

如果 length 超出了 string 的长度，LEFT() 函数返回 string。
如果 length 为零或负数，LEFT() 函数将返回一个空串。
当任意一个参数为 NULL 时， LEFT() 函数将返回 NULL。

LEFT() 示例

SELECT
    LEFT('Hello', 1),
    LEFT('Hello', 2),
    LEFT('Hello', 3),
    LEFT('Hello', 0),
    LEFT('Hello', -1),
    LEFT('Hello', NULL),
    LEFT(NULl, NULL)\G

*************************** 1. row ***************************
   LEFT('Hello', 1): H
   LEFT('Hello', 2): He
   LEFT('Hello', 3): Hel
   LEFT('Hello', 0):
  LEFT('Hello', -1):
LEFT('Hello', NULL): NULL
   LEFT(NULl, NULL): NULL

1.6 LENGTH()

LENGTH(string)

参数：

string

必需的。需要计算长度的字符串。参数可以是其他的类型，比如数字或者日期等，LENGTH() 函数会首先将其转为字符串再计算长度。

返回值：

LENGTH(string) 函数返回指定字符串的以字节为单位的长度，即字节的数量。

当参数为 NULL 时， LENGTH() 函数将返回 NULL。

LENGTH() 示例

SELECT
    LENGTH('Hello'),
    LENGTH(''),
    LENGTH(20),
    LENGTH(-20),
    LENGTH(+20),
    LENGTH(NOW()),
    LENGTH(CURDATE()),
    LENGTH('你好'),
    LENGTH(NULl)\G

*************************** 1. row ***************************
  LENGTH('Hello'): 5
       LENGTH(''): 0
       LENGTH(20): 2
      LENGTH(-20): 3
      LENGTH(+20): 2
    LENGTH(NOW()): 19
LENGTH(CURDATE()): 10
    LENGTH('你好'): 6
     LENGTH(NULl): NULL

注意：

LENGTH(-20) 的结果是 3。这是因为 -20 对应的字符串是 '-20', LENGTH('-20') 的结果为 3。
LENGTH(+20) 的结果是 2。这是因为 +20 相当于 20，对应的字符串是 '20', LENGTH('20') 的结果为 2。
LENGTH(NOW()) 相当于 LENGTH('2021-04-02 21:18:57')。
LENGTH('你好') 的结果是 6。这是因为我这里用的编码是 UTF-8，其中一个中文字占用 3 个字节。

1.7 LPAD()

LPAD(str, len, padstr)

参数

str

必需的。需要被填充的字符串。

len

必需的。填充字符串需要达到的长度。

padstr

可选的。被用来填充到原字符串左侧的字符串。

返回值

LPAD() 函数在字符串的左侧填充指定的字符串，使其达到指定的长度，并返回填充后的字符串。

如果 len 小于原字符串 str 的长度， str 将被截断到 len 的长度。
如果 len 为负数时，LPAD() 函数将返回 NULL。
当任意一个参数为 NULL 时， LPAD() 函数将返回 NULL。

LPAD() 示例

SELECT
    LPAD('oh', 10, 'o'),
    LPAD('oh', 1, 'o'),
    LPAD('oh', -1, 'o'),
    LPAD('World', 15, 'Hello')

*************************** 1. row ***************************
       LPAD('oh', 10, 'o'): oooooooooh
        LPAD('oh', 1, 'o'): o
       LPAD('oh', -1, 'o'): NULL
LPAD('World', 15, 'Hello'): HelloHelloWorld

1.8 RIGHT()

RIGHT(string, length)

参数

string

必需的。需要从中提取字符的字符串。

length

必需的。需要从字符串中提取字符的数量。

返回值

RIGHT(string, length) 从指定字符串的左侧返回指定数量的字符组成的字符串。

如果 length 超出了 string 的长度，RIGHT() 函数返回 string。
如果 length 为零或负数，RIGHT() 函数将返回一个空串。
当任意一个参数为 NULL 时， RIGHT() 函数将返回 NULL。

RIGHT() 示例

SELECT
    RIGHT('Hello', 1),
    RIGHT('Hello', 2),
    RIGHT('Hello', 3),
    RIGHT('Hello', 0),
    RIGHT('Hello', -1),
    RIGHT('Hello', NULL),
    RIGHT(NULl, NULL)

*************************** 1. row ***************************
   RIGHT('Hello', 1): o
   RIGHT('Hello', 2): lo
   RIGHT('Hello', 3): llo
   RIGHT('Hello', 0):
  RIGHT('Hello', -1):
RIGHT('Hello', NULL): NULL
   RIGHT(NULl, NULL): NULL

1.9 RPAD()

RPAD(str, len, padstr)

参数

str

必需的。需要被填充的字符串。

len

必需的。填充字符串需要达到的长度。

padstr

可选的。被用来填充到原字符串右侧的字符串。

返回值

RPAD() 函数在字符串的右侧填充指定的字符串，使其达到指定的长度，并返回填充后的字符串。

如果 len 小于原字符串 str 的长度， str 将被截断到 len 的长度。
如果 len 为负数时，RPAD() 函数将返回 NULL。
当任意一个参数为 NULL 时， RPAD() 函数将返回 NULL。

RPAD() 示例

SELECT
    RPAD('oh', 10, 'h'),
    RPAD('oh', 1, 'h'),
    RPAD('oh', -1, 'h'),
    RPAD('Hello', 13, 'World'),
    RPAD('Hello', 13, NULL)

*************************** 1. row ***************************
       RPAD('oh', 10, 'h'): ohhhhhhhhh
        RPAD('oh', 1, 'h'): o
       RPAD('oh', -1, 'h'): NULL
RPAD('Hello', 13, 'World'): HelloWorldWor
   RPAD('Hello', 13, NULL): NULL

1.10 REPEAT()

REPEAT(str, count)

参数

str

必需的。需要重复的字符串。

count

必需的。需要重复的次数。

返回值

REPEAT(str, count) 函数将指定的字符串重复指定的次数并返回。

当 count 为 0 或者负数时， REPEAT() 函数将返回空字符串 ''。
当任意一个参数为 NULL 时， REPEAT() 函数将返回 NULL。

REPEAT() 示例

SELECT
    REPEAT('MySQL', 3),
    REPEAT('Go ', 3),
    REPEAT('MySQL', 0),
    REPEAT('MySQL', -1),
    REPEAT('MySQL', NULL),
    REPEAT(NULL, 3)

*************************** 1. row ***************************
   REPEAT('MySQL', 3): MySQLMySQLMySQL
     REPEAT('Go ', 3): Go Go Go
   REPEAT('MySQL', 0):
  REPEAT('MySQL', -1):
REPEAT('MySQL', NULL): NULL
      REPEAT(NULL, 3): NULL

1.11 REPLACE()

REPLACE(str, from_str, to_str)

参数

str

必需的。原字符串。

from_str

必需的。被替换的子字符串。

to_str

必需的。用来替换的新子字符串。

返回值

REPLACE(str, from_str, to_str) 函数返回 str 中的所有 from_str 被 to_str 替换后的字符串。

当任意一个参数为 NULL 时， REPLACE() 函数将返回 NULL。

REPLACE() 示例

SELECT
    REPLACE('Hello World', 'World', 'Alice'),
    REPLACE('Hello World', 'l', 'L'),
    REPLACE('Hello World', 'h', 'HH')

*************************** 1. row ***************************
REPLACE('Hello World', 'World', 'Alice'): Hello Alice
        REPLACE('Hello World', 'l', 'L'): HeLLo WorLd
       REPLACE('Hello World', 'h', 'HH'): Hello World

注意：由于 REPLACE 执行的是区分大小写的搜索，因此 REPLACE('Hello World', 'h', 'HH') 不会发生任何替换。

1.12 STRCMP()

STRCMP(str1, str2)

参数

str1

必需的。参与比较的第 1 个字符串。

str2

必需的。参与比较的第 2 个字符串。

返回值

STRCMP(str1, str2) 函数两个字符串比较后的结果。

如果 str1 等于 str1， STRCMP() 函数将返回 0。
如果 str1 小于 str1， STRCMP() 函数将返回 -1。
如果 str1 大于 str1， STRCMP() 函数将返回 1。
当任意个参数为 NULL 时， STRCMP() 函数将返回 NULL。

STRCMP() 示例

SELECT
    STRCMP('hello', 'hello'),
    STRCMP('hello1', 'hello'),
    STRCMP('hello', 'hello1'),
    STRCMP('hello', 'world'),
    STRCMP(NULL, NULL)

 STRCMP('hello', 'hello'): 0
STRCMP('hello1', 'hello'): 1
STRCMP('hello', 'hello1'): -1
 STRCMP('hello', 'world'): -1
       STRCMP(NULL, NULL): NULL

STRCMP() 函数基于字符串采用的字符排列规则（collation）比较两个字符串。让我们看下面的例子：

SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);

+------------------+------------------+
| STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
+------------------+------------------+
|                0 |               -1 |
+------------------+------------------+

这里：

@s1 和 @s2 的排序规则 (COLLATE) 为 utf8mb4_0900_ai_ci，即不区分大小写，因此 x 和 X 的比较结果返回了 0。
@s3 和 @s4 的排序规则 (COLLATE) 为 utf8mb4_0900_as_cs，即区分大小写，因此 x 和 X 的比较结果返回了 -1。

1.13 SUBSTR()/SUBSTRING()

SUBSTR(str, pos)
SUBSTR(str FROM pos)
SUBSTR(str, pos, len)
SUBSTR(str FROM pos FOR len)

SUBSTRING(str, pos)
SUBSTRING(str FROM pos)
SUBSTRING(str, pos, len)
SUBSTRING(str FROM pos FOR len)

参数

str

必需的。从中提取子字符串的原字符串。

pos

必需的。开始提取的位置。它可以是正数或者负数。如果是正数，则从字符串的开头为起始位置确定开始提取的位置提取子字符串。如果是负数，则从字符串的结尾为起始位置确定开始提取的位置提取子字符串。

len

可选的。子字符串的长度（字符数）。如果不指定，则提取到原字符串的结尾。

返回值

SUBSTR()/SUBSTRING()函数从原字符串提取的子字符串并返回。

当 pos 为 0 或者超过原字符串的长度时， SUBSTRING() 函数将返回空串 ''。
当 pos 为负数时， SUBSTRING() 函数将返回空串 ''。
当 pos + len 超过原字符串的长度时， SUBSTRING() 函数提取到原字符串的结尾。
当参数为 NULL 时， SUBSTRING() 函数将返回 NULL。

示例

SELECT
    SUBSTR('Hello', 1, 2),
    SUBSTR('Hello', 2, 2),
    SUBSTR('Hello', 1),
    SUBSTR('Hello', 1, 8),
    SUBSTR('Hello', -2, 2),
    SUBSTR('Hello', 8, 2),
    SUBSTR('Hello', 0, 2),
    SUBSTR('Hello', 0),
    SUBSTR('Hello' FROM 2 FOR 2)

*************************** 1. row ***************************
       SUBSTR('Hello', 1, 2): He
       SUBSTR('Hello', 2, 2): el
          SUBSTR('Hello', 1): Hello
       SUBSTR('Hello', 1, 8): Hello
      SUBSTR('Hello', -2, 2): lo
       SUBSTR('Hello', 8, 2):
       SUBSTR('Hello', 0, 2):
          SUBSTR('Hello', 0):
SUBSTR('Hello' FROM 2 FOR 2): el

SELECT
    SUBSTRING('Hello', 1, 2),
    SUBSTRING('Hello', 2, 2),
    SUBSTRING('Hello', 1),
    SUBSTRING('Hello', 1, 8),
    SUBSTRING('Hello', -2, 2),
    SUBSTRING('Hello', 8, 2),
    SUBSTRING('Hello', 0, 2),
    SUBSTRING('Hello', 0),
    SUBSTRING('Hello' FROM 2 FOR 2)

*************************** 1. row ***************************
       SUBSTRING('Hello', 1, 2): He
       SUBSTRING('Hello', 2, 2): el
          SUBSTRING('Hello', 1): Hello
       SUBSTRING('Hello', 1, 8): Hello
      SUBSTRING('Hello', -2, 2): lo
       SUBSTRING('Hello', 8, 2):
       SUBSTRING('Hello', 0, 2):
          SUBSTRING('Hello', 0):
SUBSTRING('Hello' FROM 2 FOR 2): el

二、数值函数

函数	用法
ABS(x)	返回x的绝对值
EXP(x)	返回自然常数 `e` 的指定数值的x次方
CELL(x)	返回大于或等于x的最小整数值
FLOOR(x)	返回小于或等于x的最大整数值
GREATEST(e1,e2,e3…)	返回列表中的最大值
LEAST(e1,e2,e3…)	返回列表中的最小值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回一个 0 (包含) 和 1 (不包含) 之间的随机数
RAND(x)	返回一个对x的值进行四舍五入后，最接近于x的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后面y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果

2.1 ABS()

ABS(number)

参数： number必需的。一个用于计算绝对值的数字。

返回值：

MySQL ABS() 函数返回数字的绝对（正）值。

如果参数 number 为字符串，ABS() 将按照如下规则尝试转为数字：
- 如果以数字开头，则将开头的数字部分转为数字。
- 如果不能转为数字，则按照 0 处理。
如果参数 number 为 NULL，ABS() 函数将会返回 NULL。

ABS() 示例

SELECT
    ABS(100),
    ABS(-100),
    ABS('100'),
    ABS('-100'),
    ABS('-100A'),
    ABS('A100'),
    ABS(NULL)

*************************** 1. row ***************************
    ABS(100): 100
   ABS(-100): 100
  ABS('100'): 100
 ABS('-100'): 100
ABS('-100A'): 100
 ABS('A100'): 0
   ABS(NULL): NULL

2.2 EXP()

EXP(number)

参数： number必需的。次方值。

返回值：

MySQL EXP() 函数返回自然常数 e 的指定数值的次方。

如果参数 number 为 NULL，EXP() 函数将会返回 NULL。

EXP() 示例

SELECT
    EXP(0),
    EXP(1),
    EXP(2),
    EXP(-1),
    EXP(-2),
    EXP(NULL)

*************************** 1. row ***************************
   EXP(0): 1
   EXP(1): 2.718281828459045
   EXP(2): 7.38905609893065
  EXP(-1): 0.36787944117144233
  EXP(-2): 0.1353352832366127
EXP(NULL): NULL

2.3 CELL()

CEIL(number)

参数： number必需的。一个数字。

返回值：

MySQL CEIL() 函数返回大于或等于指定数字的最小整数值。

如果参数 number 为 NULL，CEIL() 函数将会返回 NULL。

CEIL() 示例

SELECT
    CEIL(123.123),
    CEIL(123.789),
    CEIL(123),
    CEIL(-123.123),
    CEIL(-123.789),
    CEIL(-123),
    CEIL(-100),
    CEIL(NULL)

*************************** 1. row ***************************
 CEIL(123.123): 124
 CEIL(123.789): 124
     CEIL(123): 123
CEIL(-123.123): -123
CEIL(-123.789): -123
    CEIL(-123): -123
    CEIL(-100): -100
    CEIL(NULL): NULL

2.4 FLOOR()

FLOOR(number)

参数： number必需的。一个数字。

返回值：

MySQL FLOOR() 函数返回小于或等于指定数字的最大整数值。

如果参数 number 为 NULL，FLOOR() 函数将会返回 NULL。

FLOOR() 示例

SELECT
    FLOOR(123.123),
    FLOOR(123.789),
    FLOOR(123),
    FLOOR(-123.123),
    FLOOR(-123.789),
    FLOOR(-123),
    FLOOR(-100),
    FLOOR(NULL)

*************************** 1. row ***************************
 FLOOR(123.123): 123
 FLOOR(123.789): 123
     FLOOR(123): 123
FLOOR(-123.123): -124
FLOOR(-123.789): -124
    FLOOR(-123): -123
    FLOOR(-100): -100
    FLOOR(NULL): NULL

2.5 GREATEST()

GREATEST(param1, param2, ..., paramN)

参数： param1, param2, ..., paramN 必需的。用于比较的参数列表。所有的参数都参与比较。参数可以是任意的数据类型，或者表达式。

返回值：

MySQL GREATEST() 函数返回参数列表中的最大值。

如果任意一个参数为 NULL，GREATEST() 函数将返回 NULL。

GREATEST() 示例

SELECT
    GREATEST(2, 1, 5),
    GREATEST(2, 1, 5, '0'),
    GREATEST('a', 'b', 'c'),
    GREATEST('Hello', 'World'),
    GREATEST('a', 'b', NULL)

*************************** 1. row ***************************
         GREATEST(2, 1, 5): 5
    GREATEST(2, 1, 5, '0'): 5
   GREATEST('a', 'b', 'c'): c
GREATEST('Hello', 'World'): World
  GREATEST('a', 'b', NULL): NULL

2.6 LEAST()

LEAST(param1, param2, ..., paramN)

参数： param1, param2, ..., paramN 必需的。用于比较的参数列表。所有的参数都参与比较。参数可以是任意的数据类型，或者表达式。

返回值：

MySQL LEAST() 函数返回参数列表中的最小值。

如果任意一个参数为 NULL，LEAST() 函数将返回 NULL。

LEAST() 示例

SELECT
    LEAST(2, 1, 5),
    LEAST(2, 1, 5, '0'),
    LEAST('a', 'b', 'c'),
    LEAST('Hello', 'World'),
    LEAST('a', 'b', NULL)

*************************** 1. row ***************************
         LEAST(2, 1, 5): 1
    LEAST(2, 1, 5, '0'): 0
   LEAST('a', 'b', 'c'): a
LEAST('Hello', 'World'): Hello
  LEAST('a', 'b', NULL): NULL

2.7 MOD()

MOD(number1, number2)
number1 MOD number2
number1 % number2

参数：

number1

必需的。被除数。

number2

必需的。除数。

返回值：

MySQL MOD() 函数返回一个数字除以另一个数字的余数。

如果 number2 等于 0，MOD() 函数将会返回 NULL。
如果参数 number 为 NULL，MOD() 函数将会返回 NULL。

MOD() 示例

SELECT
    MOD(100, 7),
    MOD(100, 10),
    100 MOD 7,
    100 MOD 10,
    100 % 7,
    100 % 10,
    MOD(0, 1),
    MOD(1, 0),
    MOD(NULL, 1)

*************************** 1. row ***************************
 MOD(100, 7): 2
MOD(100, 10): 0
   100 MOD 7: 2
  100 MOD 10: 0
     100 % 7: 2
    100 % 10: 0
   MOD(0, 1): 0
   MOD(1, 0): NULL
MOD(NULL, 1): NULL

2.8 RAND()

RAND()
RAND(seed)

参数：

seed 可选的。产生随机数的种子。如果提供了 seed，RAND(seed) 函数将产生一个可重复的随机数序列。

返回值：

MySQL RAND() 函数返回一个 0 (包含) 和 1 (不包含) 之间的随机数。

如果您提供了参数 seed，MySQL RAND(seed) 函数将产生一个可重复的随机数序列。

RAND() 返回的是 0 和 1 之间的随机小数，如果你想返回其他区间（比如 m 和 n）的随机数，请使用以下公式：

m 和 n 之间的随机数： RAND() * (n - m) + m
m 和 n 之间的随机整数： FLOOR(RAND() * (n - m + 1) + m)

RAND() 示例

返回 0 和 1 之间的随机数

SELECT
    RAND(),
    RAND(),
    RAND(),
    RAND()

*************************** 1. row ***************************
RAND(): 0.45966783674402895
RAND(): 0.011888444434747514
RAND(): 0.6804387426752958
RAND(): 0.3665284108058814

返回 10 和 20 之间的随机数

SELECT
    RAND() * (20 - 10) + 10,
    RAND() * (20 - 10) + 10,
    RAND() * (20 - 10) + 10,
    RAND() * (20 - 10) + 10

*************************** 1. row ***************************
RAND() * (20 - 10) + 10: 12.783272138594903
RAND() * (20 - 10) + 10: 17.496861179821995
RAND() * (20 - 10) + 10: 19.134489790661718
RAND() * (20 - 10) + 10: 13.181865721179047

返回 10 和 20 之间的随机整数

SELECT
    FLOOR(RAND() * (20 - 10 + 1) + 10),
    FLOOR(RAND() * (20 - 10 + 1) + 10),
    FLOOR(RAND() * (20 - 10 + 1) + 10),
    FLOOR(RAND() * (20 - 10 + 1) + 10)

*************************** 1. row ***************************
FLOOR(RAND() * (20 - 10) + 10): 20
FLOOR(RAND() * (20 - 10) + 10): 13
FLOOR(RAND() * (20 - 10) + 10): 15
FLOOR(RAND() * (20 - 10) + 10): 16

2.9 ROUND()

ROUND(x, d)

参数：

x

必需的。被处理的数字。

d

必需的。需要保留的小数位数。

返回值：

MySQL ROUND() 函数将数字四舍五入到指定的小数位数。

如果 d 大于等于 x 的小数位数，则返回原数字。
如果 d 小于 x 的小数位数，则将 x 的小数位四舍五入到 d 位后返回。
如果 d 为负数，ROUND() 函数将会从小数点开始向整数部分的 d 位数字替换为 0。
如果任意一个参数为 NULL，ROUND() 函数将会返回 NULL。

ROUND() 示例

SELECT
    ROUND(123.179, 1),
    ROUND(123.179, 2),
    ROUND(123.179, 4),
    ROUND(123.179, 0),
    ROUND(123.179, -1),
    ROUND(123.179, -2),
    ROUND(123.179, NULL)

*************************** 1. row ***************************
   ROUND(123.179, 1): 123.2
   ROUND(123.179, 2): 123.18
   ROUND(123.179, 4): 123.179
   ROUND(123.179, 0): 123
  ROUND(123.179, -1): 120
  ROUND(123.179, -2): 100
ROUND(123.179, NULL): NULL

2.10 TRUNCATE()

TRUNCATE(x, d)

参数：

x

必需的。被处理的数字。

d

必需的。需要保留的小数位数。

返回值：

MySQL TRUNCATE() 函数返回保留了指定小数位的数字。

如果 d 大于等于 x 的小数位数，则返回原数字。
如果 d 小于 x 的小数位数，则将 x 的小数位数截断为 d 位后返回。
如果 d 为负数，TRUNCATE() 函数将会从小数点开始向整数部分的 d 位数字替换为 0。
如果任意一个参数为 NULL，TRUNCATE() 函数将会返回 NULL。

SELECT
    TRUNCATE(123.179, 1),
    TRUNCATE(123.179, 2),
    TRUNCATE(123.179, 4),
    TRUNCATE(123.179, 0),
    TRUNCATE(123.179, -1),
    TRUNCATE(123.179, -2),
    TRUNCATE(123.179, NULL)

*************************** 1. row ***************************
   TRUNCATE(123.179, 1): 123.1
   TRUNCATE(123.179, 2): 123.17
   TRUNCATE(123.179, 4): 123.179
   TRUNCATE(123.179, 0): 123
  TRUNCATE(123.179, -1): 120
  TRUNCATE(123.179, -2): 100
TRUNCATE(123.179, NULL): NULL

三、日期和时间函数

函数	用法
CURDATE() CURRENT_DATE()	返回系统的当前日期（年月日）
CURTIME() CURRENT_TIME()	返回系统的当前时间（时分秒）
NOW() CURRENT_TIMESTAMP()	返回系统的当前时间（年月日时分秒）
DATE_ADD(date, INTERVAL value unit)	在指定的日期/时间上加上指定到时间间隔加并返回新的日期/时间
DATE_FORMAT(date, format)	按照指定的格式格式化日期时间并返回格式化后的字符串
DATE_SUB(date, INTERVAL value unit)	在指定的日期/时间上减去指定到时间间隔加并返回新的日期/时间
DATEDIFF(date1, date2)	返回两个日期值之间的天数
DAY(expr) DAYOFMONTH(expr)	返回日期时间表达式中的代表月份中的一天的数字
DAYNAME(expr)	返回给定日期的工作日名称
DAYOFWEEK(expr)	返回给定日期的工作日名称
DAYOFYEAR(expr)	返回一个从 `1` 到 `366` 代表给定日期是一年中的第几天的数字
STR_TO_DATE(str, format)	将指定的字符串根据指定日期格式转为日期/时间
SYSDATE()	按 `YYYY-MM-DD hh:mm:ss` 格式返回当前时间和日期，与 NOW函数不同，`SYSDATE()` 函数返回的该函数执行的准确系统时间，而 `NOW()` 函数返回的是所在的语句、函数、存储过程开始执行的时间。
WEEK(date) WEEK(date, mode)	返回给定日期位于当年的第几周
WEEKDAY(date)	返回给定日期的工作日编号
WEEKOFYEAR(date)	返回给定日期位于当年的第几周

3.1 CURDATE()/CURRENT_DATE()

CURDATE()
CURRENT_DATE
CURRENT_DATE()

返回值：

返回系统的当前日期。

示例：

SELECT
    CURDATE(),
    CURDATE() + 0;

+------------+---------------+
| CURDATE()  | CURDATE() + 0 |
+------------+---------------+
| 2022-04-12 |      20220412 |
+------------+---------------+

SELECT
    CURRENT_DATE,
    CURRENT_DATE(),
    CURRENT_DATE() + 0;

+--------------+----------------+--------------------+
| CURRENT_DATE | CURRENT_DATE() | CURRENT_DATE() + 0 |
+--------------+----------------+--------------------+
| 2022-04-12   | 2022-04-12     |           20220412 |
+--------------+----------------+--------------------+

3.2 CURTIME()/CURRENT_TIME()

CURTIME()
CURRENT_TIME()

返回值：

返回系统的当前时间。

示例：

SELECT CURTIME(), CURTIME() + 0;

+-----------+---------------+
| CURTIME() | CURTIME() + 0 |
+-----------+---------------+
| 02:04:35  |         20435 |
+-----------+---------------+

SELECT 
    CURRENT_TIME,
    CURRENT_TIME(),
    CURRENT_TIME() + 0;

+--------------+----------------+--------------------+
| CURRENT_TIME | CURRENT_TIME() | CURRENT_TIME() + 0 |
+--------------+----------------+--------------------+
| 02:11:03     | 02:11:03       |              21103 |
+--------------+----------------+--------------------+

注意： CURRENT_TIME() + 0 的结果为 hhmmss 格式。

CURRENT_TIME() + N 意味着当前时间加上 N 秒。比如，系统当前时间加 1 秒：

SELECT CURRENT_TIME(), CURRENT_TIME() + 1;

+----------------+--------------------+
| CURRENT_TIME() | CURRENT_TIME() + 1 |
+----------------+--------------------+
| 02:09:39       |              20940 |
+----------------+--------------------+

3.3 NOW()/CURRENT_TIMESTAMP()

NOW()
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()

返回值：

返回系统的当前时间。

示例：

SELECT CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP() + 1;

+---------------------+-------------------------+
| CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP() + 1 |
+---------------------+-------------------------+
| 2022-04-12 02:29:32 |          20220412022933 |
+---------------------+-------------------------+

SELECT NOW(), NOW() + 1;

+---------------------+----------------+
| NOW()               | NOW() + 1      |
+---------------------+----------------+
| 2022-04-12 02:19:34 | 20220412021935 |
+---------------------+----------------+

注意： NOW() + 0 的结果为 YYYYMMDDhhmmss 格式。NOW() + N 意味着当前时间加上 N 秒。

与 SYSDATE() 函数不同，NOW() 函数返回的是所在的语句、函数、存储过程开始执行的时间，并不一定是执行的 NOW() 语句时确切的系统时间。请看下面的示例：

SELECT
    NOW(),
    SYSDATE(),
    SLEEP(2),
    NOW(),
    SYSDATE()

    NOW(): 2022-04-12 02:23:14
SYSDATE(): 2022-04-12 02:23:14
 SLEEP(2): 0
    NOW(): 2022-04-12 02:23:14
SYSDATE(): 2022-04-12 02:23:16

3.4 DATE_ADD()

DATE_ADD(date, INTERVAL value unit)

参数：

date

必需的。需要操作的日期。

value

必需的。时间/日期间隔。正数和负数都是允许的。

unit

必需的。时间/日期间隔的单位。

时间/日期间隔的单位可以是以下值中的一个：

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

返回值：

DATE_ADD() 函数指定的日期/时间添加指定的时间间隔，并返回一个新的日期/时间。DATE_ADD() 函数的返回值和参数有关：

如果 date 参数是 DATE 类型，并且时间间隔为 YEAR, MONTH 或者 DAY，返回 DATE。
如果 date 参数是 DATE 类型，并且时间间隔为 HOURS, MINUTES 或者 SECONDS，返回 DATETIME。
如果 date 参数是 DATETIME 类型，返回 DATETIME。
如果 date 参数是 TIME 类型，并且时间间隔为 YEAR, MONTH 或者 DAY，返回 DATETIME。
如果 date 参数是 TIME 类型，并且计算只涉及 HOURS, MINUTES 和 SECONDS 部分，返回 TIME。（开始于 MySQL 8.0.28）
其他情况下返回字符串。

DATE_ADD() 示例

示例1

SELECT
    DATE_ADD('2020-06-10', 10),
    DATE_ADD('2020-06-10', -10)

 DATE_ADD('2020-06-10', 10): 2020-06-20
DATE_ADD('2020-06-10', -10): 2020-05-31

示例 2

SELECT
    DATE_ADD('2020-06-10', INTERVAL 10 DAY),
    DATE_ADD('2020-06-10', INTERVAL 10 HOUR)

 DATE_ADD('2020-06-10', INTERVAL 10 DAY): 2020-06-20
DATE_ADD('2020-06-10', INTERVAL 10 HOUR): 2020-06-10 10:00:00

示例3

SELECT
    DATE_ADD('2020-06-10 10:00:00', INTERVAL 10 HOUR),
    DATE_ADD('2020-06-10 10:00:00', INTERVAL 10 MINUTE)

  DATE_ADD('2020-06-10 10:00:00', INTERVAL 10 HOUR): 2020-06-10 20:00:00
DATE_ADD('2020-06-10 10:00:00', INTERVAL 10 MINUTE): 2020-06-10 10:10:00

示例4

SELECT
    DATE_ADD(CURDATE(), INTERVAL 10 HOUR),
    DATE_ADD(NOW(), INTERVAL 10 MINUTE)

DATE_ADD(CURDATE(), INTERVAL 10 HOUR): 2022-04-11 10:00:00
  DATE_ADD(NOW(), INTERVAL 10 MINUTE): 2022-04-11 08:35:42

3.5 DATE_FORMAT()

DATE_FORMAT(date, format)

参数：

date

必需的。需要格式化的日期。

format

必需的。格式化模式字符串。

下表整理了 format 中可采用的格式化符号

符号	说明
`%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`	十二小时制时间 (`hh:mm:ss` 后跟 `AM` 或 `PM`)
`%S`	秒 (`00`..`59`)
`%s`	秒 (`00`..`59`)
`%T`	二十四小时制时间 (`hh:mm:ss`)
`%U`	一年中的星期 (`00`..`53`), 每周的开始是星期天; WEEK() 函数中的 mode 0
`%u`	一年中的星期 (`00`..`53`), 每周的开始是星期一; WEEK() 函数中的 mode 1
`%V`	一年中的星期 (`01`..`53`), 每周的开始是星期天; WEEK() 函数中的 mode 2, 用于 `%X`
`%v`	一年中的星期 (`01`..`53`), 每周的开始是星期一; WEEK() 函数中的 mode 3, 用于 `%x`
`%W`	星期的名称 (`Sunday`..`Saturday`)
`%w`	星期中的每天 (`0`=星期天..`6`=星期六)
`%X`	一年中的星期，每周的开始是星期天，四位数字，用于 `%V`
`%x`	一年中的星期，每周的开始是星期一，四位数字，用于 `%v`
`%Y`	四位数字年份
`%y`	两位数字年份
`%%`	转义 `%`
`%x`	`x`, 上面为列举的其他字符

常用例如2022-12-20 21:20:20这种格式，要用 %Y-%m-%d %H:%i:%s

返回值：

DATE_FORMAT() 函数按照指定的格式格式化日期时间并返回格式化后的字符串。

如果其中任何一个参数为 NULL，DATE_FORMAT() 函数将返回 NULL。

DATE_FORMAT() 示例

SELECT NOW(), DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')

                             NOW(): 2022-04-12 03:18:38
DATE_FORMAT(NOW(), '%Y%m%d%H%i%S'): 20220412031838

3.6 DATEDIFF()

DATEDIFF(date1, date2)

参数：

date1

必需的。一个日期或者日期时间表达式。

date2

必需的。一个日期或者日期时间表达式。

返回值：

MySQL DATEDIFF() 函数返回两个日期值之间的天数。DATEDIFF() 函数只比较 date1 和 date2 的日期部分。如果 date1 的日期晚于 date2 的日期，它返回一个整数，否则返回一个负数或者 0。

如果指定的表达式不是一个合法的日期或者日期时间，DATEDIFF() 函数将返回 NULL。

如果参数为 NULL，DATEDIFF() 函数将返回 NULL。

DATEDIFF() 示例

SELECT
    DATEDIFF('2022-02-28', '2022-02-28'),
    DATEDIFF('2022-02-28', '2022-02-28 10:10:10'),
    DATEDIFF('2022-02-28 10:10:10', '2022-02-28'),
    DATEDIFF('2022-02-28', '2022-02-27'),
    DATEDIFF('2022-02-28', '2022-02-27 10:10:10'),
    DATEDIFF('2022-02-28 10:10:10', '2022-02-27'),
    DATEDIFF('2022-02-30', '2022-02-28 10:10:10'),
    DATEDIFF('Not A DATEDIFF', 'Not A DATEDIFF'),
    DATEDIFF(NULL, '2022-02-28')

         DATEDIFF('2022-02-28', '2022-02-28'): 0
DATEDIFF('2022-02-28', '2022-02-28 10:10:10'): 0
DATEDIFF('2022-02-28 10:10:10', '2022-02-28'): 0
         DATEDIFF('2022-02-28', '2022-02-27'): 1
DATEDIFF('2022-02-28', '2022-02-27 10:10:10'): 1
DATEDIFF('2022-02-28 10:10:10', '2022-02-27'): 1
DATEDIFF('2022-02-30', '2022-02-28 10:10:10'): NULL
 DATEDIFF('Not A DATEDIFF', 'Not A DATEDIFF'): NULL
                 DATEDIFF(NULL, '2022-02-28'): NULL

3.7 DAY()/DAYOFMONTH()

DAY(expr)
DAYOFMONTH(expr)

参数：

expr

必需的。一个日期或者日期时间表达式。

返回值：

MySQL DAY() 函数返回日期时间表达式中的代表月份中的一天的数字，从 1 到 31。

如果给定的日期的天的部分为 0 ，比如： '0000-00-00' 和 '2008-00-00'，DAY() 函数将返回 0。
如果指定的表达式不是一个合法的日期或者日期时间，DAY() 函数将返回 NULL。
如果参数为 NULL，DAY() 函数将返回 NULL。

DAY() 示例

SELECT
    DAY('2022-02-28'),
    DAY('2022-02-28 10:10:10'),
    DAY(NOW()),
    DAY('2022-02-00'),
    DAY('2022-02-30'),
    DAY('Not A DATE'),
    DAY(NULL)

         DAY('2022-02-28'): 28
DAY('2022-02-28 10:10:10'): 28
                DAY(NOW()): 13
         DAY('2022-02-00'): 0
         DAY('2022-02-30'): NULL
         DAY('Not A DATE'): NULL
                 DAY(NULL): NULL

SELECT
    DAYOFMONTH('2022-02-28'),
    DAYOFMONTH('2022-02-28 10:10:10'),
    DAYOFMONTH(NOW()),
    DAYOFMONTH('2022-02-00'),
    DAYOFMONTH('2022-02-30'),
    DAYOFMONTH('Not A DATE'),
    DAYOFMONTH(NULL)

         DAYOFMONTH('2022-02-28'): 28
DAYOFMONTH('2022-02-28 10:10:10'): 28
                DAYOFMONTH(NOW()): 13
         DAYOFMONTH('2022-02-00'): 0
         DAYOFMONTH('2022-02-30'): NULL
         DAYOFMONTH('Not A DATE'): NULL
                 DAYOFMONTH(NULL): NULL

3.8 DAYNAME()

DAYNAME(expr)

参数：

expr

必需的。一个日期或者日期时间表达式。

返回值：

MySQL DAYNAME() 函数返回给定日期的工作日名称，返回值将是以下值中的一个： Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday。

如果指定的表达式不是一个合法的日期或者日期时间，DAYNAME() 函数将返回 NULL。
如果参数为 NULL，DAYNAME() 函数将返回 NULL。

DAYNAME() 示例

SELECT
    DAYNAME('2022-02-21'),
    DAYNAME('2022-02-22'),
    DAYNAME('2022-02-23'),
    DAYNAME('2022-02-24'),
    DAYNAME('2022-02-25'),
    DAYNAME('2022-02-26'),
    DAYNAME('2022-02-27'),
    DAYNAME(NOW()),
    DAYNAME('2022-02-00'),
    DAYNAME('2022-02-30'),
    DAYNAME('Not A DATE'),
    DAYNAME(NULL)

DAYNAME('2022-02-21'): Monday
DAYNAME('2022-02-22'): Tuesday
DAYNAME('2022-02-23'): Wednesday
DAYNAME('2022-02-24'): Thursday
DAYNAME('2022-02-25'): Friday
DAYNAME('2022-02-26'): Saturday
DAYNAME('2022-02-27'): Sunday
       DAYNAME(NOW()): Wednesday
DAYNAME('2022-02-00'): NULL
DAYNAME('2022-02-30'): NULL
DAYNAME('Not A DATE'): NULL
        DAYNAME(NULL): NULL

3.9 DAYOFWEEK()

DAYOFWEEK(expr)

参数：

expr

必需的。一个日期或者日期时间表达式。

返回值：

MySQL DAYOFWEEK() 函数返回给定日期的工作日的索引。它返回一个从 1 到 7 之间的数字，代表的含义如下：

1 - 星期天
2 - 星期一
3 - 星期二
4 - 星期三
5 - 星期四
6 - 星期五
7 - 星期六

如果指定的表达式不是一个合法的日期或者日期时间，DAYOFWEEK() 函数将返回 NULL。

如果参数为 NULL，DAYOFWEEK() 函数将返回 NULL。

DAYOFWEEK() 示例

SELECT
    DAYOFWEEK('2022-02-21'),
    DAYOFWEEK('2022-02-22'),
    DAYOFWEEK('2022-02-23'),
    DAYOFWEEK('2022-02-24'),
    DAYOFWEEK('2022-02-25'),
    DAYOFWEEK('2022-02-26'),
    DAYOFWEEK('2022-02-27'),
    DAYOFWEEK('2022-02-00'),
    DAYOFWEEK('2022-02-30'),
    DAYOFWEEK('Not A DATE'),
    DAYOFWEEK(NULL)

DAYOFWEEK('2022-02-21'): 2
DAYOFWEEK('2022-02-22'): 3
DAYOFWEEK('2022-02-23'): 4
DAYOFWEEK('2022-02-24'): 5
DAYOFWEEK('2022-02-25'): 6
DAYOFWEEK('2022-02-26'): 7
DAYOFWEEK('2022-02-27'): 1
DAYOFWEEK('2022-02-00'): NULL
DAYOFWEEK('2022-02-30'): NULL
DAYOFWEEK('Not A DATE'): NULL
        DAYOFWEEK(NULL): NULL

3.10 DAYOFYEAR()

DAYOFYEAR(expr)

参数：

expr

必需的。一个日期或者日期时间表达式。

返回值：

MySQL DAYOFYEAR() 函数返回一个从 1 到 366 代表给定日期是一年中的第几天的数字。

如果指定的表达式不是一个合法的日期或者日期时间，DAYOFYEAR() 函数将返回 NULL。
如果参数为 NULL，DAYOFYEAR() 函数将返回 NULL。

DAYOFYEAR() 示例

SELECT
    DAYOFYEAR('2022-01-01'),
    DAYOFYEAR('2022-01-31'),
    DAYOFYEAR('2022-02-28 10:10:10'),
    DAYOFYEAR('2022-02-00'),
    DAYOFYEAR('2022-02-30'),
    DAYOFYEAR('Not A DATE'),
    DAYOFYEAR(NULL)

         DAYOFYEAR('2022-01-01'): 1
         DAYOFYEAR('2022-01-31'): 31
DAYOFYEAR('2022-02-28 10:10:10'): 59
         DAYOFYEAR('2022-02-00'): NULL
         DAYOFYEAR('2022-02-30'): NULL
         DAYOFYEAR('Not A DATE'): NULL
                 DAYOFYEAR(NULL): NULL

3.11 STR_TO_DATE()

STR_TO_DATE(str, format)

参数：

str

必需的。需要格式化成日期的字符串。

format

必需的。格式化字符串。

format 中可采用的格式化符号，可参考 3.5章节

返回值：

STR_TO_DATE() 函数返回一个日期/时间/日期时间。

如果不能按照 format 解析 str，STR_TO_DATE() 函数将返回 NULL。
如果其中任何一个参数为 NULL，STR_TO_DATE() 函数将返回 NULL。

STR_TO_DATE() 示例

示例1：转为日期

SELECT STR_TO_DATE('2022/02/01', '%Y/%m/%d');

+---------------------------------------+
| STR_TO_DATE('2022/02/01', '%Y/%m/%d') |
+---------------------------------------+
| 2022-02-01                            |
+---------------------------------------+

示例2：转为日期时间

SELECT STR_TO_DATE('2022/02/01 10:10:10', '%Y/%m/%d %H:%i:%S');

+---------------------------------------------------------+
| STR_TO_DATE('2022/02/01 10:10:10', '%Y/%m/%d %H:%i:%S') |
+---------------------------------------------------------+
| 2022-02-01 10:10:10                                     |
+---------------------------------------------------------+

3.12 WEEK()

WEEK(date)
WEEK(date, mode)

参数：

date

必需的。一个日期或者日期时间表达式。

mode

可选的。确定计算周的逻辑。如果没有指定该参数，默认将使用 default_week_format 变量的值。

返回值：

MySQL WEEK() 函数返回给定日期位于当年的第几周，取值范围为 0 到 53。

下表整理了 WEEK() 函数对 mode 参数的处理逻辑：

Mode	每周的第一天	返回值范围	对第一周的要求
`0`	星期天	`0-53`
`1`	星期一	`0-53`	在本年至少 4 天
`2`	星期天	`1-53`
`3`	星期一	`1-53`	在本年至少 4 天
`4`	星期天	`0-53`	在本年至少 4 天
`5`	星期一	`0-53`
`6`	星期天	`1-53`	在本年至少 4 天
`7`	星期一	`1-53`

如果指定的表达式不是一个合法的日期或者日期时间，WEEK() 函数将返回 NULL。
如果参数为 NULL，WEEK() 函数将返回 NULL。

WEEK() 示例

SELECT
    WEEK('2022-01-01', 0),
    WEEK('2022-01-03', 0),
    WEEK('2022-01-01', 1),
    WEEK('2022-01-03', 1),
    WEEK('2022-01-01', 2),
    WEEK('2022-01-03', 2),
    WEEK('2022-01-01', 3),
    WEEK('2022-01-03', 3),
    WEEK('2022-01-01', 4),
    WEEK('2022-01-03', 4),
    WEEK('2022-01-01', 5),
    WEEK('2022-01-03', 5),
    WEEK('2022-01-01', 6),
    WEEK('2022-01-03', 6),
    WEEK('2022-01-01', 7),
    WEEK('2022-01-03', 7)

WEEK('2022-01-01', 0): 0
WEEK('2022-01-03', 0): 1
WEEK('2022-01-01', 1): 0
WEEK('2022-01-03', 1): 1
WEEK('2022-01-01', 2): 52
WEEK('2022-01-03', 2): 1
WEEK('2022-01-01', 3): 52
WEEK('2022-01-03', 3): 1
WEEK('2022-01-01', 4): 0
WEEK('2022-01-03', 4): 1
WEEK('2022-01-01', 5): 0
WEEK('2022-01-03', 5): 1
WEEK('2022-01-01', 6): 52
WEEK('2022-01-03', 6): 1
WEEK('2022-01-01', 7): 52
WEEK('2022-01-03', 7): 1

3.13 WEEKDAY()

WEEKDAY(date)

参数：

date

必需的。一个日期或者日期时间表达式。

返回值：

MySQL WEEKDAY() 函数返回给定日期的工作日编号，取之范围为 0 到 6，他们分别代表如下含义：

0 - Monday
1 - Tuesday
2 - Wednesday
3 - Thursday
4 - Friday
5 - Saturday
6 - Sunday
如果指定的表达式不是一个合法的日期或者日期时间，WEEKDAY() 函数将返回 NULL。
如果参数为 NULL，WEEKDAY() 函数将返回 NULL。

WEEKDAY() 示例

SELECT
    WEEKDAY('2022-02-21'),
    WEEKDAY('2022-02-22'),
    WEEKDAY('2022-02-23'),
    WEEKDAY('2022-02-24'),
    WEEKDAY('2022-02-25'),
    WEEKDAY('2022-02-26'),
    WEEKDAY('2022-02-27')

WEEKDAY('2022-02-21'): 0
WEEKDAY('2022-02-22'): 1
WEEKDAY('2022-02-23'): 2
WEEKDAY('2022-02-24'): 3
WEEKDAY('2022-02-25'): 4
WEEKDAY('2022-02-26'): 5
WEEKDAY('2022-02-27'): 6

3.14 WEEKOFYEAR()

WEEKOFYEAR(date)

参数：

date

必需的。一个日期或者日期时间表达式。

返回值：

MySQL WEEKOFYEAR() 函数返回给定日期位于当年的第几周，取值范围为 1 到 53。

该函数的前提是“一周的第一天是星期一，并且一年中的第一周有 3 天以上”，它相当于 WEEK(date,3)。

如果指定的表达式不是一个合法的日期或者日期时间，WEEKOFYEAR() 函数将返回 NULL。
如果参数为 NULL，WEEKOFYEAR() 函数将返回 NULL。

WEEKOFYEAR() 示例

SELECT
    WEEKOFYEAR('2022-01-01'),
    WEEKOFYEAR('2022-01-03');

+--------------------------+--------------------------+
| WEEKOFYEAR('2022-01-01') | WEEKOFYEAR('2022-01-03') |
+--------------------------+--------------------------+
|                       52 |                        1 |
+--------------------------+--------------------------+

3.15 SYSDATE()

MySQL SYSDATE() 函数按 YYYY-MM-DD hh:mm:ss 格式返回当前时间和日期。

与 NOW() 函数不同，SYSDATE() 函数返回的该函数执行的准确系统时间，而 NOW() 函数返回的是所在的语句、函数、存储过程开始执行的时间。

SYSDATE()

返回值：返回系统的当前时间。

SYSDATE() 示例

SELECT SYSDATE(), SYSDATE() + 1;

+---------------------+----------------+
| SYSDATE()           | SYSDATE() + 1  |
+---------------------+----------------+
| 2022-04-12 03:34:13 | 20220412033414 |
+---------------------+----------------+

注意： SYSDATE() + 0 的结果为 YYYYMMDDhhmmss 格式。SYSDATE() + N 意味着当前时间加上 N 秒。

与 NOW() 函数不同，SYSDATE() 函数返回的该函数执行的准确系统时间，而 NOW() 函数返回的是所在的语句、函数、存储过程开始执行的时间。请看下面的示例：

SELECT
    NOW(),
    SYSDATE(),
    SLEEP(2),
    NOW(),
    SYSDATE()

    NOW(): 2022-04-12 03:35:10
SYSDATE(): 2022-04-12 03:35:10
 SLEEP(2): 0
    NOW(): 2022-04-12 03:35:10
SYSDATE(): 2022-04-12 03:35:12

四、聚合函数

函数	用法
AVG(expr) AVG(DISTINCT expr)	计算并返回表达式的平均值
COUNT(expr)	用于统计表达式代表的所有值的中的非 NULL 值的数量
GROUP_CONCAT()	将一个分组中指定的列或表达式的值连接成一个字符串并返回
JSON_ARRAYAGG(expr)	将指定的列或者表达式的值聚合为一个 JSON 数组
JSON_OBJECTAGG(key_expr, value_expr)	将由第一个参数作为键和第二个参数作为值的键值对聚合为一个 JSON 对象
MAX(expr)	返回表达式代表的所有值中的最大值
MIN()	返回表达式代表的所有值中的最小值
SUM()	计算所有指定的非 NULL 值的总和并返回

4.1 GROUP_CONCAT()

GROUP_CONCAT(
    [DISTINCT] expr [, expr2 ...]
    [ORDER BY ...]
    [SEPARATOR separator]
)

我们通常在 MySQL 中按如下方式使用 GROUP_CONCAT() 函数：

SELECT GROUP_CONCAT(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

参数：

expr [, expr2 ...]

必须的。它指定了要连接的一个或者多个列或表达式。

ORDER BY

可选的。它用于对要连接的内容排序。

SEPARATOR separator

可选的。separator 连接符。默认是 ,。

返回值：

MySQL GROUP_CONCAT(expr) 函数返回一个字符串，它将通过列或者表达式指定的内容连接起来。

如果结果集没有任何行，此函数将返回 NULL。

GROUP_CONCAT() 示例

我们将通过 student_score 表进行演示。

首先，让我们使用如下语句创建表 student_score：

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);

这里我们创建了表用于存储学生的成绩，这个表有 4 个列组成：

id 是主键
name 是学生的名字
subject 是学生的科目
score 是学生的成绩

其次，让我们插入一些数据用于演示：

INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES
    ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Tim', 'English', 98);

以下是表中的数据：

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Tim  | English |    98 |
+----+------+---------+-------+

现在，我们可以使用 GROUP_CONCAT() 函数获取每个人参加的考试科目的列表，如下：

SELECT
    name AS `Name`,
    GROUP_CONCAT(subject) AS `Subjects`
FROM student_score
GROUP BY name;

+------+--------------+
| Name | Subjects     |
+------+--------------+
| Tim  | English      |
| Tom  | Math,English |
+------+--------------+

这里，我们按照 name 分组，并使用 GROUP_CONCAT() 函数将每组中的 subject 列的内容连接起来。

上面的语句中，由于未指定分隔符，因此使用了逗号 , 进行连接。如果我们想要使用其他的分隔符（比如： /），可是使用 SEPARATOR 指定：

SELECT
    name AS `Name`,
    GROUP_CONCAT(subject SEPARATOR '/') AS `Subjects`
FROM student_score
GROUP BY name;

+------+--------------+
| Name | Subjects     |
+------+--------------+
| Tim  | English      |
| Tom  | Math/English |
+------+--------------+

如果我们想要每个组中连接的科目按照科目的名称排序，可以使用 ORDER BY，如下：

SELECT
    name AS `Name`,
    GROUP_CONCAT(subject ORDER BY subject SEPARATOR '/') AS `Subjects`
FROM student_score
GROUP BY name;

+------+--------------+
| Name | Subjects     |
+------+--------------+
| Tim  | English      |
| Tom  | English/Math |
+------+--------------+

这里，由于设定了 ORDER BY subject，那么对于 Tom 而言， English 排到了 Math 的前面。

如果我们想要同时拼接科目和成绩，请使用如下语句：

SELECT
    name AS `Name`,
    GROUP_CONCAT(
        subject, score
        ORDER BY subject
        SEPARATOR '/'
    ) AS `Scores`
FROM student_score
GROUP BY name;

+------+------------------+
| Name | Scores           |
+------+------------------+
| Tim  | English98        |
| Tom  | English90/Math80 |
+------+------------------+

这里，我们在 GROUP_CONCAT() 函数中传入了多个列，subject 和 score。注意输出，科目和成绩直接拼接在了一起，中间没有连接符。

如果我们想要在科目和成绩之间使用一个连接符 -, 请使用如下语句：

SELECT
    name AS `Name`,
    GROUP_CONCAT(
        CONCAT(subject, '-'), score
        ORDER BY subject
        SEPARATOR '/'
    ) AS `Scores`
FROM student_score
GROUP BY name;

+------+--------------------+
| Name | Scores             |
+------+--------------------+
| Tim  | English-98         |
| Tom  | English-90/Math-80 |
+------+--------------------+

注意，我们在这里使用了表达式 CONCAT(subject, '-') 作为 GROUP_CONCAT() 函数的一个输入参数，它的目的是在科目的后面连接一个 ‘-’。

或者我们使用 CONCAT_WS() 函数来达到相同的目的：

SELECT
    name AS `Name`,
    GROUP_CONCAT(
        CONCAT_WS('-', subject, score)
        ORDER BY subject
        SEPARATOR '/'
    ) AS `Scores`
FROM student_score
GROUP BY name;

+------+--------------------+
| Name | Scores             |
+------+--------------------+
| Tim  | English-98         |
| Tom  | English-90/Math-80 |
+------+--------------------+

4.2 JSON_ARRAYAGG()

JSON_ARRAYAGG(expr)

我们通常在 MySQL 中按如下方式使用 JSON_ARRAYAGG() 函数：

SELECT JSON_ARRAYAGG(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

参数：

expr

必须的。它可以是一个列名，也可以是一个表达式。

返回值：

MySQL JSON_ARRAYAGG(expr) 函数返回一个 JSON 数组，它聚合了所有符合条件的值。

如果结果集没有任何行，此函数将返回 NULL。

JSON_ARRAYAGG() 示例

我们将通过 student_score 表进行演示。

首先，让我们使用如下语句创建表 student_score：

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);

这里我们创建了表用于存储学生的成绩，这个表有 4 个列组成：

id 是主键
name 是学生的名字
subject 是学生的科目
score 是学生的成绩

其次，让我插入一些数据用于演示：

INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES
    ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Tim', 'English', 98);

以下是表中的数据：

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Tim  | English |    98 |
+----+------+---------+-------+

现在，我们可以使用 JSON_ARRAYAGG() 函数获取每个人参加的考试科目的列表，如下：

SELECT
    name AS `Name`,
    JSON_ARRAYAGG(subject) AS `Subjects`
FROM student_score
GROUP BY name;

+------+---------------------+
| Name | Subjects            |
+------+---------------------+
| Tim  | ["English"]         |
| Tom  | ["Math", "English"] |
+------+---------------------+

这里，我们知道了 Tom 参加了 Math 和 English 两门考试，而 Tim 只参加了 English 考试。

4.3 JSON_OBJECTAGG()

JSON_OBJECTAGG(key_expr, value_expr)

我们通常在 MySQL 中按如下方式使用 JSON_OBJECTAGG() 函数：

SELECT JSON_OBJECTAGG(key_expr, value_expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

参数：

key_expr

必须的。它的值作为结果对象中的键值对中的键。它可以是一个列名，也可以是一个表达式。

value_expr

可选的。它的值作为结果对象中的键值对中的值。它可以是一个列名，也可以是一个表达式。

返回值：

MySQL JSON_OBJECTAGG() 函数返回一个 JSON 对象，其中的键值对中的键是 key_expr 的值，值是 value_expr 的值。

如果存在重复的键，则只保留最后一个键作为键值对，其他重复的键值对都被丢弃。

如果结果集没有任何行，此函数将返回 NULL。

JSON_OBJECTAGG() 示例

我们将通过 student_score 表进行演示。

首先，让我们使用如下语句创建表 student_score：

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);

这里我们创建了表用于存储学生的成绩，这个表有 4 个列组成：

id 是主键
name 是学生的名字
subject 是学生的科目
score 是学生的成绩

其次，让我插入一些数据用于演示：

INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES
    ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Tim', 'English', 98);

以下是表中的数据：

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Tim  | English |    98 |
+----+------+---------+-------+

现在，我们可以使用 JSON_OBJECTAGG() 函数获取每个人考试成绩，如下：

SELECT
    name AS `Name`,
    JSON_OBJECTAGG(subject, score) AS `Scores`
FROM student_score
GROUP BY name;

+------+-----------------------------+
| Name | Scores                      |
+------+-----------------------------+
| Tim  | {"English": 98}             |
| Tom  | {"Math": 80, "English": 90} |
+------+-----------------------------+

这里，在结果集中的 Scores 列的值是 JSON 对象。对于 Tim 来说，由于他只参加了一门考试，因此 JSON 对象中只有一个键值对。对于 Tom 来说，由于他参加了两门考试，因此 JSON 对象中有两个键值对。

五、JSON函数

函数	用法
JSON_ARRAY(value1[, value2[, ...]])	返回一个包含了所有参数的 JSON 数组
JSON_CONTAINS(target_json, candidate_json) JSON_CONTAINS(target_json, candidate_json, path)	检查一个 JSON 文档中是否包含另一个 JSON 文档
JSON_CONTAINS_PATH(json, one_or_all, path[, path])	检查一个 JSON 文档中在指定的路径上是否有值存在
JSON_DEPTH(json)	返回一个 JSON 文档的最大深度
JSON_KEYS(json) JSON_KEYS(json, path)	返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组
JSON_OBJECT(key, value[, key2, value2, ...])	返回一个包含了由参数指定的所有键值对的 JSON 对象
JSON_OVERLAPS(json1, json2)	检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素
JSON_PRETTY(json)	格式化输出一个 JSON 文档，以便更易于阅读

5.1 JSON_ARRAY()

JSON_ARRAY(value1[, value2[, ...]])

参数

value1[, value2[, ...]]

可选的。一些值，他们将被放在 JSON 数组中。

返回值

JSON_ARRAY() 函数评估参数中的所有的值，并返回一个包含了所有参数的 JSON 数组。

这里可能有一些转换发生：

TRUE 被转换为 true
FALSE 被转换为 false
NULL 被转换为 null
日期，时间，日期时间被转换为字符串

JSON_ARRAY() 示例

SELECT JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW());

+---------------------------------------------------------------+
| JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW())              |
+---------------------------------------------------------------+
| [123, "abc", null, true, false, "2022-04-18 07:47:23.000000"] |
+---------------------------------------------------------------+

包含数组的数组

SELECT JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec'));

+------------------------------------------------------------+
| JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec')) |
+------------------------------------------------------------+
| [[123, 456], ["abc", "dec"]]                               |
+------------------------------------------------------------+

这里，我们使用了 JSON_ARRAY() 的结果作为 JSON_ARRAY() 的参数。

包含对象的数组

SELECT JSON_ARRAY(
        JSON_OBJECT('name', 'Jim', 'age', 20),
        JSON_OBJECT('name', 'Tim', 'age', 18)
    ) AS objct_array;

+----------------------------------------------------------+
| objct_array                                              |
+----------------------------------------------------------+
| [{"age": 20, "name": "Jim"}, {"age": 18, "name": "Tim"}] |
+----------------------------------------------------------+

这里，我们使用了 JSON_OBJECT() 的结果作为 JSON_ARRAY() 的参数。

5.2 JSON_CONTAINS()

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)

参数

target_json

必需的。一个 JSON 文档。

candidate_json

必需的。被包含的 JSON 文档。

path

可选的。一个路径表达式。

返回值

如果在 JSON 文档 target_json 中包含了 JSON 文档 candidate_json，JSON_CONTAINS() 函数将返回 1，否则返回 0。如果提供了 path 参数，则检查由 path 匹配的部分是否包含 candidate_json JSON 文档。

如果存在以下的情况， JSON_CONTAINS() 函数将返回 NULL：

如果 JSON 文档中不存在指定的路径。
如果任意一个参数为 NULL。

JSON_CONTAINS() 函数将在以下情况下返回错误：

如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_CONTAINS() 示例

示例: 数组

SELECT
    JSON_CONTAINS('[1, 2, {"x": 3}]', '1') as `1`,
    JSON_CONTAINS('[1, 2, {"x": 3}]', '{"x": 3}') as `{"x": 3}`,
    JSON_CONTAINS('[1, 2, {"x": 3}]', '3') as `3`;

+------+----------+------+
| 1    | {"x": 3} | 3    |
+------+----------+------+
|    1 |        1 |    0 |
+------+----------+------+

示例: 指定路径

SELECT
    JSON_CONTAINS('[1, 2, [3, 4]]', '2'),
    JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]');

+--------------------------------------+----------------------------------------------+
| JSON_CONTAINS('[1, 2, [3, 4]]', '2') | JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]') |
+--------------------------------------+----------------------------------------------+
|                                    1 |                                            0 |
+--------------------------------------+----------------------------------------------+

这里，我们已经在 JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]') 指定了路径表达式 $[2]，其匹配的内容是 [3, 4]。 [3, 4] 中没有包含 2，因此，它返回了 0。

5.3 JSON_CONTAINS_PATH()

JSON_CONTAINS_PATH(json, one_or_all, path[, path])

参数

json

必需的。一个 JSON 文档。

one_or_all

必需的。可用值：'one', 'all'。它指示是否检查所有的路径。

path

必需的。您应该至少指定一个路径表达式。

返回值

如果 JSON 文档在指定的路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。

JSON_CONTAINS_PATH() 根据 one_or_all 参数决定是否检查所有的路径：

如果是 'one'，且至少有一个路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。
如果是 'all'，且所有的路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。

如果任意一个参数为 NULL， JSON_CONTAINS_PATH() 函数将返回 NULL。

JSON_CONTAINS_PATH() 函数将在以下情况下返回错误：

如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_CONTAINS_PATH() 示例

示例: 数组

SELECT
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]') as `$[0]`,
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[3]') as `$[3]`,
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[2].x') as `$[2].x`;

+------+------+--------+
| $[0] | $[3] | $[2].x |
+------+------+--------+
|    1 |    0 |      1 |
+------+------+--------+

示例: one vs all

SELECT
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'one', '$[0]', '$[3]') as `one`,
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]', '$[3]') as `all`;

+------+------+
| one  | all  |
+------+------+
|    1 |    0 |
+------+------+

5.4 JSON_DEPTH()

JSON_DEPTH(json)

参数

json

必需的。一个 JSON 文档。

返回值

JSON_DEPTH() 函数返回一个 JSON 文档的最大深度。JSON_DEPTH() 函数按如下规则计算 JSON 文档的深度：

一个空的数组、空的对象或者纯值的深度是 1。
一个仅包含深度为 1 的元素的数组的深度是 2。
一个所有成员的值的深度为 1 的对象的深度是 2。
除此之外的其他 JSON 文档的深度都大于 2.

如果参数为 NULL，此函数将返回 NULL。

如果参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_DEPTH() 示例

示例 1

SELECT JSON_DEPTH('[]'), JSON_DEPTH('[1, 2]'), JSON_DEPTH('[1, [2, 3]]');

+------------------+----------------------+---------------------------+
| JSON_DEPTH('[]') | JSON_DEPTH('[1, 2]') | JSON_DEPTH('[1, [2, 3]]') |
+------------------+----------------------+---------------------------+
|                1 |                    2 |                         3 |
+------------------+----------------------+---------------------------+

示例 2

SELECT JSON_DEPTH('{}'), JSON_DEPTH('{"x": 1}'), JSON_DEPTH('{"x": {"y": 1}}');

+------------------+------------------------+-------------------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('{"x": 1}') | JSON_DEPTH('{"x": {"y": 1}}') |
+------------------+------------------------+-------------------------------+
|                1 |                      2 |                             3 |
+------------------+------------------------+-------------------------------+

5.5 JSON_KEYS()

JSON_KEYS(json)
JSON_KEYS(json, path)

参数

json

必需的。一个 JSON 对象文档。

path

可选的。路径表达式。

返回值

MySQL JSON_KEYS() 函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。如果指定了路径表达式，则返回路径表达式匹配的 JSON 对象中的最上层的成员组成的数组。

如果存在以下的情况， JSON_KEYS() 函数将返回 NULL：

未指定路径，且 JSON 文档不是一个 JSON 对象。
指定了路径，且路径匹配的 JSON 值不是 JSON 对象。
任意参数为 NULL。

JSON_KEYS() 函数将在以下情况下返回错误：

如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_KEYS() 示例

下面的示例返回一个 JSON 对象的所有顶层成员组成的数组。

SELECT JSON_KEYS('{"x": 1, "y": 2, "z": 3}');

+---------------------------------------+
| JSON_KEYS('{"x": 1, "y": 2, "z": 3}') |
+---------------------------------------+
| ["x", "y", "z"]                       |
+---------------------------------------+

我们也可以返回有路径表达式匹配的 JSON 对象的键。

SELECT JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]');

+----------------------------------------------------+
| JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]') |
+----------------------------------------------------+
| ["x", "y", "z"]                                    |
+----------------------------------------------------+

如果匹配的 JSON 文档不是 JSON 对象，JSON_KEYS() 返回 NULL。这个示例说明了这一点：

SELECT
    JSON_KEYS('1') as `keys of 1`,
    JSON_KEYS('true') as `keys of true`,
    JSON_KEYS('"hello"') as `keys of "hello"`,
    JSON_KEYS('[1, 2]') as `keys of [1, 2]`;

+-----------+--------------+-----------------+----------------+
| keys of 1 | keys of true | keys of "hello" | keys of [1, 2] |
+-----------+--------------+-----------------+----------------+
| NULL      | NULL         | NULL            | NULL           |
+-----------+--------------+-----------------+----------------+

5.6 JSON_OBJECT()

JSON_OBJECT(key, value[, key2, value2, ...])

参数

key

必需的。对象中的键。

value

必需的。对象中的 key 的值。

返回值

JSON_OBJECT() 函数评估参数中的所有的键值对，并返回一个包含了所有键值对的 JSON 对象。

由于 JSON 对象中的所有键为字符串，因此 JSON_OBJECT() 会将不是字符串类型的 key 转为字符串类型。为了保证程序的稳定性，我们一般使用字符串类型的 key.

执行过程中可能会出现如下错误：

如果 key 是 NULL， MySQL 将返回错误： ERROR 3158 (22032): JSON documents may not contain NULL member names.。
如果由奇数个参数， MySQL 将返回错误： ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'。

JSON_OBJECT() 示例

示例 1

SELECT JSON_OBJECT('name', 'Jim', 'age', 20);

+---------------------------------------+
| JSON_OBJECT('name', 'Jim', 'age', 20) |
+---------------------------------------+
| {"age": 20, "name": "Jim"}            |
+---------------------------------------+

这里， JSON_OBJECT() 返回的 JSON 对象中由两个成员： name 和 age。其中 name 的值为 'Jim'， age 的值为 20。

重复的键

如果 JSON_OBJECT() 的参数中出现了重复的键值对，那么后面的键值对保留在最终返回的对象中。

SELECT JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim');

+------------------------------------------------------+
| JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim') |
+------------------------------------------------------+
| {"age": 20, "name": "Tim"}                           |
+------------------------------------------------------+

包含复杂的对象

复杂的 JSON 对象可以存储更多的信息。

SELECT JSON_OBJECT(
        'name',
        'Tim',
        'age',
        20,
        'friend',
        JSON_OBJECT('name', 'Jim', 'age', 20),
        'hobby',
        JSON_ARRAY('games', 'sports')
    ) AS object;

+------------------------------------------------------------------------------------------------+
| object                                                                                         |
+------------------------------------------------------------------------------------------------+
| {"age": 20, "name": "Tim", "hobby": ["games", "sports"], "friend": {"age": 20, "name": "Jim"}} |
+------------------------------------------------------------------------------------------------+

这里，我们创建了如下 JSON 对象：

{
  "age": 20,
  "name": "Tim",
  "hobby": ["games", "sports"],
  "friend": { "age": 20, "name": "Jim" }
}

其中：

hobby 的值是个数组，通过 JSON_ARRAY() 函数计算得出.
friend 的值是个对象，通过 JSON_OBJECT() 函数计算得出.

5.7 JSON_OVERLAPS()

JSON_OVERLAPS(json1, json2)

参数

json1

必需的。一个 JSON 文档。

json2

必需的。另一个 JSON 文档。

返回值

JSON_OVERLAPS() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。如果两个 JSON 文档有重叠的内容，JSON_OVERLAPS() 函数返回 1，否则返回 0。

JSON_OVERLAPS() 函数按照如下规则比较两个 JSON 文档：

比较两个数组时，如果两个数组至少有一个相同的元素返回 1，否则返回 0。
比较两个对象时，如果两个对象至少有一个相同的键值对返回 1，否则返回 0。
比较两个纯值时，如果两个值相同返回 1，否则返回 0。
比较纯值和数组时，如果值是这个数组中的直接元素返回 1，否则返回 0。
比较纯值和对象的结果为 0。
比较数组和对象的结果为 0。
JSON_OVERLAPS() 不会对参数的数据类型进行转换。

如果参数为 NULL，此函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_OVERLAPS() 示例

比较数组

SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]');

+-----------------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

这里，由于两个数组都有共同的元素 3，因此 JSON_OVERLAPS() 返回了 1。也就是说 [1, 2, 3] 和 [3, 4, 5] 有重叠。

让我们再看一个例子：

SELECT JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]');

+-------------------------------------------+
| JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

这里，由于 [1, 2, [3]] 中的元素 [3] 和 [3, 4, 5] 中的 3 是不同的，因此 JSON_OVERLAPS() 返回了 0。也就是说 [1, 2, [3]] 和 [3, 4, 5] 没有交集。

比较对象

SELECT
    JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}'),
    JSON_OVERLAPS('{"x": 1}', '{"y": 2}');

+-----------------------------------------------+---------------------------------------+
| JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}') | JSON_OVERLAPS('{"x": 1}', '{"y": 2}') |
+-----------------------------------------------+---------------------------------------+
|                                             1 |                                     0 |
+-----------------------------------------------+---------------------------------------+

这里， {"x": 1} 和 {"x": 1, "y": 2} 都有共同的键值对 "x": 1，因此 JSON_OVERLAPS() 返回了 1。而 {"x": 1} 和 {"y": 2} 没有共同的键值对，因此 JSON_OVERLAPS() 返回了 0。

比较纯值和数组

SELECT
    JSON_OVERLAPS('[1, 2, 3]', '3'),
    JSON_OVERLAPS('[1, 2, [3]]', '3');

+---------------------------------+-----------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '3') | JSON_OVERLAPS('[1, 2, [3]]', '3') |
+---------------------------------+-----------------------------------+
|                               1 |                                 0 |
+---------------------------------+-----------------------------------+

这里，3 是 [1, 2, 3] 的元素，而不是 [1, 2, [3]] 的元素，因此他们返回了不同的结果。

比较纯值

SELECT JSON_OVERLAPS('1', '1'), JSON_OVERLAPS('1', '"1"');

+-------------------------+---------------------------+
| JSON_OVERLAPS('1', '1') | JSON_OVERLAPS('1', '"1"') |
+-------------------------+---------------------------+
|                       1 |                         0 |
+-------------------------+---------------------------+

这里，由于 1 和 "1" 是不同类型的数据，因此 JSON_OVERLAPS() 返回了 0。

5.8 JSON_PRETTY()

JSON_PRETTY(json)

参数

json

必需的。一个 JSON 文档或 JSON 类型的值。

返回值

JSON_PRETTY() 函数格式化输出一个 JSON 文档，以便更易于阅读。

JSON_PRETTY() 函数按照如下美化输出 JSON 文档：

每个数组元素或每个对象成员都显示在单独的行上，与其父级相比，缩进一个附加级别。
每个缩进级别都会添加两个前导空格。
分隔各个数组元素或对象成员的逗号打印在分隔两个元素或成员的换行符之前。
对象成员的键和值由冒号后跟空格分隔（: ）。
空对象或数组打印在一行上。左括号和右括号之间未打印空格。
字符串标量和键名中的特殊字符使用与 JSON_QUOTE() 函数相同的规则进行转义。

如果参数为 NULL，此函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用
JSON_VALID()
验证 JSON 文档的有效性。

JSON_PRETTY() 示例

格式化输出数组

SELECT JSON_PRETTY('[1, 2, 3]');

+--------------------------+
| JSON_PRETTY('[1, 2, 3]') |
+--------------------------+
| [
  1,
  2,
  3
]        |
+--------------------------+

格式化输出对象

SELECT JSON_PRETTY('{"x": 1, "y": 2}');

+---------------------------------+
| JSON_PRETTY('{"x": 1, "y": 2}') |
+---------------------------------+
| {
  "x": 1,
  "y": 2
}          |
+---------------------------------+

格式化输出复杂对象

SELECT JSON_PRETTY('{"x": 1, "y": [1, 2, 3], "z": {"a": "a", "b": true}}');

+------------------------------------------------------------------------------------------+
| JSON_PRETTY('{"x": 1, "y": [1, 2, 3], "z": {"a": "a", "b": true}}')                      |
+------------------------------------------------------------------------------------------+
| {
  "x": 1,
  "y": [
    1,
    2,
    3
  ],
  "z": {
    "a": "a",
    "b": true
  }
} |
+------------------------------------------------------------------------------------------+

六、流程控制函数

函数	用法
CASE	`CASE` 语句相当于 if-elseif-else，用于流程控制中的多分支情况
IF(condition, expr_if_true, expr_if_false)	是一个 if-else 的函数，根据测试条件是否为真分别返回指定的值
IFNULL(expr1, expr2)	一个 if-else 的函数，如果第一个参数为 `NULl`，返回第二个参数，否则返回第一个参数
NULLIF(expr1, expr2)	根据两个参数是否相等决定返回 `NULL` 还是第一个参数

6.1 CASE

CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

或者

CASE
    WHEN condition THEN result
    [WHEN condition THEN result ...]
    [ELSE result]
END

返回值：

CASE 语句返回 condition 为真或者 value = compare_value 为真的 THEN 子句中指定的值。

CASE 示例

CASE 语句用于多个逻辑判断分支的情况。下面的例子展示了如何通过工作日索引(0 到 6)返回对应的工作日的名称。

首先，让我们创建一个表 test_case_weekday 用于演示。

DROP TABLE IF EXISTS test_case_weekday;
CREATE TABLE test_case_weekday (
    weekday_index INT NOT NULL
);

让我们插入几个数据：

INSERT INTO test_case_weekday
VALUES (0), (1), (2), (3), (4), (5), (6);

现在表中具有了如下数据：

+---------------+
| weekday_index |
+---------------+
|             0 |
|             1 |
|             2 |
|             3 |
|             4 |
|             5 |
|             6 |
+---------------+

如果我们想在查询中同时查出工作日索引对应的工作日的名称，可以使用 CASE 语句。如下：

SELECT
    weekday_index AS `Weekday Index`,
    CASE weekday_index
        WHEN 0 THEN 'Monday'
        WHEN 1 THEN 'Tuesday'
        WHEN 2 THEN 'Wednesday'
        WHEN 3 THEN 'Thursday'
        WHEN 4 THEN 'Friday'
        WHEN 5 THEN 'Saturday'
        WHEN 6 THEN 'Sunday'
        ELSE 'Error'
    END AS `Weekday Name`
FROM
    test_case_weekday;

+---------------+--------------+
| Weekday Index | Weekday Name |
+---------------+--------------+
|             0 | Monday       |
|             1 | Tuesday      |
|             2 | Wednesday    |
|             3 | Thursday     |
|             4 | Friday       |
|             5 | Saturday     |
|             6 | Sunday       |
+---------------+--------------+

这里我们也可以使用第二种语法来改写上面的语句：

SELECT
    weekday_index AS `Weekday Index`,
    CASE
        WHEN weekday_index = 0 THEN 'Monday'
        WHEN weekday_index = 1 THEN 'Tuesday'
        WHEN weekday_index = 2 THEN 'Wednesday'
        WHEN weekday_index = 3 THEN 'Thursday'
        WHEN weekday_index = 4 THEN 'Friday'
        WHEN weekday_index = 5 THEN 'Saturday'
        WHEN weekday_index = 6 THEN 'Sunday'
        ELSE 'Error'
    END AS `Weekday Name`
FROM
    test_case_weekday;

输出结果与上面完全相同。

6.2 IF()

IF(condition, expr_if_true, expr_if_false)

参数：

condition

必需的。测试条件表达式。

expr_if_true

必需的。如果 condition 为 TRUE 返回此值。

expr_if_false

必需的。如果 condition 为 FALSE 返回此值。

返回值：

如果 condition 为 TRUE，IF() 函数返回 expr_if_true，否则返回 expr_if_false。

在 MySQL 中，如果 condition 不等于 0，并且不是 NULL 时，它是 TRUE。

IF() 示例

SELECT IF(1 > 2, 'NO', 'YES'), IF(2 > 1, 'NO', 'YES');

+------------------------+------------------------+
| IF(1 > 2, 'NO', 'YES') | IF(2 > 1, 'NO', 'YES') |
+------------------------+------------------------+
| YES                    | NO                     |
+------------------------+------------------------+

这里，

表达式 1 > 2 返回了 0，因此 IF(1 > 2, 'NO', 'YES') 返回了 YES。
表达式 2 > 1 返回了 1，因此 IF(2 > 1, 'NO', 'YES') 返回了 NO。

6.3 IFNULL()

IFNULL(expr1, expr2)

此函数相当于 IF() 和 ISNULL() 函数的结合： IF(ISNULL(expr1), expr2, expr1)。

参数：

expr1

必需的。判断此表达式是否为 NULL。

expr2

必需的。当 expr1 为 NULL 时，返回 expr2。

返回值：

如果 expr1 为 NULl，IFNULL() 函数返回 expr1，否则返回 expr2。

IFNULL() 示例

SELECT
    IFNULL(NULL, 'It is NULL'),
    IFNULL('I am not NULL', 'I am NULL');

+----------------------------+--------------------------------------+
| IFNULL(NULL, 'It is NULL') | IFNULL('I am not NULL', 'I am NULL') |
+----------------------------+--------------------------------------+
| It is NULL                 | I am not NULL                        |
+----------------------------+--------------------------------------+

6.4 NULLIF()

NULLIF(expr1, expr2)

参数：

expr1

必需的。一个值或者表达式。

expr2

必需的。另一个值或者表达式。

返回值：

如果 expr1 = expr2，NULLIF() 函数返回 NULL，否则返回 expr1。

NULLIF() 示例

SELECT
    NULLIF(1, 1),
    NULLIF(1, 2);

+--------------+--------------+
| NULLIF(1, 1) | NULLIF(1, 2) |
+--------------+--------------+
|         NULL |            1 |
+--------------+--------------+

NULLIF() 相当于 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END。如下：

SELECT
    CASE WHEN 1 = 1 THEN NULL ELSE 1 END,
    CASE WHEN 1 = 2 THEN NULL ELSE 1 END;

+--------------------------------------+--------------------------------------+
| CASE WHEN 1 = 1 THEN NULL ELSE 1 END | CASE WHEN 1 = 2 THEN NULL ELSE 1 END |
+--------------------------------------+--------------------------------------+
|                                 NULL |                                    1 |
+--------------------------------------+--------------------------------------+

七、其他函数

函数	用法
ANY_VALUE(column_name)	返回指定列中的任意一个值，它用在包含了 `GROUP BY` 的语句中，以抑制 `ONLY_FULL_GROUP_BY` 导致的错误
IS_IPV4(ip)	检查给定的参数是否是一个合法的 IPv4 地址
IS_IPV6(ip)	检查给定的参数是否是一个合法的 IPv6 地址
ISNULL(expr)	检查指定的参数是否为 `NULL`
UUID()	根据 RFC 4122 生成一个通用唯一标识符（UUID）并返回

7.1 ANY_VALUE()

ANY_VALUE(column_name)

参数

column_name

必需的。一个列名。

返回值

MySQL ANY_VALUE() 函数返回指定列中的任意一个值。

ANY_VALUE() 示例

为了演示 ANY_VALUE() 函数的用法，让我们使用如下语句创建一个表 test_any_value，它包含了 id, user, 和 address 三个列：

CREATE TABLE test_any_value (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

然后，让我们再插入几条数据：

INSERT INTO test_any_value (name, address)
VALUES
    ('Tom', 'Address 1 of Tom'),
    ('Tom', 'Address 2 of Tom'),
    ('Adam', 'Address of Adam');

让我们看一下表中的数据：

SELECT * FROM test_any_value;

+----+------+------------------+
| id | name | address          |
+----+------+------------------+
|  1 | Tom  | Address 1 of Tom |
|  2 | Tom  | Address 2 of Tom |
|  3 | Adam | Address of Adam  |
+----+------+------------------+

现在，我们想要统计每个人的地址的数量，并显示每个人的任意一个地址：

SELECT name,
    COUNT(address) AS count,
    address
FROM test_any_value
GROUP BY name;

当我们运行这个语句的时候，MySQL 返回了一个错误：”ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ’testdb.test_any_value.address’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by“。

这里，让我们使用 ANY_VALUE() 解决这个问题：

SELECT name,
    COUNT(address) AS count,
    ANY_VALUE(address) AS address
FROM test_any_value
GROUP BY name;

+------+-------+------------------+
| name | count | address          |
+------+-------+------------------+
| Tom  |     2 | Address 1 of Tom |
| Adam |     1 | Address of Adam  |
+------+-------+------------------+

现在，MySQL 没有给出错误并返回了我们期望的结果。这与在不使用 ANY_VALUE() 并将 ONLY_FULL_GROUP_BY 模式禁用掉的结果完全一样。

7.2 IS_IPV4()

IS_IPV4(ip)

参数

ip

必需的。一个待检查的 IPv4 地址。

返回值

MySQL IS_IPV4() 函数检查给定的参数是否是一个合法的 IPv4 地址，如果是返回 1，否则返回 0。

如果参数为 NULL，该函数将返回 NULL。

IS_IPV4() 示例

SELECT IS_IPV4('192.168.1.100'), IS_IPV4('192.168.1.300');

+--------------------------+--------------------------+
| IS_IPV4('192.168.1.100') | IS_IPV4('192.168.1.300') |
+--------------------------+--------------------------+
|                        1 |                        0 |
+--------------------------+--------------------------+

这里，由于 192.168.1.300 不是一个合法的 IPv4 地址，因此 IS_IPV4('192.168.1.300') 返回了 0。

7.3 IS_IPV6()

IS_IPV6(ip)

参数

ip

必需的。一个待检查的 IPv6 地址。

返回值

MySQL IS_IPV6() 函数检查给定的参数是否是一个合法的 IPv6 地址，如果是返回 1，否则返回 0。

对于一个给定的参数来说，如果 IS_IPV6() 返回 1， INET6_ATON() 返回一个非 NULL 值。

如果参数为 NULL，该函数将返回 NULL。

IS_IPV6() 示例

SELECT IS_IPV6('192.168.1.100'), IS_IPV6('fdfe::5a55:caff:fefa:9089');

+--------------------------+--------------------------------------+
| IS_IPV6('192.168.1.100') | IS_IPV6('fdfe::5a55:caff:fefa:9089') |
+--------------------------+--------------------------------------+
|                        0 |                                    1 |
+--------------------------+--------------------------------------+

这里，由于 192.168.1.100 是一个 IPv4 地址，不是一个 IPv6 地址，因此 IS_IPV6('192.168.1.100') 返回了 0。

7.4 ISNULL()

ISNULL(expr)

参数

expr

必需的。一个被测试的表达式。

返回值

MySQL ISNULL() 函数检查指定的参数是否为 NULL。如果参数为 NULL，该函数返回 1，否则返回 0。

如果您没有为 ISNULL() 提供参数，MySQL 将返回一个错误。

ISNULL() 示例

SELECT 
    ISNULL(NULL), 
    ISNULL(""), 
    ISNULL("A"), 
    ISNULL(0), 
    ISNULL(1);

+--------------+------------+-------------+-----------+-----------+
| ISNULL(NULL) | ISNULL("") | ISNULL("A") | ISNULL(0) | ISNULL(1) |
+--------------+------------+-------------+-----------+-----------+
|            1 |          0 |           0 |         0 |         0 |
+--------------+------------+-------------+-----------+-----------+

这里，只有 ISNULL(NULL) 返回了 1，其他都返回了 0。

7.5 UUID()

UUID()

参数

MySQL UUID() 函数没有任何参数。

返回值

MySQL UUID() 函数返回一个根据 RFC 4122 生成的通用唯一标识符（UUID）。

UUID() 返回的值符合 RFC 4122 中描述的 UUID 版本 1。该值是一个 128 位数字，表示为一个由五个十六进制数字组成的 utf8 字符串，格式为aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee：

前三个数字是从时间戳的低、中和高三部分生成的。高位部分还包括 UUID 版本号。
第四个数字保留时间唯一性，以防时间戳值失去单调性（例如，由于夏令时）。
第五个数字是 IEEE 802 节点号，它提供空间唯一性。如果后者不可用（例如，因为主机设备没有以太网卡，或者不知道如何在主机操作系统上找到接口的硬件地址），则用随机数代替。在这种情况下，无法保证空间唯一性。然而，碰撞的概率应该 非常低。

接口的 MAC 地址仅在 FreeBSD、Linux 和 Windows 上被使用。在其他操作系统上，MySQL 使用随机生成的 48 位数字。

请使用
UUID_TO_BIN()
和 BIN_TO_UUID() 函数以在 UUID 的字符串值和二进制值之间进行转换。

请使用 IS_UUID 函数检查一个字符串是否为有效的 UUID 值。

UUID() 示例

SELECT UUID();

+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| d114115a-ce6a-11ec-8607-63ec778e6346 |
+--------------------------------------+

八、窗口函数

函数	用法
CUME_DIST()	返回当前行的累积分布，即从第一行到与当前行值相同的最后一行的行数在分区内的总行数中的占比。常用于显示一个记录集中最高或者最低百分比数量的记录。比如，全国收入的前 10% 的人、此次考试最后 5% 的学生等。
DENSE_RANK()	返回当前行所在的分区内的排名，从 1 开始，但没有间隔。也就是说，相同的值具有相同的排名，但是下一个不同的值的排名按顺序增加。比如，如果有 2 个第一名，那么第三位的排名是 `2`。这与 rank() 函数是不同的。
FIRST_VALUE()	从当前行关联的窗口框架的第一行中返回评估的值
LAG()	返回来自当前行所在的分区内当前行之前的指定行之内的值
LAST_VALUE()	从当前行关联的窗口框架的最后一行中返回评估的值
LEAD()	返回来自当前行所在的分区内当前行之后的指定行之内的行的值
NTH_VALUE()	从当前行关联的窗口框架的指定的一行中返回评估的值
NTILE()	将当前行所在的分区内的所有行尽可能平均的分成指定数量的区间，并返回当前行所在的区间编号。每个区间， MySQL 称之为一个排名桶。 `NTILE()` 根据指定排序为每个桶指设定排名。
PERCENT_RANK()	返回当前行所在的分区内的相对排名，也就是 `(rank() - 1) / (分区总行数 - 1)`。
RANK()	返回当前行所在的分区内的排名，从 1 开始，但有间隔。也就是说，相同的值具有相同的排名，但是下一个不同的值的排名采用 row_number() 编号。比如，如果有 2 个第一名，那么第三位的排名是 `3`。这与 dense_rank() 函数是不同的。
ROW_NUMBER()	返回当前行所在的分区内的序号，从 1 开始

演示数据

表一：student_grade

使用下面的 CREATE TABLE 语句创建一个表 student_grade 以存储学生的班级和成绩：

CREATE TABLE student_grade (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class CHAR(1) NOT NULL,
  subject VARCHAR(20) NOT NULL,
  grade INT NOT NULL
);

这里创建了一个 student_grade 表，它有 5 个列：

id - 行 ID，主键。
name - 学生的姓名。
class - 学生所在的班级。
subject - 科目的名称。
grade - 该科目的成绩。

使用下面的 INSERT 语句向 student_grade 表中插入一些行：

INSERT INTO student_grade
  (name, class, subject, grade)
VALUES
  ('Tim', 'A', 'Math', 9),
  ('Tom', 'A', 'Math', 7),
  ('Jim', 'A', 'Math', 8),
  ('Tim', 'A', 'English', 7),
  ('Tom', 'A', 'English', 8),
  ('Jim', 'A', 'English', 7),
  ('Lucy', 'B', 'Math', 8),
  ('Jody', 'B', 'Math', 6),
  ('Susy', 'B', 'Math', 9),
  ('Lucy', 'B', 'English', 6),
  ('Jody', 'B', 'English', 7),
  ('Susy', 'B', 'English', 8);

使用下面的 SELECT 语句检索表中的数据：

SELECT * FROM student_grade;

+----+------+-------+---------+-------+
| id | name | class | subject | grade |
+----+------+-------+---------+-------+
|  1 | Tim  | A     | Math    |     9 |
|  2 | Tom  | A     | Math    |     7 |
|  3 | Jim  | A     | Math    |     8 |
|  4 | Tim  | A     | English |     7 |
|  5 | Tom  | A     | English |     8 |
|  6 | Jim  | A     | English |     7 |
|  7 | Lucy | B     | Math    |     8 |
|  8 | Jody | B     | Math    |     6 |
|  9 | Susy | B     | Math    |     9 |
| 10 | Lucy | B     | English |     6 |
| 11 | Jody | B     | English |     7 |
| 12 | Susy | B     | English |     8 |
+----+------+-------+---------+-------+
12 rows in set (0.00 sec)

表二：tax_revenue

使用下面的 CREATE TABLE 语句创建一个表 tax_revenue 以存储每季度的税收收益：

CREATE TABLE tax_revenue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  year CHAR(4) NOT NULL,
  quarter CHAR(1) NOT NULL,
  revenue INT NOT NULL
);

这里创建了一个 tax_revenue 表，它有 5 个列：

id - 行 ID，主键。
year - 年份。
quarter - 季节，1 - 4。
revenue - 税收收益。

使用下面的 INSERT 语句向 tax_revenue 表中插入一些行：

INSERT INTO tax_revenue
  (year, quarter, revenue)
VALUES
  ('2020', '1', 3515),
  ('2020', '2', 3678),
  ('2020', '3', 4203),
  ('2020', '4', 3924),
  ('2021', '1', 3102),
  ('2021', '2', 3293),
  ('2021', '3', 3602),
  ('2021', '4', 2901);

使用下面的 SELECT 语句检索表中的数据：

SELECT * FROM tax_revenue;

+----+------+---------+---------+
| id | year | quarter | revenue |
+----+------+---------+---------+
|  1 | 2020 | 1       |    3515 |
|  2 | 2020 | 2       |    3678 |
|  3 | 2020 | 3       |    4203 |
|  4 | 2020 | 4       |    3924 |
|  5 | 2021 | 1       |    3102 |
|  6 | 2021 | 2       |    3293 |
|  7 | 2021 | 3       |    3602 |
|  8 | 2021 | 4       |    2901 |
+----+------+---------+---------+
8 rows in set (0.00 sec)

8.1 CUME_DIST()

CUME_DIST()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

partition_column_list

参与分区的列的列表。

partition_column_list

参与排序的列的列表。

返回值

MySQL CUME_DIST() 函数返回一个大于 0 并且小于等于 1 的值，它是当前行的累积分布。它的计算公式为：

(当前行之前的行数 + 与当前行值相同的行数) / 分区内的总行数

CUME_DIST() 示例

使用 student_grade 表演示

使用 MySQL CUME_DIST() 计算每个成绩的累积分布

下面的语句，使用 MySQL CUME_DIST() 函数按科目分组计算每个学生的成绩在每组中的累积分布：

SELECT *,
  CUME_DIST() OVER (
    PARTITION BY subject
    ORDER BY grade
  ) "cume_dist"
FROM student_grade;

+----+------+-------+---------+-------+---------------------+
| id | name | class | subject | grade | CUME_DIST           |
+----+------+-------+---------+-------+---------------------+
| 10 | Lucy | B     | English |     6 | 0.16666666666666666 |
|  4 | Tim  | A     | English |     7 |  0.6666666666666666 |
|  6 | Jim  | A     | English |     7 |  0.6666666666666666 |
| 11 | Jody | B     | English |     7 |  0.6666666666666666 |
|  5 | Tom  | A     | English |     8 |                   1 |
| 12 | Susy | B     | English |     8 |                   1 |
|  8 | Jody | B     | Math    |     6 | 0.16666666666666666 |
|  2 | Tom  | A     | Math    |     7 |  0.3333333333333333 |
|  3 | Jim  | A     | Math    |     8 |  0.6666666666666666 |
|  7 | Lucy | B     | Math    |     8 |  0.6666666666666666 |
|  1 | Tim  | A     | Math    |     9 |                   1 |
|  9 | Susy | B     | Math    |     9 |                   1 |
+----+------+-------+---------+-------+---------------------+
12 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

CUME_DIST() OVER (
  PARTITION BY subject
  ORDER BY grade
)

在 OVER 子句中，

PARTITION BY subject 将所有行按照科目进行分区
ORDER BY grade 将每个分区内的行按照成绩升序排列
CUME_DIST() 计算每行的累积分布。

注意，在每个科目内，成绩相同的行具有相同的累积分布。

8.2 DENSE_RANK()

DENSE_RANK()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL DENSE_RANK() 函数返回当前行所在的分区内的排名，从 1 开始，但没有间隔。

DENSE_RANK() 示例

使用 student_grade 表演示

按照科目查看每个学生的成绩排名

要查看在每个科目中每个学生按照成绩从高到低的排名，请使用如下语句：

SELECT *,
  DENSE_RANK() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "dense_rank",
  rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "rank"
FROM student_grade;

+----+------+-------+---------+-------+------------+------+
| id | name | class | subject | grade | DENSE_RANK | rank |
+----+------+-------+---------+-------+------------+------+
|  5 | Tom  | A     | English |     8 |          1 |    1 |
| 12 | Susy | B     | English |     8 |          1 |    1 |
|  4 | Tim  | A     | English |     7 |          2 |    3 |
|  6 | Jim  | A     | English |     7 |          2 |    3 |
| 11 | Jody | B     | English |     7 |          2 |    3 |
| 10 | Lucy | B     | English |     6 |          3 |    6 |
|  1 | Tim  | A     | Math    |     9 |          1 |    1 |
|  9 | Susy | B     | Math    |     9 |          1 |    1 |
|  3 | Jim  | A     | Math    |     8 |          2 |    3 |
|  7 | Lucy | B     | Math    |     8 |          2 |    3 |
|  2 | Tom  | A     | Math    |     7 |          3 |    5 |
|  8 | Jody | B     | Math    |     6 |          4 |    6 |
+----+------+-------+---------+-------+------------+------+
12 rows in set (0.01 sec)

注意，上面 SQL 语句中的窗口函数：

DENSE_RANK() OVER (
  PARTITION BY subject
  ORDER BY grade DESC
)

在 OVER 子句中，

PARTITION BY subject 将按照学科进行分区
ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
DENSE_RANK() 返回每行在其关联的分区内的排名。

您从上面也可以看出 DENSE_RANK() 与 rank() 的不同。

按照班级查看每个学生的总成绩排名

要查看在每个班级中每个学生按照总成绩从高到底的排名，请使用下面的语句：

SELECT 
  t.*,
  DENSE_RANK() OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
  ) "dense_rank"
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

+-------+------+-----------+------------+
| class | name | sum_grade | DENSE_RANK |
+-------+------+-----------+------------+
| A     | Tim  |        16 |          1 |
| A     | Tom  |        15 |          2 |
| A     | Jim  |        15 |          2 |
| B     | Susy |        17 |          1 |
| B     | Lucy |        14 |          2 |
| B     | Jody |        13 |          3 |
+-------+------+-----------+------------+
6 rows in set (0.00 sec)

在上面的语句中，注意这个子查询：

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)

主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 DENSE_RANK() 返回每行在其关联的分区内的排名。

8.3 FIRST_VALUE()

FIRST_VALUE(expr)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

expr

必需的。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL FIRST_VALUE() 函数从当前行关联的窗口框架的第一行中返回评估的值。

FIRST_VALUE() 示例

使用 student_grade 表演示

按照科目查看每个学生的成绩排名

要查看在每个科目中每个学生按照成绩从高到低的排序号和每个科目中的最好成绩，请使用如下语句：

SELECT 
  *,
  FIRST_VALUE(grade) OVER (
    PARTITION BY subject
    ORDER BY grade DESC
    RANGE BETWEEN 
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) first_grade
FROM student_grade;

+----+------+-------+---------+-------+-------------+
| id | name | class | subject | grade | first_grade |
+----+------+-------+---------+-------+-------------+
|  5 | Tom  | A     | English |     8 |           8 |
| 12 | Susy | B     | English |     8 |           8 |
|  4 | Tim  | A     | English |     7 |           8 |
|  6 | Jim  | A     | English |     7 |           8 |
| 11 | Jody | B     | English |     7 |           8 |
| 10 | Lucy | B     | English |     6 |           8 |
|  1 | Tim  | A     | Math    |     9 |           9 |
|  9 | Susy | B     | Math    |     9 |           9 |
|  3 | Jim  | A     | Math    |     8 |           9 |
|  7 | Lucy | B     | Math    |     8 |           9 |
|  2 | Tom  | A     | Math    |     7 |           9 |
|  8 | Jody | B     | Math    |     6 |           9 |
+----+------+-------+---------+-------+-------------+
12 rows in set (0.01 sec)

注意，上面 SQL 语句中的窗口函数：

FIRST_VALUE(grade) OVER (
  PARTITION BY subject
  ORDER BY grade DESC
  RANGE BETWEEN 
    UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

在 OVER 子句中，

PARTITION BY subject 将按照学科进行分区
ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 定义了每个分区中与当前行相关的窗口框架，这里是从分区的第一行到最后一行。
FIRST_VALUE(grade) 返回每行在其关联的分区的第一行的成绩（grade）。因为是按照 grade 逆序排序，于是每行中添加了一列显示最好的成绩。

按照班级查看每个学生的总成绩排名

要查看在每个班级中每个学生按照总成绩从高到底的排序号和每个班级的最好成绩，请使用下面的语句：

SELECT 
  t.*,
  FIRST_VALUE(t.sum_grade) OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
    RANGE BETWEEN 
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) first_grade
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

+-------+------+-----------+-------------+
| class | name | sum_grade | first_grade |
+-------+------+-----------+-------------+
| A     | Tim  |        16 |          16 |
| A     | Tom  |        15 |          16 |
| A     | Jim  |        15 |          16 |
| B     | Susy |        17 |          17 |
| B     | Lucy |        14 |          17 |
| B     | Jody |        13 |          17 |
+-------+------+-----------+-------------+
6 rows in set (0.00 sec)

在上面的语句中，注意这个子查询：

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)

主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 FIRST_VALUE(grade) 返回每行在其关联的分区内第一行中的成绩。

8.4 LAG()

LAG(expr[, offset[, default]])
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

expr

必需的。它可以是一个列名或者表达式。

offset

可选的。相对于当前行的偏移的行数。默认值为 1。

default

可选的。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL LAG() 函数返回来自当前行所在的分区内当前行之前的指定行之内的值。

LAG() 示例

使用 tax_revenue表演示

使用 MySQL LAG() 函数函数和下一季度的收益比较

下面的语句，在使用 MySQL LAG() 函数在每行中添加 next_quarter_revenue 列以比较当前季度和下一季度的收益：

SELECT
  *,
  LAG(revenue, 1) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) next_quarter_revenue
FROM tax_revenue;

+----+------+---------+---------+----------------------+
| id | year | quarter | revenue | next_quarter_revenue |
+----+------+---------+---------+----------------------+
|  4 | 2020 | 4       |    3924 |                 NULL |
|  3 | 2020 | 3       |    4203 |                 3924 |
|  2 | 2020 | 2       |    3678 |                 4203 |
|  1 | 2020 | 1       |    3515 |                 3678 |
|  8 | 2021 | 4       |    2901 |                 NULL |
|  7 | 2021 | 3       |    3602 |                 2901 |
|  6 | 2021 | 2       |    3293 |                 3602 |
|  5 | 2021 | 1       |    3102 |                 3293 |
+----+------+---------+---------+----------------------+
8 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

LAG(revenue, 1) OVER (
  PARTITION BY year
  ORDER BY quarter DESC
) next_quarter_revenue

在 OVER 子句中，

PARTITION BY year 将所有行按照年份进行分区
ORDER BY quarter DESC 将每个分区内的行按照季度逆序排列。
LAG(revenue, 1) 返回每行在其关联的分区内之前一行(1)中的收益(revenue)。

那么在 next_quarter_revenue 列中存放的就是当前行的下一个季度的收益。所以，每个分区的第一行中的 next_quarter_revenue 列的值为 null。

当然，您可以为 next_quarter_revenue 列中的 null 值指定要给默认值。下面的语句使用了 0 作为默认值：

SELECT
  *,
  LAG(revenue, 1, 0) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) next_quarter_revenue
FROM tax_revenue;

+----+------+---------+---------+----------------------+
| id | year | quarter | revenue | next_quarter_revenue |
+----+------+---------+---------+----------------------+
|  4 | 2020 | 4       |    3924 |                    0 |
|  3 | 2020 | 3       |    4203 |                 3924 |
|  2 | 2020 | 2       |    3678 |                 4203 |
|  1 | 2020 | 1       |    3515 |                 3678 |
|  8 | 2021 | 4       |    2901 |                    0 |
|  7 | 2021 | 3       |    3602 |                 2901 |
|  6 | 2021 | 2       |    3293 |                 3602 |
|  5 | 2021 | 1       |    3102 |                 3293 |
+----+------+---------+---------+----------------------+
8 rows in set (0.00 sec)

8.5 LAST_VALUE()

LAST_VALUE(expr)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

expr

必需的。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL LAST_VALUE() 函数从当前行关联的窗口框架的最后一行中返回评估的值。

LAST_VALUE() 示例

使用 student_grade 表演示

按照科目查看每个学生的成绩排名

要查看在每个科目中每个学生按照成绩从高到低的排序号和每个科目中的最好成绩，请使用如下语句：

SELECT 
  *,
  LAST_VALUE(grade) OVER (
    PARTITION BY subject
    ORDER BY grade DESC
    RANGE BETWEEN 
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) last_grade
FROM student_grade;

+----+------+-------+---------+-------+------------+
| id | name | class | subject | grade | last_grade |
+----+------+-------+---------+-------+------------+
|  5 | Tom  | A     | English |     8 |          6 |
| 12 | Susy | B     | English |     8 |          6 |
|  4 | Tim  | A     | English |     7 |          6 |
|  6 | Jim  | A     | English |     7 |          6 |
| 11 | Jody | B     | English |     7 |          6 |
| 10 | Lucy | B     | English |     6 |          6 |
|  1 | Tim  | A     | Math    |     9 |          6 |
|  9 | Susy | B     | Math    |     9 |          6 |
|  3 | Jim  | A     | Math    |     8 |          6 |
|  7 | Lucy | B     | Math    |     8 |          6 |
|  2 | Tom  | A     | Math    |     7 |          6 |
|  8 | Jody | B     | Math    |     6 |          6 |
+----+------+-------+---------+-------+------------+
12 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

LAST_VALUE(grade) OVER (
  PARTITION BY subject
  ORDER BY grade DESC
  RANGE BETWEEN 
    UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

在 OVER 子句中，

PARTITION BY subject 将按照学科进行分区
ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 定义了每个分区中与当前行相关的窗口框架，这里是从分区的第一行到最后一行。
LAST_VALUE(grade) 返回每行在其关联的分区的第一行的成绩（grade）。因为是按照 grade 逆序排序，于是每行中添加了一列显示最差的成绩。

按照班级查看每个学生的总成绩排名

要查看在每个班级中每个学生按照总成绩从高到底的排序号和每个班级的最好成绩，请使用下面的语句：

SELECT 
  t.*,
  LAST_VALUE(t.sum_grade) OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
    RANGE BETWEEN 
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) last_grade
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

+-------+------+-----------+------------+
| class | name | sum_grade | last_grade |
+-------+------+-----------+------------+
| A     | Tim  |        16 |         15 |
| A     | Tom  |        15 |         15 |
| A     | Jim  |        15 |         15 |
| B     | Susy |        17 |         13 |
| B     | Lucy |        14 |         13 |
| B     | Jody |        13 |         13 |
+-------+------+-----------+------------+
6 rows in set (0.00 sec)

在上面的语句中，注意这个子查询：

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)

主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 LAST_VALUE(grade) 返回每行在其关联的分区内最后一行中的成绩。

8.6 LEAD()

LEAD(expr[, offset[, default]])
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

expr

必需的。它可以是一个列名或者表达式。

offset

可选的。相对于当前行的偏移的行数。默认值为 1。

default

可选的。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL LEAD() 函数返回来自当前行所在的分区内当前行之后的指定行之内的行的值。

LEAD() 示例

使用 tax_revenue表演示

使用 MySQL LEAD() 函数函数和上一季度的收益比较

下面的语句，在使用 MySQL LEAD() 函数在每行中添加 last_quarter_revenue 列以比较当前季度和上一季度的收益：

SELECT
  *,
  LEAD(revenue, 1) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) last_quarter_revenue
FROM tax_revenue;

+----+------+---------+---------+----------------------+
| id | year | quarter | revenue | last_quarter_revenue |
+----+------+---------+---------+----------------------+
|  4 | 2020 | 4       |    3924 |                 4203 |
|  3 | 2020 | 3       |    4203 |                 3678 |
|  2 | 2020 | 2       |    3678 |                 3515 |
|  1 | 2020 | 1       |    3515 |                 NULL |
|  8 | 2021 | 4       |    2901 |                 3602 |
|  7 | 2021 | 3       |    3602 |                 3293 |
|  6 | 2021 | 2       |    3293 |                 3102 |
|  5 | 2021 | 1       |    3102 |                 NULL |
+----+------+---------+---------+----------------------+
8 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

LEAD(revenue, 1) OVER (
  PARTITION BY year
  ORDER BY quarter DESC
) last_quarter_revenue

在 OVER 子句中，

PARTITION BY year 将所有行按照年份进行分区
ORDER BY quarter DESC 将每个分区内的行按照季度逆序排列。
LEAD(revenue, 1) 返回每行在其关联的分区内之后一行(1)中的收益(revenue)。

那么在 last_quarter_revenue 列中存放的就是当前行的上一个季度的收益。所以，每个分区的最后一行中的 last_quarter_revenue 列的值为 null。

当然，您可以为 last_quarter_revenue 列中的 null 值指定要给默认值。下面的语句使用了 0 作为默认值：

SELECT
  *,
  LEAD(revenue, 1, 0) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) last_quarter_revenue
FROM tax_revenue;

+----+------+---------+---------+----------------------+
| id | year | quarter | revenue | last_quarter_revenue |
+----+------+---------+---------+----------------------+
|  4 | 2020 | 4       |    3924 |                 4203 |
|  3 | 2020 | 3       |    4203 |                 3678 |
|  2 | 2020 | 2       |    3678 |                 3515 |
|  1 | 2020 | 1       |    3515 |                    0 |
|  8 | 2021 | 4       |    2901 |                 3602 |
|  7 | 2021 | 3       |    3602 |                 3293 |
|  6 | 2021 | 2       |    3293 |                 3102 |
|  5 | 2021 | 1       |    3102 |                    0 |
+----+------+---------+---------+----------------------+
8 rows in set (0.00 sec)

8.7 NTH_VALUE()

NTH_VALUE(expr, n)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

NTH_VALUE(expr, 1) 等效于 first_value(expr)。

参数

expr

必需的。它可以是一个列名或者表达式。

n

必需的。指定行的编号。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL NTH_VALUE() 函数从当前行关联的窗口框架的指定的一行中返回评估的值。如果指定的行不存在，此函数将返回 NULL。

NTH_VALUE() 示例

使用 student_grade 表演示

按照科目查看每个学生的成绩排名

要查看在每个科目中每个学生按照成绩从高到低的排序号和每个科目中的最好成绩，请使用如下语句：

SELECT 
  *,
  NTH_VALUE(grade, 1) OVER (
    PARTITION BY subject
    ORDER BY grade DESC
    RANGE BETWEEN 
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) first_grade
FROM student_grade;

+----+------+-------+---------+-------+-------------+
| id | name | class | subject | grade | first_grade |
+----+------+-------+---------+-------+-------------+
|  5 | Tom  | A     | English |     8 |           8 |
| 12 | Susy | B     | English |     8 |           8 |
|  4 | Tim  | A     | English |     7 |           8 |
|  6 | Jim  | A     | English |     7 |           8 |
| 11 | Jody | B     | English |     7 |           8 |
| 10 | Lucy | B     | English |     6 |           8 |
|  1 | Tim  | A     | Math    |     9 |           9 |
|  9 | Susy | B     | Math    |     9 |           9 |
|  3 | Jim  | A     | Math    |     8 |           9 |
|  7 | Lucy | B     | Math    |     8 |           9 |
|  2 | Tom  | A     | Math    |     7 |           9 |
|  8 | Jody | B     | Math    |     6 |           9 |
+----+------+-------+---------+-------+-------------+
12 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

NTH_VALUE(grade, 1) OVER (
  PARTITION BY subject
  ORDER BY grade DESC
  RANGE BETWEEN 
    UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

在 OVER 子句中，

PARTITION BY subject 将按照学科进行分区
ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 定义了每个分区中与当前行相关的窗口框架，这里是从分区的第一行到最后一行。
NTH_VALUE(grade, 1) 返回每行在其关联的分区的第一行的成绩（grade）。因为是按照 grade 逆序排序，于是每行中添加了一列显示最好的成绩。

如果您想在每行中显示每个科目中最差的成绩，可以使用 NTH_VALUE(grade, 3) 替换上面语句中的 NTH_VALUE(grade, 1)。

按照班级查看每个学生的总成绩排名

要查看在每个班级中每个学生按照总成绩从高到底的排序号和每个班级的最好成绩，请使用下面的语句：

SELECT 
  t.*,
  NTH_VALUE(t.sum_grade, 1) OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
    RANGE BETWEEN 
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) first_grade
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

+-------+------+-----------+-------------+
| class | name | sum_grade | first_grade |
+-------+------+-----------+-------------+
| A     | Tim  |        16 |          16 |
| A     | Tom  |        15 |          16 |
| A     | Jim  |        15 |          16 |
| B     | Susy |        17 |          17 |
| B     | Lucy |        14 |          17 |
| B     | Jody |        13 |          17 |
+-------+------+-----------+-------------+
6 rows in set (0.00 sec)

在上面的语句中，注意这个子查询：

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)

主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 NTH_VALUE(t.sum_grade, 1) 返回每行在其关联的分区内第一行中的成绩。

8.8 NTILE()

NTILE(buckets)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

buckets

必需的。桶的数量。桶的数量最大为此分区内的行的数量。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL NTILE() 函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的排名桶，并返回当前行所在的桶的排名。

假设，您有 1 到 9 这 9 个数字，您使用 NTILE(3) 将他们按照升序分成 3 个桶，按照尽可能平均分配的原则，那么 1-3 的桶排名是 1， 4-6 的桶排名是 2， 7-9 的桶排名是 3。下面的语句展示了它：

SELECT
  x,
  NTILE(3) over (
    ORDER BY x
  ) "ntile"
FROM (
    SELECT 1 x
    UNION
    SELECT 2 x
    UNION
    SELECT 3 x
    UNION
    SELECT 4 x
    UNION
    SELECT 5 x
    UNION
    SELECT 6 x
    UNION
    SELECT 7 x
    UNION
    SELECT 8 x
    UNION
    SELECT 9 x
) t;

+---+-------+
| x | ntile |
+---+-------+
| 1 |     1 |
| 2 |     1 |
| 3 |     1 |
| 4 |     2 |
| 5 |     2 |
| 6 |     2 |
| 7 |     3 |
| 8 |     3 |
| 9 |     3 |
+---+-------+

NTILE() 示例

使用 tax_revenue表演示

使用 MySQL NTILE() 分成 2 个桶

下面的语句，在使用 MySQL NTILE() 函数将每年的收益按照升序分成 2 桶：

SELECT
  *,
  NTILE(2) OVER (
    PARTITION BY year
    ORDER BY revenue
  ) "ntile"
FROM tax_revenue;

+----+------+---------+---------+-------+
| id | year | quarter | revenue | NTILE |
+----+------+---------+---------+-------+
|  1 | 2020 | 1       |    3515 |     1 |
|  2 | 2020 | 2       |    3678 |     1 |
|  4 | 2020 | 4       |    3924 |     2 |
|  3 | 2020 | 3       |    4203 |     2 |
|  8 | 2021 | 4       |    2901 |     1 |
|  5 | 2021 | 1       |    3102 |     1 |
|  6 | 2021 | 2       |    3293 |     2 |
|  7 | 2021 | 3       |    3602 |     2 |
+----+------+---------+---------+-------+
8 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

NTILE(2) OVER (
  PARTITION BY year
  ORDER BY revenue
)

在 OVER 子句中，

PARTITION BY year 将所有行按照年份进行分区
ORDER BY revenue 将每个分区内的行按照收益升序排列
NTILE(2) 将每个分区的收益尽可能平均的分成 2 个桶。由于每年有 4 行，所以每个桶有 2 行。所以每年的前两行的桶排名为 1，后两行的桶排名为 2。

8.9 PERCENT_RANK()

PERCENT_RANK()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL PERCENT_RANK() 函数返回一个介于 0 和 1 (包含) 之间的数，表示当前行所在的分区内的相对排名，其计算公式为是 (rank() - 1) / (分区总行数 - 1)。

PERCENT_RANK() 示例

使用 student_grade 表演示

按照科目查看每个学生的成绩的相对排名

要查看在每个科目中每个学生按照成绩从高到低的相对排名，请使用如下语句：

SELECT *,
  rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "rank",
  PERCENT_RANK() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "percent_rank"
FROM student_grade;

+----+------+-------+---------+-------+------+--------------+
| id | name | class | subject | grade | rank | PERCENT_RANK |
+----+------+-------+---------+-------+------+--------------+
|  5 | Tom  | A     | English |     8 |    1 |            0 |
| 12 | Susy | B     | English |     8 |    1 |            0 |
|  4 | Tim  | A     | English |     7 |    3 |          0.4 |
|  6 | Jim  | A     | English |     7 |    3 |          0.4 |
| 11 | Jody | B     | English |     7 |    3 |          0.4 |
| 10 | Lucy | B     | English |     6 |    6 |            1 |
|  1 | Tim  | A     | Math    |     9 |    1 |            0 |
|  9 | Susy | B     | Math    |     9 |    1 |            0 |
|  3 | Jim  | A     | Math    |     8 |    3 |          0.4 |
|  7 | Lucy | B     | Math    |     8 |    3 |          0.4 |
|  2 | Tom  | A     | Math    |     7 |    5 |          0.8 |
|  8 | Jody | B     | Math    |     6 |    6 |            1 |
+----+------+-------+---------+-------+------+--------------+
12 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

PERCENT_RANK() OVER (
  PARTITION BY subject
  ORDER BY grade DESC
)

在 OVER 子句中，

PARTITION BY subject 将按照学科进行分区
ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
PERCENT_RANK() 返回每行在其关联的分区内的相对排名。

您从上面也可以看出 PERCENT_RANK() 与 RANK() 的不同。

按照班级查看每个学生的总成绩的相对排名

要查看在每个班级中每个学生按照总成绩从高到底的相对排名，请使用下面的语句：

SELECT t.*,
  PERCENT_RANK() OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
  ) "percent_rank"
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

+-------+------+-----------+--------------+
| class | name | sum_grade | PERCENT_RANK |
+-------+------+-----------+--------------+
| A     | Tim  |        16 |            0 |
| A     | Tom  |        15 |          0.5 |
| A     | Jim  |        15 |          0.5 |
| B     | Susy |        17 |            0 |
| B     | Lucy |        14 |          0.5 |
| B     | Jody |        13 |            1 |
+-------+------+-----------+--------------+
6 rows in set (0.01 sec)

在上面的语句中，注意这个子查询：

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)

主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 PERCENT_RANK() 返回每行在其关联的分区内的相对排名。

8.10 RANK()

RANK()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL RANK() 函数返回当前行所在的分区内的排名，从 1 开始，但有间隔。

RANK() 示例

使用 student_grade 表演示

按照科目查看每个学生的成绩排名

要查看在每个科目中每个学生按照成绩从高到低的排名，请使用如下语句：

SELECT *,
  RANK() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "rank",
  dense_rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "dense_rank"
FROM student_grade;

+----+------+-------+---------+-------+------+------------+
| id | name | class | subject | grade | RANK | dense_rank |
+----+------+-------+---------+-------+------+------------+
|  5 | Tom  | A     | English |     8 |    1 |          1 |
| 12 | Susy | B     | English |     8 |    1 |          1 |
|  4 | Tim  | A     | English |     7 |    3 |          2 |
|  6 | Jim  | A     | English |     7 |    3 |          2 |
| 11 | Jody | B     | English |     7 |    3 |          2 |
| 10 | Lucy | B     | English |     6 |    6 |          3 |
|  1 | Tim  | A     | Math    |     9 |    1 |          1 |
|  9 | Susy | B     | Math    |     9 |    1 |          1 |
|  3 | Jim  | A     | Math    |     8 |    3 |          2 |
|  7 | Lucy | B     | Math    |     8 |    3 |          2 |
|  2 | Tom  | A     | Math    |     7 |    5 |          3 |
|  8 | Jody | B     | Math    |     6 |    6 |          4 |
+----+------+-------+---------+-------+------+------------+
12 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

RANK() OVER (
  PARTITION BY subject
  ORDER BY grade DESC
)

在 OVER 子句中，

PARTITION BY subject 将按照学科进行分区
ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
RANK() 返回每行在其关联的分区内的排名。

您从上面也可以看出 RANK() 与 dense_rank() 的不同。

按照班级查看每个学生的总成绩排名

要查看在每个班级中每个学生按照总成绩从高到底的排名，请使用下面的语句：

SELECT t.*,
  RANK() OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
  ) "rank"
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

+-------+------+-----------+------+
| class | name | sum_grade | RANK |
+-------+------+-----------+------+
| A     | Tim  |        16 |    1 |
| A     | Tom  |        15 |    2 |
| A     | Jim  |        15 |    2 |
| B     | Susy |        17 |    1 |
| B     | Lucy |        14 |    2 |
| B     | Jody |        13 |    3 |
+-------+------+-----------+------+
6 rows in set (0.01 sec)

在上面的语句中，注意这个子查询：

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)

主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 RANK() 返回每行在其关联的分区内的排名。

8.11 ROW_NUMBER()

ROW_NUMBER()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

返回值

MySQL ROW_NUMBER() 函数返回当前行所在的分区内的序号，从 1 开始。

ROW_NUMBER() 示例

使用 student_grade 表演示

按照科目查看每个学生的成绩的排序号

要查看在每个科目中每个学生按照成绩从高到低的排序号，请使用如下语句：

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "row_number"
FROM student_grade;

+----+------+-------+---------+-------+------------+
| id | name | class | subject | grade | ROW_NUMBER |
+----+------+-------+---------+-------+------------+
|  5 | Tom  | A     | English |     8 |          1 |
| 12 | Susy | B     | English |     8 |          2 |
|  4 | Tim  | A     | English |     7 |          3 |
|  6 | Jim  | A     | English |     7 |          4 |
| 11 | Jody | B     | English |     7 |          5 |
| 10 | Lucy | B     | English |     6 |          6 |
|  1 | Tim  | A     | Math    |     9 |          1 |
|  9 | Susy | B     | Math    |     9 |          2 |
|  3 | Jim  | A     | Math    |     8 |          3 |
|  7 | Lucy | B     | Math    |     8 |          4 |
|  2 | Tom  | A     | Math    |     7 |          5 |
|  8 | Jody | B     | Math    |     6 |          6 |
+----+------+-------+---------+-------+------------+
12 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

ROW_NUMBER() OVER (
  PARTITION BY subject
  ORDER BY grade DESC
)

在 OVER 子句中，

PARTITION BY subject 将按照学科进行分区
ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
ROW_NUMBER() 返回每行在其关联的分区内的序号。

按照班级查看每个学生的总成绩的排序号

要查看在每个班级中每个学生按照总成绩从高到底的排序号，请使用下面的语句：

SELECT t.*,
  ROW_NUMBER() OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
  ) "row_number"
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

+-------+------+-----------+------------+
| class | name | sum_grade | ROW_NUMBER |
+-------+------+-----------+------------+
| A     | Tim  |        16 |          1 |
| A     | Tom  |        15 |          2 |
| A     | Jim  |        15 |          3 |
| B     | Susy |        17 |          1 |
| B     | Lucy |        14 |          2 |
| B     | Jody |        13 |          3 |
+-------+------+-----------+------------+
6 rows in set (0.00 sec)

在上面的语句中，注意这个子查询：

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)

主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 ROW_NUMBER() 返回每行在其关联的分区内的排序号。