已经有一个可以正常使用的基于sql server 2000的jsp系统,为了在mysql中导入sql server中的数据库,我按照以下步骤操作,结果原有系统 无法正常使用。我怀疑是SQL Server中的表结构如何与Mysql中的表结构映射方法上出错,但是却无法知道错在哪里,急!!。请大虾们指教,谢谢先!! 1、从SQL Server 2000中导出了8个库表的sql脚本文件,内容如下:
CREATE TABLE [dbo].[Bulletin] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[poster] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[posttime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[CustomOper] (
[operId] [int] IDENTITY (1, 1) NOT NULL ,
[operName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[operAction] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[operImage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[resId] [int] NULL ,
[operLevel] [int] NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[Msg] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[poster] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[posttime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[jsr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[Resource] (
[resId] [int] IDENTITY (1, 1) NOT NULL ,
[resName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[resUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[parentId] [int] NULL ,
[resSeq] [int] NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[Role] (
[roleId] [int] IDENTITY (1, 1) NOT NULL ,
[roleName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[roleDesc] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[RoleRes] (
[roleResId] [int] IDENTITY (1, 1) NOT NULL ,
[roleId] [int] NULL ,
[resId] [int] NULL ,
[operate] [ntext] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO CREATE TABLE [dbo].[Users] (
[userId] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[userName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userPhone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userPwd] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[roleId] [int] NULL ,
[userAddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[depaId] [int] NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[depa] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO 2、在sql server 2000中打开表,把各个表数据分别复制到与各自表相同名字的txt文件中(ANSI编码),同一条记录的字段值间间隔Tab字符。 3、在mysql中建立一个数据库。 4、编写一个导入数据到mysql库的sql脚本文件input.sql。
(1)创建表部分内容如下:
CREATE TABLE IF NOT EXISTS mldn.Bulletin (
id int NOT NULL PRIMARY key,
title varchar (50) NULL ,
content varchar (500) NULL ,
poster varchar (50) NULL ,
posttime varchar (50) NULL
) ; CREATE TABLE IF NOT EXISTS mldn.CustomOper (
operId int NOT NULL PRIMARY key,
operName nvarchar (50) NULL ,
operAction nvarchar (50) NULL ,
operImage nvarchar (50) NULL ,
resId int NULL ,
operLevel int NULL
) ; CREATE TABLE IF NOT EXISTS mldn.Msg (
id int NOT NULL PRIMARY key,
title varchar (50) NULL ,
content varchar (500) NULL ,
poster varchar (50) NULL ,
posttime varchar (50) NULL ,
state varchar (50) NULL ,
jsr varchar (50) NULL
) ; CREATE TABLE IF NOT EXISTS mldn.Resource (
resId int NOT NULL PRIMARY key,
resName nvarchar (50) NULL ,
resUrl nvarchar (255) NULL ,
parentId int NULL ,
resSeq int NULL
) ; CREATE TABLE IF NOT EXISTS mldn.Role (
roleId int NOT NULL PRIMARY key,
roleName nvarchar (50) NULL ,
roleDesc nvarchar (255) NULL
) ; CREATE TABLE IF NOT EXISTS mldn.RoleRes (
roleResId int NOT NULL PRIMARY key,
roleId int NULL ,
resId int NULL ,
operate text (16) NULL
); CREATE TABLE IF NOT EXISTS mldn.Users (
userId nvarchar (50) NOT NULL PRIMARY key,
userName nvarchar (50) NULL ,
userPhone nvarchar (50) NULL ,
userPwd nvarchar (50) NULL ,
roleId int NULL ,
userAddress nvarchar (255) NULL ,
depaId int NULL
) ; CREATE TABLE IF NOT EXISTS mldn.depa (
id int NOT NULL PRIMARY key,
name varchar (50) NULL
) ; commit; (2)批量导入数据部门sql脚本如下:
LOAD DATA LOCAL INFILE 'e:/project/data/Bulletin.txt' INTO TABLE Bulletin;
LOAD DATA LOCAL INFILE 'e:/project/data/CustomOper.txt' INTO TABLE CustomOper;
LOAD DATA LOCAL INFILE 'e:/project/data/Msg.txt' INTO TABLE Msg;
LOAD DATA LOCAL INFILE 'e:/project/data/Resource.txt' INTO TABLE Resource;
LOAD DATA LOCAL INFILE 'e:/project/data/Role.txt' INTO TABLE Role;
LOAD DATA LOCAL INFILE 'e:/project/data/RoleRes.txt' INTO TABLE RoleRes;
LOAD DATA LOCAL INFILE 'e:/project/data/Users.txt' INTO TABLE Users;
LOAD DATA LOCAL INFILE 'e:/project/data/depa.txt' INTO TABLE depa; commit; (3)把(1)、(2)两部分代码放入文件input.sql。 5、登陆进入mysql命令行客户端,使用命令导入数据:
source e:/project/input.sql;
数据是导入了,但是原来正常的系统却变得不正常了。====================================================================================
后来,我用source命令直接导入SQL Server的备份文件,但是Mysql报错:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 549
Current database: wapERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 550
Current database: wapERROR 2006 (HY000): MySQL server has gone away真是郁闷。
CREATE TABLE [dbo].[Bulletin] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[poster] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[posttime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[CustomOper] (
[operId] [int] IDENTITY (1, 1) NOT NULL ,
[operName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[operAction] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[operImage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[resId] [int] NULL ,
[operLevel] [int] NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[Msg] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[poster] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[posttime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[jsr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[Resource] (
[resId] [int] IDENTITY (1, 1) NOT NULL ,
[resName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[resUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[parentId] [int] NULL ,
[resSeq] [int] NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[Role] (
[roleId] [int] IDENTITY (1, 1) NOT NULL ,
[roleName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[roleDesc] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[RoleRes] (
[roleResId] [int] IDENTITY (1, 1) NOT NULL ,
[roleId] [int] NULL ,
[resId] [int] NULL ,
[operate] [ntext] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO CREATE TABLE [dbo].[Users] (
[userId] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[userName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userPhone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userPwd] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[roleId] [int] NULL ,
[userAddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[depaId] [int] NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[depa] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO 2、在sql server 2000中打开表,把各个表数据分别复制到与各自表相同名字的txt文件中(ANSI编码),同一条记录的字段值间间隔Tab字符。 3、在mysql中建立一个数据库。 4、编写一个导入数据到mysql库的sql脚本文件input.sql。
(1)创建表部分内容如下:
CREATE TABLE IF NOT EXISTS mldn.Bulletin (
id int NOT NULL PRIMARY key,
title varchar (50) NULL ,
content varchar (500) NULL ,
poster varchar (50) NULL ,
posttime varchar (50) NULL
) ; CREATE TABLE IF NOT EXISTS mldn.CustomOper (
operId int NOT NULL PRIMARY key,
operName nvarchar (50) NULL ,
operAction nvarchar (50) NULL ,
operImage nvarchar (50) NULL ,
resId int NULL ,
operLevel int NULL
) ; CREATE TABLE IF NOT EXISTS mldn.Msg (
id int NOT NULL PRIMARY key,
title varchar (50) NULL ,
content varchar (500) NULL ,
poster varchar (50) NULL ,
posttime varchar (50) NULL ,
state varchar (50) NULL ,
jsr varchar (50) NULL
) ; CREATE TABLE IF NOT EXISTS mldn.Resource (
resId int NOT NULL PRIMARY key,
resName nvarchar (50) NULL ,
resUrl nvarchar (255) NULL ,
parentId int NULL ,
resSeq int NULL
) ; CREATE TABLE IF NOT EXISTS mldn.Role (
roleId int NOT NULL PRIMARY key,
roleName nvarchar (50) NULL ,
roleDesc nvarchar (255) NULL
) ; CREATE TABLE IF NOT EXISTS mldn.RoleRes (
roleResId int NOT NULL PRIMARY key,
roleId int NULL ,
resId int NULL ,
operate text (16) NULL
); CREATE TABLE IF NOT EXISTS mldn.Users (
userId nvarchar (50) NOT NULL PRIMARY key,
userName nvarchar (50) NULL ,
userPhone nvarchar (50) NULL ,
userPwd nvarchar (50) NULL ,
roleId int NULL ,
userAddress nvarchar (255) NULL ,
depaId int NULL
) ; CREATE TABLE IF NOT EXISTS mldn.depa (
id int NOT NULL PRIMARY key,
name varchar (50) NULL
) ; commit; (2)批量导入数据部门sql脚本如下:
LOAD DATA LOCAL INFILE 'e:/project/data/Bulletin.txt' INTO TABLE Bulletin;
LOAD DATA LOCAL INFILE 'e:/project/data/CustomOper.txt' INTO TABLE CustomOper;
LOAD DATA LOCAL INFILE 'e:/project/data/Msg.txt' INTO TABLE Msg;
LOAD DATA LOCAL INFILE 'e:/project/data/Resource.txt' INTO TABLE Resource;
LOAD DATA LOCAL INFILE 'e:/project/data/Role.txt' INTO TABLE Role;
LOAD DATA LOCAL INFILE 'e:/project/data/RoleRes.txt' INTO TABLE RoleRes;
LOAD DATA LOCAL INFILE 'e:/project/data/Users.txt' INTO TABLE Users;
LOAD DATA LOCAL INFILE 'e:/project/data/depa.txt' INTO TABLE depa; commit; (3)把(1)、(2)两部分代码放入文件input.sql。 5、登陆进入mysql命令行客户端,使用命令导入数据:
source e:/project/input.sql;
数据是导入了,但是原来正常的系统却变得不正常了。====================================================================================
后来,我用source命令直接导入SQL Server的备份文件,但是Mysql报错:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 549
Current database: wapERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 550
Current database: wapERROR 2006 (HY000): MySQL server has gone away真是郁闷。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货