Question

将逗号分隔的 txt 文件装入表格时我遇到了问题。文件是巨大的。它的大小为 < 强 > 2GB , 包含 < 强 > 65 列 < /强 > 和 < 强 > 34 Lac < /强 > 行。我按照设置装入了此文件 :

Query-LOAD DATA LOCAL INFILE filename INTO TABLE tablename FIELDS TERMINATED BY , ENCLOSED BY " LINES TERMINATED BY IGNORE 1 LINES;

在文件my.ini 中,我添加了以下变量

[mysqld] max_allowed_packet=60M
[myisamchk] key_buffer_size=512M myisam_max_sort_file_size=3G

我是新来的 Mysql 。有人能帮我载入这个文件吗。当我在查询上方运行时, 我得到了4-5次结果。

Query OK,0 rows affected(25.09 sec)
Records:0 Deleted:0 Skipped:0 Warnings:0

Answer 1

使用外部ELil

mysqlimport --ignore-lines=1 --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="
" --user=YOUR_USERNAME --password YOUR_DATABASE tablename.txt

Answer 2

Try not using the LOCAL keyword, If this does not solve your problem, post some first lines of the text file so we can see if all the lines are being ignored for not matching the patterns specified in the parameters.

Update 1: Now I see you are using the terminator in

LINES TERMINATED BY

Have you tried modify this line to use only :

LINES TERMINATED BY

?????

Update 2: Try specify the table columns in LOAD DATA command;

LOAD DATA INFILE filename
INTO TABLE tablename
FIELDS TERMINATED BY  ;  ENCLOSED BY  "  ESCAPED BY  \  LINES TERMINATED BY  
 (
`tableColumn1` , `tableColumn2`, ..., `tableColumn65`
)

可能文件和表格的列数不匹配,或者文件列的顺序与表格列的顺序不同。

Answer 3

我自己也找到问题答案了

1. 我以 gslipts 软件“ 坚固” 分割了我的文件,并对每个新建文件做了相应的修改。

2. 在打开这些文件后,我打开了这些“强度”绝版格式,正确格式为“/强度”半个小时,然后“强度”创建的csv文件

Answer 4

When your INFILE is huge, max_allowed_packet must be adjusted to accommodate a max of 1G per this link in the refman for 5.6. https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_packet Preparation could include for the session SET local_infile=1; SET max_allowed_packet=1073741824 (possibly more options required) before LOAD DATA INFILE filename INTO TABLE tablename ..... filename could require full path ie. C:foldersubfoldermyfilename.ext

更改您. ini/. confg 到 1G 中最大允许的软件包的警告可能会在您的日常操作中造成意外的内存压力。使用会话变量在本次会话期间满足 1G 的要求。如果您没有用于此目的的1G 内存, 您还是无法成功。

友情链接