什么是JSON类型
作为DBA，可能会对这个概念稍微有点陌生，但是对于开发者来说，这是一个十分熟悉的事物。
JSON(JavaScript Object Notation, JS 对象简谱) 是一种轻量级的数据交换格式。它基于 ECMAScript (欧洲计算机协会制定的js规范)的一个子集，采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。 易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。
MySQL原始JSON类型的优势在哪？
原生的JSON优势如下：
存储上类似text,可以存非常大的数据。
存储在JSON列中的JSON文档的自动验证 。无效的文档会产生错误。
优化的存储格式。存储在JSON列中的JSON文档将 转换为内部格式，以允许对文档元素进行快速读取访问。
相比于传统形式，不需要遍历所有字符串才能找到数据。
支持索引：通过虚拟列的功能可以对JSON中部分的数据进行索引。
MySQL的JSON类型
创建JSON类型表
创建一个基础的员工表，除了工号字段外，还有一个个人基础信息字段和一个个人能力信息字段
MySQL [test]> CREATE TABLE employee (
->
-> `empno` int(10) unsigned NOT NULL AUTO_INCREMENT,
->
-> `basic_info` JSON NOT NULL,
->
-> `skill_info` JSON NOT NULL,
->
-> PRIMARY KEY (`empno`)
->
-> );
Query OK, 0 rows affected (0.02 sec)表的基础信息，其中JSON类型的字段，是不可以有默认值的，这点需要注意
MySQL [test]> desc employee;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| empno | int(10) unsigned | NO | PRI | NULL | auto_increment |
| basic_info | json | NO | | NULL | |
| skill_info | json | NO | | NULL | |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)试着插入几条数据
我们手动插入几条数据进这张表中，在前两条数据中，在个人能力信息上使用的是数组的方式，后面两条则是使用对象的形式。在MySQL5.7.8版本后的JSON类型中，这两种都是可以的。
INSERT INTO `employee` VALUES (1,"{"name": "wangyiyi", "age": "23" ,"from": "hangzhou"}", "["java", "go", "python"]");
INSERT INTO `employee` VALUES (2,"{"name": "linxue", "age": 24 ,"from": "shanghai"}", "["mysql", "oracle", "python"]");
INSERT INTO `employee` VALUES (3,"{"name": "zhaoqing", "age": 24 ,"from": "shanghai"}", "{"system": "linux","database": "mysql", "language": "python"}");
INSERT INTO `employee` VALUES (4,"{"name": "zhouxixi", "age": 30 ,"from": "nanjing"}", "{"system": ["linux","windows"],"database": ["mysql","oracle","postgresql"], "language": ["python","java","go"]}");插入多个数据后,表中内容为如下
MySQL [test]> select * from employee;
+-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| 1 | {"age": "23", "from": "hangzhou", "name": "wangyiyi"} | ["java", "go", "python"] |
| 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] |
| 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "python"} |
| 4 | {"age": 30, "from": "nanjing", "name": "zhouxixi"} | {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} |
+-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)json数据查询方式
在插入了json类型的数据之后，可以针对JSON类型做一些特定的查询，如查询年龄大于20的记录
在SQL的语句中使用 字段->键名 就可以查询出所对应的键值
MySQL [test]> select * from employee WHERE basic_info->"$.age"> 20;
+-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| 1 | {"age": "23", "from": "hangzhou", "name": "wangyiyi"} | ["java", "go", "python"] |
| 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] |
| 3 | {"age": 28, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} |
| 4 | {"age": 30, "from": "nanjing", "name": "zhouxixi"} | {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} |
+-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
MySQL [test]> select * from employee WHERE basic_info->"$.age"< 20;
Empty set (0.00 sec)除了使用上述方式外，也可使用 提取json值的 函数 json_extract （使用函数的方式）
MySQL [test]> select * from employee where json_extract(basic_info,"$.age") = 24;
+-------+-----------------------------------------------------+----------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-----------------------------------------------------+----------------------------------------------------------------+
| 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] |
| 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "python"} |
+-------+-----------------------------------------------------+----------------------------------------------------------------+
2 rows in set (0.00 sec)对于数值查询也可做一个范围内查询，如下：
MySQL [test]> select * from employee WHERE basic_info->"$.age" in (23, 24);
+-------+-------------------------------------------------------+----------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-------------------------------------------------------+----------------------------------------------------------------+
| 1 | {"age": "23", "from": "hangzhou", "name": "wangyiyi"} | ["java", "go", "python"] |
| 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] |
| 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "python"} |
+-------+-------------------------------------------------------+----------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)因为 JSON 不同于字符串，所以如果用字符串和 JSON 字段比较，是不会相等的；
如下，直接使用字符串查询，查询不出来内容
MySQL [test]> select * from employee where basic_info = "{"age": 24, "from": "shanghai", "name": "linxue"}";
Empty set (0.00 sec)可以通过 CAST 将字符串转成 JSON 的形式，如下：
MySQL [test]> select * from employee where basic_info = CAST("{"age": 24, "from": "shanghai", "name": "linxue"}" AS JSON);
+-------+---------------------------------------------------+-------------------------------+
| empno | basic_info | skill_info |
+-------+---------------------------------------------------+-------------------------------+
| 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] |
+-------+---------------------------------------------------+-------------------------------+
1 row in set (0.00 sec)查看单纯数组类型的函数JSON_CONTAINS
MySQL [test]> select * from employee where JSON_CONTAINS (skill_info,""mysql"");
+-------+---------------------------------------------------+-------------------------------+
| empno | basic_info | skill_info |
+-------+---------------------------------------------------+-------------------------------+
| 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] |
+-------+---------------------------------------------------+-------------------------------+
1 row in set (0.00 sec)JSON_PRETTY函数： 以易于阅读的格式打印出JSON值。便于在一些外部应用引用数据时，更方便的使用它
MySQL [test]> select JSON_PRETTY(basic_info) from employee;
+---------------------------------------------------------------+
| JSON_PRETTY(basic_info) |
+---------------------------------------------------------------+
| {
"age": "23",
"from": "hangzhou",
"name": "wangyiyi"
} |
| {
"age": 24,
"from": "shanghai",
"name": "linxue"
} |
| {
"age": 28,
"from": "shanghai",
"name": "zhaoqing"
} |
| {
"age": 30,
"from": "nanjing",
"name": "zhouxixi"
} |
+---------------------------------------------------------------+
4 rows in set (0.00 sec)MySQL 5.7.22中添加了此功能，此函数返回用于存储JSON文档的二进制表示的字节数，用于查看当前JSON字段的存储大小
MySQL [test]> select skill_info,JSON_STORAGE_SIZE(skill_info) AS Size from employee;
+-----------------------------------------------------------------------------------------------------------------------+------+
| skill_info | Size |
+-----------------------------------------------------------------------------------------------------------------------+------+
| ["java", "go", "python"] | 29 |
| ["mysql", "oracle", "python"] | 34 |
| {"system": "linux", "database": "mysql", "language": "go"} | 63 |
| {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} | 137 |
+-----------------------------------------------------------------------------------------------------------------------+------+
4 rows in set (0.00 sec)查询JSON字段的长度
MySQL [test]> select JSON_LENGTH(basic_info) from employee;
+-------------------------+
| JSON_LENGTH(basic_info) |
+-------------------------+
| 3 |
| 3 |
| 3 |
| 3 |
+-------------------------+
4 rows in set (0.00 sec)查看数据的类型：可以是对象，数组或标量类型
MySQL [test]> select JSON_TYPE(skill_info) from employee;
+-----------------------+
| JSON_TYPE(skill_info) |
+-----------------------+
| ARRAY |
| ARRAY |
| OBJECT |
| OBJECT |
+-----------------------+
4 rows in set (0.00 sec)json数据修改方式
如果是整个 json 更新的话，和一般类型插入是一样的
json_array_insert是在指定下标插入，这是插入一般数组类型时的操作
MySQL [test]> SELECT json_array_insert(skill_info, "$[1]", "php") from employee;
+-----------------------------------------------------------------------------------------------------------------------+
| json_array_insert(skill_info, "$[1]", "php") |
+-----------------------------------------------------------------------------------------------------------------------+
| ["java", "php", "go", "python"] |
| ["mysql", "php", "oracle", "python"] |
| {"system": "linux", "database": "mysql", "language": "python"} |
| {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} |
+-----------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)替换操作，也就是修改update操作，使用的是 json_replace 函数
json_replace：只替换已经存在的旧值，不存在则忽略；
MySQL [test]> update employee set skill_info = json_replace(skill_info, "$.language", "go") where empno = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]> select * from employee where empno = 3;
+-------+-----------------------------------------------------+------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-----------------------------------------------------+------------------------------------------------------------+
| 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} |
+-------+-----------------------------------------------------+------------------------------------------------------------+
1 row in set (0.00 sec)json_set：替换旧值，并插入不存在的新值；
MySQL [test]> update employee set basic_info = json_set(basic_info, "$.age", 28,"$.sex" ,"man") where empno = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]>
MySQL [test]> select * from employee where empno = 3;
+-------+-------------------------------------------------------------------+------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-------------------------------------------------------------------+------------------------------------------------------------+
| 3 | {"age": 28, "sex": "man", "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} |
+-------+-------------------------------------------------------------------+------------------------------------------------------------+
1 row in set (0.00 sec)json_insert：插入新值，但不替换已经存在的旧值；
MySQL [test]> update employee set basic_info = json_insert (basic_info, "$.age", 30, "$.phone" ,"123456789") where empno = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]> select * from employee where empno = 3;
+-------+-----------------------------------------------------------------------------------------+------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-----------------------------------------------------------------------------------------+------------------------------------------------------------+
| 3 | {"age": 28, "sex": "man", "from": "shanghai", "name": "zhaoqing", "phone": "123456789"} | {"system": "linux", "database": "mysql", "language": "go"} |
+-------+-----------------------------------------------------------------------------------------+------------------------------------------------------------+
1 row in set (0.00 sec)json_remove() 删除元素函数。
MySQL [test]> update employee set basic_info = json_remove (basic_info, "$.sex", "$.phone") where empno = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]>
MySQL [test]> select * from employee where empno = 3;
+-------+-----------------------------------------------------+------------------------------------------------------------+
| empno | basic_info | skill_info |
+-------+-----------------------------------------------------+------------------------------------------------------------+
| 3 | {"age": 28, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} |
+-------+-----------------------------------------------------+------------------------------------------------------------+
1 row in set (0.00 sec)结语
JSON数据类型是一个对开发十分友好的功能，有了它，MySQL的功能才更趋于完善。
