MySQL 如何存储 JSON|极客笔记

MySQL 如何存储 JSON

MySQL是一种流行的关系型数据库管理系统，它提供了许多功能和特性来帮助开发人员有效地管理和存储数据。其中一个比较常见的需求是存储和操作JSON数据，在MySQL中，我们可以使用JSON数据类型来存储和操作JSON格式的数据。本文将详细介绍如何在MySQL中存储JSON数据。

什么是 JSON

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，它基于JavaScript的语法，但是独立于编程语言。JSON被广泛应用于Web开发中，用来在客户端和服务器之间传递数据。

JSON数据由键值对组成，用花括号{}表示对象，键值对之间使用冒号:分隔，多个键值对用逗号,分隔。以下是一个简单的JSON对象的示例：

{
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
}

MySQL 的 JSON 数据类型

MySQL 5.7版本引入了对JSON数据类型的支持，可以用来存储JSON格式的数据。JSON数据类型支持验证和存储JSON数据，并提供了许多用于操作JSON数据的函数和操作符。

JSON 数据类型的定义

在MySQL中，JSON数据类型可以用来声明表的列为JSON类型。JSON类型的列可以存储JSON格式的数据，可以是一个对象、数组或其他合法的JSON数据。

JSON类型的定义方式如下：

columnName JSON [GENERATED ALWAYS] [AS generatedColumn]

其中，columnName为列名，GENERATED ALWAYS表示生成的列，AS generatedColumn用于生成虚拟列。

JSON 数据类型的属性

JSON数据类型的列可以有以下属性：

STRICT – 严格模式，启用了严格模式的列会对存储的数据进行验证
AS – 生成的列，可以基于其他列生成虚拟列

存储 JSON 数据

在MySQL中存储JSON数据可以直接使用JSON数据类型的列。下面我们通过一个示例来演示如何创建表并存储JSON数据。

创建表

首先，我们创建一个表来存储JSON数据：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    info JSON
);

这里创建了一个名为users的表，包含两个列id和info，其中info列的数据类型为JSON。

插入 JSON 数据

接着，我们向表中插入JSON数据：

INSERT INTO users (info) VALUES 
('{"name": "Bob", "age": 30, "email": "bob@example.com"}'),
('{"name": "Alice", "age": 25, "email": "alice@example.com"}');

这里分别插入了两条JSON格式的数据。

查询 JSON 数据

我们可以使用一些函数和操作符来查询JSON数据的字段，如下所示：

SELECT info->' $.name' AS name, info->'$ .age' AS age FROM users;

这里使用->操作符来访问JSON对象的字段。

操作 JSON 数据

MySQL提供了很多函数和操作符来操作JSON数据，下面列举一些常用的函数和操作符：

JSON_EXTRACT() – 从JSON数据中提取字段的值
JSON_SET() – 设置JSON数据中的字段值
JSON_INSERT() – 在JSON数据中插入新字段
JSON_REMOVE() – 移除JSON数据中的字段
JSON_SEARCH() – 在JSON数据中查找字段

示例

下面我们通过一个示例来演示如何使用这些函数来操作JSON数据。

创建一个带有JSON数据的表

首先，我们创建一个表来存储JSON数据：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

插入 JSON 数据

接着，我们向表中插入JSON数据：

INSERT INTO products (data) VALUES 
('{"name": "Phone", "price": 500}'),
('{"name": "Laptop", "price": 1200}');

查询 JSON 数据

我们可以查询JSON数据的字段，如下所示：

SELECT data->' $.name' AS name, data->'$ .price' AS price FROM products;

更新 JSON 数据

我们可以使用JSON_SET()函数来更新JSON数据的字段值，如下所示：

UPDATE products SET data = JSON_SET(data, ' $.price', 600) WHERE data->'$ .name' = 'Phone';

删除 JSON 数据

我们可以使用JSON_REMOVE()函数来删除JSON数据的字段，如下所示：

UPDATE products SET data = JSON_REMOVE(data, ' $.price') WHERE data->'$ .name' = 'Phone';

总结

在MySQL中存储和操作JSON数据可以通过JSON数据类型和相应的函数和操作符来实现。JSON数据类型可用于声明列的数据类型为JSON，便于存储和操作JSON数据。

MySQL 如何存储 JSON