将MySQL数据导入到SQL,批量拆分SQL语句为业务并批

作为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中时,该怎么搞呢?

透过对MySQL innodb的有的属性测验,开掘一些方可拉长insert效用的法子,供大家参谋参谋。

 

本来使用工具最简单易行,可是笔者就轮廓工具!!!

  1. 一条SQL语句插入多条数据。

二〇一五年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 '全角单引号’';
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 

图片 1

##=====================================================================##
采纳mysqldump来导出与MS SQL SE奔驰M级VETiggo宽容的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
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1); 

图片 2

上面脚本的部分解说表达:

修改后的插入操作能够进步程序的插入效能。这里第三种SQL实行功能高的主因是统一前几日志量(MySQL的binlog和innodb的业务让 日志)收缩了,减弱日志刷盘的数据量和频率,进而升高效用。通过集结SQL语句,同有时间也能减徐熙娣女士(Elephant Dee)QL语句分析的次数,减弱网络传输的IO。

像这种类型实现有以下有一点:

--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' ##表名

这里提供部分测量试验对照数据,分别是进展单条数据的导入与转化成一条SQL语句实行导入,分别测量试验1百、1千、1万条数据记录。

1.  施用“SET NOCOUNT ON”来隐瞒每条语句重回影响行数,收缩与cmd窗口交互的次数;

导出结果为:

图片 3

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,'全角单引号’');
  1. 在专门的工作中张开插队管理。

3.  打字与印刷输出事务实践结果,方便排查错误(可修改代码只输出试行歇业的作业)

对于列名用双引号的主题材料,能够运用SET QUOTED_IDENTIFIE奥迪Q7 ON 来管理,也能够接纳SQLCMD加-I参数来拍卖
但是对文件中的单引号就无解了,MySQL中采取""来作为转义符,而SQL Server中动用五个单引号来表示多个单引号。

把插入修改成:

实施结果截图:

MySQLdump能够将数据导成INSERT语句,并提供配置宽容其余数据库的参数,但鉴于不一样数据库转义字符分化,因而尽管使用compatible=mssql也不能够保证导出的台本能在SQL Server中常常施行。

START TRANSACTION; 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 
... 
COMMIT; 

图片 4

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

接纳专业能够增进数据的插入功效,那是因为进行贰个INSERT操作时,MySQL内部会创制二个政工,在作行业内部才实行真正插入管理操作。通过利用专业能够减掉成立专门的学问的消耗,全数插入都在执行后才进行提交操作。

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

使用SELECT INTO OUTFILE来导出多少

那边也提供了测验对照,分别是不行使专门的学问与利用专门的学问在记录数为1百、1千、1万的情事。

在个人计算机测验,以100条一个事情来拆分,大致1分钟能够导入50万到60万,按区别的行数举行拆分插入功用分化,具体适用的拆分行数须要依附实况测量试验为准。

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

图片 5

对此超大数据量的导入,依旧引入使用csv bcp的法子来导入,INSERT SQLCMD的功用始终太低太低!

在Linux下看到的是如此:

  1. 数占有序插入。

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

图片 6

多少有序的插入是指插入记录在主键上是铁板钉钉排列,举个例子datetime是记录的主键:

没啥能力含量,厚脸拿出去共享,只是因为相当久没写博客啦!

固然有一点点乱,不过忍啦!

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2); 

图片 7

接下来下载文件,使用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
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2); 

也得以选择SQL Server的导入导出工具来拍卖,首要修改分隔符。

出于数据库插入时,供给敬服索引数据,冬季的记录会增大维护索引的资金。大家能够参照innodb使用的B tree索引,要是老是插入记录都在索 引的最终面,索引的一定成效非常高,並且对索引调解十分的小;假如插入的笔录在目录中间,必要B tree举行分歧合併等拍卖,会损耗比比较多划算能源,並且插入 记录的目录定位成效会减低,数据量极大时会有频仍的磁盘操作。

只顾采用SELECT INTO OUTFILE导出文件时,NULL值被代表为N,而N在导入SQL Server时会被当下字符串“N”来管理,由此提议先创造一个完全由NVARCHALacrosse类型列组成的表来“暂存”导入的时候,然后经过数据清理后再导入正式表中,对于懒与一列一列折腾的人的话,能够拼下SQL来赢得表的具备列调换:

上面提供随机数据与各种数据的属性相比,分别是记录为1百、1千、1万、10万、100万。

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')

图片 8

是因为大家强行将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

此处提供了况兼选取方面二种艺术进行INSERT效能优化的测量检验。

实施结果为:

图片 9

图片 10

从测验结果能够看出,合併数据 事务的秘技在异常的小数据量时,品质进步是很明显的,数据量十分大时(1千万以上),品质会小幅下落,那是由于此时数据量 超过了innodb_buffer的体量,每回一定索引涉及很多的磁盘读写操作,品质减弱异常快。而使用合併数据 事务 有序数据的秘诀在数据量达到千万级 以上表现依然是优质,在数据量很大时,有序数据索引定位较为有利,不必要再三对磁盘实行读写操作,所以能够维持较高的属性。

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

注意事项:

导出INSERT脚本存在转义字符单引号的标题,同期导出数据不带有GO,在需求大量数码导入到SQL SE福睿斯VE卡宴时存在严重的属性难题,能够尝尝参谋本身的《Powershell--批量拆分SQL语句为作业并批管理》来拍卖,但也是主题材料多多。

1. SQL语句是有长度限制,在拓宽数据统一在同一SQL中必得不可能超过SQL长度限制,通过max_allowed_packet配置能够修改,暗中认可是1M,测量试验时修改为8M。

而导出文件然后导入的措施,必要对文件进行贰次转换,文件相当的大时notepad 只怕不可能打卡,UE能稍微给力点,但面临有些个G的公文文件也是敬谢不敏,同有时候NULL值管理也亟需审慎对待。

2. 作业供给调整大小,事务太大或许会潜移默化实行的功能。MySQL有innodb_log_buffer_size配置项,超越那个值会把innodb的数量刷到磁盘中,这时,功效会具有回退。所以比较好的做法是,在数量到达那一个这些值前进行当务提交。

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

上述正是本文的全部内容,希望对大家的学习抱有助于,也期望大家多多点拨脚本之家。

好啊,是时候上妹子啊。

您可能感兴趣的篇章:

  • MYSQL开拓性质钻探之批量布置数据的优化措施
  • MySql批量插入优化Sql奉行功效实例详解
  • mysql怎样优化插入记录速度
  • MySQL实现批量插入以优化质量的课程
  • 剖判优化MySQL插入方法的多少个妙计
  • 深深mysql并发插入优化详解
  • MySQL 大数据量快捷插入方法和话语优化共享
  • mysql 数据插入优化措施
  • MySql中把三个表的数码插入到另三个表中的落到实处代码
  • mysql中飞速插入百万条测量检验数据的章程

图片 11

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:将MySQL数据导入到SQL,批量拆分SQL语句为业务并批

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。