将MySQL迁移到不同结构的表
我的公司目前正在迁移我们的数据库,将一组表从旧的 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).
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.
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
首先,使用旧结构创建新数据库,或在当前数据库中创建临时表。然后为每行运行带有插入语句的脚本,但值中必须只有新结构中的那些字段。
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.
使用最快的方式,加载数据到文件中:
-- 转储数据
-- 加载数据
参考:
http://dev.mysql.com/doc/refman/5.6/en/insert-speed.html
http://dev.mysql.com/doc/refman /5.6/en/load-data.html
Use the fastest way, load data infile :
-- Dump datas
-- Load datas
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
更新以下内容以适应:
您需要对新数据库上的表使用 INSERT 语句,并包含列列表。然后根据旧表中的值相应地填充值部分。在旧环境中运行,您将获得包含新环境数据的插入内容 - 只需复制并粘贴到脚本中即可。
请注意,数据类型必须进行相应的处理 - 日期(包括时间),并且必须处理字符串,因为您正在处理文本。
Update the following to suit:
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.
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.
如果您使用 MySQL 5.1,一个功能强大(尽管在本例中可能有点过大)的解决方案是执行 xml mysqldump 并使用 XSLT 对其进行转换。不幸的是,5.0 不支持重新导入该 xml 文件,您需要 5.1、5.4 或 6.0
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