SQL Server迁移重构MySQL与InnoDB密钥和约束问题

I am redeveloping a web application and its infrastructure originally using SQL Server 2005, ASP.NET & Windows 2003 to a LAMMP (extra M for memcached of course) stack and since the schema is heavily refactored (with very good reason to do so) I must write a custom migration app.

我正在重新开发一个Web应用程序及其基础结构,最初使用SQL Server 2005,ASP.NET和Windows 2003到LAMMP(当然是memcached的额外M)堆栈,并且因为模式被重构(非常有理由这样做)我必须编写自定义迁移应用程序。

The problem is the InnoDB primary + foreign key constraints are hindering my ability to insert the data into its MySQL/InnoDB tables. I have already tried using DISABLE KEYS & FOREIGN_KEY_CHECKS methods and temporarily removing the auto-increment on the primary with it either throwing an error when try to make one of these changes such as the DISABLE KEYS since it is unsupported in InnoDB or trying to remove the primary key assignment on a column in an empty table or the migration app throwing errors saying key already exists when entering a record when the table is empty. Is there anything else that can be done beyond this besides removing all keys first and putting them back afterwards (which I assume will give me hell too)?

问题是InnoDB主要+外键约束阻碍了我将数据插入MySQL / InnoDB表的能力。我已经尝试过使用DISABLE KEYS&FOREIGN_KEY_CHECKS方法并暂时删除主要的自动增量,并在尝试进行其中一项更改时抛出错误,例如DISABLE KEYS,因为它在InnoDB中不受支持或者尝试删除在表为空时输入记录时,空表中的列上的主键分配或迁移应用程序抛出错误说明密钥已存在。除了首先删除所有键并在之后将它们放回去之后还有什么可以做的吗?(我认为这也会给我带来地狱般的感觉)?

Thanks!

2 个解决方案

#1

Turns out this works:

事实证明这是有效的:

ALTER TABLE `site_oltp`.`members` MODIFY COLUMN `id` INT(11) NOT NULL;
SET FOREIGN_KEY_CHECKS = 0, UNIQUE_CHECKS = 0;

insert records....

ALTER TABLE `site_oltp`.`members` MODIFY COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `site_oltp`.`members` AUTO_INCREMENT = (LAST_ID_GOES_HERE + 1);

SET FOREIGN_KEY_CHECKS = 1, UNIQUE_CHECKS = 1";

#2

I prefer a third party applpications for migrating my data, i use data loader when i was migrating MS SQL to Foxpro it work great, and it can migrate almost any database.

我更喜欢第三方应用程序来迁移我的数据,我使用数据加载器,当我将MS SQL迁移到Foxpro时,它工作得很好,它几乎可以迁移任何数据库。

Download Free : http://www.dbload.com

免费下载:http://www.dbload.com

#1