MySQL 在phpmyadmin中导入时如何跳过重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18485674/
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
how to skip duplicate records when importing in phpmyadmin
提问by Jo E.
I have a db on my local machine and I want to import the data to the db on my hosting. Both db's are identical, the same table names, column names, etc.
我的本地机器上有一个数据库，我想将数据导入到我的主机上的数据库中。这两个数据库的是相同的，相同的table names，column names等等。
When I exportthe table from my local db through phpmyadminand importit through phpmyadmin on my hosting an error pops up telling me that there are duplicate entries for the primary keyand stops the whole operation.
当我export在表中从我的本地数据库，通过phpmyadmin与import它通过对我的托管错误的phpmyadmin弹出告诉我，有对重复条目primary key，并停止整个操作。
How can I import the data through phpmyadmin, skip the duplicate entries, and display a list of the duplicates at the end of the process?
如何通过 phpmyadmin 导入数据，跳过重复项，并在流程结束时显示重复项列表？
A solution that I can do is call all the values of the primary key in the db at my hosting and filter the duplicates before import. BUT I am wondering if there is a quick solution for this with phpmyadmin?
我可以做的一个解决方案是在我的主机上调用数据库中主键的所有值，并在导入之前过滤重复项。但我想知道 phpmyadmin 是否有快速解决方案？
回答by nl-x
In phpMyAdmin , in Settings tab, you can try checking the following values:
在 phpMyAdmin 中，在 Settings 选项卡中，您可以尝试检查以下值：
- Settings -> SQL Queries -> Ignore multiple statement errors
- 设置 -> SQL 查询 -> 忽略多个语句错误
If you are using CSV format:
如果您使用 CSV 格式：
- Settings -> Import -> CSV -> Do not abort on INSERT error
- 设置 -> 导入 -> CSV -> 不要中止 INSERT 错误
If you are using SQL format:
如果您使用的是 SQL 格式：
- Settings -> Export -> SQL -> Use ignore inserts
- 设置 -> 导出 -> SQL -> 使用忽略插入
回答by Elias Van Ootegem
There are a couple of ways to do what you want:
有几种方法可以做你想做的事：
The brutal way:
残酷的方式：
TRUNCATE TABLE yourTbl; -- emtpies out the table
Then import, but you might loose data, so perhaps create a backup table. All things considered, just don't do this, check the alternatives listed below:
然后导入，但您可能会丢失数据，因此可能会创建一个备份表。考虑到所有因素，只是不要这样做，请检查下面列出的替代方案：
Write your own INSERTquery, with IGNOREclause:
INSERT使用IGNORE子句编写您自己的查询：
INSERT IGNORE INTO yourTbl -- as shown in the linked duplicate
But, since you are importing a file, the query will, most likely be a LOAD DATA [LOCAL] INFILE. As you can see in the manual, you can easily add an IGNOREto that query, too:
但是，由于您要导入文件，因此查询很可能是LOAD DATA [LOCAL] INFILE. 正如您在手册中所见，您也可以轻松地IGNORE向该查询添加一个：
LOAD DATA LOCAL INFILE '/path/to/files/filename1.csv' IGNORE -- IGNORE goes here
INTO TABLE your_db.your_tbl
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(`field1`,`field2`);
That's it. Don't worry if you're not too comfortable writing your own queries like this, there are other ways of doing what you want to do:
The CLI way:
就是这样。如果您不太习惯像这样编写自己的查询，请不要担心，还有其他方法可以做您想做的事情：
CLI 方式：
mysqlimport -i dbname fileToImport
# Or
mysqlimport --ignore dbname fileToImport
Also CLI, create a file containing the LOAD DATAquery above, then:
同样是 CLI，创建一个包含上述LOAD DATA查询的文件，然后：
$: mysql -u root -p
*********** #enter password
mysql> source /path/to/queryFile.sql
This requires you to have access to the command line, and run this command Here's the manual page of MySQL
这需要您访问命令行，并运行此命令这是 MySQL 的手册页
Using phpMyAdmin, when importing, you'll find a checkbox saying "Ignore duplicates", check that and import. Here's a page with screenshots
You could also choose to check "Ignore errors", but that's another brute-force approach, and I wouldn't recommend that.
使用 phpMyAdmin，导入时，您会发现一个复选框，上面写着“忽略重复项”，检查并导入。这是一个带有屏幕截图的页面
您也可以选择检查“忽略错误”，但这是另一种蛮力方法，我不建议这样做。
