MYSQL常用函数详解

详解"/>

MYSQL常用函数详解

今天查缺陷发现同事写的一个MYSQL的SQL中用到函数JSON_CONTAINS，我当时第一反应是这个函数是Mysql8新加的么？原来小丑尽是我自己。

有必要巩固一下Mysql函数知识，并记录一下。（如果对您也有用，麻烦您动动发财的手点个赞咯，谢谢了！）

目录

一、字符串函数

1.1 CONCAT函数

1.2 SUBSTRING函数

1.3 REPLACE函数

1.4 UPPER函数

1.5 LENGTH函数

1.6 TRIM函数

1.7 REVERSE函数

1.8 CONCAT_WS函数

1.9 更多及实战示例

二、数字函数

2.1 ABS函数

2.2 ROUND函数

2.3 SUM函数

2.4 AVG函数

2.5 MAX函数

2.6 MIN函数

2.7 COUNT函数

2.8 FLOOR函数函数

2.9 更多及实战示例

三、日期和时间函数

3.1 NOW函数

3.2 CURDATE函数

3.3 CURTIME函数

3.4 UNIX_TIMESTAMP函数

3.5 FROM_DAYS函数

3.6 UNIX_TIMESTAMP函数

3.7 DATE_FORMAT函数

3.8 ADDDATE函数

3.9 更多及实战示例

四、正则表达式函数

4.1 REGEXP操作符

4.2 NOT REGEXP操作符

4.3 RLIKE操作符

4.4 NOT RLIKE操作符

4.5 REGEXP_LIKE函数

4.6 REGEXP_REPLACE函数

4.7 REGEXP_INSTR函数

4.8 REGEXP_SUBSTR函数

4.9 更多及实战示例

五、JSON函数

5.1 SON_EXTRACT函数

5.2 JSON_UNQUOTE函数

5.3 JSON_KEYS函数

5.4 JSON_ARRAY函数

5.5 JSON_OBJECT函数

5.6 JSON_DEPTH函数

5.7 JSON_LENGTH函数

5.8 JSON_CONTAINS函数

5.9 更多及实战示例

---

一、字符串函数

MySQL提供了许多字符串函数，用于处理和操作字符串数据。以下是一些常用的MySQL字符串函数。

1.1 CONCAT函数

MySQL CONCAT() 函数用来按顺序拼接指定的 2 个或多个字符串，并返回拼接后的字符串。如果您需要使用分隔符连接多个字符串，请使用 CONCAT_WS() 函数。如果拼接的参数中一个为 NULL, 则返回 NULL。

语法：

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

参数说明：

• string1, string2, ..., stringN 必需的，至少应指定一个字符串；拼接中若有一个为 NULL, 则返回 NULL；

返回值：

• 返回拼接后的字符串。
• 只有一个参数，返回参数本身。
• 若有一个参数为NULL，返回NULL。
• 无参数时报错：ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT'。

1.2 SUBSTRING函数

MySQL SUBSTRING() 函数从一个字符串中返回一个从指定位置开始的指定长度的子字符串。 SUBSTR() 等同于 SUBSTRING() 函数。

语法：

有4种形式：
SUBSTRING(str, pos)
SUBSTRING(str FROM pos)
SUBSTRING(str, pos, len)
SUBSTRING(str FROM pos FOR len)

参数说明：

• str：必需的，从中提取子字符串的原字符串。
• pos：必需的，开始提取的位置。它可以是正数或者负数。如果是正数，则从字符串的开头为起始位置确定开始提取的位置提取子字符串。如果是负数，则从字符串的结尾为起始位置确定开始提取的位置提取子字符串。
• len：可选的。子字符串的长度（字符数）。如果不指定，则提取到原字符串的结尾。

返回值：

• 当 pos 为 0 或者超过原字符串的长度时， 返回空串 ''。
• 当 pos 为负数时，从字符串尾部向前计数。
• 当 pos + len 超过原字符串的长度时，从pos位置提取到原字符串的结尾。
• 当参数为 NULL 时， 返回 NULL。

1.3 REPLACE函数

MySQL REPLACE() 函数将字符串中出现的所有子字符串替换为新的子字符串。 REPLACE() 函数是基于字符的替换，并且替换字符串时是区分大小写的。

语法：

REPLACE(str, from_str, to_str)

参数说明：

• str：必需的，原字符串。
• from_str：必需的，被替换的子字符串。
• to_str：必需的，用来替换的新子字符串。

返回值：

• 返回 str 中的所有 from_str 被 to_str 替换后的字符串。
• 当任意一个参数为 NULL 时， REPLACE() 函数将返回 NULL。

1.4 UPPER函数

MySQL UPPER() 函数将指定字符串转为大写并返回。 UCASE() 函数是 UPPER() 函数的别名。

如果想将字符串转换为小写，请使用 LCASE() 或者 LOWER() 函数。

语法：

UPPER(str)

参数说明：

• str：必需的。 被处理的字符串。

返回值：

• 定字符串的大写形式。
• 当参数 str 为 NULL 时， UPPER() 函数将返回 NULL。

1.5 LENGTH函数

MySQL LENGTH() 函数返回指定字符串的以字节为单位的长度。如果想返回字符的数量，请使用 CHAR_LENGTH() 函数或者 CHARACTER_LENGTH() 函数。

LENGTH() 函数与 OCTET_LENGTH() 函数相同。

语法：

LENGTH(string)

参数说明：

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

返回值：

• 返回指定字符串的以字节为单位的长度，即字节的数量。
• 当参数为 NULL 时， 返回 NULL。

1.6 TRIM函数

MySQL TRIM() 函数从指定字符串的开头和结尾删除由指定字符组成的最长字符串。如果只想删除字符串的前导空格，请使用 LTRIM() 函数；如果只想删除字符串的尾随空格，请使用 RTRIM() 函数。

语法：

TRIM(str)
TRIM(remove_str FROM str)
TRIM({BOTH | LEADING | TRAILING} remove_str FROM str)

参数说明：

• str：必需的。需要处理的字符串。
• remove_str：可选的。需要删除的前缀和后缀字符串。如果不指定这个 remove_str，则默认值为空格。
• BOTH：代表同时删除前导和尾随的字符。
• LEADING：代表只删除前导字符。
• TRAILING：代表只删除尾随的字符。
• 如果不指定 {BOTH | LEADING | TRAILING}，则默认值为 BOTH。

返回值：

• 从指定字符串的开头、结尾或者两端删除由指定字符组成的最长字符串。

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

1.7 REVERSE函数

MySQL REVERSE() 函数返回反转后的字符串。 REVERSE() 函数基于字符操作而不是基于字节。

语法：

REVERSE(str)

参数说明：

• str：必需的。需要反转的字符串。

返回值：

• 返回反转后的字符串。
• 当参数为 NULL 时， REVERSE() 函数将返回 NULL。

1.8 CONCAT_WS函数

MySQL CONCAT_WS() 函数用来通过指定的分隔符按顺序拼接指定的 2 个或多个字符串，并返回拼接后的字符串。

如果拼接的参数中一个为 NULL, CONCAT_WS() 函数会忽略 NULL 值。

如果只有一个参与拼接的字符串参数，则原样返回此字符串。

语法：

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

参数说明：

• separator：分隔符，必须的。您可以使用单个字符，也可以使用字符串。
• string1, string2, ..., stringN：必需的，至少应指定一个字符串；如果拼接的参数为 NULL, 则会被忽略；

返回值：

• 返回使用分隔符连接后的多个字符串。
• 如果 separator 为 NULL，则返回 NULL。
• 如果 string1, string2, ..., stringN 中有 NULL，则会被 CONCAT_WS() 函数忽略。
• 如果没有指定 string1, string2, ..., stringN，MySQL 将会报错： ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT_WS'。

1.9 更多及实战示例

以上只是一些常用的MySQL字符串函数，还有许多其他函数可用于处理字符串数据。更多字符串函数详解和实战见：

二、数字函数

MySQL提供了许多数字函数，可以用于执行各种数学计算和操作。以下是一些常用的MySQL数字函数。

2.1 ABS函数

MySQL ABS() 函数返回指定数字的绝对值。

语法：

ABS(number)

参数说明：

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

返回值：

• 如果参数 number 为字符串，按照如下规则尝试转为数字：
  • 若数字开头，则将开头的数字部分转为数字。
  • 若不能转为数字，则按照 0 处理
• 如果参数 number 为 NULL，返回 NULL。

2.2 ROUND函数

MySQL ROUND() 函数将数字四舍五入到指定的小数位数。如果您只需要按位数截取小数位，请使用 TRUNCATE() 函数。如果需要返回数字的整数部分，请使用 FLOOR(), CEIL(), CEILING() 函数。

语法：

ROUND(x, d)

参数说明：

• x：必需的。 被处理的数字。
• d：必需的。 需要保留的小数位数。

返回值：

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

2.3 SUM函数

MySQL SUM() 函数计算所有指定的非 NULL 值的总和并返回。

语法：

SUM(expr)

参数说明：

• expr：一个需要统计数量的表达式。它可以是一个列名，也可以是一个表达式。

返回值：

• 返回所有指定的非 NULL 值的总和。
• 如果没有匹配的行，SUM() 函数返回 NULL。

2.4 AVG函数

MySQL AVG() 函数计算并返回表达式的平均值。

语法：

AVG(expr)
AVG(DISTINCT expr)

参数说明：

• expr：一个用于聚合运算的表达式。它可以是一个列名，也可以是一个表达式。

返回值：

• 返回 expr 中唯一值的平均值
• 忽略 NULL 值。
• 如果没有匹配的行，返回 NULL。

2.5 MAX函数

MySQL MAX() 函数返回表达式代表的所有值中的最大值。如果您需要获取一个表达式中的最小值，请使用 MIN() 函数。

语法：

MAX(expr)

参数说明：

• expr 一个用于聚合运算的表达式。它可以是一个列名，也可以是一个表达式。

返回值：

• 返回表达式代表的所有值中的最大值。
• 如果没有匹配的行，返回 NULL。

2.6 MIN函数

MySQL MIN() 函数返回表达式代表的所有值中的最小值。如果您需要获取一个表达式中的最大值，请使用 MAX() 函数。

语法：

MIN(expr)

参数说明：

• expr：一个用于聚合运算的表达式。它可以是一个列名，也可以是一个表达式。

返回值：

• 返回表达式代表的所有值中的最小值。
• 如果没有匹配的行，返回 NULL。

2.7 COUNT函数

MySQL COUNT() 函数用于统计表达式代表的所有值的中的非 NULL 值的数量。

语法：

COUNT(expr)

参数说明：

• expr：一个需要统计数量的表达式。它可以是一个列名，也可以是一个表达式。

返回值：

• 返回 SELECT 语句返回的所有行中的表达式代表的所有值的中的非 NULL 值的数量。

2.8 FLOOR函数函数

MySQL FLOOR() 函数返回小于或等于指定数字的最大整数值。
如果您想要返回大于或等于指定数字的最小整数值，请使用 CEIL() 或者 CEILING() 函数。
如果您想要将数字四舍五入到指定的小数位数，请使用 ROUND() 函数。
如果您想要按位数截取小数位，请使用 TRUNCATE() 函数。

语法：

FLOOR(number)

参数说明：

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

返回值：

• 返回小于或等于指定数字的最大整数值。
• 如果参数 number 为 NULL，FLOOR() 函数将会返回 NULL。

2.9 更多及实战示例

以上这些函数只是MySQL中可用的一小部分数字函数示例。更多数字函数详解和实战见：

三、日期和时间函数

MySQL提供了许多日期和时间函数，可以用于处理和操作日期和时间数据。以下是一些常用的MySQL日期和时间函数。

3.1 NOW函数

MySQL NOW() 函数按 YYYY-MM-DD hh:mm:ss 格式返回当前时间和日期。
与 SYSDATE() 函数不同，NOW() 函数返回的是所在的语句、函数、存储过程开始执行的时间，并不一定是执行的 NOW() 函数时确切的系统时间。

语法：

NOW()

示例：

说明：

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

3.2 CURDATE函数

MySQL CURDATE() 函数按 YYYY-MM-DD 或 YYYYMMDD 格式返回系统的当前日期。
CURDATE() 函数与 CURRENT_DATE() 函数完全相同。

语法：

CURDATE()

示例：

说明：

• CURDATE() + 0 的结果为 YYYYMMDD 格式。NOW() + N 意味着当前日期加上 N 天。

3.3 CURTIME函数

MySQL CURTIME() 函数按 hh:mm:ss 格式返回系统的当前时间。
CURTIME() 函数与 CURRENT_TIME() 函数完全相同。

语法：

CURTIME()

示例：

说明： 

• CURTIME() + 0 的结果为 hhmmss 格式。CURTIME() + N 意味着当前时间加上 N 秒。

3.4 UNIX_TIMESTAMP函数

MySQL UNIX_TIMESTAMP() 函数将指定的日期/日期时间转为 UNIX 时间戳值。 UNIX 时间戳值是距离 UTC 时间 1970-01-01 00:00:00 的秒数。

语法：

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date_or_datetime)

参数说明：

• date_or_datetime：可选的。默认值为 NOW()。

返回值：

• 将指定的日期/日期时间转为距离 UTC 时间 1970-01-01 00:00:00 的秒数。
• 如果参数为 NULL，UNIX_TIMESTAMP() 函数将返回 NULL。

3.5 FROM_DAYS函数

MySQL FROM_DAYS() 函数将指定的天数转为日期并返回。FROM_DAYS() 函数与 TO_DAYS() 函数是相反的。

语法：

FROM_DAYS(days)

参数说明：

• days：必需的。一个表示天数的整数值。

返回值：

• 返回一个由指定天数表示的日期。
• 如果参数为 NULL，FROM_DAYS() 函数将返回 NULL。

3.6 UNIX_TIMESTAMP函数

MySQL UNIX_TIMESTAMP() 函数将指定的日期/日期时间转为 UNIX 时间戳值。 UNIX 时间戳值是距离 UTC 时间 1970-01-01 00:00:00 的秒数。

语法：

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date_or_datetime)

参数说明：

• date_or_datetime：可选的。默认值为 NOW()。

返回值：

• UNIX 时间戳值。

3.7 DATE_FORMAT函数

MySQL DATE_FORMAT() 函数按照指定的格式格式化日期时间。

语法：

DATE_FORMAT(date, format)

参数说明：

• date：必需的。需要格式化的日期。
• format：必需的。格式化模式字符串。格式化字符串可以包含以下占位符和格式：
  • %Y：四位数的年份，例如2023
  • %y：两位数的年份，例如23
  • %m：两位数的月份，例如01到12
  • %c：月份，例如1到12
  • %d：两位数的日期，例如01到31
  • %e：日期，例如1到31
  • %H：24小时制的小时数，例如00到23
  • %h：12小时制的小时数，例如01到12
  • %i：分钟数，例如00到59
  • %s：秒数，例如00到59
  • %p：AM或PM
  • %a：表示星期几的缩写
  • %W：星期几的完整名称，例如Sunday
  • %w：星期几的数字表示，例如0到6，其中0表示星期日
  • %b：月份的完整名称，例如January
  • %B：月份的完整名称，例如January
  • %D：日期格式化的字符串，例如01/23/2023
  • %M：月份的缩写名称，例如Jan
  • %S：年份的缩写名称，例如23
  • %n：换行符
  • %t：制表符
  • %r：时间格式化的字符串，例如08:34:56 PM
  • %T：时间格式化的字符串，例如08:34:56
  • %f：微秒数
  • %F：日期格式化的字符串，例如January 23, 2023
  • %Z：时区名称（如果未定义，则为空）
  • %z：时区偏移量（如果未定义，则为空）

返回值：

• 格式化后的字符串。

3.8 ADDDATE函数

MySQL ADDDATE() 函数在指定的日期/时间上加上指定到时间间隔加并返回新的日期/时间。

语法：

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

参数说明：

• date：必需的。需要操作的日期。
• days：必需的。在 date 上加上的天数。
• value：必需的。时间/日期间隔。正数和负数都是允许的。
• unit：必需的。时间/日期间隔的单位。有：
  • MICROSECOND：微秒
  • SECOND：秒
  • MINUTE：分钟
  • HOUR：小时
  • DAY：天
  • WEEK：周
  • MONTH：月
  • QUARTER：季度
  • YEAR：年
  • 更多见3.9连接

返回值：

• 如果 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）
• 其他情况下返回字符串。

3.9 更多及实战示例

这些函数只是MySQL中可用的一小部分日期和时间函数示例。更多日期时间函数详解和实战见：

四、正则表达式函数

MySQL支持正则表达式，并提供了几个函数和操作符来使用它们。以下是一些常用的MySQL正则表达式函数和操作符。

4.1 REGEXP操作符

在 MySQL 中， REGEXP 操作符检查一个字符串和一个正则表达式是否匹配。
REGEXP 操作符等同于 RLIKE 操作符和 REGEXP_LIKE() 函数。REGEXP 不区分大小写。
NOT REGEXP 操作符是 REGEXP 操作符的否定操作。

语法：

str REGEXP regexp

参数说明：

• str：必需的。一个字符串。
• regexp：必需的。正则表达式。

返回值：

• 若字符串 str 和正则表达式 regexp 匹配，返回 1，否则返回 0。
• 若任意一个参数为 NULL，返回 NULL。

4.2 NOT REGEXP操作符

在 MySQL 中， NOT REGEXP 操作符检查一个字符串和一个正则表达式是否不匹配。
NOT REGEXP 操作符是 REGEXP 操作符的否定操作。NOT REGEXP 不区分大小写。

语法：

str NOT REGEXP regexp

参数说明：

• str：必需的。一个字符串。
• regexp：必需的。正则表达式。

返回值：

• 若字符串 str 和正则表达式 regexp 不匹配，返回 1，否则返回 0。
• 若任意一个参数为 NULL，返回 NULL。

4.3 RLIKE操作符

在 MySQL 中， RLIKE 操作符检查一个字符串和一个正则表达式是否匹配。
RLIKE 操作符等同于 REGEXP 操作符和 RLIKE_LIKE() 函数。RLIKE 不区分大小写。

语法：

str RLIKE regexp

参数说明：

• str：必需的。一个字符串。
• regexp：必需的。正则表达式。

返回值：

• 若字符串 str 和正则表达式 regexp 匹配，返回 1，返回 0。
• 若任意一个参数为 NULL，返回NULL。

4.4 NOT RLIKE操作符

在 MySQL 中， NOT RLIKE 操作符检查一个字符串是否和一个正则表达式不匹配。
NOT RLIKE 操作符是 RLIKE 操作符的否定操作。

语法：

str NOT RLIKE regexp

参数说明：

• str：必需的。一个字符串。
• regexp：必需的。正则表达式。

返回值：

• 若字符串 str 和正则表达式 regexp 不匹配，返回 1，否则返回 0。
• 若任意一个参数为 NULL，NULL。

4.5 REGEXP_LIKE函数

MySQL REGEXP_LIKE() 函数检查一个字符串是否和一个正则表达式是否匹配。
REGEXP 和 RLIKE 操作符等同于 REGEXP_LIKE() 函数。
默认情况下， REGEXP_LIKE() 函数执行不区分大小写的匹配。

语法：

REGEXP_LIKE(str, regexp)
REGEXP_LIKE(str, regexp, mode)

参数说明：

• str：必需的。一个字符串。
• regexp：必需的。正则表达式。
• mode：可选的。执行匹配采用的模式。可使用以下其中一个或多个：
  • c: 区分大小写
  • i: 不区分大小写
  • m: 多行匹配模式
  • n: 点 . 可以匹配行结束
  • u: 仅限 unix 行结尾
• 如果匹配模式存在矛盾的选项，则以最右边的为准。

返回值：

• 若字符串 str 和正则表达式 regexp 匹配，返回 1，否则返回 0。
• 若 str 或者 regexp 为 NULL，返回 NULL。

4.6 REGEXP_REPLACE函数

MySQL REGEXP_REPLACE() 函数在一个字符串中使用新内容替换一个和指定的正则表达式匹配的内容。
默认情况下， REGEXP_REPLACE() 函数执行不区分大小写的匹配。

语法：

REGEXP_REPLACE(str, regexp, replacement)
REGEXP_REPLACE(str, regexp, replacement, position)
REGEXP_REPLACE(str, regexp, replacement, position, occurrence)
REGEXP_REPLACE(str, regexp, replacement, position, occurrence, mode)

参数说明：

• str：必需的。一个字符串。
• egexp：必需的。正则表达式。
• replacement：必需的。用来替换的字符串。
• position：可选的。开始搜索的起始位置。默认为从头开始。
• occurrence：可选的。替换第几次匹配的内容。默认为替换所有匹配的内容。
• mode：可选的。执行匹配采用的模式。可使用以下其中一个或多个：
  • c: 区分大小写
  • i: 不区分大小写
  • m: 多行匹配模式
  • n: 点 . 可以匹配行结束
  • u: 仅限 unix 行结尾
• 如果匹配模式存在矛盾的选项，则以最右边的为准。

返回值：

• 在 str 中使用 replacement 替换和正则表达式 regexp 匹配的内容，并返回替换后的结果。
• 若str, regexp 或者 replacement 为 NULL，返回 NULL。

4.7 REGEXP_INSTR函数

MySQL REGEXP_INSTR() 函数从一个字符串中搜索和指定模式匹配的子串并返回的子串的索引。
默认情况下， REGEXP_INSTR() 函数执行不区分大小写的匹配。
语法：

REGEXP_INSTR(str, regexp)
REGEXP_INSTR(str, regexp, position)
REGEXP_INSTR(str, regexp, position, occurrence)
REGEXP_INSTR(str, regexp, position, occurrence, return_option)
REGEXP_INSTR(str, regexp, position, occurrence, return_option, mode)

参数说明：

• str：必需的。一个字符串。
• regexp：必需的。正则表达式。
• position：可选的。开始搜索的起始位置。默认为 1。
• occurrence：可选的。第几次匹配的内容。默认为 1。
• return_option：可选的。指定返回哪一种位置索引。如果为 0，返回匹配的子串的第一个字符的位置索引；如果为 1，返回匹配的子串的后面的位置索引。默认为 0.
• mode：可选的。执行匹配采用的模式。可使用以下其中一个或多个：
  • c: 区分大小写
  • i: 不区分大小写
  • m: 多行匹配模式
  • n: 点 . 可以匹配行结束
  • u: 仅限 unix 行结尾
• 如果匹配模式存在矛盾的选项，则以最右边的为准。

返回值：

• 从一个字符串 str 中搜索和指定模式 regexp 匹配的子串并返回的子串的索引。如果没有匹配到，则返回 NULL。
• 若str 或者 regexp 为 NULL，返回 NULL。

4.8 REGEXP_SUBSTR函数

MySQL REGEXP_SUBSTR() 函数从一个字符串获取和指定模式匹配的子串并返回。
默认情况下， REGEXP_SUBSTR() 函数执行不区分大小写的匹配。

语法：

REGEXP_SUBSTR(str, regexp)
REGEXP_SUBSTR(str, regexp, position)
REGEXP_SUBSTR(str, regexp, position, occurrence)
REGEXP_SUBSTR(str, regexp, position, occurrence, mode)

参数说明：

• str：必需的。一个字符串。
• regexp：必需的。正则表达式。
• position：可选的。开始搜索的起始位置。默认为 1。
• occurrence：可选的。第几次匹配的内容。默认为 1。
• mode：可选的。执行匹配采用的模式。。可使用以下其中一个或多个：
  • c: 区分大小写
  • i: 不区分大小写
  • m: 多行匹配模式
  • n: 点 . 可以匹配行结束
  • u: 仅限 unix 行结尾
• 如果匹配模式存在矛盾的选项，则以最右边的为准。

返回值：

• 在 str 中搜索和正则表达式 regexp 匹配的子字符串并返回。如果没有匹配到，则返回 NULL。
• 若 str 或者 regexp 为 NULL，返回 NULL。

4.9 更多及实战示例

在MySQL中，正则表达式使用的是POSIX风格的正则表达式语法。更多正则表达式函数详解和实战见：MYSQL正则表达式函数详解和实战-CSDN博客正则表达式是一种方便的方式来匹配一系列的字符串，其能够将复杂的匹配表达式转换为一种简单的匹配模式，并且能够支持大多数的搜索关键字。因此，使用正则表达式来优化 MySQL 查询可以减小搜索范围的方式来实现。虽然MySQL正则表达式非常强大，但在处理大量数据时可能会影响性能。在某种场景下不推荐使用正则表达式。MySQL支持正则表达式，并提供了几个函数和操作符来使用它们。以下是一些常用的MySQL正则表达式函数和操作符。

五、JSON函数

MySQL提供了几个JSON函数来处理JSON数据。以下是一些常用的MySQL JSON函数。

5.1 SON_EXTRACT函数

语法：

JSON_EXTRACT(json, path, ...)

参数说明：

• json：必需的。一个 JSON 文档。
• path：必需的。您应该至少指定一个路径表达式。

返回值：

• 返回JSON 文档中由路径表达式匹配的所有的值。如果路径表达式匹配了一个值，则返回该值，如果路径表达式匹配了多个值，则返回一个包含了所有值的数组。
• 返回NULL情况：
  • 如果 JSON 文档中不存在指定的路径。
  • 如果任意一个参数为 NULL。
• 返回错误情况：
  • 参数 json 不是有效的 JSON 文档。（可用 JSON_VALID() 验证 JSON 文档的有效性）
  • 参数 path 不是有效的路径表达式。

5.2 JSON_UNQUOTE函数

MySQL JSON_UNQUOTE() 函数取消双引号引用 JSON 值，并将结果作为字符串返回。

语法：

JSON_UNQUOTE(json_val)

参数说明：

• json_val：必需的。一个字符串。

返回值：

• 取消双引号引用 JSON 值，并将结果作为字符串返回。
• 若参数为 NULL，JSON_UNQUOTE() 函数返回 NULL。
• 若值以双引号开头和结尾，但不是有效的 JSON 字符串文本，MySQL 将返回类似的错误： ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_unquote: xxx.。

能识别有转义字符：

• \"：双引号 "
• \b：退格字符
• \f：换页符
• \n：换行符
• \r：回车符
• \t：制表符
• \\：反斜杠 \
• \uXXXX：Unicode 值 XXXX 的 UTF-8 字节

5.3 JSON_KEYS函数

MySQL JSON_KEYS() 函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。

语法：

JSON_KEYS(json)
JSON_KEYS(json, path)

参数说明：

• json：必需的。一个 JSON 对象文档。
• path：可选的。路径表达式。

返回值：

• 返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。
• 若指定了路径表达式，则返回路径表达式匹配的 JSON 对象中的最上层的成员组成的数组。
• 返回NULL情况：
  • 未指定路径，且 JSON 文档不是一个 JSON 对象。
  • 指定了路径，且路径匹配的 JSON 值不是 JSON 对象。
  • 任意参数为 NULL。
• 返回错误情况：
  • 若参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
  • 若参数 path 不是有效的路径表达式, MySQL 将会给出错误。

5.4 JSON_ARRAY函数

MySQL JSON_ARRAY() 函数返回一个包含了所有参数的 JSON 数组。

语法：

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

参数说明：

• value1[, value2[, ...]]：可选的。一些值，他们将被放在 JSON 数组中。

返回值：

• 评估参数中的所有的值，并返回一个包含了所有参数的 JSON 数组。
• 可能有一些转换发生：
  • TRUE 被转换为 true
  • FALSE 被转换为 false
  • NULL 被转换为 null
  • 日期，时间，日期时间 被转换为 字符串

5.5 JSON_OBJECT函数

MySQL JSON_OBJECT() 函数返回一个包含了由参数指定的所有键值对的 JSON 对象。

语法：

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

参数说明：

• key：必需的。对象中的键。
• value：必需的。对象中的 key 的值。

返回值：

• 返回一个包含了所有键值对的 JSON 对象。
  • key 转为字符串类型。为了保证程序的稳定性，我们一般使用字符串类型的 key.
• 错误情况：
  • key 是 NULL， 错误： ERROR 3158 (22032): JSON documents may not contain NULL member names。
  • 奇数个参数， 错误： ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'。

5.6 JSON_DEPTH函数

MySQL JSON_DEPTH() 函数返回一个 JSON 文档的最大深度。

语法：

JSON_DEPTH(json)

参数说明：

• json：必需的。一个 JSON 文档。

返回值：

• 返回一个 JSON 文档的最大深度。
• 按如下规则计算深度：
  • 一个空的数组、空的对象或者纯值的深度是 1。
  • 一个仅包含深度为 1 的元素的数组的深度是 2。
  • 一个所有成员的值的深度为 1 的对象的深度是 2。
  • 除此之外的其他 JSON 文档的深度都大于 2.
• 如果参数为 NULL，此函数将返回 NULL。
• 错误情况：
  • 参数不是有效的 JSON 文档。（可以使用 JSON_VALID() 验证 JSON 文档的有效性）

5.7 JSON_LENGTH函数

MySQL JSON_LENGTH() 函数返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度。

语法：

JSON_LENGTH(json)
JSON_LENGTH(json, path)

参数说明：

• json：必需的。一个 JSON 文档。
• path：可选的。一个路径表达式。

返回值：

• 若指定了 path, 返回 JSON 文档中由路径指定的值的长度，否则返回 JSON 文档的长度。
• 按照如下规则计算 JSON 文档的长度:
  • 纯值的长度是 1。比如， 1, '"x"', true, false, null 的长度都是 1。
  • 数组的长度是数组元素的数量。 比如， [1, 2] 的长度是 2。
  • 对象的长度是对象成员的数量。 比如， {"x": 1} 的长度是 1。
  • 内嵌的数组或对象不参与计算长度。 比如， {"x": [1, 2]} 的长度是 1。
  • MySQL 8之后（本次测试版本是8.0.32），参数path中包含 * （如'$.*'），返回节点长度最大的值；
• 返回 NULL情况：
  • 如果 JSON 文档中不存在指定的路径。
  • 如果任意一个参数为 NULL。
• 返回错误情况：（本地mysql8版本是8.0.32，mysq5版本是5.7.36）
  • 参数 json 不是有效的 JSON 文档。
  • 参数 path 不是有效的路径表达式。
  • MySQL 8之前，参数 path 中包含 * 或 **。（MySQL说明：具体哪个版本没有逐一验证）
  • MySQL 8，参数中path中包含 **（如'$.**'）。

5.8 JSON_CONTAINS函数

MySQL JSON_CONTAINS() 函数检查一个 JSON 文档中是否包含另一个 JSON 文档。
如果您需要检查 JSON 文档中指定的路径下的是否存在数据，请使用 JSON_CONTAINS_PATH() 函数。

语法：

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 文档。
• 返回 NULL情况：
  • JSON 文档中不存在指定的路径。
  • 任意一个参数为 NULL。
• 返回错误情况：
  • json 不是有效的 JSON 文档。
  • path 不是有效的路径表达式。

5.9 更多及实战示例

这些是MySQL中一些常用的JSON函数，可以帮助您处理和操作JSON数据。请注意，在使用这些函数之前，您需要确保已启用了MySQL的JSON扩展。多JSON函数详解和实战见：

PS：总有忙不完的工作、总有理不清的烦脑、总有数不尽的应酬、总有调不好的心情。去创造快乐吧，努力学习起来吧，那怕快乐只有那短暂时间。

在这场人生闯关游戏中，我又顺利闯了一关，今跟大家分享这几天的愉悦：10月17日成功获得Project Management Professional认证（简称PMP认证）。（若有想了解的同鞋私信留言，必将所知与您分享）