MySQL--将MySQL数据导入到SQL Server

时间:2022-12-04 10:47:46

随着时代的进步,社会的发展,各种技术层出不穷五花八门乱七八糟数不胜数(写作文呢!!!)

不扯废话,简单而言,很多公司都会同时使用多种数据库,因此数据在不同数据库之间导入导出就成为一个让人蛋疼的问题,对于周期行的需求,可以开发专门的程序处理,但是对于偶尔不确定性的需求,就到了需要DBA献身的时候啦,当需要将MySQL数据导入到SQL Server中时,该怎么搞呢?

当然使用工具最简单,但是我就忽略工具!!!

在MySQL中创建测试数据:

create table tb001(c1 int auto_increment primary key,c2 varchar(2000));
insert into tb001(c2) select 'abc\r\n';
insert into tb001(c2) select '你好啊\r\n';
insert into tb001(c2) select '你好啊\n';
insert into tb001(c2) select '双引号"';
insert into tb001(c2) select '全角双引号“';
insert into tb001(c2) select '单引号''';
insert into tb001(c2) select '全角单引号’';

##=====================================================================##
使用mysqldump来导出与MS SQL SERVER兼容的INSERT 语句:

mysqldump --host='192.168.166.169' --port=3358 --user='mysql_admin' --password='mysql@Admin@Pwd' --skip-add-locks --compatible=mssql --complete-insert --compact --extended-insert=false --default-character-set=utf8 -t --databases 'test' --table 'tb001' >/tmp/t4.sql

上面脚本的一些注释说明:

--compatible=mssql ##导出的SQL与MS SQL Server兼容
--complete-insert ##导出的INSERT语句包含列名
--compact ##采用精简模式,不输出各种MySQL信息
--extended-insert=false ##采用一行数据一条INSERT的方式
--default-character-set=utf8 ##指定导出的字符集
-t ##-t表示只导出数据,-d表示只导出数据结构
--databases 'test' ##数据库名称
--table 'CityMatchup' ##表名

导出结果为:

INSERT INTO "tb001" ("c1", "c2") VALUES (1,'abc\r\n');
INSERT INTO "tb001" ("c1", "c2") VALUES (2,'你好啊\r\n');
INSERT INTO "tb001" ("c1", "c2") VALUES (3,'你好啊\n');
INSERT INTO "tb001" ("c1", "c2") VALUES (4,'双引号\"');
INSERT INTO "tb001" ("c1", "c2") VALUES (5,'全角双引号“');
INSERT INTO "tb001" ("c1", "c2") VALUES (6,'单引号\'');
INSERT INTO "tb001" ("c1", "c2") VALUES (7,'全角单引号’');

对于列名用双引号的问题,可以使用SET QUOTED_IDENTIFIER ON 来处理,也可以使用SQLCMD加-I参数来处理
但是对文本中的单引号就无解了,MySQL中使用"\"来作为转义符,而SQL Server中使用两个单引号来表示一个单引号。

MySQLdump可以将数据导成INSERT语句,并提供配置兼容其他数据库的参数,但由于不同数据库转义字符不同,因此即使使用compatible=mssql也不能保证导出的脚本能在SQL Server中正常执行。

##===========================================================================##

使用SELECT INTO OUTFILE来导出数据

SELECT * INTO OUTFILE '/tmp/tb001.txt'
FIELDS TERMINATED BY '||--||'
LINES TERMINATED BY '||==||' FROM test.tb001;

在Linux下看到的是这样:

MySQL--将MySQL数据导入到SQL Server

虽然有点乱,但是忍啦!

然后下载文件,使用notepad++打开,选择“格式”>> "转为ANSI编码格式" ,然后另存为新文件,在SQL Server中使用BULK INSERT来导入:

CREATE TABLE tmp_tb001(id NVARCHAR(2000),c1 NVARCHAR(2000))
GO
BULK INSERT tmp_tb001
FROM 'D:\tb002.txt'
WITH(FIELDTERMINATOR='||--||',
ROWTERMINATOR='||==||'
)
GO
SELECT * FROM tmp_tb001

也可以使用SQL Server的导入导出工具来处理,主要修改分隔符。

注意使用SELECT INTO OUTFILE导出文件时,NULL值被表示为\N,而\N在导入SQL Server时会被当初字符串“\N”来处理,因此建议先建立一个完全由NVARCHAR类型列组成的表来“暂存”导入的时候,然后经过数据清理后再导入正式表中,对于懒与一列一列折腾的人来说,可以拼下SQL来获取表的所有列转换:

SELECT
'CASE WHEN ['+T1.name+']=''\N'' THEN NULL ELSE ['+T1.name+'] END AS ['+T1.name+'],'
FROM sys.all_columns T1
WHERE T1.object_id=OBJECT_ID('tmp_tb001')

由于我们强行将\N当成NULL来转换,难免会造成误伤,将真实数据就为’\N‘的值变为NULL,因此导完数据后检查是必须的。

最后语句为:

SELECT
CASE WHEN [id]='\N' THEN NULL ELSE [id] END AS [id],
CASE WHEN [c1]='\N' THEN NULL ELSE [c1] END AS [c1]
FROM tmp_tb001

执行结果为:

MySQL--将MySQL数据导入到SQL Server

##=======================================================================##

导出INSERT脚本存在转义字符单引号的问题,同时导出数据不包含GO,在需要大量数据导入到SQL SERVER时存在严重的性能问题,可以尝试参考本人的《Powershell--批量拆分SQL语句为事务并批处理》来处理,但也是问题多多。

而导出文件然后导入的方式,需要对文件进行一次转换,文件较大时notepad++可能无法打卡,UE能稍微给力点,但面对好几个G的文本文件也是无力回天,同时NULL值处理也需要慎重对待。

##========================================================================##

好啦,是时候上妹子啦。

MySQL--将MySQL数据导入到SQL Server