MYSQL：在 LOAD DATA INFILE 后显示跳过的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2746436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL: Display Skipped records after LOAD DATA INFILE?
提问by Nate Murray
In MySQL I've used LOAD DATA LOCAL INFILEwhich works fine. At the end I get a message like:
在 MySQL 中，我使用过LOAD DATA LOCAL INFILE它工作正常。最后，我收到如下消息：
Records: 460377 Deleted: 0 Skipped: 145280 Warnings: 0
How can I view the line number of the records that were skipped? SHOW warningsdoesn't work:
如何查看跳过的记录的行号？SHOW warnings不起作用：
mysql> show warnings;
Empty set (0.00 sec)
回答by oabarca
If there was no warnings, but some rows were skipped, then it may mean that the primary key was duplicated for the skipped rows.
如果没有警告，但跳过了某些行，则可能意味着跳过的行的主键是重复的。
The easiest way to find out duplicates is by openning the local file in excel and performing a duplicate removal on the primary key column to see if there are any.
找出重复的最简单的方法是在excel中打开本地文件，并在主键列上执行重复删除，看看是否有重复。
回答by Steve
You could create a temp table removing the primary key items so that it allows duplications, and then insert the data.
您可以创建一个临时表，删除主键项，以便允许重复，然后插入数据。
Construct a SQL statement like
构造一个 SQL 语句，如
select count(column_with_duplicates) AS num_duplicates,column_with_duplicates
from table
group by column_with_duplicates
having num_duplicates > 1;
This will show you the rows with redundancies. Another way is to just dump out the rows that were actually inserted into the table, and run a file difference command against the original to see which ones weren't included.
这将显示具有冗余的行。另一种方法是转储出实际插入表中的行，然后针对原始行运行文件差异命令以查看哪些行未包含在内。
回答by Robert Brisita
For anyone stumbling onto to this:
对于任何绊倒这一点的人：
Another option would be to do a SELECT INTO and diff the two files. For example:
另一种选择是执行 SELECT INTO 并比较两个文件。例如：
LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE my_table FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r' IGNORE 1 LINES (title, desc, is_viewable);
SELECT title, desc, is_viewable INTO OUTFILE 'data_rows.txt' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r' FROM my_table;
Then execute FileMerge (on Mac OS X) data.txt data_rows.txt to see the differences. If you are getting an access denied error when doing the SELECT INTO make sure you:
然后执行 FileMerge（在 Mac OS X 上）data.txt data_rows.txt 以查看差异。如果在执行 SELECT INTO 时遇到拒绝访问错误，请确保：
GRANT FILE ON *.* TO 'mysql_user'@'localhost';
flush privileges;
As the root user in the mysql client.
作为 mysql 客户端中的 root 用户。
回答by cyber.sh
Records would be skipped, when any database constraint is not met. Check for common ones like
当不满足任何数据库约束时，将跳过记录。检查常见的，如
- Primary key duplication
- Unique key condition
- Partition condition
- 主键复制
- 唯一键条件
- 分区条件
回答by Shaul Mertzbach
I use bash command-line to find the duplicate row in the csv file:
我使用 bash 命令行在 csv 文件中查找重复的行：
awk -F\, '{print }' /my/source/file.csv| sort -n| uniq -c| grep -v "^\ *1"
when the two first columns are the primary key.
当前两列是主键时。
