mysql - 将 Json 字段从 csv 导入 MySQL 5.7.19-0ubuntu0.16.04.1

Question

我的 MySQL 表由 3 列组成，类型为 (cookie vachar, userdata json, userpreference json)。我正在尝试从 csv 加载数据，但出现以下错误:

错误:

错误代码:3140 无效的 JSON 文本:列 userdata 的值中位置 0 处的值无效

我已经尝试验证它看起来不错的 JSON。现在我不明白这是什么问题

示例行:

ZwpBHCrWObHE61rSOpp9dkUfJ, '{"bodystyle": {"SUV/MUV": 2}, "budgetsegment": {"EP": 2}, "models": {"Grand Cherokee": 1, "XC90": 1}}', '{"bodystyle": "SUV/MUV", "budgetsegment": "EP", "models": "Grand Cherokee,XC90"}'

Answer 1

我无法重现问题:

文件:/path/to/file/data.csv

ZwpBHCrWObHE61rSOpp9dkUfJ, '{"bodystyle": {"SUV/MUV": 2}, "budgetsegment": {"EP": 2}, "models": {"Grand Cherokee": 1, "XC90": 1}}', '{"bodystyle": "SUV/MUV", "budgetsegment": "EP", "models": "Grand Cherokee,XC90"}'

MySQL 命令行:

mysql> \! lsb_release --description
Description:    Ubuntu 16.04.2 LTS

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

mysql> DROP TABLE IF EXISTS `table`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `table` (
    ->   `cookie` VARCHAR(25) NOT NULL,
    ->   `userdata` JSON NOT NULL,
    ->   `userprefernce` JSON NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA LOCAL INFILE '/path/to/file/data.csv'
    ->   INTO TABLE `table`
    ->   FIELDS TERMINATED BY ', '
    ->          OPTIONALLY ENCLOSED BY '\''
    ->   LINES TERMINATED BY '\n';
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    ->   `cookie`,
    ->   `userdata`,
    ->   `userprefernce`
    -> FROM
    ->   `table`\G
*************************** 1. row ***************************
       cookie: ZwpBHCrWObHE61rSOpp9dkUfJ
     userdata: {"models": {"XC90": 1, "Grand Cherokee": 1}, "bodystyle": {"SUV/MUV": 2}, "budgetsegment": {"EP": 2}}
userprefernce: {"models": "Grand Cherokee,XC90", "bodystyle": "SUV/MUV", "budgetsegment": "EP"}
1 row in set (0.00 sec)