MySQL数据类型之JSON

MySQL数据类型之JSON

SON类型是MySQL 5.7版本新增的数据类型，用好JSON数据类型可以有效解决很多业务中实际问题。

使用JSON数据类型，推荐用MySQL 8.0.17以上的版本，性能更好，同时也支持Multi-Valued Indexes；

JSON数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；

不要将有明显关系型的数据用JSON存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；

JSON数据类型推荐使用在不经常更新的静态数据存储。

JSON数据类型

mysql> create table json_user(id int primary key auto_increment, data json); Query OK, 0 rows affected (0.09 sec) mysql> insert into json_user values(0, '{"name":"morris","age":18,"address":"china"}'); Query OK, 1 row affected (0.03 sec) mysql> insert into json_user values(0, '{"name":"tom","age":16,"mail":"tomcat@google "}'); Query OK, 1 row affected (0.01 sec) mysql> select * from json_user; +----+---------------------------------------------------------+ | id | data | +----+---------------------------------------------------------+ | 1 | {"age": 18, "name": "morris", "address": "china"} | | 2 | {"age": 16, "mail": "tomcat@google ", "name": "tom"} | +----+---------------------------------------------------------+ 2 rows in set (0.01 sec)

常见JSON函数的使用 json_extract抽取字段

mysql> select json_extract('[1, 2, 3, [4, 5]]', '$[1]'); +-------------------------------------------+ | json_extract('[1, 2, 3, [4, 5]]', '$[1]') | +-------------------------------------------+ | 2 | +-------------------------------------------+ 1 row in set (0.02 sec) mysql> select json_extract('{"age": 18, "name": "morris", "address": "china"}', '$.name'); +-----------------------------------------------------------------------------+ | json_extract('{"age": 18, "name": "morris", "address": "china"}', '$.name') | +-----------------------------------------------------------------------------+ | "morris" | +-----------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select data->"$.name" from json_user; +----------------+ | data->"$.name" | +----------------+ | "morris" | | "tom" | +----------------+ 2 rows in set (0.00 sec)

json_object将对象转为json

mysql> select json_object('name','bob','age','22','email','bob@sina '); +-------------------------------------------------------------+ | json_object('name','bob','age','22','email','bob@sina ') | +-------------------------------------------------------------+ | {"age": "22", "name": "bob", "email": "bob@sina "} | +-------------------------------------------------------------+ 1 row in set (0.01 sec)

json_insert插入数据 key存在则忽略，不存在则插入。

mysql> select json_insert('{"age": 18, "name": "morris", "address": "china"}', '$.male', 'male', '$.age', 22); +-------------------------------------------------------------------------------------------------+ | json_insert('{"age": 18, "name": "morris", "address": "china"}', '$.male', 'male', '$.age', 22) | +-------------------------------------------------------------------------------------------------+ | {"age": 18, "male": "male", "name": "morris", "address": "china"} | +-------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

json_merge合并数据 json_merge（已不推荐使用）与json_merge_preserve类似，相同的key会合并为数组。

json_merge_patch中相同的key会使用后面的key替换前面的key。

mysql> select json_merge_patch('{"name": "enjoy"}', '{"id": 47, "name":"morris"}'); +----------------------------------------------------------------------+ | json_merge_patch('{"name": "enjoy"}', '{"id": 47, "name":"morris"}') | +----------------------------------------------------------------------+ | {"id": 47, "name": "morris"} | +----------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_merge('{"name": "enjoy"}', '{"id": 47, "name":"morris"}'); +----------------------------------------------------------------+ | json_merge('{"name": "enjoy"}', '{"id": 47, "name":"morris"}') | +----------------------------------------------------------------+ | {"id": 47, "name": ["enjoy", "morris"]} | +----------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select json_merge_preserve('{"name": "enjoy"}', '{"id": 47, "name":"morris"}'); +-------------------------------------------------------------------------+ | json_merge_preserve('{"name": "enjoy"}', '{"id": 47, "name":"morris"}') | +-------------------------------------------------------------------------+ | {"id": 47, "name": ["enjoy", "morris"]} | +-------------------------------------------------------------------------+ 1 row in set (0.00 sec)

json_array_append后面追加元素

mysql> select json_array_append('["a", "b", "c"]', "$", "x"); +------------------------------------------------+ | json_array_append('["a", "b", "c"]', "$", "x") | +------------------------------------------------+ | ["a", "b", "c", "x"] | +------------------------------------------------+ 1 row in set (0.00 sec)

json_array_insert里面插入元素

mysql> select json_array_insert('["a", "b", "c"]', "$[1]", "x"); +---------------------------------------------------+ | json_array_insert('["a", "b", "c"]', "$[1]", "x") | +---------------------------------------------------+ | ["a", "x", "b", "c"] | +---------------------------------------------------+ 1 row in set (0.00 sec)

json_contains包含某个json子串

mysql> select json_contains('{"a":1,"b":4}','{"a":1}'); +------------------------------------------+ | json_contains('{"a":1,"b":4}','{"a":1}') | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_contains('{"age": 18, "name": "morris", "address": "china"}', '18', '$.name'); +------------------------------------------------------------------------------------+ | json_contains('{"age": 18, "name": "morris", "address": "china"}', '18', '$.name') | +------------------------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

json_array创建json数组

mysql> select json_array("a", "b", "c", "d"); +--------------------------------+ | json_array("a", "b", "c", "d") | +--------------------------------+ | ["a", "b", "c", "d"] | +--------------------------------+ 1 row in set (0.00 sec)

json_contains_path判断json是否包含某个key

mysql> select json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e'); +----------------------------------------------------------------------------+ | json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') | +----------------------------------------------------------------------------+ | 1 | +----------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e'); +----------------------------------------------------------------------------+ | json_contains_path('{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') | +----------------------------------------------------------------------------+ | 0 | +----------------------------------------------------------------------------+ 1 row in set (0.00 sec)

json_keys查询json所有的key

mysql> select json_keys('{"a": 1, "b": 2, "c": {"d": 4}}'); +----------------------------------------------+ | json_keys('{"a": 1, "b": 2, "c": {"d": 4}}') | +----------------------------------------------+ | ["a", "b", "c"] | +----------------------------------------------+ 1 row in set (0.00 sec)

json_pretty格式化json

mysql> select json_pretty('{"a": 1, "b": 2, "c": {"d": 4}}'); +-------------------------------------------------+ | json_pretty('{"a": 1, "b": 2, "c": {"d": 4}}') | +-------------------------------------------------+ | { "a": 1, "b": 2, "c": { "d": 4 } } | +-------------------------------------------------+ 1 row in set (0.00 sec)

json_depth查询json的深度

mysql> select json_depth('{"a": 1, "b": 2, "c": {"d": 4}}'); +-----------------------------------------------+ | json_depth('{"a": 1, "b": 2, "c": {"d": 4}}') | +-----------------------------------------------+ | 3 | +-----------------------------------------------+ 1 row in set (0.00 sec)

json_length返回json元素的个数

mysql> select json_length('{"a": 1, "b": 2, "c": {"d": 4}}'); +------------------------------------------------+ | json_length('{"a": 1, "b": 2, "c": {"d": 4}}') | +------------------------------------------------+ | 3 | +------------------------------------------------+ 1 row in set (0.00 sec) ————————————————

json_remove根据key删除元素

mysql> select json_remove('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c"); +-------------------------------------------------------+ | json_remove('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c") | +-------------------------------------------------------+ | {"a": 1, "b": 2} | +-------------------------------------------------------+ 1 row in set (0.00 sec)

json_replace替换元素

mysql> select json_replace('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c", "cc"); +--------------------------------------------------------------+ | json_replace('{"a": 1, "b": 2, "c": {"d": 4}}', "$.c", "cc") | +--------------------------------------------------------------+ | {"a": 1, "b": 2, "c": "cc"} | +--------------------------------------------------------------+ 1 row in set (0.01 sec)

json_search搜索元素

mysql> select json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc'); +--------------------------------------------------------------------------------------+ | json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc') | +--------------------------------------------------------------------------------------+ | "$[0]" | +--------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc'); +--------------------------------------------------------------------------------------+ | json_search('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc') | +--------------------------------------------------------------------------------------+ | ["$[0]", "$[2].x"] | +--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

json_set往json中插入元素 存在则覆盖，不存在则新增。

mysql> select json_set('{"a": 1, "b": 2, "c": {"d": 4}}', "$.a", "aaa", "$.e", "eee"); +-------------------------------------------------------------------------+ | json_set('{"a": 1, "b": 2, "c": {"d": 4}}', "$.a", "aaa", "$.e", "eee") | +-------------------------------------------------------------------------+ | {"a": "aaa", "b": 2, "c": {"d": 4}, "e": "eee"} | +-------------------------------------------------------------------------+ 1 row in set (0.00 sec)

json_storage_size查看json存储占用的空间大小

mysql> select json_storage_size('{"a": 1, "b": 2, "c": {"d": 4}}'); +------------------------------------------------------+ | json_storage_size('{"a": 1, "b": 2, "c": {"d": 4}}') | +------------------------------------------------------+ | 41 | +------------------------------------------------------+ 1 row in set (0.00 sec)

json_type查询json的类型

mysql> select json_type('{"a": 1, "b": 2, "c": {"d": 4}}'); +----------------------------------------------+ | json_type('{"a": 1, "b": 2, "c": {"d": 4}}') | +----------------------------------------------+ | OBJECT | +----------------------------------------------+ 1 row in set (0.01 sec)

json_valid判断json是否是有效的

mysql> select json_valid('{"a": 1, "b": 2, "c": {"d": 4}}'); +-----------------------------------------------+ | json_valid('{"a": 1, "b": 2, "c": {"d": 4}}') | +-----------------------------------------------+ | 1 | +-----------------------------------------------+ 1 row in set (0.00 sec)

JSON索引 JSON类型数据本身无法直接创建索引，需要将需要索引的JSON数据重新生成虚拟列（Virtual Columns）之后，对该列进行索引。

mysql> create table test_json_index(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (json_extract(data, '$.name'))); mysql> insert into test_json_index(data) values('{"name":"morris","age":18,"address":"china"}'); mysql> insert into test_json_index(data) values('{"name":"tom","age":16,"mail":"tomcat@google "}'); mysql> select * from test_json_index; +----+---------------------------------------------------------+----------+ | id | data | gen_col | +----+---------------------------------------------------------+----------+ | 1 | {"age": 18, "name": "morris", "address": "china"} | "morris" | | 2 | {"age": 16, "mail": "tomcat@google ", "name": "tom"} | "tom" | +----+---------------------------------------------------------+----------+ mysql> select * from test_json_index where gen_col='morris'; -- 查不到数据 mysql> select * from test_json_index where gen_col='"morris"'; +----+---------------------------------------------------+----------+ | id | data | gen_col | +----+---------------------------------------------------+----------+ | 1 | {"age": 18, "name": "morris", "address": "china"} | "morris" | +----+---------------------------------------------------+----------+

要想在查询时不加引号，可以在加索引时使用json_unquote去除引号。

mysql> create table test_json_index2(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (json_unquote(json_extract(data, '$.name')))); Query OK, 0 rows affected (0.08 sec) mysql> create table test_json_index3(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (JSON_UNQUOTE(data->'$.name'))); Query OK, 0 rows affected (0.08 sec) mysql> create table test_json_index4(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (data->>'$.name')); Query OK, 0 rows affected (0.05 sec)