批量执行SQL文件，统计每个文件的执行时间

     当有一批sql文件需要实行的时候，每个文件执行的时间不一样， 上线之前需要记录运行时间，如果sql文件不能并行执行必须按照顺序执行（假设已经通过测试不会出错），那么就得守在终端前面等待一个sql执行完之后再执行下一个。将所有文件名集中在一个sql里面执行可以省下大把的时光。并且还能记录每个文件执行的时间点。

  

 1、将所有sql文件名写到一个sql文件里，用 ls  >  all.sql ，打开文件需要删除 all.sql 行 ，如下：

 

2、利用sed编辑文件

sed 's/\([^;]*\.sql\)/set @start:=unix_timestamp(),@sqlname="\1";source \1/; s/$/; select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime /g ' all.sql

 

 3、结果如下，执行该脚本即可：

 1 select @start:=unix_timestamp(),@sqlname:="optimize-1-role.sql";source optimize-1-role.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 2 select @start:=unix_timestamp(),@sqlname:="optimize-2-couple.sql";source optimize-2-couple.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 3 select @start:=unix_timestamp(),@sqlname:="optimize-2-dance-group.sql";source optimize-2-dance-group.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 4 select @start:=unix_timestamp(),@sqlname:="optimize-2-event.sql";source optimize-2-event.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 5 select @start:=unix_timestamp(),@sqlname:="optimize-2-friend.sql";source optimize-2-friend.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 6 select @start:=unix_timestamp(),@sqlname:="optimize-2-item.sql";source optimize-2-item.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 7 select @start:=unix_timestamp(),@sqlname:="optimize-2-magic-lamp.sql";source optimize-2-magic-lamp.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 8 select @start:=unix_timestamp(),@sqlname:="optimize-2-mail.sql";source optimize-2-mail.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
 9 select @start:=unix_timestamp(),@sqlname:="optimize-2-medal-progress.sql";source optimize-2-medal-progress.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
10 select @start:=unix_timestamp(),@sqlname:="optimize-2-medal.sql";source optimize-2-medal.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
11 select @start:=unix_timestamp(),@sqlname:="optimize-2-other-2.sql";source optimize-2-other-2.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
12 select @start:=unix_timestamp(),@sqlname:="optimize-2-other.sql";source optimize-2-other.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
13 select @start:=unix_timestamp(),@sqlname:="optimize-2-player.sql";source optimize-2-player.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
14 select @start:=unix_timestamp(),@sqlname:="optimize-2-quest.sql";source optimize-2-quest.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;
15 select @start:=unix_timestamp(),@sqlname:="optimize-3-rank.sql";source optimize-3-rank.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;

 

转载于:https://www.cnblogs.com/huangjinbin/p/8557187.html