MySQL phpMyAdmin 导出/导入导致重复主键错误

phpMyAdmin export/import results in duplicate primary key error

提问by LandedGently

I want to globally replace all instances of my site url in the Wordpress MySQL database with a new site url. To do that, I'm using phpMyAdmin to export the database to a .sql file, then doing a global replace in a text editor, then using phpMyAdmin to import the .sql file.

我想用新的站点 url 全局替换 Wordpress MySQL 数据库中我的站点 url 的所有实例。为此，我使用 phpMyAdmin 将数据库导出到 .sql 文件，然后在文本编辑器中进行全局替换，然后使用 phpMyAdmin 导入 .sql 文件。

During the import, I'm encountering a duplicate entry for primary key error. Trying to debug this, I exported the file, then imported the identical file, making no changes and I still get the same error.

在导入过程中，我遇到了主键错误的重复条目。为了调试这个，我导出了文件，然后导入了相同的文件，没有做任何更改，但我仍然遇到相同的错误。

I appreciate any help solving this.

我感谢任何帮助解决这个问题。

--
-- Dumping data for table `wp_comments`
--
INSERT INTO  `wp_comments` 
  (`comment_ID`, `comment_post_ID`, `comment_author`, `comment_author_email`
  ,`comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt` 
  ,`comment_content`, `comment_karma`, `comment_approved`, `comment_agent` 
  ,`comment_type`, `comment_parent`, `user_id`) 
VALUES (1, 1, 'Mr WordPress', ''
       ,'http://wordpress.org/', '', '2011-04-28 00:49:55', '2011-04-28 00:49:55'
       ,'Hi, this is a comment.<br />To delete a comment, 
         just log in and view the post&#039;s comments. 
         There you will have the option to edit or delete them.'
       , 0, 'post-trashed',  '',  '', 0, 0 ) ;

MySQL said: 

#1062 - Duplicate entry '1' for key 'PRIMARY' 

回答by Lightness Races in Orbit

The original data is still in the database.

原始数据仍在数据库中。

If you were working manually you'd send a UPDATErather than INSERTquery with the new values, but since you're doing a batch import, it may just be better to wipe the existing table clean before the import.

如果您手动工作，您将发送一个UPDATE而不是INSERT使用新值查询，但由于您正在执行批量导入，因此最好在导入之前将现有表擦干净。

DELETE FROM `tblName`;

Be sure to back-up your data, first.

请务必先备份您的数据。

回答by George

To avoid duplicates you have to use UPDATE instead of INSERT statements. To achieve this in phpMyAdmin, follow these steps:

为避免重复，您必须使用 UPDATE 而不是 INSERT 语句。要在 phpMyAdmin 中实现这一点，请按照下列步骤操作：

1. Select your database in the tree.
2. OPTIONAL. Go to "Search" tab and search for string you want to replace in all tables. If string is found in several tables only, note their names. This will help to speed up process by updating only the tables which needs updating. This my be important if you have lot of data.
3. Go to "Export" tab.
4. In the "Export method:" select "Custom".
5. OPTIONAL. If you noted the tables which need updating in step 2 above, then in the "Table(s):" section, click "Unselect all" and then select only the tables which need to be updated.
6. Scroll down to "Data creation options" section, and in the drop box labeled "Function to use when dumping data:" select "UPDATE" (default is "INSERT").
7. Click "Go".
8. Open the downloaded SQL dump file.
9. IMPORTANT! Save the file with a new name for backup purposes before any changes are made.
10. Use Search & replace function of your editor to change what you want. Then save the file.
11. In phpMyAdmin go to "Import" tab.
12. In the "File to import:" section click the "Choose file" button and browse for the edited file. Click GO

1. 在树中选择您的数据库。
2. 可选的。转到“搜索”选项卡并在所有表中搜索要替换的字符串。如果仅在多个表中找到字符串，请记下它们的名称。这将有助于通过仅更新需要更新的表来加快进程。如果您有大量数据，这很重要。
3. 转到“导出”选项卡。
4. 在“导出方法：”中选择“自定义”。
5. 可选的。如果您在上面的步骤 2 中注意到需要更新的表，则在“表：”部分，单击“取消全选”，然后仅选择需要更新的表。
6. 向下滚动到“数据创建选项”部分，然后在标记为“转储数据时使用的功能：”的下拉框中选择“更新”（默认为“插入”）。
7. 点击“前往”。
8. 打开下载的 SQL 转储文件。
9. 重要的！在进行任何更改之前，使用新名称保存文件以进行备份。
10. 使用编辑器的搜索和替换功能来更改您想要的内容。然后保存文件。
11. 在 phpMyAdmin 中，转到“导入”选项卡。
12. 在“要导入的文件：”部分，单击“选择文件”按钮并浏览已编辑的文件。点击前往

You are ready! To check if everything is OK, search the database second time (repeat step 2). You should not find any tables containing your old string.

你准备好了！要检查一切是否正常，请第二次搜索数据库（重复步骤 2）。您不应该找到任何包含旧字符串的表格。

回答by Pedro M. Silva

If you're exporting, that means that the main content stays in the database. So, when you try to insert a new row with the same PRIMARY KEY, which are always UNIQUE, you'll get an error.

如果您要导出，则意味着主要内容保留在数据库中。因此，当您尝试插入具有相同 PRIMARY KEY（始终是 UNIQUE）的新行时，您将收到错误消息。

Solution: You must delete the row from the table that has the same comment_ID. You must open the PHPMyAdmin and go your table page, and check the row with the ID you want. In this case is 1, which means that it is probabily in the first results page of your table listing. Just click on the red X which appears in the row.

解决方案：您必须从表中删除具有相同comment_ID 的行。您必须打开 PHPMyAdmin 并转到您的表格页面，然后检查具有您想要的 ID 的行。在这种情况下是 1，这意味着它可能出现在您的表格列表的第一个结果页面中。只需单击出现在该行中的红色 X。

This could also be done by SQL, which could be simple too.

这也可以通过 SQL 来完成，这也可以很简单。

DELETE FROM `wp_comments` WHERE `comment_ID` = 1 LIMIT 1

For this, just select your database in PHPMyAdmin and go to the SQL tab, and insert the code above in the text area.

为此，只需在 PHPMyAdmin 中选择您的数据库并转到 SQL 选项卡，然后在文本区域中插入上面的代码。

回答by John

Had the same problem and error number. Deleted the database, recreate with no tables, and import the changed export file worked for me.

有同样的问题和错误号。删除了数据库，在没有表的情况下重新创建，然后导入对我有用的更改后的导出文件。

回答by bito_

The problem is related with your file - you are trying to create a DB using a copy - at the top of your file you will find something like this:

问题与您的文件有关 - 您正在尝试使用副本创建数据库 - 在文件顶部，您会发现如下内容：

CREATE DATABASE IF NOT EXISTS *THE_NAME_OF_YOUR_DB*DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; USE *THE_NAME_OF_YOUR_DB*;

如果不存在，则创建数据库*THE_NAME_OF_YOUR_DB*默认字符集 latin1 COLLATE latin1_general_ci; 使用*THE_NAME_OF_YOUR_DB*;

and I'm sure that you already have a DB with this name - IN THE SAME SERVER - please check, because you are trying to overwrite!! Just change the name OR (better) ERASE THIS LINE!

并且我确定您已经有一个具有此名称的数据库 - 在同一服务器中 - 请检查，因为您正在尝试覆盖！！只需更改名称或（更好）删除此行！

回答by aokelly

Since you already have that record there, you can just update the record rather than inserting. It would go something like this.

由于您已经在那里拥有该记录，您可以只更新记录而不是插入。它会像这样。

  UPDATE `wp_comments` 
  SET 'comment_author_url' = 'YOUR NEW ADDRESS'
  WHERE  `comment_ID` = 1

Just update every instance of your old address using this method. You can search through all of all posts by saying "WHERE 'comment_author_url' = 'YOUR OLD ADDRESS'"

只需使用此方法更新旧地址的每个实例。您可以通过说“WHERE 'comment_author_url' = 'YOUR OLD ADDRESS'”来搜索所有帖子

回答by itsols

If all you want to do is replace your URL, I believe this is all you must do:

如果您只想替换您的网址，我相信这就是您必须做的所有事情：

Update `wp_comments` Set 
`comment_author_url` = 'http://wordpress.org/'
Where `comment_author` = 'Mr WordPress'

Just type the above SQL into PHPMyAdmin's SQL box and execute.

只需在 PHPMyAdmin 的 SQL 框中键入上述 SQL 并执行。

NOTE: First make sure you have a backup. And there's no need to do all that export and import stuff :)

注意：首先确保您有备份。而且没有必要做所有的导出和导入的事情:)

回答by Johan

Change to code to

将代码更改为

INSERT .... (what you already have) 
ON DUPLICATE KEY UPDATE;

This will fix your problem with a minimum of fuss, whilst still inserting new rows.

这将以最少的麻烦解决您的问题，同时仍然插入新行。