完成了一个3G的大表导入了MYSQL,记录一下.
一、mysql:
----------------------
DROP TABLE IF EXISTS `test`.`myt1`;
CREATE TABLE `test`.`myt1` (
`id` int(10) unsigned DEFAULT NULL,
`title` varchar(45) DEFAULT NULL,
`body` varchar(2000) DEFAULT NULL,
`origin` varchar(100) DEFAULT NULL,
`times` datetime DEFAULT NULL,
`encrypt` int(10) unsigned DEFAULT NULL,
`Columns` varchar(45) DEFAULT NULL,
`keyword1` varchar(100) DEFAULT NULL,
`keyword2` varchar(100) DEFAULT NULL,
`borough` int(10) unsigned DEFAULT NULL,
`city_id` varchar(45) DEFAULT NULL,
`writer` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1;
----------------------
二、sql server:
-------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myt1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myt1]
GO
CREATE TABLE [dbo].[myt1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[body] [varchar] (7500) COLLATE Chinese_PRC_CI_AS NULL ,
[origin] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[times] [datetime] NULL ,
[encrypt] [int] NULL ,
[Columns] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[keyword1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[keyword2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[borough] [int] NULL ,
[city_id] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[writer] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
---------------------
三、将原SQL SERVER数据导入SQL SERVER另一张新表myt1(select into产生的表有问题)
--------------
insert into myt1(title,body,origin,times,encrypt,Columns,keyword1,keyword2,borough,city_id,writer) (SELECT top 10 title,body,origin,times,encrypt,Columns,keyword1,keyword2,borough,city_id,writer FROM jiage07old);
--------------
四、原SQL SERVER表的text字段要改成varchar
五、用SQL SERVER导出向导导出,选择要导出的表,其它默认。
六、把MYSQL 中的表改为自动增加。