Mysql 函数大全 mysql常用函数大全

MYSQL 内置函数整理

MySQL数据库提供了很多函数包括：

• 数学函数；
• 字符串函数；
• 日期和时间函数；
• 条件判断函数；
• 系统信息函数；
• 加密函数；
• 格式化函数；

一、数学函数

数学函数主要用于处理数字，包括整型、浮点数等。

函数	作用
ABS(x)	返回x的绝对值　　 SELECT ABS(-1) -- 返回1
CEIL(x),CEILING(x)	返回大于或等于x的最小整数　　 SELECT CEIL(1.5) -- 返回2
FLOOR(x)	返回小于或等于x的最大整数　　 SELECT FLOOR(1.5) -- 返回1
RAND()	返回0->1的随机数　　 SELECT RAND() --0.93099315644334
RAND(x)	返回0->1的随机数，x值相同时返回的随机数相同　　 SELECT RAND(2) --1.5865798029924
SIGN(x)	返回x的符号，x是负数、0、正数分别返回-1、0和1　　 SELECT SIGN(-10) -- (-1)
PI()	返回圆周率(3.141593）　　 SELECT PI() --3.141593
TRUNCATE(x,y)	返回数值x保留到小数点后y位的值（与ROUND最大的区别是不会进行四舍五入）　　 SELECT TRUNCATE(1.23456,3) -- 1.234
ROUND(x)	返回离x最近的整数　　SELECT ROUND(1.23456) --1
ROUND(x,y)	保留x小数点后y位的值，但截断时要进行四舍五入　　 SELECT ROUND(1.23456,3) -- 1.235
POW(x,y).POWER(x,y)	返回x的y次方　　 SELECT POW(2,3) -- 8
SQRT(x)	返回x的平方根　　 SELECT SQRT(25) -- 5
EXP(x)	返回e的x次方　　 SELECT EXP(3) -- 20.085536923188
MOD(x,y)	返回x除以y以后的余数　　 SELECT MOD(5,2) -- 1
LOG(x)	返回自然对数(以e为底的对数)　　 SELECT LOG(20.085536923188) -- 3
LOG10(x)	返回以10为底的对数　　 SELECT LOG10(100) -- 2
RADIANS(x)	将角度转换为弧度　　 SELECT RADIANS(180) -- 3.1415926535898
DEGREES(x)	将弧度转换为角度　　 SELECT DEGREES(3.1415926535898) -- 180
SIN(x)	求正弦值(参数是弧度)　　 SELECT SIN(RADIANS(30)) -- 0.5
ASIN(x)	求反正弦值(参数是弧度)
COS(x)	求余弦值(参数是弧度)
ACOS(x)	求反余弦值(参数是弧度)
TAN(x)	求正切值(参数是弧度)
ATAN(x) ATAN2(x)	求反正切值(参数是弧度)
COT(x)	求余切值(参数是弧度)

 

二、字符串函数

字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。

函数	说明
CHAR_LENGTH(s)	返回字符串s的字符数 SELECT CHAR_LENGTH('你好123') -- 5
LENGTH(s)	返回字符串s的长度 SELECT LENGTH('你好123') -- 9
CONCAT(s1,s2,...)	将字符串s1,s2等多个字符串合并为一个字符串 SELECT CONCAT('12','34') -- 1234
CONCAT_WS(x,s1,s2,...)	同CONCAT(s1,s2,...)函数，但是每个字符串直接要加上x SELECT CONCAT_WS('@','12','34') -- 12@34
INSERT(s1,x,len,s2)	将字符串s2替换s1的x位置开始长度为len的字符串 SELECT INSERT('12345',1,3,'abc') -- abc45
UPPER(s),UCAASE(S)	将字符串s的所有字母变成大写字母 SELECT UPPER('abc') -- ABC
LOWER(s),LCASE(s)	将字符串s的所有字母变成小写字母 SELECT LOWER('ABC') -- abc
LEFT(s,n)	返回字符串s的前n个字符 SELECT LEFT('abcde',2) -- ab
RIGHT(s,n)	返回字符串s的后n个字符 SELECT RIGHT('abcde',2) -- de
LPAD(s1,len,s2)	字符串s2来填充s1的开始处，使字符串长度达到len SELECT LPAD('abc',5,'xx') -- xxabc
RPAD(s1,len,s2)	字符串s2来填充s1的结尾处，使字符串的长度达到len SELECT RPAD('abc',5,'xx') -- abcxx
LTRIM(s)	去掉字符串s开始处的空格
RTRIM(s)	去掉字符串s结尾处的空格
TRIM(s)	去掉字符串s开始和结尾处的空格
TRIM(s1 FROM s)	去掉字符串s中开始处和结尾处的字符串s1 SELECT TRIM('@' FROM '@@abc@@') -- abc
REPEAT(s,n)	将字符串s重复n次 SELECT REPEAT('ab',3) -- ababab
SPACE(n)	返回n个空格
REPLACE(s,s1,s2)	将字符串s2替代字符串s中的字符串s1 SELECT REPLACE('abc','a','x') --xbc
STRCMP(s1,s2)	比较字符串s1和s2
SUBSTRING(s,n,len)	获取从字符串s中的第n个位置开始长度为len的字符串
MID(s,n,len)	同SUBSTRING(s,n,len)
LOCATE(s1,s),POSITION(s1 IN s)	从字符串s中获取s1的开始位置 SELECT LOCATE('b', 'abc') -- 2
INSTR(s,s1)	从字符串s中获取s1的开始位置 SELECT INSTR('abc','b') -- 2
REVERSE(s)	将字符串s的顺序反过来 SELECT REVERSE('abc') -- cba
ELT(n,s1,s2,...)	返回第n个字符串 SELECT ELT(2,'a','b','c') -- b
EXPORT_SET(x,s1,s2)	返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个 “off”字符串。每个字符串用“separator”分隔(缺省“,”)，并且只有“bits”的“number_of_bits” (缺省64)位被使用。 SELECT EXPORT_SET(5,'Y','N',',',4) -- Y,N,Y,N
FIELD(s,s1,s2...)	返回第一个与字符串s匹配的字符串位置 SELECT FIELD('c','a','b','c') -- 3
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置
MAKE_SET(x,s1,s2)	返回一个集合 (包含由“,” 字符分隔的子串组成的一个 字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对 应位1，等等。 SELECT MAKE_SET(1|4,'a','b','c'); -- a,c
SUBSTRING_INDEX	返回从字符串str的第count个出现的分隔符delim之后的子串。 如果count是正数，返回第count个字符左边的字符串。 如果count是负数，返回第(count的绝对值(从右边数))个字符右边的字符串。 SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c
LOAD_FILE(file_name)	读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权 限。文件必须所有内容都是可读的并且小于max_allowed_packet。 如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。

 

三、日期时间函数

MySQL的日期和时间函数主要用于处理日期时间。

函数	说明
CURDATE(),CURRENT_DATE()	返回当前日期 SELECT CURDATE() ->2014-12-17
CURTIME(),CURRENT_TIME	返回当前时间 SELECT CURTIME() ->15:59:02
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(), SYSDATE(),LOCALTIMESTAMP()	返回当前日期和时间 SELECT NOW() ->2014-12-17 15:59:02
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间 SELECT UNIX_TIMESTAMP() ->1418803177
UNIX_TIMESTAMP(d)	将时间d以UNIX时间戳的形式返回 SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11') ->1320981071
FROM_UNIXTIME(d)	将UNIX时间戳的时间转换为普通格式的时间 SELECT FROM_UNIXTIME(1320981071) ->2011-11-11 11:11:11
UTC_DATE()	返回UTC日期 SELECT UTC_DATE() ->2014-12-17
UTC_TIME()	返回UTC时间 SELECT UTC_TIME() ->08:01:45 (慢了8小时)
MONTH(d)	返回日期d中的月份值，1->12 SELECT MONTH('2011-11-11 11:11:11') ->11
MONTHNAME(d)	返回日期当中的月份名称，如Janyary SELECT MONTHNAME('2011-11-11 11:11:11') ->November
DAYNAME(d)	返回日期d是星期几，如Monday,Tuesday SELECT DAYNAME('2011-11-11 11:11:11') ->Friday
DAYOFWEEK(d)	日期d今天是星期几，1星期日，2星期一 SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
WEEKDAY(d)	日期d今天是星期几，  0表示星期一，1表示星期二
WEEK(d)，WEEKOFYEAR(d)	计算日期d是本年的第几个星期，范围是0->53 SELECT WEEK('2011-11-11 11:11:11') ->45
DAYOFYEAR(d)	计算日期d是本年的第几天 SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
DAYOFMONTH(d)	计算日期d是本月的第几天 SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
QUARTER(d)	返回日期d是第几季节，返回1->4 SELECT QUARTER('2011-11-11 11:11:11') ->4
HOUR(t)	返回t中的小时值 SELECT HOUR('1:2:3') ->1
MINUTE(t)	返回t中的分钟值 SELECT MINUTE('1:2:3') ->2
SECOND(t)	返回t中的秒钟值 SELECT SECOND('1:2:3') ->3
EXTRACT(type FROM d)	从日期d中获取指定的值，type指定返回的值 SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') ->11 type可取值为： 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
TIME_TO_SEC(t)	将时间t转换为秒 SELECT TIME_TO_SEC('1:12:00') ->4320
SEC_TO_TIME(s)	将以秒为单位的时间s转换为时分秒的格式 SELECT SEC_TO_TIME(4320) ->01:12:00
TO_DAYS(d)	计算日期d距离0000年1月1日的天数 SELECT TO_DAYS('0001-01-01 01:01:01') ->366
FROM_DAYS(n)	计算从0000年1月1日开始n天后的日期 SELECT FROM_DAYS(1111) ->0003-01-16
DATEDIFF(d1,d2)	计算日期d1->d2之间相隔的天数 SELECT DATEDIFF('2001-01-01','2001-02-02') ->-32
ADDDATE(d,n)	计算其实日期d加上n天的日期
ADDDATE(d，INTERVAL expr type)	计算起始日期d加上一个时间段后的日期 SELECT ADDDATE('2011-11-11 11:11:11',1) ->2011-11-12 11:11:11 (默认是天) SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) ->2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)
DATE_ADD(d,INTERVAL expr type)	同上
SUBDATE(d,n)	日期d减去n天后的日期 SELECT SUBDATE('2011-11-11 11:11:11', 1) ->2011-11-10 11:11:11 (默认是天)
SUBDATE(d,INTERVAL expr type)	日期d减去一个时间段后的日期 SELECT SUBDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) ->2011-11-11 11:06:11 (TYPE的取值与上面那个列出来的函数类似)
ADDTIME(t,n)	时间t加上n秒的时间 SELECT ADDTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:16 (秒)
SUBTIME(t,n)	时间t减去n秒的时间 SELECT SUBTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:06 (秒)
DATE_FORMAT(d,f)	按表达式f的要求显示日期d SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') ->2011-11-11 11:11:11 AM
TIME_FORMAT(t,f)	按表达式f的要求显示时间t SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM
GET_FORMAT(type,s)	获得国家地区时间格式函数 select get_format(date,'usa') ->%m.%d.%Y (注意返回的就是这个奇怪的字符串(format字符串))

四、聚合函数

用于查询结果的计算如，求和，商，积，差等

sum（）	求和，汇总数据总和
count()	计数，汇总行数等
avg()
min()	求最小值
max()	最大值
Group_concat()

 

五、条件判断函数

1、IF(expr,v1,v2)函数

　　如果表达式expr成立，返回结果v1；否则，返回结果v2。

1. SELECT IF(1 > 0,'正确','错误') 
2. ->正确

2、IFNULL(v1,v2)函数

　　如果v1的值不为NULL，则返回v1，否则返回v2。

1. SELECT IFNULL(null,'Hello Word')
2. ->Hello Word

3、CASE

　　语法1：

1. CASE 
2. 　　WHEN e1
3. 　　THEN v1
4. 　　WHEN e2
5. 　　THEN e2
6. 　　...
7. 　　ELSE vn
8. END

　　CASE表示函数开始，END表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

1. SELECT CASE 
2. 　　WHEN 1 > 0
3. 　　THEN '1 > 0'
4. 　　WHEN 2 > 0
5. 　　THEN '2 > 0'
6. 　　ELSE '3 > 0'
7. 　　END
8. ->1 > 0

 

　　语法2：

1. CASE expr 
2. 　　WHEN e1 THEN v1
3. 　　WHEN e1 THEN v1
4. 　　...
5. 　　ELSE vn
6. END

　　如果表达式expr的值等于e1，返回v1；如果等于e2,则返回e2。否则返回vn。

1. SELECT CASE 1 
2. 　　WHEN 1 THEN '我是1'
3. 　　WHEN 2 THEN '我是2'
4. ELSE '你是谁'

 

五、系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数	作用
VERSION()	返回数据库的版本号 SELECT VERSION() ->5.0.67-community-nt
CONNECTION_ID()	返回服务器的连接数
DATABASE()、SCHEMA	返回当前数据库名
USER()、SYSTEM_USER()、SESSION_USER()、 CURRENT_USER()、CURRENT_USER	返回当前用户
CHARSET(str)	返回字符串str的字符集
COLLATION(str)	返回字符串str的字符排列方式
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENT值

 

六、加密函数

加密函数是MySQL用来对数据进行加密的函数。

1、PASSWORD(str)

　　该函数可以对字符串str进行加密，一般情况下，PASSWORD(str)用于给用户的密码加密。

• SELECT PASSWORD('123')
• ->*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

2、MD5

　　MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。

• SELECT md5('123')
• ->202cb962ac59075b964b07152d234b70

3、ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str)

　　ENCODE函数可以使用加密密码pswd_str来加密字符串str，加密结果是二进制数，需要使用BLOB类型的字段保存。该函数与DECODE是一对，需要同样的密码才能够解密。

• SELECT ENCODE('123','xxoo')
• ->;vx
• SELECT DECODE(';vx','xxoo')
• ->123

 

七，其他函数

1、格式化函数FORMAT(x,n)

　　FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。

 

• SELECT FORMAT(3.1415926,3)
• ->3.142

2、不同进制的数字进行转换

• ASCII(s) 返回字符串s的第一个字符的ASCII码；
• BIN（x） 返回x的二进制编码；
• HEX(x) 返回x的十六进制编码；
• OCT(x) 返回x的八进制编码；
• CONV(x,f1,f2) 返回f1进制数变成f2进制数；

3、IP地址与数字相互转换的函数

• INET_ATON(IP)函数可以将IP地址转换为数字表示；IP值需要加上引号；
• INET_NTOA(n)函数可以将数字n转换成IP形式。

• SELECT INET_ATON('192.168.0.1')
• ->3232235521
• SELECT INET_NTOA(3232235521)
• ->192.168.0.1

4、加锁函数和解锁函数

• GET_LOCK(name,time)函数定义一个名称为nam、持续时间长度为time秒的锁。如果锁定成功，则返回1；如果尝试超时，则返回0；如果遇到错误，返回NULL。
• RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，则返回1；如果尝试超时，返回0了如果解锁失败，返回NULL；
• IS_FREE_LOCK(name)函数判断是否已使用名为name的锁定。如果使用，返回0，否则，返回1；

• SELECT GET_LOCK('MySQL',10)
• ->1 (持续10秒)
• SELECT IS_FREE_LOCK('MySQL')
• ->1 
• SELECT RELEASE_LOCK('MySQL')
• ->1

5、重复执行指定操作的函数

　　BENCHMARK(count.expr)函数将表达式expr重复执行count此，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。

• SELECT BENCHMARK(10000,NOW())
• ->0 返回系统时间1万

6、改变字符集的函数

　　CONVERT(s USING cs)函数将字符串s的字符集变成cs。

 

• SELECT CHARSET('ABC')
• ->utf-8 
•  
• SELECT CHARSET(CONVERT('ABC' USING gbk))
• ->gbk

7、转换数据类型

• CAST(x AS type)
• CONVERT(x,type)

　　这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER。

• SELECT CAST('123' AS UNSIGNED INTEGER) + 1
• ->124
•  
• SELECT '123' + 1
• ->124 其实MySQL能默认转换
•  
• SELECT CAST(NOW() AS DATE)
• 　　->2014-12-18

mysql 5.7版本增加对json支持--JSON 函数列表

MySQL 官方列出 JSON 相关的函数，完整列表如下 [doc ]：

分类	函数	描述
json 创建函数	json_array()	创建 json 数组
	json_object()	创建 json 对象
	json_quote()	用双引号包裹 json 文档
json 查询函数	json_contains()	判断是否包含某个 json 值
	json_contains_path()	判断某个路径下是否包 json 值
	json_extract()	提取 json 值
	column->path	json_extract() 的简洁写法，5.7.9 开始支持
	column->>path	json_unquote(json_extract()) 的简洁写法，5.7.13 开始支持
	json_keys()	把 json 对象的顶层的全部键提取为 json 数组
	json_search()	按给定字符串关键字搜索 json，返回匹配的路径
json 修改函数	json_append()	5.7.9 废弃，改名为 json_array_append
	json_array_append()	在 josn 文档末尾添加数组元素
	json_array_insert()	在 josn 数组中插入元素
	json_insert()	插入值（只插入新值，不替换旧值）
	json_merge()	5.7.22 废弃，与 json_merge_preserve() 同义
	json_merge_patch()	合并 json 文档，重复键的值将被替换掉
	json_merge_preserve()	合并 json 文档，保留重复键
	json_remove()	删除 json 文档中的数据
	json_replace()	替换值（只替换旧值，不插入新值）
	json_set()	设置值（替换旧值，或插入新值）
	json_unquote()	移除 json 值的双引号包裹
json 属性函数	json_depth()	返回 json 文档的最大深度
	json_length()	返回 json 文档的长度
	json_type()	返回 json 值的类型
	json_valid()	判断是否为合法 json 文档
json 工具函数	json_pretty()	美化输出 json 文档，5.7.22 新增
	json_storage_size()	返回 json 文档占用的存储空间，5.7.22 新增

官方文档对全部函数都作了充分解释并提供一定的示例代码。下文挑选了部分函数，演示它们的使用方法。

创建与插入 JSON

上面的 SQL 示例简单验演示了创建 JSON 列以及写入并查询 JSON 数据，比较简单，就不做解释了。

查询 JSON

json_extract() 与 -> 操作符

如果要查询 JSON 文档中内容，提取 JSON 中的值，可以使用 json_extract() 函数。函数定义如下：

json_extract(json_doc, path[, path] ...)

先来看下 SQL 示例：

示例中的 $.name，使用的是 JSON 路径语法，用来提取 JSON 文档的内容。JSON 路径语法，源自 Stefan Goessner 的 JsonPath，不过 MySQL 作了简化。路径语法使用 $ 开头来表示整个 JSON 文档。如果要提取部分 JSON 文档，可以在路径后面添加选择符：

• 在路径 path 后上追加对象的键名称，可以获取这个键下成员。如果加键名称后，路径表达式非法，需要对键名称用双引号包裹（比如，键名称中包含空格的情况）
• 在路径 path 后加上追加 [N]，用于选择数组的第 N 个元素。数组索引从 0 开始。如果 path 下并不是数组，path[0] 获取结果就是 path 本身。
• 路径可以包含 * 和 ** 通配符：

• .[*] 用于获取 JSON 对象的全部成员。
• [*] 用于获取 JSON 数组的全部元素。
• prefix**suffix 表示全部以 prefix 开始，以 suffix 结尾的路径。

• 如果路径在 JSON 文档中不存在数据，将返回 NULL。

假设 $ 引用的是如下 JSON 数组：

[3, {"a": [5, 6], "b": 10}, [99, 100]]

$[0] 获取到的值为 3，$[1] 获取到 {"a": [5, 6], "b": 10}，$[2] 获取到 [99, 100]，$[3] 获取到 NULL（因为不存在第 4 个元素）。

因为 $[1] 和 $[2] 获取的并非纯量（nonscalar），它们可以进一步使用路径访问到内嵌的值，比如：$[1].a 获取到 [5, 6]，$[1].a[1] 获取到 6，$[1].b 获取到 10，$[2][0] 获取到 99。

上文提到，如果追加键值名后，路径表达式非法，需要对键名称用双引号包裹。假设 $ 引用的是如下 JSON 对象：

{"name 1": "Will", "name 2": "Andy"}

两个键都包含空格，需要加上双引号，才能使用路径表达式访问。$."name 1" 将获取到 Will，而 $."name 2" 将获取到 Andy。

现在来看下通配符的示例，假设 JSON 对象如下：

{"a": {"b": 1}, "c": {"b": 2}, "d": [3, 4, 5]}

使用 $.* 将获取到 [{"b": 1}, {"b": 2}, [3, 4, 5]]；
使用 $.d[*] 将获取到 [3, 4, 5]；
使用 $**.b（对应 $.a.b 和 $.c.b）将获取到 [1, 2]。

MySQL 5.7.9 开始，官方支持 json_extract(column, path) 的简洁写法，内联 JSON 路径表达式 column->path（WL#8607）。示例如下：

本质上，这种写法是语法糖，column->path 等价于 json_extract(column, path)，内联 JSON 路径表达式会在语法解析阶段被转换为 json_extract() 调用。另外，column->path，存在以下限制 [ref ]

即，1. 数据源必须是表字段，2. 路径表达式必须为字符串，3. SQL 语句中最多只支持一个。

现在来试验下这个限制，如果使用内联 JSON 路径表达式查询 MySQL 变量，将会报语法错误：

json_unquote() 与 ->> 操作符

假设数据如下：

来看下使用 -> 提取获得 JSON 值：

可以看到，对于 string 类型的 JSON 值，使用 json_extract() 或 -> 获取的都是被双引号包裹的字符串。MySQL 提供 json_unquote() 函数，用于去掉双引号包裹。另外，MySQL 支持 column->>path 语法，通过 ->> 操作符获取纯量（scalar）。column->>path 写法等价于 json_unquote( json_extract(column, path) ) 或者 json_unquote(column -> path)。来看下 SQL 示例：

MySQL 这种区分 -> 和 ->> 的写法，怀疑是源自 Postgres。因为 Postgres 也分别提供了 -> 和 ->> 操作符，-> 也是保留双引号（get JSON object field by key），而 ->> 才能获取实际的字符串值（get JSON object field as text） [doc, stackoverflow ]。

在笔者看来，这种需要通过 json_unquote() 才能获取实际字符串值的写法完全没有必要，因为很难想到有需要保留双引号的使用场景，而就获取实际的字符串值才是多数情况。实际上，SQLite 的开发者也持有相同的想法。2015 年 10 月，SQLite 3.9 发布，开始支持 JSON 类型 [infoq, doc ]。简单对比下，可以发现 SQLite 提供的 JSON 函数和 MySQL 极其相似，很多函数同名并且同语义。SQLite 也提供了 json_extract() 函数，与 MySQL 不同，SQLite 返回的是移除双引号后的字符串（the dequoted text for a JSON string value）。看下示例：

对于提取 JSON 文档中的纯量（scalar），SQL 标准定义了的 json_value() 函数，MySQL 没有支持，但 Oracle、MariaDB、MSSQL 都有支持。MariaDB 在兼容 MySQL 的同时也支持 SQL 标准，json_extract() 和 json_value() 在 MariaDB 下都可用。来看下 SQL 示例：

其他查询函数

除了上文的 json_extract() 函数，查询 JSON 文档相关的还有其他函数，如 json_contains()、json_contains_path()、json_keys()、json_search()。示例如下：

函数的完整定义和用法可以参考官方文档，本文不再一一举例说明。

修改 JSON

对于 MySQL 的 JSON 类型的数据，若要修改数据，可以使用类似如下的 SQL：

如果要修改 JSON 内部数据，是否可以通过 JSON 路径表达式直接赋值呢？答案是，不行，MySQL 不支持。

MySQL 提供了数个函数来修改 JSON 数据。我们先来看看 json_replace()、json_set() 和 json_insert() 这三个函数：

• json_replace()：替换值。替换旧值，但不插入新值
• json_set()：设置值。替换旧值，或插入新值
• json_insert()：插入值。只插入新值，不替换旧值

json_insert() 只能插入数据， json_replace() 只能更新数据，json_set() 能更新或插入数据。

替换值，json_replace() 示例：

设置值，json_set() 示例：

插入值，json_insert() 示例：

现在，我们来看下修改 JSON 数组的两个函数，json_array_insert() 和 json_array_append()，函数定义如下：

json_array_insert()，参数 path 必须指向 JSON 数组某个位置的元素，若该位置存在值，将会把 val 插入该位置，然后其他元素向右移动；若该位置超出数组大小范围，将会把 val 插入到数组末尾。SQL 示例如下：

json_array_append()，如果参数 path 指向的 JSON 是数组，将在数组末尾添加元素；如果参数 path 指向的 JSON 是值或对象，该值或对象将被包裹为数组，然后在这个数组末尾添加元素。

除了上文提到的函数，还有 json_merge_patch()、json_merge_preserve()、json_remove() 这个些函数，可以参考官方文档的介绍，本文不再一一举例说明。

索引 JSON：生成列

现在来看下根据 JSON 列查询表数据的执行计划，如下：

可以看到，因为没有加索引，访问类型是全表扫描 type: ALL。来试下在 JSON 类型的 data 列上添加索引，会提示如下错误：

对于索引 JSON 类型列问题，MySQL 文档有如下阐述 [doc ]：

JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. See  Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.

就是说，不能直接在 JSON 列上创建索引；替代方式是，先创建提取 JSON 纯量的生成列（generated column），然后在这个生成列上创建索引。回过头来，ERROR 3152，这个报错提示信息其实让人有点困惑，对没仔细阅读文档的人来说，可能会误以为 MySQL 不支持索引 JSON 列（Bug #81364）。于是，在 MySQL 8.0 错误提示信息优化为：

ERROR 3152 (42000): JSON column '%s' supports indexing only via generated columns on a specified JSON path.

生成列以及在生成列上创建索引，是 MySQL 5.7 开始支持的新特性。但其实，在 SQL:2003 标准中，生成列就早已经被定义为可选特性，“Optional Features of SQL/Foundation:2003, T175 Generated columns”。这个特性在其他 DBMS 中很早就有支持。2007 年 9 月发布的 Oracle Database 11g 开始支持生成列，不过它们称之为称之为虚拟列（virtual column）。2008 年 8 月发布的 SQL Server 2008 开始支持计算列（computed column），实现的就是 SQL 标准中的生成列。在相近的时间点，MySQL 创建了WL#411: Computed virtual columns as MS SQL server has。之后，MySQL 的社区贡献者 Andrey Zhakov 实现了 WL#411 描述的特性，并发布了实现的代码补丁 [ref, blog, doc ]。可惜的是 MySQL 官方很长一段时间都没把这个补丁合并进来，直到 2015 年的 MySQL 5.7（7年后）才官方实现 WL#411，同时 WL#411 的标题也被更新为符合 SQL 标准术语的 “Generated columns”。与之相对比的是，2010 年 4 月发布的 MariaDB 5.2 就开始支持虚拟列，实现上同样也是基于 Andrey Zhakov 贡献的代码 [ref ]。关于生成列或虚拟列，wikipedia 总结了各大 DBMS 的支持情况，可以参阅。总结下，标准 SQL 定义生成列的语法和 SQL Server 2008、Oracle 11g、MariaDB、MySQL 的区别 [ref1, ref2 ]：

回到正题，我们现在来试试 MySQL 的生成列：

上面的示例，创建生成列 id，生成列对应的表达式是 data -> "$.id"。现在再试试在生成列 id 上，创建索引：

从上面的执行计划可以看到，查询条件用 id 或者 data -> "$.id" 都能使用索引 idx_id。

JSON 二进制格式

内部实现上，保存到数据库的 JSON 数据并非以 JSON 文本存储，而是二进制格式，具体可以参见，WL#8132: JSON datatype and binary storage format，当然也可以直接阅读源码 json_binary.h、json_binary.cc（doxygen）。

MySQL 的 JSON 二进制格式，其中有一点比较值得注意，WL#8132 提到：

The keys are sorted, so that lookup can use binary search to locate the key quickly.

就是，为了能利用二分搜索快速定位键，存入数据库的JSON 对象的键是被排序过的。来看下下面的 SQL：

上面的 SQL 可以看到，insert 写入时键并没有按次序排列，而用 select 将 JSON 数据反序列化读出，发现实际保存的键是有序的。排序规则是，先按字符串长度排序，若长度相同按字母排序。同样的，键关联的值，按键排序后的次序排列。对键排序，显然只能针对 JSON 对象，若要存储 JSON 数组，值按索引位置排序。

MySQL 5.7.22 新增 json_storage_size() 函数，用于返回 json 文档二进制表示占用的存储空间。先来看下 SQL 示例：

WL#8132 给出了 JSON 二进制格式的 BNF 语法描述。参考这个语法描述，可以推算出上文示例中的 "abc"、[42, "xy", "abc"]、{"b": 42, "a": "xy"} 对应的二进制表示。先来看下 "abc" 纯量，语法推导过程如下：

对应的二进制值，共 5 个字节，依次为 0x0c 0x03 0x61 0x62 0x63，其中 0x61 0x62 0x63，就是 16 进制表示的字符串 abc。占用 5个字节，与 json_storage_size() 函数返回的结果一致。相应的语法树如下：

从二进制的角度看，纯量 "abc" 的 JSON 二进制表示如下：

[42, "xy", "abc"] 的推导过程，如下：

[42, "xy", "abc"] 对应的二进制表示，共 21 个字节，依次为 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 0x02 0x78 0x79 0x03 0x61 0x62 0x63。如下图：

相对来说，产生式 array ::= element-count size value-entry* value*，是整个JSON 数组二进制表示语法的核心。element-count，表示元素个数。上图中，第 4、5 个字节是 size 字段，十进制值为 20（0x14），是完整二进制表示去掉开头 type 字段后的大小（文档没有明确这个字段的含义，不过通过源码推断出来）。另外，value-entry 由 type 和 offset-or-inlined-value 字段组成。type 很好理解，不做解释。offset-or-inlined-value 字段，官方文档给出了含义，含义如下：

就是说，如果实际要保存的值足够小，将直接内联在这个字段中，否则将保存偏移量（offset），也就是指向实际值的指针。在示例中，保存 xy 对应的 offset 值为 13（0x0d），指向的相对地址是 14。因为这里的 offset 并不是以相对地址 0 为基准地址，是以相对地址 1 为基准地址（图中箭头 B 指向的位置），所以偏移量是 13 而不是 14（这个字段的明确含义也是从源码推断而来）。类似的，保存 abc 对应的 offset 值为 16（0x10），指向的相对地址是 17。

阅读文档容易发现，element-count、size、offset 字段占用的字节大小是固定的，小 JSON（64KB 以内）是 2 字节，大 JSON 是 4 字节。所以，若要查找 JSON 数组的第 pos 个元素的 value-entry 的偏移量，可以使用下面的式子快速定位：

entry_offset = offset_size * 2 + (1 + offset_size) * pos

JSON 数组二进制表示的其他字段比较容易理解，文档都有解释，就不展开阐述了。

现在来看下，JSON 对象 {"b": 42, "a": "xy"} 的二进制表示，如下图：

对于 JSON 对象二进制表示的语法，核心的产生式是 object ::= element-count size key-entry* value-entry* key* value*。element-count、size 和 value-entry 字段，在 JSON 数组中也有，不再赘述。而 key-entry 字段，类似于 value-entry。key-entry 中的 key-offset 保存的是偏移量，是指向键的指针。另外，正如上文提到的 MySQL 会对 JSON 键排序，所以上图示例的第 20 和 21 个字节值分别是 0x61和 0x62，即 a 和 b，而非 b 和 a。同样的，键关联的值，按键排序后的次序排列，依次是 "xy" 和 42。