将JSON文件导入MySQL 5.7

有几个选项是：

1. 13.2.6 加载数据语法
2. 7.2 JSON导入实用程序

测试：

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.26 MySQL Community Server (GPL)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `test`.`example_table`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `test`.`example_table` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `json_data` JSON NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

1. 加载数据

文件：/path/to/file/sample.json

{"price": null, "sale_list": [{"buyer": "SmackMe089", "date": "April 29th 2019 21:20:50", "id": "1234", "item_desc": ""}]}

MySQL命令行客户端

mysql> LOAD DATA INFILE '/path/to/file/sample.json'
    -> INTO TABLE `test`.`example_table` (`json_data`);
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT `id`, `json_data`
    -> FROM `test`.`example_table`\G
*************************** 1. row ***************************
       id: 1
json_data: {"price": null, "sale_list": [{"id": "1234", "date": "April 29th 2019 21:20:50", "buyer": "SmackMe089", "item_desc": ""}]}
1 row in set (0.00 sec)

2. JSON导入实用程序

文件：/path/to/file/sample.json

{
    "price": null,
    "sale_list": [
        {
            "buyer": "SmackMe089",
            "date": "April 29th 2019 21:20:50",
            "id": "1234",
            "item_desc": ""
        }
    ]
}

MySQL Shell：正如@TimBiegeleisen所说，您可以使用 MySQL Shell（即使使用 MySQL 5.7），但您必须激活 X 插件：

$ mysqlsh --sql
MySQL Shell 8.0.16

Your MySQL connection id is 2 (X protocol)
Server version: 5.7.26 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.

MySQL 127.0.0.1:15726+ SQL > SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.0004 sec)

MySQL 127.0.0.1:15726+ SQL > SELECT `id`, `json_data`
                          -> FROM `test`.`example_table`\G
*************************** 1. row ***************************
       id: 1
json_data: {"price": null, "sale_list": [{"id": "1234", "date": "April 29th 2019 21:20:50", "buyer": "SmackMe089", "item_desc": ""}]}
1 row in set (0.00 sec)

MySQL 127.0.0.1:15726+ SQL > \js
Switching to JavaScript mode...

MySQL 127.0.0.1:15726+ JS > util.importJson('/path/to/file/sample.json', {schema: 'test', table: 'example_table', tableColumn: 'json_data'});
Importing from file "/path/to/file/sample.json" to table `test`.`example_table` in MySQL Server at 127.0.0.1:15726

.. 1.. 1
Processed 204 bytes in 1 document in 0.0007 sec (1.36K documents/s)
Total successfully imported documents 1 (1.36K documents/s)

MySQL 127.0.0.1:15726+ JS > \sql
Switching to SQL mode... Commands end with ;

MySQL 127.0.0.1:15726+ SQL > SELECT `id`, `json_data`
                          -> FROM `test`.`example_table`\G
*************************** 1. row ***************************
       id: 1
json_data: {"price": null, "sale_list": [{"id": "1234", "date": "April 29th 2019 21:20:50", "buyer": "SmackMe089", "item_desc": ""}]}
1 row in set (0.0007 sec)
*************************** 2. row ***************************
       id: 2
json_data: {"price": null, "sale_list": [{"id": "1234", "date": "April 29th 2019 21:20:50", "buyer": "SmackMe089", "item_desc": ""}]}
2 rows in set (0.0006 sec)

无法使用 LOAD DATA INFILE 加载 JSON，因为正如上面的评论所说，该语句仅加载 CSV 数据。

如果要将 JSON 文档作为标量加载以插入到表的一行中，可以使用 LOAD_FILE（） 函数。

INSERT INTO example_table SET json_data = LOAD_FILE('/home/path/json_data/sample.json');

这将只创建一行来存储整个 JSON 文档。它不会解析 JSON，而是为 JSON 中 sales_list 数组中的每个条目插入单独的行。

阅读我链接到的文档，了解有关该函数的更多信息。LOAD_FILE()