MySQL LOAD DATA INFILE 带有特殊分隔符

发布于 2024-11-09 10:05:48 字数 553 浏览 3 评论 0原文

我的数据包含由 \u0001 分隔的字段和由 \u0002\n 分隔的记录。我想使用 LOAD DATA INFILE 一次性将所有数据导入 MySQL 数据库。我知道\u0001可以写成X'01'，但我不知道如何写“\u0002\n”。

这是我到目前为止所拥有的。

LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE my_table 
    FIELDS TERMINATED BY X'01'
    LINES TERMINATED BY '\n'
    (col1, col2, col3);

上面的 SQL 语句有效，但我担心 \u0002 可能会与数据一起导入数据库。

我知道 \u0002 可以写成 X'02' 但我不太确定如何将 X'02' 与<代码>\n。

有什么建议吗？

原文

I have data that has fields deliminated by \u0001 and records deliminated by \u0002\n. I want to use LOAD DATA INFILE to import all of the data at once into a MySQL database. I know that \u0001 can be written as X'01', but I am not sure how to write `\u0002\n'.

This is what I have so far.

LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE my_table 
    FIELDS TERMINATED BY X'01'
    LINES TERMINATED BY '\n'
    (col1, col2, col3);

The above SQL statement works but I'm afraid that the \u0002 might be imported into the database along with the data.

I know that \u0002 can be written as X'02' but I'm not too sure how I can combine X'02' with \n.

Any suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

很糊涂小朋友 2024-11-16 10:05:48

看来您正在尝试实施 iTunes EPF :)，
X'01' + '\n' => X'020a'
所以你的查询可以写成：

LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE my_table 
    FIELDS TERMINATED BY X'01'
    LINES TERMINATED BY X'020a'
    (col1, col2, col3);

如果你真的致力于实现 iTunes EPF 集成，你可以使用下一个查询：

LOAD DATA LOCAL INFILE '<path to your file>' INTO TABLE `<table name>` CHARACTER SET UTF8MB4 FIELDS TERMINATED BY X'01' LINES TERMINATED BY X'020a' IGNORE 34 LINES;

例如苹果提供 4 字节 UTF8 的文本数据，它可以使用 UTF8MB4 字符集存储在 MySQL 中（我的意思是更正端口所有文本记录）。
前 34 行将被忽略（应该如此 — 它是“法律政策”数据）。
并且需要从文件中删除最后一行。（我在移植之前用脚本删除它）

希望它对某人有帮助。

It looks like you are trying to implement iTunes EPF :),
X'01' + '\n' => X'020a'
So your query can be written as:

LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE my_table 
    FIELDS TERMINATED BY X'01'
    LINES TERMINATED BY X'020a'
    (col1, col2, col3);

If you are really working on implementing an iTunes EPF integration, you can use next query:

LOAD DATA LOCAL INFILE '<path to your file>' INTO TABLE `<table name>` CHARACTER SET UTF8MB4 FIELDS TERMINATED BY X'01' LINES TERMINATED BY X'020a' IGNORE 34 LINES;

Such as apple provide text data in 4-byte UTF8 which can be stored in MySQL just with UTF8MB4 charset (I mean to correct port all text records).
First 34 lines will be ignored ( as should — its is 'legal policy' data).
and need to remove last line from file. (I remove it with script before porting)

Hopes it helps someone.

回复收藏 0 原文

~没有更多了~