mysql - 在 MySQL 中使用一些 JSON 列加载查询得到 {无效的 JSON 文本 : "Invalid value." at position 0 in value for column }

Question

我正在尝试将文件加载到 MySQL 中，但出现错误，如 ERROR 3140 (22032): Invalid JSON text: "Invalid value."在列值的位置 0。文件中的数据如下所示，

id,name,jfield1,jfield2
1,"A","{\"Key1\": 100,\"key2\": \"Abc\"}","{}"
2,"B","{\"Key1\": 101,\"key2\": \"Def\"}","{}"

我的 table :

CREATE TABLE `test` (
  `id` int(3) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `jfield1` json NOT NULL,
  `jfield2` json NOT NULL
) ;

加载查询:

mysql> load data local infile "/home/user/sample.csv" into table test fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'test.jfield1'.
mysql>

如果我使用 insert 将数据直接插入到表中，则数据插入正确。

mysql> insert into test (id,name,jfield1,jfield2) values (1,"A","{\"Key1\": 100,\"key2\": \"Abc\"}","{}");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (id,name,jfield1,jfield2) values (1,"B","{\"Key1\": 110,\"key2\": \"Def\"}","{}");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+------------------------------+---------+
| id   | name | jfield1                      | jfield2 |
+------+------+------------------------------+---------+
|    1 | A    | {"Key1": 100, "key2": "Abc"} | {}      |
|    1 | B    | {"Key1": 110, "key2": "Def"} | {}      |
+------+------+------------------------------+---------+
2 rows in set (0.00 sec)

mysql>

请有人告诉我查询中有什么问题。提前致谢!

Answer 1

检查参数的顺序。