将MySQL迁移到具有不同结构的表

My company's currently moving our databases around, shifting one set of tables out from the old MySQL instance into the new. We've done some development prior to this migration, and some tables' structure has been altered from the original (eg. columns were dropped).

我的公司目前正在移动我们的数据库，将一组表从旧的MySQL实例转移到新的MySQL实例中。我们在此迁移之前已经完成了一些开发，并且一些表的结构已经从原始变更（例如，列被删除）。

So currently I've dumped the data from the old database and am now attempting to reinsert them into the new table. Of course, the import borks when it tries to insert rows with more fields than the table has.

所以目前我已经从旧数据库中转储了数据，现在正试图将它们重新插入到新表中。当然，导入borks时，它尝试插入的字段比表中的字段多。

What's the best way (preferably scriptable, because I foresee myself having to do this a few more times) to import only the fields I need into the new table?

什么是最好的方法（最好是可编写脚本的，因为我预见自己要多做几次）才能将我需要的字段导入到新表中？

4 个解决方案

#1

Update the following to suit:

更新以下内容以适应：

SELECT 'INSERT INTO NEW_TABLE ... ('+ to.column +');'
  FROM OLD_TABLE ot

You need an INSERT statement for the table on the new database, with column list. Then populate the value portion accordingly based on the values in the old table. Run in the old environment, and you'll have your inserts with data for the new environment - just copy'n'paste into a script.

您需要一个INSERT语句，用于新数据库上的表，列列表。然后根据旧表中的值相应地填充值部分。在旧环境中运行，您将拥有包含新环境数据的插件 - 只需将'n'paste复制到脚本中即可。

Mind though that datatypes have to be handled accordingly - dates (incl. time), and strings will have to be handled because you're dealing in text.

请注意，必须相应地处理数据类型 - 日期（包括时间），并且必须处理字符串，因为您正在处理文本。

#2

First of all, create new database with old structure, or temp tables in current database. Then run script with insert statements for each row, but in values must be only those fields that are in new structure.

首先，使用旧结构或当前数据库中的临时表创建新数据库。然后使用每行的insert语句运行脚本，但值必须只是那些新结构的字段。

insert into newTable select row1,row2 from tempTable

#3

Use the fastest way, load data infile :

使用最快的方式，加载数据infile：

-- Dump datas

- 转储数据

SELECT * INTO OUTFILE 'mybigtable.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM mybigtable

-- Load datas

- 加载数据

LOAD DATA LOCAL INFILE 'mybigtable.csv' 
  INTO TABLE mynewbigtable
  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
  (@col1,@col2,@col3,@col4) set name=@col4,id=@col2;

Ref :

参考：

http://dev.mysql.com/doc/refman/5.6/en/insert-speed.html

http://dev.mysql.com/doc/refman/5.6/en/load-data.html

#4

If you're using MySQL 5.1, a powerful, although maybe in this case overkill, solution is to do an xml mysqldump and use an XSLT to transform it. Unfortunately re-importing that xml file isn't supported in 5.0, you'll need 5.1, 5.4, or 6.0

如果你正在使用MySQL 5.1，一个强大的，虽然可能在这种情况下过度杀戮，解决方案是做一个xml mysqldump并使用XSLT来转换它。不幸的是，5.0中不支持重新导入该xml文件，您需要5.1,5.4或6.0

#1