【MySQL】导入 JSONL 数据到 MySQL数据库

最近在做一些数据处理工作需要将后缀为“.jsonl”的文件数据导入到 MySQL 库。由于之前没有尝试过，这次就当作经验记录一下。

首先肯定是要先建库和建表（这些就不再细说了），接着就可以通过 LOAD DATA INFILE 命令将 jsonl 文件内容进行提取。由于这次使用的是 MySQL 5.7 以上的版本（MySQL 8），因此直接使用@json 用户变量告诉 MySQL 要将数据以 JSON 格式进行存储就可以了。如下图：

LOAD DATA INFILE '<>'  
INTO TABLE <<target_table>>  
(@json)  
SET field1 = JSON_EXTRACT(@json, '$.field1'),  
    field2 = JSON_EXTRACT(@json, '$.field2'),  
    field3 = JSON_EXTRACT(@json, '$.field3'),
    ...;
1
2
3
4
5
6
7

但是在这个过程中或许会出现 “secure-file-priv” 参数异常，如下图：

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
1

这个时候就可以通过 “show variables like ‘secure_file_priv’” 查询一下当前参数状态

mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

Value 为 NULL 则表明禁止导入导出功能，这个时候需要对 my.cnf 文件进行一下配置，指定一个导入路径给它

# 这里的路径是 Docker 镜像内部的路径
secure_file_priv='/var/lib/mysql/imports'
1
2

然后重启 MySQL 服务。之后重新执行 “show variables like ‘secure_file_priv’” 看看配置是否生效。

mysql> show variables like 'secure_file_priv';
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| secure_file_priv | /var/lib/mysql/imports/ |
+------------------+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

配置生效之后记得要将 /var/lib/mysql/imports/ 路径映射到宿主机，又或者使用 docker cp 将 jsonl 文件放入到容器内部，不然会报找不到文件的错误。
在导入的过程中或许还会出现：

Error Code: 3141. Invalid JSON text in argument 1 to function json_extract: "Invalid encoding in string." at position 411.
1

这是因为 jsonl 中出现了特殊字符引起的，我这次遇到的就是“\”的问题。由于数据中有可能会出现字符转义的情况，因此那些“\n”、“\t”、“\”等等都需要先进行特殊字符的转换，不然无法导入到数据库中。譬如，在 jsonl 文件中将“\n”先全部替换成“@”，“\t”全部替换成“&”，在导入到数据库之后再重新更新回来就可以了。