此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决很多坑，才写出此文，向大神奉上膝盖html
要在MySQL中存储数据，就必须定义数据库和表结构(schema)，这是一个主要的限制。为了应对这一点，从MySQL 5.7开始，MySQL支恃了 JavaScript对象表示(JavaScriptObject Notation，JSON) 数据类型。以前，这类数据不是单独的数据类型，会被存储为字符串。新的JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。mysql
JSON文档以二进制格式存储，它提供如下功能：web
建立一个测试表sql
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)
mysql> insert into employees.emp_details (emp_no, details) -> values ('1', -> '{"location":"IN","phone":"+11800000000","email":"abc@example.com","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": "abc@example.com", "phone": "+11800000000", "address": {"pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN"} | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
可使用->和->>运算符检索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)
若是不用引号检索数据，可使用->> 运算符（推荐此方式）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提供了许多处理JSON数据的函数，让咱们看看最经常使用的几种函数。数组
想要以优雅的格式显示JSON值，请使用JSON_PRETTY()函数bash
mysql> select emp_no, json_pretty(details) -> from employees.emp_details\G *************************** 1. row *************************** emp_no: 1 json_pretty(details): { "email": "abc@example.com", "phone": "+11800000000", "address": { "pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street" }, "location": "IN" } 1 row in set (0.00 sec)
能够在WHERE子句中使用col ->> path运算符来引用JSON的某一列服务器
mysql> select emp_no, details -> from employees.emp_details -> where details ->> '$.address.pin' = "560103"; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | emp_no | details | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | {"email": "abc@example.com", "phone": "+11800000000", "address": {"pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN"} | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
也可使用JSON_CONTAINS函数查询数据。若是找到了数据，则返回1，不然返回0svg
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)
可使用三种不一样的函数来修改数据：JSON_SET()、JSON_INSERT()和JSON _REPLACE()。 在MySQL 8以前的版本中，咱们还须要对整个列进行完整的更新，这并非最佳的方法。
替换现有值并添加不存在的值
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": "abc@example.com", "phone": "+11800000000", "address": { "pin": "560100", "city": "Bangalore", "line1": "abc", "line2": "xyz street" }, "location": "IN", "nickname": "kai" } 1 row in set (0.00 sec)
插入值，但不替换现有值
在这种状况下，$.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": "abc@example.com", "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)
仅替换现有值
在这种状况下，$.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": "abc@example.com", "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)
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": "abc@example.com", "phone": "+11800000000", "address": { "pin": "560132", "city": "Bangalore", "line1": "abc", "line2": "xyz street" }, "location": "IN", "nickname": "kai" } 1 row in set (0.00 sec)
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)
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