MySQL 导入JSON

在现代的Web应用程序中，使用JSON作为数据交换格式变得越来越流行。MySQL是最常用的关系型数据库之一，可以轻松地在应用程序中使用JSON格式。在本文中，我们将学习如何将JSON数据导入MySQL中。

阅读更多：MySQL 教程

准备工作

在我们开始实现之前，有一些要求需要满足。

MySQL版本

首先，确保安装的MySQL版本是5.7.8或更高版本。在这之前的版本，不支持JSON数据类型。

可以使用以下命令检查MySQL版本：

mysql -V

创建数据库

在我们开始导入JSON数据之前，必须在MySQL中创建一个数据库。

可以使用以下命令创建一个名为”my_database”的数据库：

CREATE DATABASE my_database;

创建数据表

下一步是创建一个表来存储我们的JSON数据。

创建一个名为”my_table”的表：

USE my_database;

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

在上面的表中，有两个列。第一个列是”id”，它是自动递增的，用作主键。第二个列是”data”，它将存储JSON数据。

导入JSON数据

到这里，我们已经准备好在MySQL中导入JSON数据。下面将介绍两种不同的方法。

方法一：使用INSERT语句

可以使用INSERT语句将JSON数据插入MySQL表。

以下是一个包含JSON数据的示例：

{
    "name": "John Doe",
    "age": 30,
    "city": "New York"
}

运行以下命令，将这段JSON数据插入到”my_table”表中：

INSERT INTO my_table (data)
VALUES ('{"name": "John Doe", "age": 30, "city": "New York"}');

我们可以通过以下命令来查询表中的数据：

SELECT * FROM my_table;

查询结果应该类似于：

+----+---------------------------------------------------------------+
| id | data                                                          |
+----+---------------------------------------------------------------+
|  1 | {"name": "John Doe", "age": 30, "city": "New York"}            |
+----+---------------------------------------------------------------+

方法二：使用LOAD DATA INFILE语句

除了使用INSERT语句外，还可以使用LOAD DATA INFILE语句将JSON数据导入MySQL中。这个方法适用于JSON文件较大时。

有两个要求必须满足。第一，JSON文件必须是单行格式，也就是说，必须在一行中包含完整的JSON数据。第二，在MySQL服务器上必须具有文件读取权限。

考虑到上面的要求，我们需要创建一个包含JSON数据的文本文件，如下所示：

{"name": "John Doe", "age": 30, "city": "New York"}
{"name": "Jane Doe", "age": 28, "city": "Los Angeles"}
{"name": "Bob Smith", "age": 35, "city": "San Francisco"}

现在，我们可以使用LOAD DATA INFILE命令将JSON数据导入MySQL中。

运行以下命令，导入JSON数据：

LOAD DATA INFILE '/path/to/jsonfile'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(data);

在上面的命令中，我们使用了FIELDS TERMINATED BY和LINES TERMINATED BY子句，以指定字段分隔符和行结束符。注意，我们将”data”列作为目标列。

我们可以通过以下命令来查询表中的数据：

SELECT * FROM my_table;

查询结果应该类似于：

+----+---------------------------------------------------------------+
| id | data                                                          |
+----+---------------------------------------------------------------+
|  1 | {"name": "John Doe", "age": 30, "city": "New York"}            |
|  2 | {"name": "Jane Doe", "age": 28, "city": "Los Angeles"} |
|  3 | {"name": "Bob Smith", "age": 35, "city": "San Francisco"}        |
+----+---------------------------------------------------------------+

列查询和过滤

现在我们已经成功地将JSON数据导入MySQL中，让我们看一下如何查询和过滤数据。

查询整个JSON对象

要查询整个JSON对象，可以直接使用SELECT语句并指定”data”列。

例如，运行以下命令：

SELECT data FROM my_table;

查询结果应该类似于：

+---------------------------------------------------------------+
| data                                                          |
+---------------------------------------------------------------+
| {"name": "John Doe", "age": 30, "city": "New York"}            |
| {"name": "Jane Doe", "age": 28, "city": "Los Angeles"}         |
| {"name": "Bob Smith", "age": 35, "city": "San Francisco"}       |
+---------------------------------------------------------------+

查询JSON对象的特定属性

要查询JSON对象的特定属性，可以使用MySQL的JSON_EXTRACT函数。在函数中指定JSON对象和属性路径。

例如，运行以下命令：

SELECT JSON_EXTRACT(data, '$.name') AS name FROM my_table;

查询结果应该类似于：

+-----------+
| name      |
+-----------+
| John Doe  |
| Jane Doe  |
| Bob Smith |
+-----------+

过滤JSON对象的特定属性

要过滤JSON对象的特定属性，可以使用MySQL的JSON_CONTAINS函数。在函数中指定JSON对象和属性值。

例如，运行以下命令：

SELECT data FROM my_table
WHERE JSON_CONTAINS(data, '{"name": "John Doe"}');

查询结果应该类似于：

+---------------------------------------------------------------+
| data                                                          |
+---------------------------------------------------------------+
| {"name": "John Doe", "age": 30, "city": "New York"}            |
+---------------------------------------------------------------+

在上面的命令中，我们使用WHERE子句来指定要过滤的属性和值。