mysql与LOAD DATA INFILE重复 [英] mysql duplicates with LOAD DATA INFILE

问题描述

使用LOAD DATA INFILE时，是否有标记重复行或将任何/所有重复项转储到单独表中的方法？

When using LOAD DATA INFILE, is there a way to either flag a duplicate row, or dump any/all duplicates into a separate table?

推荐答案

从 LOAD DATE INFILE文档：

REPLACE 和IGNORE关键字控制对唯一键值上重复现有行的输入行的处理：

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:

• 如果指定REPLACE，输入行将替换现有行。换句话说，具有与主键或唯一索引相同的值作为现有行的行。 请参见第12.2.7节替换语法。 li>
  
• 如果指定IGNORE，将跳过重复唯一键值上现有行的输入行。如果不指定任何选项，则行为取决于是否指定了LOCAL关键字。如果没有LOCAL，则在找到重复键值时会发生错误，并忽略文本文件的其余部分。使用LOCAL，默认行为与指定IGNORE相同;这是因为服务器在操作过程中无法停止传输文件。

• If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row. See Section 12.2.7, "REPLACE Syntax".
• If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

实际上，没有办法将重复记录重定向到不同的表。您必须将它们全部加载，然后创建另一个表来保存非重复的记录。

Effectively, there's no way to redirect the duplicate records to a different table. You'd have to load them all in, and then create another table to hold the non-duplicated records.

这篇关于mysql与LOAD DATA INFILE重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！