一. MySQL JSON类型

1. JSON介绍

什么是 JSON ？
- JSON 指的是 JavaScript 对象表示法（JavaScript Object Notation）
- JSON 是轻量级的文本数据交换格式
- JSON 独立于语言 *
- JSON 具有自我描述性，更易理解
MySQL5.7.8开始支持JSON数据类型。
对比存储在字符串，JSON格式的JSON数据类型提供了这些优点：
- 自动验证存储在JSON列中的JSON文档。无效的文档会产生错误。
- 优化的存储格式

官方文档(JSON类型)

2. JSON格式示例

--这个 employee 对象是包含 3 个员工记录（对象）的数组。

{

"employees": [

{ "firstName":"John" , "lastName":"Doe" },

{ "firstName":"Anna" , "lastName":"Smith" },

{ "firstName":"Peter" , "lastName":"Jones" }

]

}

3. JSON VS BLOB

JSON
- JSON数据可以做有效性检查;
- JSON使得查询性能提升;
- JSON支持部分属性索引，通过虚拟列的功能可以对JSON中的部分数据进行索引;
BLOB
- BLOB类型无法在数据库层做约束性检查;
- BLOB进行查询，需要遍历所有字符串;
- BLOB做只能做指定长度的索引;

5.7之前，只能把JSON当作BLOB进行存储。数据库层面无法对JSON数据做一些操作，只能由应用程序处理。

4.结构化和非结构化

结构化
- 二维表结构（行和列）
- 使用SQL语句进行操作
非结构化
- 使用Key-Value格式定义数据，无结构定义
- Value可以嵌套Key-Value格式的数据
- 使用JSON进行实现

--

-- SQL创建User表

--

create table user (

    id bigint not null auto_increment,

    user_name varchar(10),

    age int,

    primary key(id)

);

#

# JSON定义的User表

#

db.user.insert({

    user_name:"tom",

    age:30

})

db.createCollection("user")

5. JSON操作示例

5.1 JSON入门

--

-- 创建带json字段的表

--

CREATE TABLE t_user (

    uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(32) NOT NULL,

    email VARCHAR(128) NOT NULL,

    address VARCHAR(256) NOT NULL,

    UNIQUE KEY (name),

    UNIQUE KEY (email)

)charset=utf8mb4;

mysql> INSERT INTO t_user VALUES (NULL,'David','david@gmail','Shanghai ...');

INSERT INTO t_user VALUES (NULL,'Amy','amy@gmail','Beijing ...');

INSERT INTO t_user VALUES (NULL,'Tom','tom@gmail','Guangzhou ...');

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

mysql> select * from t_user;

+-----+-------+-------------+---------------+

| uid | name  | email       | address       |

+-----+-------+-------------+---------------+

|   1 | David | david@gmail | Shanghai ...  |

|   2 | Amy   | amy@gmail   | Beijing ...   |

|   3 | Tom   | tom@gmail   | Guangzhou ... |

+-----+-------+-------------+---------------+

3 rows in set

mysql> DROP TABLE IF EXISTS t_user_json;

    CREATE TABLE t_user_json(

	uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    data JSON

);

-- JSON_OBJECT将对象列表转化成JSON对象(key唯一,必须是偶数 key - value key - value)

mysql> insert into t_user_json

SELECT uid,JSON_OBJECT('name',name,'email',email,'address',address) AS data

FROM t_user;

Query OK, 3 rows affected

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user_json;

+-----+----------------------------------------------------------------------+

| uid | data                                                                 |

+-----+----------------------------------------------------------------------+

|   1 | {"name": "David", "email": "david@gmail", "address": "Shanghai ..."} |

|   2 | {"name": "Amy", "email": "amy@gmail", "address": "Beijing ..."}      |

|   3 | {"name": "Tom", "email": "tom@gmail", "address": "Guangzhou ..."}    |

+-----+----------------------------------------------------------------------+

3 rows in set

--JSON_EXTRACT() 是JSON提取函数，$.address 就是一个 JSON path，表示定位文档的 address 字段

JSON path 

mysql> SELECT uid,JSON_EXTRACT(data,'$.address') from t_user_json;

+-----+--------------------------------+

| uid | JSON_EXTRACT(data,'$.address') |

+-----+--------------------------------+

|   1 | "Shanghai ..."                 |

|   2 | "Beijing ..."                  |

|   3 | "Guangzhou ..."                |

+-----+--------------------------------+

3 rows in set

mysql> SELECT uid,JSON_EXTRACT(data,'$.address2') from t_user_json;

+-----+---------------------------------+

| uid | JSON_EXTRACT(data,'$.address2') |

+-----+---------------------------------+

|   1 | NULL                            |

|   2 | NULL                            |

|   3 | NULL                            |

+-----+---------------------------------+

3 rows in set

--JSON_INSERT() 是JSON 插入函数

mysql>  UPDATE t_user_json set data = json_insert(data,"$.address2","HangZhou ...") where uid = 1;

Query OK, 1 row affected

Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT uid,JSON_EXTRACT(data,'$.address1') from t_user_json;

+-----+---------------------------------+

| uid | JSON_EXTRACT(data,'$.address1') |

+-----+---------------------------------+

|   1 | NULL                            |

|   2 | NULL                            |

|   3 | NULL                            |

+-----+---------------------------------+

3 rows in set

mysql> SELECT * from t_user_json;

+-----+--------------------------------------------------------------------------------------------------+

| uid | data                                                                                             |

+-----+--------------------------------------------------------------------------------------------------+

|   1 | {"name": "David", "email": "david@gmail", "address": "Shanghai ...", "address2": "HangZhou ..."} |

|   2 | {"name": "Amy", "email": "amy@gmail", "address": "Beijing ..."}                                  |

|   3 | {"name": "Tom", "email": "tom@gmail", "address": "Guangzhou ..."}                                |

+-----+--------------------------------------------------------------------------------------------------+

3 rows in set

-- JSON_MERGE将两个或以上的JSON对象融合 

mysql> select json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2'))

from t_user_json;

+-----------------------------------------------------------------------------+

| json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2')) |

+-----------------------------------------------------------------------------+

| ["Shanghai ...", "HangZhou ..."]                                            |

| NULL                                                                        |

| NULL                                                                        |

+-----------------------------------------------------------------------------+

3 rows in set

mysql> begin;

UPDATE t_user_json set data = json_array_append(data,"$.address",JSON_EXTRACT(data,'$.address2'))

where JSON_EXTRACT(data,'$.address2') IS NOT NULL AND uid >0;

select JSON_EXTRACT(data,'$.address') from t_user_json;

UPDATE t_user_json set data = JSON_REMOVE(data,'$.address2') where uid>0;

commit;

Query OK, 0 rows affected

Query OK, 1 row affected

Rows matched: 1  Changed: 1  Warnings: 0

+----------------------------------+

| JSON_EXTRACT(data,'$.address')   |

+----------------------------------+

| ["Shanghai ...", "HangZhou ..."] |

| "Beijing ..."                    |

| "Guangzhou ..."                  |

+----------------------------------+

3 rows in set

Query OK, 1 row affected

Rows matched: 3  Changed: 1  Warnings: 0

Query OK, 0 rows affected

5.2 JSON常用函数介绍

create table demo(id int unsigned primary key auto_increment,comment json);

insert into demo(id,name) values(1,'{"programmers": [{"email": "aaaa", "lastName": "McLaughlin", "firstName": "Brett"}, {"email": "bbbb", "lastName": "Hunter", "firstName": "Jason"}]}');

-- 检查第一层的key值 json_keys

-- select json_keys(comment) from demo

-- 从JSON中提取 json_extract

-- select json_extract(comment,'$.programmers[0].email') from demo 

-- 从Json中去除元素 json_remove

-- select json_extract(comment,'$.programmers'),json_remove(comment,'$.programmers[0]') from demo

-- Json是否包含当前路径 json_contains_path

-- select json_contains_path(comment,"all","$.programmers[0].firstName") from demo 

-- 判断JSON当前路径对象类型 JSON_TYPE

-- select JSON_TYPE(comment),JSON_TYPE(comment->"$.programmers[0].firstName"),JSON_TYPE(comment->"$.programmers")="ARRAY" from demo

-- 创建数组对象 JSON_ARRAY

-- SELECT JSON_ARRAY('a', 1, RAND());

-- 将对象列表转化成JSON对象(key唯一,必须是偶数 key - value key - value) JSON_OBJECT

-- SELECT JSON_OBJECT('key1', 1, 'key2', 'abc'),JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def'); 

-- 将两个或以上的JSON对象融合 JSON_MERGE

-- SELECT JSON_MERGE('["a", 1]', '{"key": "value"}'),JSON_MERGE('{"a": 2,"b":"2"}','{"key": "value"}'),JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');

-- 用户定义的变量不能JSON数据类型 函数 COLLATION(返回字符串参数的排序方式)

-- SET @j = JSON_OBJECT('key', 'value'); SELECT CHARSET(@j), COLLATION(@j);

-- 因为utf8mb4_bin是二进制排序规则，JSON值比较是区分大小写的。

-- SELECT JSON_ARRAY('x') = JSON_ARRAY('X');

-- JSON对大小写敏感,SQL非空不敏感

-- SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL'),ISNULL(null), ISNULL(Null), ISNULL(NULL);

-- 将JSON中元素替换;如果位置不存在，则追加 JSON_SET

-- SET @j = '["a", {"b": [true, false]}, [10, 20]]';SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][3]', 3);

-- 向JSON中添加元素,原来位置存在数据不会替换 JSON_INSERT

-- SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);

-- 替换JSON中原有值,不存在的不会替换 JSON_REPLACE

-- SELECT JSON_REPLACE(@j, '$[1].b[0]', JSON_OBJECT('key', 'value'), '$[2][2]', 2);

-- 移除JSON元素列表 JSON_REMOVE

-- SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');

-- 判断JSON中是否包含'值',在这个路径下 JSON_CONTAINS

-- SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.a');

-- 去掉JSON格式""号 json_unquote , ->> 这两个是等价的

-- select json_unquote(comment->"$.programmers[1].email"), comment->>"$.programmers[1].email"from demo

--

-- json_remove 从json记录中删除数据

-- 原型 : JSON_REMOVE(json_doc, path[, path] ...)

--

mysql> set @j = '["a", ["b", "c"], "d"]';

Query OK, 0 rows affected (0.00 sec)

mysql> select json_remove(@j, '$[1]');

+-------------------------+

| json_remove(@j, '$[1]') |

+-------------------------+

| ["a", "d"]              |  -- 删除了下标为1的元素["b", "c"]

+-------------------------+

1 row in set (0.00 sec)

mysql> update user set data = json_remove(data, "$.address_2") where uid = 1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;

+-----+------------------------------------------------------+

| uid | data                                                 |

+-----+------------------------------------------------------+

|   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]}  |  -- address_2 的字段删除了

|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |

|   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |

+-----+------------------------------------------------------+

3 rows in set (0.00 sec)

官方文档(JSON函数)

5.3 JSON创建索引

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

官方文档--JSON创建索引

新建表时创建JSON索引

mysql> create table test_inex_1(

    -> data json,

    -> gen_col varchar(10) generated always as (json_extract(data, '$.name')),  -- 抽取data中的name， 生成新的一列，名字为gen_col

    -> index idx (gen_col)  -- 将gen_col 作为索引

    -> );

Query OK, 0 rows affected (0.13 sec)

mysql> show create table test_index_1；

-- -----省略表格线-----

| test_index_1 | CREATE TABLE `test_index_1` (

  `data` json DEFAULT NULL,

  `gen_col` varchar(10) GENERATED ALWAYS AS (json_extract(data, '$.name')) VIRTUAL,

  KEY `idx` (`gen_col`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

-- -----省略表格线-----

1 row in set (0.00 sec)

mysql> insert into test_index_1(data) values ('{"name":"tom", "age":18, "address":"SH"}');

Query OK, 1 row affected (0.04 sec)

mysql> insert into test_index_1(data) values ('{"name":"jim", "age":28, "address":"SZ"}');

Query OK, 1 row affected (0.03 sec)

mysql> select * from test_index_1;

+---------------------------------------------+---------+

| data                                        | gen_col |

+---------------------------------------------+---------+

| {"age": 18, "name": "tom", "address": "SH"} | "tom"   |

| {"age": 28, "name": "jim", "address": "SZ"} | "jim"   |

+---------------------------------------------+---------+

2 rows in set (0.00 sec)

mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col="tom";  -- 如果这样做，为空，原因如下

Empty set (0.00 sec)

mysql> select hex('"');

+----------+

| hex('"') |

+----------+

| 22       |  -- 双引号的 16进制

+----------+

1 row in set (0.00 sec)

mysql> select hex(gen_col) from test_index_1;

+--------------+

| hex(gen_col) |

+--------------+

| 226A696D22   |  -- 双引号本身也作为了存储内容

| 22746F6D22   |

+--------------+

2 rows in set (0.00 sec)

mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col='"tom"';  -- 使用'"tome"',用单引号括起来

+----------+

| username |

+----------+

| "tom"    |  -- 找到了对应的数据

+----------+

1 row in set (0.00 sec)

mysql> explain select json_extract(data,"$.name") as username from test_index_1 where gen_col='"tom"'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test_index_1

   partitions: NULL

         type: ref

possible_keys: idx    -- 使用了 key idx

          key: idx

      key_len: 43

          ref: const

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

---

--- 建立表的时候去掉双引用

---

mysql> create table test_index_2 (

    -> data json,

    -> gen_col varchar(10) generated always as (

    ->    json_unquote(    -- 使用json_unquote函数进行去掉双引号

    ->             json_extract(data, "$.name")

    ->    )),

    -> key idx(gen_col)

    -> );

Query OK, 0 rows affected (0.13 sec)

mysql> show create table test_index_2;

-- -----省略表格线-----

| test_index_2 | CREATE TABLE `test_index_2` (

  `data` json DEFAULT NULL,

  `gen_col` varchar(10) GENERATED ALWAYS AS (json_unquote(

            json_extract(data, "$.name")

   )) VIRTUAL,

  KEY `idx` (`gen_col`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

-- -----省略表格线-----

1 row in set (0.00 sec)

mysql> insert into test_index_2(data) values ('{"name":"tom", "age":18, "address":"SH"}');

Query OK, 1 row affected (0.03 sec)

mysql> insert into test_index_2(data) values ('{"name":"jim", "age":28, "address":"SZ"}');

Query OK, 1 row affected (0.02 sec)

mysql> select json_extract(data,"$.name") as username from test_index_2 where gen_col="tom";  -- 未加单引号

+----------+

| username |

+----------+

| "tom"    |  -- 可以找到数据

+----------+

1 row in set (0.00 sec)

mysql> explain select json_extract(data,"$.name") as username from test_index_2 where gen_col="tom"\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test_index_2

   partitions: NULL

         type: ref

possible_keys: idx   -- 使用了 key idx

          key: idx

      key_len: 43

          ref: const

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

修改已存在的表创建JSON索引

--

-- 使用之前的user表操作

--

mysql> show create table user;

-- -----省略表格线-----

| user  | CREATE TABLE `user` (

  `uid` int(11) NOT NULL AUTO_INCREMENT,

  `data` json DEFAULT NULL,

  PRIMARY KEY (`uid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |

-- -----省略表格线-----

1 row in set (0.00 sec)

mysql> select * from user;

+-----+------------------------------------------------------+

| uid | data                                                 |

+-----+------------------------------------------------------+

|   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]}  |

|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |

|   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |

+-----+------------------------------------------------------+

mysql> alter table user

    -> add user_name varchar(32)

    -> generated always as (json_extract(data,"$.name")) virtual;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

-- virtual 关键字是不将该列的字段值存储，对应的是stored

mysql> select user_name from user;

+-----------+

| user_name |

+-----------+

| "tom"     |

| "jim"     |

| "jery"    |

+-----------+

3 rows in set (0.00 sec)

mysql> alter table user add index idx(user_name);

Query OK, 0 rows affected (0.13 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from user where user_name='"tom"';  -- 加单引号

+-----+-----------------------------------------------------+-----------+

| uid | data                                                | user_name |

+-----+-----------------------------------------------------+-----------+

|   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]} | "tom"     |

+-----+-----------------------------------------------------+-----------+

1 row in set (0.00 sec)

mysql> explain select * from user where user_name='"tom"'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: user

   partitions: NULL

         type: ref

possible_keys: idx   -- 使用了 key idx

          key: idx

      key_len: 131

          ref: const

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

mysql> show create table user;

-- -----省略表格线-----

| user  | CREATE TABLE `user` (

  `uid` int(11) NOT NULL AUTO_INCREMENT,

  `data` json DEFAULT NULL,

  `user_name` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL,

  `user_name2` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL,

  PRIMARY KEY (`uid`),

  KEY `idx` (`user_name`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |

-- -----省略表格线-----

1 row in set (0.00 sec)

二. 附录

--

-- 姜boss演示JSON的SQL

--

drop table if exists User;

CREATE TABLE User (

    uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(32) NOT NULL,

    email VARCHAR(256) NOT NULL,

    address VARCHAR(512) NOT NULL,

    UNIQUE KEY (name),

    UNIQUE KEY (email)

);

INSERT INTO User VALUES (NULL,'David','david@gmail','Shanghai ...');

INSERT INTO User VALUES (NULL,'Amy','amy@gmail','Beijing ...');

INSERT INTO User VALUES (NULL,'Tom','tom@gmail','Guangzhou ...');

SELECT * FROM User;

ALTER TABLE User ADD COLUMN address2 VARCHAR(512) NOT NULL;

ALTER TABLE User ADD COLUMN passport VARCHAR(64) NOT NULL;

DROP TABLE IF EXISTS UserJson;

CREATE TABLE UserJson(

	uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    data JSON

);

truncate table UserJson;

insert into UserJson

SELECT

    uid,JSON_OBJECT('name',name,'email',email,'address',address) AS data

FROM

    User;

SELECT * FROM UserJson; 

SELECT uid,JSON_EXTRACT(data,'$.address2') from UserJson;

UPDATE UserJson

set data = json_insert(data,"$.address2","HangZhou ...")

where uid = 1;

SELECT JSON_EXTRACT(data,'$.address[1]') from UserJson;

select json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2'))

from UserJson;

begin;

UPDATE UserJson

set data = json_array_append(data,"$.address",JSON_EXTRACT(data,'$.address2'))

where JSON_EXTRACT(data,'$.address2') IS NOT NULL AND uid >0;

select JSON_EXTRACT(data,'$.address') from UserJson;

UPDATE UserJson

set data = JSON_REMOVE(data,'$.address2')

where uid>0;

commit;

巴特西

009：JSON

一. MySQL JSON类型

1. JSON介绍

2. JSON格式示例

3. JSON VS BLOB

4.结构化和非结构化

5. JSON操作示例

5.1 JSON入门

5.2 JSON常用函数介绍

5.3 JSON创建索引

二. 附录

最新文章

热门文章