无法将mysql数据从一个数据库传输到另一个数据库

I am trying to rework an old implementation of osCommerce and re-introduce data from the old database to the new one. I exported the data from the old tables using phpMyAdmin. When I tried to import them to the new database, I got a series of errors. I am simplifying the problem here to a single INSERT statement for the sake of clarity.

我正在尝试重新编写osCommerce的旧实现，并将旧数据库中的数据重新引入到新数据库中。我使用phpMyAdmin从旧表中导出数据。当我尝试将它们导入新数据库时，我遇到了一系列错误。为了清楚起见，我将此处的问题简化为单个INSERT语句。

The statement is:

声明是：

INSERT INTO `address_book` (`address_book_id`, 
                            `customers_id`, 
                            `entry_gender`,
                            `entry_company`, 
                            `entry_firstname`, 
                            `entry_lastname`, 
                            `entry_street_address`, 
                            `entry_suburb`, 
                            `entry_postcode`, 
                            `entry_city`, 
                            `entry_state`, 
                            `entry_country_id`, 
                            `entry_zone_id`) 
  VALUES (1, 1, 'm', '', 'Mary', 'Smith', 
          '1234 Pleasant Court', '', '67890',
          'Hometown', 'state', 123, 0);

When I try to import it from a file through phpMyAdmin I get an error: “#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.”

当我尝试通过phpMyAdmin从文件导入它时，我收到一个错误：“＃1064 - 您的SQL语法中有错误;检查与MySQL服务器版本对应的手册，以便在第1行的''附近使用正确的语法。“

If I click the Edit button and resubmit the query, I get another error: “#1062 - Duplicate entry '1' for key 'PRIMARY'.” The address_book_id field is the primary key in this table and is an auto-increment field.

如果我单击“编辑”按钮并重新提交查询，则会收到另一个错误：“＃1062 - 键'PRIMARY'的重复条目'1'。”address_book_id字段是此表中的主键，是一个自动增量字段。

Still in the edit screen, if I change the address_book_id value to NULL and resubmit, the query is accepted.

仍然在编辑屏幕中，如果我将address_book_id值更改为NULL并重新提交，则接受查询。

Thinking I had the issue resolved, I changed the address_book_id value in the import file to NULL and tried importing again. And again, I get the #1064 error. I immediately clicked edit. I made no edits to the statement but submitted it without change. This time, the statement was accepted.

认为我已解决问题，我将导入文件中的address_book_id值更改为NULL并再次尝试导入。而且，我得到＃1064错误。我立即点击编辑。我没有对该声明进行任何修改，但未经更改就提交了该声明。这一次，声明被接受了。

It appears to be a problem with handling the address_book_id value in the downloaded data. I have hundreds of index values from numerous tables from the old database. Obviously, I don't want to have to change the index values and submit them one at a time.

处理下载数据中的address_book_id值似乎是个问题。我从旧数据库的众多表中获得了数百个索引值。显然，我不想更改索引值并一次提交一个。

I am working with phpMyAdmin 3.5.1. Any idea what is going on?

我正在使用phpMyAdmin 3.5.1。知道发生了什么事吗？

2 个解决方案

#1

Do it without the address_book_id. Apparently that one is the primary key which you can't simply copy from another table. The new ID should be assigned automatically.

没有address_book_id就可以了。显然，一个是主键，你不能简单地从另一个表复制。应自动分配新ID。

#2

After playing around with this for a while, I think the best approach is to delete then recreate the tables. I tried this on two tables and was then able to upload my data, including the id fields without throwing any errors. The tables seem to be working OK.

在玩了一段时间之后，我认为最好的方法是删除然后重新创建表格。我在两个表上尝试了这个，然后能够上传我的数据，包括id字段，而不会抛出任何错误。表似乎工作正常。