有没有办法在Win7上用换行符从json文件中的数据填充MySQL 5.7表? [英] Is there a way to populate a MySQL 5.7 table with data from a json file with line breaks on Win7?

问题描述

我有以下示例json文件，并且我试图用它填充MySQL表.由于MySQL 5.7支持将json作为本机数据类型，所以我认为这应该不是问题，但是MySQL的json验证程序似乎在文件中的换行符方面存在问题.一旦我摆脱了所有换行符，并将我的示例文件写到一行中，它就可以正常工作.

I have following example json file and I'm trying to populate a MySQL table with it. Since MySQL 5.7 supports json as a native datatype I thought this shouldn't be a problem, but MySQL's json validator seems to have a problem with the line breaks inside the file. As soon as I get rid of all line breaks and write my example file in one line, it works perfectly.

[
{
    "somestuff": [
        {
            "field1": "val1",
            "field2": 17,
            "field3": 27,
            "field4": 42,
            "field5": 73
        },
        {
            "field1": "val2",
            "field2": 3,
            "field3": 12,
            "field4": 13,
            "field5": 100
        }
    ],
    "field0": "image",
    "path": "some path"
}
]

我知道json本身在包含反斜杠的字符串方面存在问题，但是即使编写任何类型的单词而不是路径，MySQL json验证器仍然无法完全读取json文件，并且仅在第一个换行符之前停止.我仍然收到以下错误:

I know json natively has problems with strings containing backslashes, but even when writing any kind of word instead of the path, the MySQL json validator still can't completely read the json file and just stops before the first line break. I still get following error:

Error Code: 3140. Invalid JSON text: "Invalid value." at position 1 in value (or column) '['. 

使用以下查询时:

-- create test table for json data
CREATE TABLE IF NOT EXISTS jsons_test(
       annotation_id INT AUTO_INCREMENT NOT NULL UNIQUE, 
        json_data json);
LOAD DATA LOCAL INFILE 'C:\\some\\path\\test.json'
INTO table json_test(json_data);

我猜这很可能是由于换行编码而发生的，但是我仍然不知道是否有解决方法.

I guess this most probably happens because of the line break encoding, but I still don't know if there's any workaround to solve it.

更新:我自己找到了解决方案... 通过添加LINES TERMINATED BY '\\n'，我可以成功地用json数据填充表，并利用MySQL新的json支持的优点.

UPDATE: I found a solution on my own... By adding LINES TERMINATED BY '\\n' I could successfully populate the table with the json data and make use of the advantages of MySQL's new json support.

推荐答案

我找到了解决问题的方法:请参见 UPDATE

I found a solution for my problem: see UPDATE

这篇关于有没有办法在Win7上用换行符从json文件中的数据填充MySQL 5.7表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！