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) | 返回一个集合 (包含由“,” 字符分隔的子串组成的一个 字符串),由相应的位在 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 |
LOAD_FILE(file_name) | 读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权 限。文件必须所有内容都是可读的并且小于max_allowed_packet。 如果文件不存在或由于上面原因之一不能被读出,函数返回NULL。 |
三、日期时间函数
MySQL的日期和时间函数主要用于处理日期时间。
函数 | 说明 |
CURDATE(),CURRENT_DATE() | 返回当前日期 SELECT CURDATE() |
CURTIME(),CURRENT_TIME | 返回当前时间 SELECT CURTIME() |
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(), SYSDATE(),LOCALTIMESTAMP() | 返回当前日期和时间 SELECT NOW() |
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间 SELECT UNIX_TIMESTAMP() |
UNIX_TIMESTAMP(d) | 将时间d以UNIX时间戳的形式返回 SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11') |
FROM_UNIXTIME(d) | 将UNIX时间戳的时间转换为普通格式的时间 SELECT FROM_UNIXTIME(1320981071) |
UTC_DATE() | 返回UTC日期 SELECT UTC_DATE() |
UTC_TIME() | 返回UTC时间 SELECT UTC_TIME() |
MONTH(d) | 返回日期d中的月份值,1->12 SELECT MONTH('2011-11-11 11:11:11') |
MONTHNAME(d) | 返回日期当中的月份名称,如Janyary SELECT MONTHNAME('2011-11-11 11:11:11') |
DAYNAME(d) | 返回日期d是星期几,如Monday,Tuesday SELECT DAYNAME('2011-11-11 11:11:11') |
DAYOFWEEK(d) | 日期d今天是星期几,1星期日,2星期一 SELECT DAYOFWEEK('2011-11-11 11:11:11') |
WEEKDAY(d) | 日期d今天是星期几, 0表示星期一,1表示星期二 |
WEEK(d),WEEKOFYEAR(d) | 计算日期d是本年的第几个星期,范围是0->53 SELECT WEEK('2011-11-11 11:11:11') |
DAYOFYEAR(d) | 计算日期d是本年的第几天 SELECT DAYOFYEAR('2011-11-11 11:11:11') |
DAYOFMONTH(d) | 计算日期d是本月的第几天 SELECT DAYOFMONTH('2011-11-11 11:11:11') |
QUARTER(d) | 返回日期d是第几季节,返回1->4 SELECT QUARTER('2011-11-11 11:11:11') |
HOUR(t) | 返回t中的小时值 SELECT HOUR('1:2:3') |
MINUTE(t) | 返回t中的分钟值 SELECT MINUTE('1:2:3') |
SECOND(t) | 返回t中的秒钟值 SELECT SECOND('1:2:3') |
EXTRACT(type FROM d) | 从日期d中获取指定的值,type指定返回的值 SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') type可取值为: MICROSECOND |
TIME_TO_SEC(t) | 将时间t转换为秒 SELECT TIME_TO_SEC('1:12:00') |
SEC_TO_TIME(s) | 将以秒为单位的时间s转换为时分秒的格式 SELECT SEC_TO_TIME(4320) |
TO_DAYS(d) | 计算日期d距离0000年1月1日的天数 SELECT TO_DAYS('0001-01-01 01:01:01') |
FROM_DAYS(n) | 计算从0000年1月1日开始n天后的日期 SELECT FROM_DAYS(1111) |
DATEDIFF(d1,d2) | 计算日期d1->d2之间相隔的天数 SELECT DATEDIFF('2001-01-01','2001-02-02') |
ADDDATE(d,n) | 计算其实日期d加上n天的日期 |
ADDDATE(d,INTERVAL expr type) | 计算起始日期d加上一个时间段后的日期 SELECT ADDDATE('2011-11-11 11:11:11',1) SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) |
DATE_ADD(d,INTERVAL expr type) | 同上 |
SUBDATE(d,n) | 日期d减去n天后的日期 SELECT SUBDATE('2011-11-11 11:11:11', 1) |
SUBDATE(d,INTERVAL expr type) | 日期d减去一个时间段后的日期 SELECT SUBDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) |
ADDTIME(t,n) | 时间t加上n秒的时间 SELECT ADDTIME('2011-11-11 11:11:11', 5) |
SUBTIME(t,n) | 时间t减去n秒的时间 SELECT SUBTIME('2011-11-11 11:11:11', 5) |
DATE_FORMAT(d,f) | 按表达式f的要求显示日期d SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') |
TIME_FORMAT(t,f) | 按表达式f的要求显示时间t SELECT TIME_FORMAT('11:11:11','%r') |
GET_FORMAT(type,s) | 获得国家地区时间格式函数 select get_format(date,'usa') |
四、聚合函数
用于查询结果的计算如,求和,商,积,差等
sum() | 求和,汇总数据总和 |
count() | 计数,汇总行数等 |
avg() |
|
min() | 求最小值 |
max() | 最大值 |
Group_concat() |
五、条件判断函数
1、IF(expr,v1,v2)函数
如果表达式expr成立,返回结果v1;否则,返回结果v2。
-
SELECT IF(1 > 0,'正确','错误')
-
->正确
2、IFNULL(v1,v2)函数
如果v1的值不为NULL,则返回v1,否则返回v2。
-
SELECT IFNULL(null,'Hello Word')
-
->Hello Word
3、CASE
语法1:
-
CASE
-
WHEN e1
-
THEN v1
-
WHEN e2
-
THEN e2
-
...
-
ELSE vn
-
END
CASE表示函数开始,END表示函数结束。如果e1成立,则返回v1,如果e2成立,则返回v2,当全部不成立则返回vn,而当有一个成立之后,后面的就不执行了。
-
SELECT CASE
-
WHEN 1 > 0
-
THEN '1 > 0'
-
WHEN 2 > 0
-
THEN '2 > 0'
-
ELSE '3 > 0'
-
END
-
->1 > 0
语法2:
-
CASE expr
-
WHEN e1 THEN v1
-
WHEN e1 THEN v1
-
...
-
ELSE vn
-
END
如果表达式expr的值等于e1,返回v1;如果等于e2,则返回e2。否则返回vn。
-
SELECT CASE 1
-
WHEN 1 THEN '我是1'
-
WHEN 2 THEN '我是2'
-
ELSE '你是谁'
五、系统信息函数
系统信息函数用来查询MySQL数据库的系统信息。
函数 | 作用 |
VERSION() | 返回数据库的版本号 SELECT VERSION() |
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
-
-- 创建 tbl 表,字段 data 为 json 类型
-
mysql>
create
table tbl (data JSON);
-
Query OK,
0 rows affected (
0.17 sec)
-
-
-- 插入 json 对象
-
mysql>
insert into tbl values (
'{"id": 1, "name": "Will"}');
-
Query OK,
1 row affected (
0.04 sec)
-
-
-- 插入 json 数组
-
mysql>
insert into tbl values (
'[1, 42, 1024]');
-
Query OK,
1 row affected (
0.01 sec)
-
-
-- 使用 json_object() 创建 json 对象
-
mysql>
insert into tbl values (json_object(
'id',
2,
'name',
'Joe'));
-
Query OK,
1 row affected (
0.02 sec)
-
-
-- 使用 json_array() 创建 json 数组
-
mysql>
insert into tbl values (json_array(
1,
"abc", null,
true, curtime()));
-
Query OK,
1 row affected (
0.02 sec)
-
-
-- 查询 tbl 表数据
-
mysql>
select * from tbl;
-
+
-------------------------------------------+
-
| data |
-
+
-------------------------------------------+
-
| {
"id":
1,
"name":
"Will"} |
-
| [
1,
42,
1024] |
-
| {
"id":
2,
"name":
"Andy"} |
-
| [
1,
"abc", null,
true,
"20:27:41.000000"] |
-
+
-------------------------------------------+
-
4 rows
in set (
0.00 sec)
上面的 SQL 示例简单验演示了创建 JSON 列以及写入并查询 JSON 数据,比较简单,就不做解释了。
查询 JSON
json_extract() 与 -> 操作符
如果要查询 JSON 文档中内容,提取 JSON 中的值,可以使用 json_extract() 函数。函数定义如下:
json_extract(json_doc, path[, path] ...)
先来看下 SQL 示例:
-
-- 使用 json_extract() 函数查询 json 对象
-
mysql> select json_extract('{
"id":
1,
"name":
"Will"}', '$.name');
-
+
-------------------------------------------------------+
-
| json_extract('{
"id":
1,
"name":
"Will"}', '$.name') |
-
|
-------------------------------------------------------|
-
|
"Will" |
-
+
-------------------------------------------------------+
-
1 row
in set (
0.01 sec)
示例中的 $.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)。示例如下:
-
-- 使用内联 json 路径表达式,查询 json 对象
-
mysql> select * from tbl
where
data -> '$.id' = 2;
-
+
---------------------------+
-
|
data |
-
+
---------------------------+
-
| {
"id":
2,
"name":
"Andy"} |
-
+
---------------------------+
-
1 row
in set (
0.00 sec)
本质上,这种写法是语法糖,column->path
等价于 json_extract(column, path)
,内联 JSON 路径表达式会在语法解析阶段被转换为 json_extract() 调用。另外,column->path
,存在以下限制 [ref ]
即,1. 数据源必须是表字段,2. 路径表达式必须为字符串,3. SQL 语句中最多只支持一个。
现在来试验下这个限制,如果使用内联 JSON 路径表达式查询 MySQL 变量,将会报语法错误:
-
mysql>
set @j =
'["a", "b"]';
-
-
-- 语法错误
-
mysql>
select @j ->
'$[0]';
-
ERROR
1064 (
42000): You have an
error
in your SQL syntax; check the manual that corresponds
to your MySQL
server version
for the
right syntax
to use near
'-> '$[0]'' at line 1
json_unquote() 与 ->> 操作符
假设数据如下:
-
mysql> select * from tbl;
-
+
-----------------------------------------------+
-
|
data |
-
+
-----------------------------------------------+
-
| {
"id":
1,
"name":
"Will"} |
-
| {
"id":
2,
"name":
"printf(\"hello world\");"} |
-
+
-----------------------------------------------+
-
2 rows
in set (
0.00 sec)
来看下使用 ->
提取获得 JSON 值:
-
mysql> select data ->
'$.id', data ->
'$.name', substr(data ->
'$.name',
1,
1) from tbl;
-
+----------------+----------------------------+--------------------------------+
-
| data -> '$.id' | data ->
'$.name'
| substr(data -> '$.name', 1, 1) |
-
+----------------+----------------------------+--------------------------------+
-
| 1 |
"Will"
| " |
-
| 2 |
"printf(\"hello world\");"
| " |
-
+----------------+----------------------------+--------------------------------+
-
2 rows
in set (
0.
00 sec)
-
-
mysql> create table tmp (id int, name varchar(
50));
-
mysql> insert tmp select data ->
'$.id', data ->
'$.name' from tbl;
-
mysql> select *, substr(name,
1,
1) from tmp;
-
+------+----------------------------+--------------------+
-
| id | name
| substr(name, 1, 1) |
-
+------+----------------------------+--------------------+
-
| 1 |
"Will"
| " |
-
| 2 |
"printf(\"hello world\");"
| " |
-
+------+----------------------------+--------------------+
-
2 rows
in set (
0.
01 sec)
可以看到,对于 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> select
data ->>
'$.id'
as id,
data ->
'$.name'
as name,
-
->
data ->>
'$.name'
as name, json_unquote(
data ->
'$.name')
as name from tbl;
-
+------+----------------------------+------------------------+------------------------+
-
| id | name | name | name |
-
+------+----------------------------+------------------------+------------------------+
-
|
1 |
"Will" | Will | Will |
-
|
2 |
"printf(\"hello world\");" | printf(
"hello world"); | printf(
"hello world"); |
-
+------+----------------------------+------------------------+------------------------+
-
2 rows
in
set (
0.00 sec)
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)。看下示例:
-
sqlite>
select json_extract('{"id": 1, "name": "Will"}', '$.name');
-
Will
-
sqlite>
select json_extract('{"code": "printf(\"hello world\");"}', '$.code');
-
printf(
"hello world");
对于提取 JSON 文档中的纯量(scalar),SQL 标准定义了的 json_value() 函数,MySQL 没有支持,但 Oracle、MariaDB、MSSQL 都有支持。MariaDB 在兼容 MySQL 的同时也支持 SQL 标准,json_extract() 和 json_value() 在 MariaDB 下都可用。来看下 SQL 示例:
-
MariaDB [testdb]> select * from tbl;
-
+
-----------------------------------------------+
-
|
data |
-
+
-----------------------------------------------+
-
| {
"id":
1,
"name":
"Will"} |
-
| {
"id":
2,
"name":
"printf(\"hello world\");"} |
-
+
-----------------------------------------------+
-
2 rows
in set (
0.00 sec)
-
-
-- 使用 json_extract() 提取 JSON 值,string 类型的值保留双引号
-
MariaDB [testdb]> select json_extract(
data, '$.id'), json_extract(data, '$.name') from tbl;
-
+
----------------------------+------------------------------+
-
| json_extract(
data, '$.id') | json_extract(data, '$.name') |
-
+
----------------------------+------------------------------+
-
|
1 |
"Will" |
-
|
2 |
"printf(\"hello world\");" |
-
+
----------------------------+------------------------------+
-
2 rows
in set (
0.00 sec)
-
-
-- 使用 json_value() 提取 JSON 值,string 类型的值自动移除双引号
-
MariaDB [testdb]> select json_value(
data, '$.id'), json_value(data, '$.name') from tbl;
-
+
--------------------------+----------------------------+
-
| json_value(
data, '$.id') | json_value(data, '$.name') |
-
+
--------------------------+----------------------------+
-
|
1 |
Will |
-
|
2 | printf(
"hello world"); |
-
+
--------------------------+----------------------------+
-
2 rows
in set (
0.00 sec)
其他查询函数
除了上文的 json_extract() 函数,查询 JSON 文档相关的还有其他函数,如 json_contains()、json_contains_path()、json_keys()、json_search()。示例如下:
-
mysql> set @j =
'{"a": 1, "b": 2, "c": {"d": 4}}';
-
Query OK,
0 rows affected (
0.00 sec)
-
-
-- 使用 json_contains() 函数判断是否存在某 JSON 值
-
mysql>
select json_contains(@j,
'{"a": 1}');
-
+
-------------------------------+
-
| json_contains(@j,
'{"a": 1}') |
-
+
-------------------------------+
-
|
1 |
-
+
-------------------------------+
-
1 row
in set (
0.00 sec)
-
-
-- 使用 json_contains_path() 函数判断是否存在某 JSON 路径
-
mysql>
select json_contains_path(@j,
'one',
'$.a',
'$.e');
-
+
-----------------------------------------------+
-
| json_contains_path(@j,
'one',
'$.a',
'$.e') |
-
|
-----------------------------------------------|
-
|
1 |
-
+
-----------------------------------------------+
-
1 row
in set (
0.00 sec)
-
-
-- 使用 json_contains_path() 函数判断是否存在某 JSON 路径
-
mysql>
select json_contains_path(@j,
'all',
'$.a',
'$.e');
-
+
-----------------------------------------------+
-
| json_contains_path(@j,
'all',
'$.a',
'$.e') |
-
|
-----------------------------------------------|
-
|
0 |
-
+
-----------------------------------------------+
-
1 row
in set (
0.00 sec)
函数的完整定义和用法可以参考官方文档,本文不再一一举例说明。
修改 JSON
对于 MySQL 的 JSON 类型的数据,若要修改数据,可以使用类似如下的 SQL:
-
mysql> select * from tbl
where
data->'$.id' = 2;
-
+
---------------------------+
-
|
data |
-
+
---------------------------+
-
| {
"id":
2,
"name":
"Will"} |
-
+
---------------------------+
-
1 row
in set (
0.00 sec)
-
-
-- 对 data 整个字段修改
-
mysql> update tbl set
data = '{"id": 2, "name": "Andy"}' where data->'$.id' = 2;
-
Query
OK,
0 rows affected (
0.00 sec)
-
Rows matched:
1
Changed:
0
Warnings:
0
-
-
mysql> select * from tbl
where
data->'$.id'= 2;
-
+
---------------------------+
-
|
data |
-
+
---------------------------+
-
| {
"id":
2,
"name":
"Andy"} |
-
+
---------------------------+
-
1 row
in set (
0.00 sec)
如果要修改 JSON 内部数据,是否可以通过 JSON 路径表达式直接赋值呢?答案是,不行,MySQL 不支持。
-
-- 语法错误,不支持通过 JSON 路径表达式赋值,修改 JSON 数据
-
mysql> update tbl
set data->
'$.name' =
'Andy' where data->
'$.id' =
2;
-
ERROR
1064 (
42000): You have an error
in your SQL syntax; check the manual that corresponds
to your MySQL server version
for the right syntax
to use
near
'->'$.
name
' = 'Andy
' where data->'$.id
' = 2' at line
1
MySQL 提供了数个函数来修改 JSON 数据。我们先来看看 json_replace()、json_set() 和 json_insert() 这三个函数:
- json_replace():替换值。替换旧值,但不插入新值
- json_set():设置值。替换旧值,或插入新值
- json_insert():插入值。只插入新值,不替换旧值
json_insert() 只能插入数据, json_replace() 只能更新数据,json_set() 能更新或插入数据。
替换值,json_replace() 示例:
-
-- 使用 json_replace() 函数
-
-- 把 {
"id":
2,
"name":
"Will"} 修改为 {
"id":
2,
"name":
"Andy"}
-
-- 路径 $.name 指向的值存在,旧值被替换为新值
-
mysql> update tbl
-
->
set
data = json_replace(
data,
'$.name',
'Andy')
-
->
where
data->
'$.id' =
2;
-
Query OK,
1 row affected (
0.03 sec)
-
Rows matched:
1 Changed:
1 Warnings:
0
设置值,json_set() 示例:
-
-- 使用 json_set() 函数
-
-- 把 {
"id":
2,
"name":
"Will"} 修改为 {
"id":
2,
"city":
"北京",
"name":
"Bill"}
-
-- 路径 $.name 指向的值存在,旧值被替换为新值;路径 $.city 指向的值不存在,将插入新值
-
mysql> update tbl
-
->
set
data = json_set(
data,
'$.name',
'Bill',
'$.city',
'北京')
-
->
where
data->
'$.id'=
2;
-
Query OK,
1 row affected (
0.00 sec)
-
Rows matched:
1 Changed:
1 Warnings:
0
-
-
mysql> select * from tbl
where
data->
'$.id'=
2;
-
+---------------------------------------------+
-
|
data |
-
+---------------------------------------------+
-
| {
"id":
2,
"city":
"北京",
"name":
"Bill"} |
-
+---------------------------------------------+
-
1 row
in
set (
0.00 sec)
插入值,json_insert() 示例:
-
-- 使用 json_set() 函数
-
-- 把 {
"id":
2,
"name":
"Will"} 修改为 {
"id":
2,
"address":
"故宫"}
-
-- 路径 $.name 指向的值存在,将不替换这个旧值;路径 $.address 指向的值不存在,将插入新值
-
mysql> update tbl
-
->
set
data = json_insert(
data,
'$.name',
'Bill',
'$.address',
'故宫')
-
->
where
data->
'$.id'=
2;
-
Query OK,
1 row affected (
0.04 sec)
-
Rows matched:
1 Changed:
1 Warnings:
0
-
-
mysql> select * from tbl
where
data->
'$.id'=
2;
-
+---------------------------------------------------------------------+
-
|
data |
-
+---------------------------------------------------------------------+
-
| {
"id":
2,
"name":
"Will",
"address":
"故宫"} |
-
+---------------------------------------------------------------------+
-
1 row
in
set (
0.00 sec)
现在,我们来看下修改 JSON 数组的两个函数,json_array_insert() 和 json_array_append(),函数定义如下:
-
json_array_insert(json_doc, path,
val[, path,
val] ...)
-
json_array_append(json_doc, path,
val[, path,
val] ...)
json_array_insert(),参数 path
必须指向 JSON 数组某个位置的元素,若该位置存在值,将会把 val
插入该位置,然后其他元素向右移动;若该位置超出数组大小范围,将会把 val
插入到数组末尾。SQL 示例如下:
-
mysql> set @j =
'["a", {"b": [1, 2]}, [3, 4]]';
-
-
-- 在数组的索引 1 的位置上插入值 5,原本索引 1 位置上的 {"b": [1, 2]} 被挤到后边
-
mysql>
select json_array_insert(@j,
'$[1]',
5);
-
+
------------------------------------+
-
| json_array_insert(@j,
'$[1]',
5) |
-
|
------------------------------------|
-
| [
"a",
5, {
"b": [
1,
2]}, [
3,
4]] |
-
+
------------------------------------+
-
1 row
in set (
0.00 sec)
-
-
-- 插入位置超出数组大小范围,将会把值插入到数组末尾
-
mysql>
select json_array_insert(@j,
'$[100]',
5);
-
+
--------------------------------------+
-
| json_array_insert(@j,
'$[100]',
5) |
-
|
--------------------------------------|
-
| [
"a", {
"b": [
1,
2]}, [
3,
4],
5] |
-
+
--------------------------------------+
-
1 row
in set (
0.00 sec)
-
-
-- path 指向不是 JSON 数组的元素,SQL 执行报错
-
mysql>
select json_array_insert(@j,
'$[1].b',
5);
-
(
3165,
'A path expression is not a path to a cell in an array.')
json_array_append(),如果参数 path
指向的 JSON 是数组,将在数组末尾添加元素;如果参数 path
指向的 JSON 是值或对象,该值或对象将被包裹为数组,然后在这个数组末尾添加元素。
-
mysql> set @j =
'["a", {"b": [1, 2]}, [3, 4]]';
-
-
-- path 指向的 JSON 是数组,将在数组末尾添加元素
-
mysql>
select json_array_append(@j,
'$',
5);
-
+
---------------------------------+
-
| json_array_append(@j,
'$',
5) |
-
+
---------------------------------+
-
| [
"a", {
"b": [
1,
2]}, [
3,
4],
5] |
-
+
---------------------------------+
-
1 row
in set (
0.00 sec)
-
-
-- path 指向的 JSON 是值或对象,该值或对象将被包裹为数组,然后在这个数组末尾添加元素
-
mysql>
select json_array_append(@j,
'$[1]',
5);
-
+
-----------------------------------+
-
| json_array_append(@j,
'$[1]',
5) |
-
+
-----------------------------------+
-
| [
"a", [{
"b": [
1,
2]},
5], [
3,
4]] |
-
+
-----------------------------------+
-
1 row
in set (
0.00 sec)
除了上文提到的函数,还有 json_merge_patch()、json_merge_preserve()、json_remove() 这个些函数,可以参考官方文档的介绍,本文不再一一举例说明。
索引 JSON:生成列
现在来看下根据 JSON 列查询表数据的执行计划,如下:
-
mysql>
explain
select
*
from
tbl
where
data
->
"$.id"
=
1
\G
-
***************************
1
.
row
***************************
-
id:
1
-
select_type:
SIMPLE
-
table:
tbl
-
partitions:
NULL
-
type:
ALL
-
possible_keys:
NULL
-
key:
NULL
-
key_len:
NULL
-
ref:
NULL
-
rows:
2
-
filtered:
100.00
-
Extra:
Using
where
-
1
row
in
set,
1
warning
(0.00
sec)
可以看到,因为没有加索引,访问类型是全表扫描 type: ALL
。来试下在 JSON 类型的 data
列上添加索引,会提示如下错误:
-
mysql> alter table tbl add index (
data);
-
ERROR
3152 (
42000):
JSON column '
data' cannot be used in key specification.
对于索引 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 ]:
-
Standard
MSSQL
2008
Oracle
11g
MariaDB
10
.1
MySQL
5
.7
-
--------
-----------
----------
------------
---------
-
column_name
column_name
column_name
column_name
column_name
-
[data type]
[data type]
data_type
data
type
-
GENERATED
ALWAYS
AS
AS
GENERATED
ALWAYS
AS
[GENERATED ALWAYS]
AS
[GENERATED ALWAYS]
AS
-
(expression) (expression) (expression) (expression) (expression)
-
[PERSISTENT]
[VIRTUAL]
[VIRTUAL | PERSISTENT]
[VIRTUAL | STORED]
-
[constraints]
[constraints]
[constraints]
[constraints]
[constraints]
-
[COMMENT 'string']
[COMMENT 'string']
回到正题,我们现在来试试 MySQL 的生成列:
-
-- 添加生成列
-
mysql> alter table tbl add id int
as (
data -> "$.id");
-
Query
OK,
0 rows affected (
0.15 sec)
-
Records:
0
Duplicates:
0
Warnings:
0
-
-
mysql> select * from tbl;
-
+
-----------------------------------------------+------+
-
|
data | id |
-
+
-----------------------------------------------+------+
-
| {
"id":
1,
"name":
"Will"} |
1 |
-
| {
"id":
2,
"name":
"printf(\"hello world\");"} |
2 |
-
+
-----------------------------------------------+------+
-
2 rows
in set (
0.00 sec)
上面的示例,创建生成列 id
,生成列对应的表达式是 data -> "$.id"
。现在再试试在生成列 id
上,创建索引:
-
--
在生成列上创建索引
idx_id
-
mysql>
create
index
idx_id
on
tbl
(id);
-
Query
OK,
0
rows
affected
(0.05
sec)
-
Records: 0 Duplicates: 0 Warnings:
0
-
-
--
执行计划
-
mysql>
explain
select
*
from
tbl
where
id
=
1
\G
-
***************************
1
.
row
***************************
-
id:
1
-
select_type:
SIMPLE
-
table:
tbl
-
partitions:
NULL
-
type:
ref
-
possible_keys:
idx_id
-
key:
idx_id
-
key_len:
5
-
ref:
const
-
rows:
1
-
filtered:
100.00
-
Extra:
NULL
-
1
row
in
set,
1
warning
(0.00
sec)
-
-
--
执行计划
-
mysql>
explain
select
*
from
tbl
where
data
->
"$.id"
=
1
\G
-
***************************
1
.
row
***************************
-
id:
1
-
select_type:
SIMPLE
-
table:
tbl
-
partitions:
NULL
-
type:
ref
-
possible_keys:
idx_id
-
key:
idx_id
-
key_len:
5
-
ref:
const
-
rows:
1
-
filtered:
100.00
-
Extra:
NULL
-
1
row
in
set,
1
warning
(0.00
sec)
从上面的执行计划可以看到,查询条件用 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:
-
mysql> truncate tbl;
-
mysql> insert into tbl values ('{
"b":
"c",
"a": {
"y":
1,
"x":
2}}');
-
Query
OK,
1 row affected (
0.02 sec)
-
-
mysql> select * from tbl;
-
+
-----------------------------------+
-
|
data |
-
+
-----------------------------------+
-
| {
"a": {
"x":
2,
"y":
1},
"b":
"c"} |
-
+
-----------------------------------+
-
1 row
in set (
0.00 sec)
上面的 SQL 可以看到,insert
写入时键并没有按次序排列,而用 select
将 JSON 数据反序列化读出,发现实际保存的键是有序的。排序规则是,先按字符串长度排序,若长度相同按字母排序。同样的,键关联的值,按键排序后的次序排列。对键排序,显然只能针对 JSON 对象,若要存储 JSON 数组,值按索引位置排序。
MySQL 5.7.22 新增 json_storage_size() 函数,用于返回 json 文档二进制表示占用的存储空间。先来看下 SQL 示例:
-
mysql> select json_storage_size('
"abc"');
-
+
----------------------------+
-
| json_storage_size('
"abc"') |
-
+
----------------------------+
-
|
5 |
-
+
----------------------------+
-
1 row
in set (
0.00 sec)
-
-
mysql> select json_storage_size('[
42,
"xy",
"abc"]');
-
+
----------------------------------------+
-
| json_storage_size('[
42,
"xy",
"abc"]') |
-
+
----------------------------------------+
-
|
21 |
-
+
----------------------------------------+
-
1 row
in set (
0.00 sec)
-
-
mysql> select json_storage_size('{
"b":
42,
"a":
"xy"}');
-
+
-------------------------------------------+
-
| json_storage_size('{
"b":
42,
"a":
"xy"}') |
-
+
-------------------------------------------+
-
|
24 |
-
+
-------------------------------------------+
-
1 row
in set (
0.00 sec)
WL#8132 给出了 JSON 二进制格式的 BNF 语法描述。参考这个语法描述,可以推算出上文示例中的 "abc"
、[42, "xy", "abc"]
、{"b": 42, "a": "xy"}
对应的二进制表示。先来看下 "abc"
纯量,语法推导过程如下:
-
doc
-
=>
type value // 使用产生式 doc ::= type value
-
=>
0x0c value // 使用产生式
type ::= 0x0c (utf8mb4 string 类型)
-
=>
0x0c string // 使用产生式 value ::= string
-
=>
0x0c
data-length utf8mb4-data // 使用产生式 string ::= data-length utf8mb4-data
-
=>
0x0c
0x03 utf8mb4-
data // 使用产生式 data-length ::= uint8*
-
=>
0x0c
0x03
0x61
0x62
0x63
对应的二进制值,共 5 个字节,依次为 0x0c 0x03 0x61 0x62 0x63
,其中 0x61 0x62 0x63
,就是 16 进制表示的字符串 abc
。占用 5个字节,与 json_storage_size() 函数返回的结果一致。相应的语法树如下:
从二进制的角度看,纯量 "abc"
的 JSON 二进制表示如下:
[42, "xy", "abc"]
的推导过程,如下:
-
doc
-
=>
type
value
//
使用产生式
doc
::=
type
value
-
=>
0x02
array
//
使用产生式
type
::=
0x02
(small
JSON
array
类型)
-
=>
0x02
element-count
size
value-entry*
value*
//
使用产生式
array
::=
element-count
size
value-entry*
value*
-
=>
0x02
0x03
0x00
size
value-entry*
value*
//
使用产生式
element-count
::=
uint16
(使用
little-endian)
-
=>
0x02
0x03
0x00
0x14
0x00
value-entry*
value*
//
使用产生式
size
::=
uint16
(使用
little-endian)
-
=>
0x02
0x03
0x00
0x14
0x00
type
offset-or-inlined-value
value-entry*
value*
//
使用产生式
value-entry
::=
type
offset-or-inlined-value
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
offset-or-inlined-value
value-entry*
value*
//
使用产生式
type
::=
0x06
(uint16
类型)
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
0x2a
0x00
value-entry*
value*
//
使用产生式
offset-or-inlined-value
::=
uint16
-
...
省略
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
0x2a
0x00
0x0c
0x0d
0x00
0x0c
0x10
0x00
value*
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
0x2a
0x00
0x0c
0x0d
0x00
0x0c
0x10
0x00
string
value
//
使用产生式
value
::=
string
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
0x2a
0x00
0x0c
0x0d
0x00
0x0c
0x10
0x00
data-length
utf8mb4-data
value
//
使用产生式
string
::=
data-length
utf8mb4-data
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
0x2a
0x00
0x0c
0x0d
0x00
0x0c
0x10
0x00
0x02
utf8mb4-data
value
//
使用产生式
data-length
::=
uint8*
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
0x2a
0x00
0x0c
0x0d
0x00
0x0c
0x10
0x00
0x02
0x78
0x78
value
-
...
省略
-
=>
0x02
0x03
0x00
0x14
0x00
0x06
0x2a
0x00
0x0c
0x0d
0x00
0x0c
0x10
0x00
0x02
0x78
0x79
0x03
0x61
0x62
0x63
[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
字段,官方文档给出了含义,含义如下:
-
// This field holds either the offset to where the value is stored,
-
// or the value itself if it is small enough to be inlined (that is,
-
// if it is a JSON literal or a small enough [u]int).
-
offset-or-inlined-value ::=
-
uint16 |
// if used in small JSON object/array
-
uint32
// if used in large JSON object/array
就是说,如果实际要保存的值足够小,将直接内联在这个字段中,否则将保存偏移量(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
。