Mysql 5.7 新特性之 json 类型的增删改查操作和用法

什么是json类型

JSON是JavaScript Object Notation的缩写，是一种用于数据交换的轻量级文本格式。MySQL 5.7支持JSON类型，可以在表的列中存储JSON格式的数据，这些数据可以在MySQL中进行查询和修改，支持JSON文档中的各种数据类型如字符串、数字、数组和对象。

json类型的创建

要使用JSON类型，必须在创建表时包含JSON类型，可以使用以下语法创建带有JSON类型的表：

CREATE TABLE tablename (
  columnname JSON,
  ...
)

json类型的增删改查操作

json类型数据在MySQL中可以直接进行增删改查等操作。

插入数据

使用INSERT INTO语句可以插入json类型数据，例如：

INSERT INTO tablename(columnname) VALUES('{"name":"Tom","age":18,"gender":"Male"}');

查询数据

使用SELECT语句可以查询json类型数据，可以使用JSON_EXTRACT函数来获取特定的数据，例如：

SELECT JSON_EXTRACT(columnname, '$.name') AS name, JSON_EXTRACT(columnname, '$.age') AS age
FROM tablename;

此时返回：

+------+------+ 
| name | age  | 
+------+------+ 
| Tom  | 18   |
+------+------+ 

更新数据

使用UPDATE语句可以更新json类型数据，可以使用JSON_SET函数来更新特定的数据，例如：

UPDATE tablename
SET columnname = JSON_SET(columnname, '$.age', 20)
WHERE JSON_EXTRACT(columnname, '$.name') = 'Tom';

此时会将age从18更改为20

删除数据

使用DELETE语句可以删除json类型数据，例如：

DELETE FROM tablename 
WHERE JSON_EXTRACT(columnname, '$.name') = 'Tom';

该语句将删除name为Tom的json类型数据。

示例

以下是一些实际示例：

示例1

创建一个含有json类型字段的表：

CREATE TABLE person_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    info JSON
);

向person_info表中插入一个json类型数据：

INSERT INTO person_info(name, info)
VALUES('Tom', '{"age": 18, "gender":"Male", "hobbies":["reading", "basketball"]}'); 

查询person_info表中所有人的年龄和性别：

SELECT name, JSON_EXTRACT(info, '$.age') AS age, JSON_EXTRACT(info, '$.gender') AS gender 
FROM person_info;

示例2

创建一个含有json类型字段的表：

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    courses JSON
);

向student表中插入一个json类型数据：

INSERT INTO student(name, courses) 
VALUES('Tom', '{"math":{"score": 90}, "english":{"score":80}}');

更新Tom的英语成绩：

UPDATE student 
SET courses = JSON_SET(courses, '$.english.score', 85) 
WHERE name = 'Tom';

查询Tom的全部课程分数：

SELECT name, JSON_EXTRACT(courses, '$.math.score') AS math_score, JSON_EXTRACT(courses, '$.english.score') AS eng_score 
FROM student 
WHERE name = 'Tom';

示例3

创建一个含有json类型字段的表：

CREATE TABLE order_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer VARCHAR(30) NOT NULL,
    ordered JSON
);

向order_info表中插入一个json类型数据：

INSERT INTO order_info(customer, ordered)
VALUES('Tom', '[{"product": "book", "price": 10}, {"product": "pen", "price":2}]');

查询Tom的所有订单中的产品和价格：

SELECT JSON_EXTRACT(ordered, '$[0].product') AS product, JSON_EXTRACT(ordered, '$[0].price') AS price 
FROM order_info 
WHERE customer = 'Tom';

以上就是Mysql 5.7 新特性之 json 类型的增删改查操作和用法的完整攻略。