MySQL8.0新特性—JSON增强
要在MySQL中存储数据，就必须定义数据库和表结构(schema)，这是一个主要的限制。为了应对这一点，从MySQL 5.7开始，MySQL支恃了JavaScript对象表示(JavaScriptObject Notation，JSON)数据类型。之前，这类数据不是单独的数据类型，会被存储为字符串。新的JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。
JSON文档以二进制格式存储，它提供以下功能：
- 对文档元素的快速读取访问。
- 当服务器再次读取
JSON文档时，不需要重新解析文本获取该值。 - 通过键或数组索引直接查找子对象或嵌套值，而不需要读取文档中的所有值。
一、创建数据
1.1 创建测试表
mysql> create table employees.emp_details ( -> emp_no int primary key, -> details json -> ); Query OK, 0 rows affected (0.17 sec) mysql> desc employees.emp_details; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | emp_no | int(11) | NO | PRI | NULL | | | details | json | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 1.2 插入JSON
mysql> insert into employees.emp_details (emp_no, details) -> values ('1', -> '{location:IN,phone:+11800000000,email:[email protected],address:{line1:abc,line2:xyz street,city:Bangalore,pin:560103}}' -> ); Query OK, 1 row affected (0.13 sec) mysql> select emp_no, details from employees.emp_details; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | emp_no | details | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | {email: [email protected], phone: +11800000000, address: {pin: 560103, city: Bangalore, line1: abc, line2: xyz street}, location: IN} | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 1.3 检索JSON
可以使用->和->>运算符检索JSON列的字段：
mysql> select emp_no, details -> '$.address.pin' pin -> from employees.emp_details; +--------+----------+ | emp_no | pin | +--------+----------+ | 1 | 560103 | +--------+----------+ 1 row in set (0.00 sec) 如果不用引号检索数据，可以使用->>运算符（推荐此方式）
mysql> select emp_no, details ->> '$.address.pin' pin -> from employees.emp_details; +--------+--------+ | emp_no | pin | +--------+--------+ | 1 | 560103 | +--------+--------+ 1 row in set (0.00 sec) 二、JSON函数
MySQL提供了许多处理JSON数据的函数，让我们看看最常用的几种函数。
2.1 优雅浏览
想要以优雅的格式显示JSON值，请使用JSON_PRETTY()函数
mysql> select emp_no, json_pretty(details) -> from employees.emp_details\G *************************** 1. row *************************** emp_no: 1 json_pretty(details): { email: [email protected], phone: +11800000000, address: { pin: 560103, city: Bangalore, line1: abc, line2: xyz street }, location: IN } 1 row in set (0.00 sec) 2.2 查找
可以在WHERE子句中使用col ->> path运算符来引用JSON的某一列
mysql> select emp_no, details -> from employees.emp_details -> where details ->> '$.address.pin' = 560103; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | emp_no | details | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | {email: [email protected], phone: +11800000000, address: {pin: 560103, city: Bangalore, line1: abc, line2: xyz street}, location: IN} | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 也可以使用JSON_CONTAINS函数查询数据。如果找到了数据，则返回1，否则返回0
mysql> select json_contains(details ->> '$.address.pin',560103) -> from employees.emp_details; +-----------------------------------------------------+ | json_contains(details ->> '$.address.pin',560103) | +-----------------------------------------------------+ | 1 | +-----------------------------------------------------+ 1 row in set (0.00 sec) 如何查询一个key？使用JSON_CONTAINS_PATH函数检查address.line1是否存在
mysql> select json_contains_path(details, 'one', $.address.line1) -> from employees.emp_details; +-------------------------------------------------------+ | json_contains_path(details, 'one', $.address.line1) | +-------------------------------------------------------+ | 1 | +-------------------------------------------------------+ 1 row in set (0.00 sec) one表示至少应该存在一个键，检查address.line1或者address.line2是否存在
mysql> select json_contains_path(details, 'one', $.address.line1, $.address.line2) -> from employees.emp_details; +--------------------------------------------------------------------------+ | json_contains_path(details, 'one', $.address.line1, $.address.line2) | +--------------------------------------------------------------------------+ | 1 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec) 如果要检查address.line1或者address.line5是否同时存在，可以使用all，而不是one
mysql> select json_contains_path(details, 'all', $.address.line1, $.address.line5) -> from employees.emp_details; +--------------------------------------------------------------------------+ | json_contains_path(details, 'all', $.address.line1, $.address.line5) | +--------------------------------------------------------------------------+ | 0 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec) 2.3 修改
可以使用三种不同的函数来修改数据：JSON_SET()、JSON_INSERT()和JSON_REPLACE()。在MySQL 8之前的版本中，我们还需要对整个列进行完整的更新，这并不是最佳的方法。
2.3.1 JSON_SET()
替换现有值并添加不存在的值
mysql> update employees.emp_details -> set details = json_set(details, $.address.pin, 560100, $.nickname,kai) -> where emp_no = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select emp_no, json_pretty(details) -> from employees.emp_details\G *************************** 1. row *************************** emp_no: 1 json_pretty(details): { email: [email protected], phone: +11800000000, address: { pin: 560100, city: Bangalore, line1: abc, line2: xyz street }, location: IN, nickname: kai } 1 row in set (0.00 sec) 2.3.2 JSON_INSERT()
插入值，但不替换现有值
在这种情况下，$.address.pin不会被更新，只会添加一个新的字段$.address.line4
mysql> update employees.emp_details -> set details = json_insert(details, $.address.pin, 560132, $.address.line4,A Wing) -> where emp_no = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select emp_no, json_pretty(details) -> from employees.emp_details\G *************************** 1. row *************************** emp_no: 1 json_pretty(details): { email: [email protected], phone: +11800000000, address: { pin: 560100, city: Bangalore, line1: abc, line2: xyz street, line4: A Wing }, location: IN, nickname: kai } 1 row in set (0.01 sec) 2.3.3 JSON_REPLACE()
仅替换现有值
在这种情况下，$.address.line5不会被添加，只有$.address.pin会被更新
mysql> update employees.emp_details -> set details = json_replace(details, $.address.pin, 560132, $.address.line5,Landmark) -> where emp_no = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select emp_no, json_pretty(details) -> from employees.emp_details\G *************************** 1. row *************************** emp_no: 1 json_pretty(details): { email: [email protected], phone: +11800000000, address: { pin: 560132, city: Bangalore, line1: abc, line2: xyz street, line4: A Wing }, location: IN, nickname: kai } 1 row in set (0.00 sec) 2.4 删除
JSON_REMOVE能从JSON文档中删除数据
mysql> update employees.emp_details -> set details = json_remove(details, $.address.line4) -> where emp_no = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select emp_no, json_pretty(details) -> from employees.emp_details\G *************************** 1. row *************************** emp_no: 1 json_pretty(details): { email: [email protected], phone: +11800000000, address: { pin: 560132, city: Bangalore, line1: abc, line2: xyz street }, location: IN, nickname: kai } 1 row in set (0.00 sec) 2.5 其他函数
JSON_KEYS()：获取JSON文档中的所有键
mysql> select json_keys(details),json_keys(details ->> $.address) -> from employees.emp_details -> where emp_no= 1; +-------------------------------------------------------+------------------------------------+ | json_keys(details) | json_keys(details ->> $.address) | +-------------------------------------------------------+------------------------------------+ | [email, phone, address, location, nickname] | [pin, city, line1, line2] | +-------------------------------------------------------+------------------------------------+ 1 row in set (0.00 sec) JSON_LENGTH()：给出JSON文档中的元素数
mysql> select json_length(details), json_length(details ->> $.address) -> from employees.emp_details -> where emp_no= 1; +----------------------+--------------------------------------+ | json_length(details) | json_length(details ->> $.address) | +----------------------+--------------------------------------+ | 5 | 4 | +----------------------+--------------------------------------+ 1 row in set (0.00 sec) 延伸阅读： https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html