MySQL数据类型之JSON

比眉伴天荒 2023-10-16 14:22 8阅读 0赞

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.com"}');
    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.com", "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.com');
    +-------------------------------------------------------------+
    | json_object('name','bob','age','22','email','bob@sina.com') |
    +-------------------------------------------------------------+
    | {
        "age": "22", "name": "bob", "email": "bob@sina.com"}       |
    +-------------------------------------------------------------+
    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.com"}');
    mysql> select * from test_json_index;
    +----+---------------------------------------------------------+----------+
    | id | data                                                    | gen_col  |
    +----+---------------------------------------------------------+----------+
    |  1 | {
        "age": 18, "name": "morris", "address": "china"}       | "morris" |
    |  2 | {
        "age": 16, "mail": "tomcat@google.com", "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)