文章中所有操作均是在 MySQL 5.7 版本下进行的
我们先创建示例表然后入几条测试数据：
create table tbl_json ( id int(11) not null auto_increment, info json default null, primary key (id) ) ENGINE=InnoDB default charset=utf8; insert into tbl_json (info) values ('{"age": 21, "name": "小张"}'); insert into tbl_json (info) values ('{"age": 22, "name": "小李"}'); insert into tbl_json (info) values ('{"age": 23, "name": "小王"}'); select * from tbl_json; +----+-------------------------------+ | id | info | +----+-------------------------------+ | 1 | {"age": 21, "name": "小张"} | | 2 | {"age": 22, "name": "小李"} | | 3 | {"age": 23, "name": "小王"} | +----+-------------------------------+如果你使用的工具版本比较低的话，是可能看到不到 json 类型字段的。
比如 navicat 低版本就看不到 json 类型的字段。
json_extract 函数是用来解析 json 并取 key 对应的数据。
select json_extract('{"name":"Lee","tel":"130000000"}','$.name') as name; +-------+ | name | +-------+ | "Lee" | +-------+如果没有对应的 key 会返回 NULL。
select json_extract('{"name":"Lee","tel":"130000000"}','$.age') as age; +------+ | age | +------+ | NULL | +------+试一试测试表：
select id, json_extract(info, '$.name') as name from tbl_json where id = 3; +----+----------+ | id | name | +----+----------+ | 3 | "小王" | +----+----------+json_type 函数是用来查看 key 对应的数据类型的。
select json_extract('{"name":"Lee","tel":"130000000"}','$.name') as name, json_type(json_extract('{"name":"Lee","tel":"130000000"}', '$.name')) as type; +-------+--------+ | name | type | +-------+--------+ | "Lee" | STRING | +-------+--------+试一试测试表：
select id, json_extract(info, '$.name') as name, json_type(json_extract(info, '$.name')) as type from tbl_json where id = 3; +----+----------+--------+ | id | name | type | +----+----------+--------+ | 3 | "小王" | STRING | +----+----------+--------+json_array 函数是用来生成一个 json 数据组。
select json_array('aaa', 'bbb', 'ccc') as array; +-----------------------+ | array | +-----------------------+ | ["aaa", "bbb", "ccc"] | +-----------------------+json_object 函数是用来生成一个 json 对象。
select json_object('1', 'aaa', '2', 'bbb', '3', 'ccc') as object; +--------------------------------------+ | object | +--------------------------------------+ | {"1": "aaa", "2": "bbb", "3": "ccc"} | +--------------------------------------+json_merge 函数是用来将多个 json 数据进行合并。
select json_merge(json_array('aaa', 'bbb', 'ccc'), json_object('1', 'aaa', '2', 'bbb', '3', 'ccc')) as json; +-------------------------------------------------------------+ | json | +-------------------------------------------------------------+ | ["aaa", "bbb", "ccc", {"1": "aaa", "2": "bbb", "3": "ccc"}] | +-------------------------------------------------------------+json_unqote 函数的作用就是将对应 key 的数据去掉引号。
select json_extract('{"name":"Lee","tel":"130000000"}','$.name') as name; +-------+ | name | +-------+ | "Lee" | +-------+通过json_unquote 函数处理之后：
select json_unquote(json_extract('{"name":"Lee","tel":"130000000"}','$.name')) as name; +------+ | name | +------+ | Lee | +------+试一试测试表：
select id, json_unquote(json_extract(info, '$.name')) as name from tbl_json; +----+--------+ | id | name | +----+--------+ | 1 | 小张 | | 2 | 小李 | | 3 | 小王 | +----+--------+MySQL 里的 json 查询基本是针对 json array 和 json object 进行操作。
对于 json array，在索引数据时用从 0 开始的下标进行索引， 表 示 整 个 j s o n 对 象 。 例 如 ： 表示整个 json 对象。例如： 表示整个json对象。例如：[0]、$[1]。
对于 json object，在索引数据时用 key 进行索引，含有特殊字符的 key 要用 “” 双引号括起来，比如 $.“user name”。
json_extract 函数的简便应用：json列 -> ‘$.键’ 。
json_unquote 函数的简便应用： json列 ->> ‘$.键’ 。
提取 json 字段的表达式可以用于 select 查询列表 ，where / having，order / group by 语句中，json 中的元素搜索也是严格区分变量类型。
json 类型不同于字符串，不能当作字符串直接做比较，通过 cast() 将字符串转换成 json 形式，再进行比较。
加入新数据：
insert into tbl_json (info) values ('["abc", {"xxx": [123, 456], "user name": "worker lee"}, [666, 888]]'); select * from tbl_json; +----+---------------------------------------------------------------------+ | id | info | +----+---------------------------------------------------------------------+ | 1 | {"age": 21, "name": "小张"} | | 2 | {"age": 22, "name": "小李"} | | 3 | {"age": 23, "name": "小王"} | | 4 | ["abc", {"xxx": [123, 456], "user name": "worker lee"}, [666, 888]] | +----+---------------------------------------------------------------------+ select * from tbl_json where id = 4; +----+---------------------------------------------------------------------+ | id | info | +----+---------------------------------------------------------------------+ | 4 | ["abc", {"xxx": [123, 456], "user name": "worker lee"}, [666, 888]] | +----+---------------------------------------------------------------------+ -- 查询json array的某个值 select info->'$[0]' from tbl_json where id = 4; +--------------+ | info->'$[0]' | +--------------+ | "abc" | +--------------+ select info->'$[1]' from tbl_json where id = 4; +------------------------------------------------+ | info->'$[1]' | +------------------------------------------------+ | {"xxx": [123, 456], "user name": "worker lee"} | +------------------------------------------------+ -- 查询json array中的json object select info->'$[1].xxx' from tbl_json where id = 4; +------------------+ | info->'$[1].xxx' | +------------------+ | [123, 456] | +------------------+ -- key用双引号引起来也可以 select info->'$[1]."xxx"' from tbl_json where id = 4; +--------------------+ | info->'$[1]."xxx"' | +--------------------+ | [123, 456] | +--------------------+ -- key如果含有特殊的必须用双引号引起来 /* select info->'$[1].user name' from tbl_json where id = 4; 以上命令报错，是因为“user name”这个key中间有空格属于含有特殊字符，正确如下 */ select info->'$[1]."user name"' from tbl_json where id = 4; +--------------------------+ | info->'$[1]."user name"' | +--------------------------+ | "worker lee" | +--------------------------+ -- 去掉查询数据的双引号 select info->>'$[1]."user name"' from tbl_json where id = 4; +---------------------------+ | info->>'$[1]."user name"' | +---------------------------+ | worker lee | +---------------------------+之前我们介绍了各种和 json 有关的函数操作，并且通过示例应用到了我们的 SQL 查询语句中。那现在就介绍一下 json 类型的字段是如何进行数据操作的。这里介绍的操作不是向表里增加数据，能看到这里的朋友，前面文章中也描述了如何 insert 一条数据，下面是介绍如何针对 json 类型的字段的操作。
json_insert 函数插入新字段，对于已存在的 key 无法修改值。
-- 利用json_insert函数增加一个key数据 update tbl_json set info = json_insert(info, '$.sex', '男') where id = 1; -- 查询之前的修改 select * from tbl_json where id = 1; +----+---------------------------------------------+ | id | info | +----+---------------------------------------------+ | 1 | {"age": 21, "sex": "男", "name": "小张"} | +----+---------------------------------------------+ -- 如果json_insert加入了之前已经存在的key，是无法修改原先key对应的value update tbl_json set info = json_insert(info, '$.name', '张三') where id = 1; -- 查询之前的修改，没有发生变化 select * from tbl_json where id = 1; +----+---------------------------------------------+ | id | info | +----+---------------------------------------------+ | 1 | {"age": 21, "sex": "男", "name": "小张"} | +----+---------------------------------------------+json_set 函数插入新值字段，如何存在 key 会覆盖已经存在 key 的值。
-- 利用json_set函数增加一个key数据 update tbl_json set info = json_set(info, '$.num', '1001') where id = 1; -- 查询之前的修改 select * from tbl_json where id = 1; +----+------------------------------------------------------------+ | id | info | +----+------------------------------------------------------------+ | 1 | {"age": 21, "num": "1001", "sex": "男", "name": "小张"} | +----+------------------------------------------------------------+ -- 如果json_set加入了之前已经存在的key，直接覆盖原先key对应的value update tbl_json set info = json_set(info, '$.name', '张三') where id = 1; -- 查询之前的修改 select * from tbl_json where id = 1; +----+------------------------------------------------------------+ | id | info | +----+------------------------------------------------------------+ | 1 | {"age": 21, "num": "1001", "sex": "男", "name": "张三"} | +----+------------------------------------------------------------+json_replace 函数只能用来替换原有 key 对应的数据，json_remove 函数是删除对应的 key 的 json 元素。
-- json_replace函数 update tbl_json set info = json_replace(info, '$.sex', '女') where id = 1; -- 查询之前的修改 select * from tbl_json where id = 1; +----+------------------------------------------------------------+ | id | info | +----+------------------------------------------------------------+ | 1 | {"age": 21, "num": "1001", "sex": "女", "name": "张三"} | +----+------------------------------------------------------------+ -- json_remove函数 update tbl_json set info = json_remove(info, '$.num') where id = 1; -- 查询之前的修改 select * from tbl_json where id = 1; +----+---------------------------------------------+ | id | info | +----+---------------------------------------------+ | 1 | {"age": 21, "sex": "女", "name": "张三"} | +----+---------------------------------------------+MySQL 官网文档中有段对 json 字段的说明是：
JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.
那意思就是现阶段 MySQL 不支持对 json 列进行索引，但是可以通过映射虚拟列（virtual generated column），在虚拟列上建立索引，便可参与高效检索。
在 MySQL 5.7+ 中，支持两种 Generated Column，即 Virtual Generated Column 和 Stored Generated Column。前者不存储元数据，后者会将 expression 的计算结果实际的存储下来。其实二者性能差距并不大，若对二者建立索引进行检索操作，前者性能可能会略低于后者，因为前者要对结果集即时的进行 expression 的演算，但后者需要消耗额外的存储空间。
虚拟列具体操作如下（仅供参考）：
-- 为了方便这里将之前的id=4的数据删除了 delete from tbl_json where id = 4; -- 创建虚拟列，去掉引号的name alter table tbl_json add name varchar(50) generated always as (info->>'$.name') virtual; /* 删除虚拟列？！其实就是“alter table 表名 drop 列名” 有的朋友可能通过info->'$.name'创建的是带双引号的虚拟列 在双引号的数据查询记得用 \ 转义 比如：select * from tbl_json where name="\"张三\""; */ -- 查询数据 select * from tbl_json； +----+---------------------------------------------+--------+ | id | info | name | +----+---------------------------------------------+--------+ | 1 | {"age": 21, "sex": "女", "name": "张三"} | 张三 | | 2 | {"age": 22, "sex": "男", "name": "小李"} | 小李 | | 3 | {"age": 23, "sex": "男", "name": "小王"} | 小王 | +----+---------------------------------------------+--------+ -- 添加索引 alter table tbl_json add index index_name(name); -- 检索时可以使用索引 explain select * from tbl_json where name = '张三'; -- 整理之后的打印信息，已使用了刚才创建的索引 id : 1 select_type : SIMPLE table : tbl_json partitions : NULL type : ref possible_keys : index_name key : index_name key_len : 153 ref : const rows : 1 filtered : 100.00 Extra : NULL这篇文章从开始到结束，都是作者一条一条命令试的，即使有部分内容是借鉴网络上的资源，为了负责也是每条命令去试的，保证不会出什么问题。
随着技术的进步，数据库也增加了对 json 的支持，这个支持也就是所谓的在数据库层面进行技术的更新。其实以往我们是不是可以在一个 varchar 类型存一个 json 串，通过程序获取将 json 的处理放到了程序中，我想很多朋友早就这么干了。现在 MySQL 数据库增加了 json 的支持，我们就可以多一条路子选择，是把 json 解析给数据库处理还是继续拿出来程序中解析，这个不是争论的焦点，只是选择层面而已。
