作为DBA,时不时会遇到将数据导入到数据库的情况,假设业务或研发提供一个包含上百万行INSERT语句的脚本文件,而且这些INSERT
语句没有使用GO来进行批处理拆分,那么直接使用SQLCMD来执行会发现该进程消耗大量物理内存并迟迟没有数据写入,即使脚本中每一行都添加了GO,你依然会发现这插入效率太差,让你无法忍受,怎么搞呢,下面小代码帮你折腾下:

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

$old_file_path= 'C:SQL001.TXT'
$new_file_path='C:SQL001_New'
$tran_rows=10
$file_rows=30
$current_file_id=0

$line_num=0
$sr1=[IO.StreamReader]$old_file_path
$line_content=""
$row_content=""
while(-not $sr1.EndOfStream)
{
    if(($line_num%$file_rows) -eq 0)
    {
        $current_file_id=[int]$current_file_id+1
        $current_file_path = $new_file_path+"_"+$current_file_id
        $line_content=""
        $line_content |Out-File $current_file_path
    }

    if(($line_num%$tran_rows) -eq 0)
    {
        $line_content="
SET NOCOUNT ON
GO
BEGIN TRAN
BEGIN TRY
"
        $line_content |Out-File -Append $current_file_path
        $line_content=""
    }
    $tmp_content=$sr1.ReadLine()
    $row_content=$row_content+"`r"+$tmp_content

    $line_num=$line_num+1

    if((($line_num%$tran_rows) -eq 0) -or ($sr1.EndOfStream))
    {
        $row_content | Out-File -Append $current_file_path
        $row_content=""
        $batch_first_line=([int](($line_num+1)/$tran_rows-1))*$tran_rows+1
        $line_content="
COMMIT
--PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行成功'
END TRY
BEGIN CATCH
ROLLBACK
PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行失败'
END CATCH
GO
"
        $line_content | Out-File -Append $current_file_path
        Write-Host "处理到行" $line_num
    }



}

$sr1.Close()

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

 

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

2016年11月2日进行了下优化,当单个文件包含过多行时,会很难编辑处理,增加了按照行数拆分成文件的功能,同时优化下代码的可读性。

在MySQL中创建测试数据:

还是看点疗效吧,原始SQL为:

create table tb001(c1 int auto_increment primary key,c2 varchar(2000));
insert into tb001(c2) select 'abcrn';
insert into tb001(c2) select '你好啊rn';
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 '全角单引号’';

图片 1

##=====================================================================##
使用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

图片 2

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

这样实现有以下有点:

–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’ ##表名

1.  使用“SET NOCOUNT
ON”来屏蔽每条语句返回影响行数,减少与cmd窗口交互的次数;

导出结果为:

2.  将每50条语句拆分到一个批处理中,降低数据库进行语法检查分析的消耗,在封装到一个事务中进行提交,减少写日志的次数;

INSERT INTO "tb001" ("c1", "c2") VALUES (1,'abcrn');
INSERT INTO "tb001" ("c1", "c2") VALUES (2,'你好啊rn');
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,'全角单引号’');

3.  打印输出事务执行结果,方便排查错误(可修改代码只输出执行失败的事务)

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

执行结果截图:

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

图片 3

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

====================================================

使用SELECT INTO OUTFILE来导出数据

在个人电脑测试,以100条一个事务来拆分,大概1分钟可以导入50万到60万,按不同的行数进行拆分插入效率不同,具体合适的拆分行数需要根据实际情况测试为准。

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

对于超大数据量的导入,还是推荐使用csv+bcp的方式来导入,INSERT+SQLCMD的效率始终太低太低!

在Linux下看到的是这样:

====================================================

图片 4

发表评论

电子邮件地址不会被公开。 必填项已用*标注