金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 将MySQL数据导入到SQL,批量拆分SQL语句为事务并批

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

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-04 21:26

对此非常的大数据量的导入,依旧引入使用csv+bcp的方法来导入,INSERT+SQLCMD的成效始终太低太低!

地点脚本的后生可畏部分注解表明:

如上便是本文的全部内容,希望对大家的学习抱有利于,也希望大家多多点拨脚本之家。

2.  将每50条语句拆分到叁个批管理中,减少数据库进行语法检查剖判的消耗,在封装到三个作业中开展提交,减少写日记的次数;

实施结果为:

  1. 数据有序插入。

用作DBA,时偶尔会蒙受将数据导入到数据库的景色,尽管工作或研发提供三个包罗上百万行INSERT语句的本子文件,并且那个INSERT 语句未有使用GO来举行批管理拆分,那么直接动用SQLCMD来实施会发觉该进度消耗大量物理内部存款和储蓄器并暂缓十分少写入,即便脚本中每风度翩翩行都增添了GO,你还是会发觉那插入作用太差,让您不能忍受,怎么搞呢,上边小代码帮你折腾下:

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

要么看点医疗效果吧,原始SQL为:

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

修改成:

图片 1

静心运用SELECT INTO OUTFILE导出文件时,NULL值被代表为N,而N在导入SQL Server时会被当场字符串“N”来拍卖,因而提出先成立八个全然由NVARCHARAV4类型列组成的表来“暂存”导入的时候,然后通过多少清理后再导入正式表中,对于懒与一列一列折腾的人的话,能够拼下SQL来博取表的具有列调换:

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

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

MySQLdump能够将数据导成INSERT语句,并提供配置包容别的数据库的参数,但鉴于分裂数据库转义字符分歧,因而尽管接受compatible=mssql也不可能担保导出的脚本能在SQL Server中平常施行。

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

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

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

此处提供了並且采取方面三种艺术实行INSERT功效优化的测量试验。

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

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; 

如此那般达成成以下有一些:

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 '全角单引号’';

从测量检验结果来看,该优化措施的习性有所提升,可是提升并非很鲜明。

二〇一四年7月2日进展了下优化,当单个文件包蕴过多行时,会很难编辑管理,扩大了如约行数拆分成文件的法力,同有的时候候优化下代码的可读性。

好啊,是时候上妹子啊。

2. 事务需求调节大小,事务太大大概会影响实践的频率。MySQL有innodb_log_buffer_size配置项,超越这么些值会把innodb的数据刷到磁盘中,这个时候,效用会持有下降。所以比较好的做法是,在多少到达这些这么些值前扩充职业提交。

实行结果截图:

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

数据有序的插入是指插入记录在主键上是逐步排列,举个例子datetime是记录的主键:

图片 2

随着一代的腾飞,社会的腾飞,种种工夫不可胜举五花八门横三竖四数不完(写作文吗!!!卡塔尔国

订正后的插入操作能够加强程序的插入功效。这里第三种SQL施行功用高的主因是联合前几天志量(MySQL的binlog和innodb的业务让 日志卡塔 尔(阿拉伯语:قطر‎裁减了,减少日志刷盘的数据量和频率,进而升高功效。通过统生龙活虎SQL语句,同时也能减少SQL语句解析的次数,减少互联网传输的IO。

没啥技能含量,厚脸拿出去分享,只是因为比较久没写博客啦!

动用SELECT INTO OUTFILE来导出多少

利用工作能够坚实数据的插入成效,那是因为实行多个INSERT操作时,MySQL内部会创建三个事务,在作行业内部才进行真正插入管理操作。通过动用工作能够收缩创制专门的学问的损耗,全部插入都在进行后才实行付出操作。

通过此脚本改善过的产生:

最终语句为:

这里也提供了测量试验对照,分别是不行使专门的职业与利用专门的职业在记录数为1百、1千、1万的景况。

1.  采取“SET NOCOUNT ON”来掩盖每条语句再次回到影响行数,缩小与cmd窗口交互的次数;

在MySQL中创建测量试验数据:

此处提供一些测量检验对照数据,分别是进行单条数据的导入与转产生一条SQL语句举办导入,分别测量检验1百、1千、1万条数据记录。

 

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

1. SQL语句是有长度约束,在张开多少统生机勃勃在同意气风发SQL中必须无法超越SQL长度约束,通过max_allowed_packet配置能够修改,私下认可是1M,测验时改进为8M。

3.  打印输出事务试行结果,方便逐个审查核对错误(可改进代码只输出实践倒闭的专门的学问卡塔尔

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

注意事项:

图片 3

图片 4

图片 5

图片 6

在Linux下见到的是如此:

由于数据库插入时,需求爱慕索引数据,冬辰的记录会增大维护索引的资金。我们能够参照innodb使用的B+tree索引,如若每一遍插入记录都在索 引的尾声面,索引的一定功效超高,并且对索引调度异常的小;若是插入的笔录在目录中间,要求B+tree实行不一致归并等拍卖,会消耗非常多划算财富,并且插入 记录的目录定位成效会下落,数据量不小时会有多次的磁盘操作。

在民用Computer测量试验,以100条多个事情来拆分,差十分的少1分钟能够导入50万到60万,按不一样的行数进行拆分插入成效分裂,具体适用的拆分行数须要凭仗实情测量试验为准。

本来使用工具最轻便易行,可是小编就大体育工作具!!!

品质综合测量检验:

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

通过对MySQL innodb的意气风发部分性格测量试验,发掘成些足以拉长insert功效的不二等秘书技,供大家参照他事他说加以考察参照他事他说加以调查。

图片 7

图片 8

SELECT * INTO OUTFILE '/tmp/tb001.txt' 
FIELDS TERMINATED BY '||--||' 
LINES TERMINATED BY '||==||' FROM test.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); 

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

把插入改正成:

对此列名用双引号的主题材料,能够运用SET QUOTED_IDENTIFIE福睿斯 ON 来管理,也能够接收SQLCMD加-I参数来拍卖
然则对文件中的单引号就无解了,MySQL中选取""来作为转义符,而SQL Server中动用五个单引号来代表二个单引号。

从测验结果能够见见,合併数据+事务的不二秘技在十分小数据量时,品质升高是很显眼的,数据量极大时(1千万以上卡塔 尔(英语:State of Qatar),质量会小幅下落,那是由于当时数据量 超越了innodb_buffer的容积,每便一定索引涉及很多的磁盘读写操作,性能裁减超级快。而选用合併数据+事务+有序数据的不二诀要在数据量达到千万级 以上表现仍为实至名归,在数据量相当的大时,有序数据索引定位较为平价,不必要频仍对磁盘举行读写操作,所以可以维持较高的性质。

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

导出结果为:

图片 9

虽说有个别乱,不过忍啦!

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

由于大家强行将N当成NULL来调换,难免会产生损害,将真实数据就为’N‘的值变为NULL,因而导完数据后检查是必得的。

你只怕感兴趣的篇章:

  • MYSQL开荒性质研讨之批量安插数据的优化措施
  • MySql批量插入优化Sql履行效能实例安详严整
  • mysql怎样优化插入记录速度
  • MySQL达成批量布置以优化品质的课程
  • 解析优化MySQL插入方法的四个高招
  • 深远mysql并发插入优化详细解释
  • MySQL 大数据量火速插入方法和言辞优化共享
  • mysql 数据插入优化措施
  • MySql中把一个表的数量插入到另三个表中的落到实处代码
  • mysql中高速插入百万条测量试验数据的形式

也得以应用SQL Server的导入导出工具来拍卖,首要改进分隔符。

对此有些数据量超级大的体系,数据库直面的难题除了查询成效低下,还会有就是数额入库时间长。非常像报表系统,天天花销在多少导入上的时间或然社长达多少个钟头或21个小时之久。因此,优化数据库插入品质是很有含义的。

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

  1. 在事业中开展插队管理。

导出INSERT脚本存在转义字符单引号的主题材料,相同的时候导出数据不带有GO,在需求大批量多少导入到SQL SE昂科拉VEEnclave时存在严重的习性难点,能够尝尝参谋本身的《Powershell--批量拆分SQL语句为职业并批管理》来管理,但也是主题材料多多。

图片 10

图片 11

常用的插入语句如:

而导出文件然后导入的诀窍,必要对文本进行贰遍转变,文件比较大时notepad++可能十分的小概打卡,UE能微微给力点,但面临某些个G的文书文件也是不能,同一时候NULL值管理也供给谨慎对待。

修改成:

##=====================================================================##
动用mysqldump来导出与MS SQL SETiguanVE奥德赛包容的INSERT 语句:

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:将MySQL数据导入到SQL,批量拆分SQL语句为事务并批

关键词: