Insert into select 批量同步数据

查看某数据库中表信息

SELECT * from information_schema.tables
WHERE table_schema not in ('information_schema', 'mysql', 'performance_schema')
and table_schema='zaip_xman_avatar'
ORDER BY table_schema, table_name;

根据表名查询所有表字段

select group_concat(COLUMN_NAME) from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'

id,ds_name,ds_desc,ds_type,ds_address,ds_cluster_name,access_id,access_key,second_level_code,creator,modifier,gmt_created,gmt_modified,is_deleted,tenant_code

防止字段名是关键字导致异常

select concat('`',group_concat(column_name separator '`,`'),'`') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'

id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,expire_time_start,expire_time_end,expire_day,delivery_time,status,is_deleted,creator,gmt_created,modifier,gmt_modified

第二种方式，比上面一种方便

select group_concat(concat('`',column_name,'`') separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'

id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,expire_time_start,expire_time_end,expire_day,delivery_time,status,is_deleted,creator,gmt_created,modifier,gmt_modified

直接将column_name替换为：

concat('`',column_name,'`')

根据字段类型写对应的表达式

select group_concat(case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',column_name,')*1000') else column_name end separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'

上面是datetime转换成时间戳对应的sql

id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,UNIX_TIMESTAMP(expire_time_start)1000,UNIX_TIMESTAMP(expire_time_end)1000,expire_day,UNIX_TIMESTAMP(delivery_time)1000,status,is_deleted,creator,UNIX_TIMESTAMP(gmt_created)1000,modifier,UNIX_TIMESTAMP(gmt_modified)*1000

直接将column_name替换为：

case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',column_name,')*1000') else column_name end

防止字段名是关键字

select group_concat(case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',concat('`',column_name,'`'),')*1000') else concat('`',column_name,'`') end separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'

id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,UNIX_TIMESTAMP(expire_time_start)1000,UNIX_TIMESTAMP(expire_time_end)1000,expire_day,UNIX_TIMESTAMP(delivery_time)1000,status,is_deleted,creator,UNIX_TIMESTAMP(gmt_created)1000,modifier,UNIX_TIMESTAMP(gmt_modified)*1000

根据字段名称写对应表达式

select group_concat(case when column_name in ('gmt_created','gmt_modified') then concat('UNIX_TIMESTAMP(',column_name,')*1000') else column_name end separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'

id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,expire_time_start,expire_time_end,expire_day,delivery_time,status,is_deleted,creator,UNIX_TIMESTAMP(gmt_created)1000,modifier,UNIX_TIMESTAMP(gmt_modified)1000

生成查询数据库sql脚本

delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200))
begin 
   declare sqltext longtext default '';
   declare num,dataNum int default 0;
   set num=1;
	 
   set @rowNo=0;
	 drop table if exists temp;
	 create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and  table_schema=databaseName ORDER BY table_schema, table_name;
	 
	 while num<=@rowNo do
			 select concat(sqltext,'\n','select ',group_concat(COLUMN_NAME),' from ',table_name,';') into sqltext from information_schema.COLUMNS where table_name in (select table_name from temp where id=num);
			 set num=num+1;
	 end while;
	 drop table if exists temp;
	 select sqltext;
end $$
delimiter ;

-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;

-- 执行存储过程
call pro('zaip_xman_avatar');

GROUP_CONCAT超长截断问题

ROUP_CONCAT拼接的最大长度默认为1024字节，超过这个长度则会被截断，可以通过SET [GLOBAL | SESSION] group_concat_max_len = val;进行修改。

比如修改GROUP_CONCAT长度为1个字节。

SET SESSION group_concat_max_len = 1;

生成Insert into select数据库sql脚本

初始版，只有查询的库名，必须在要新增数据的库中运行

delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin 
   declare sqltext longtext default '';
   declare num,dataNum int default 0;
   set num=1;
	 
   set @rowNo=0;
	 drop table if exists temp;
	 create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and  table_schema=databaseName ORDER BY table_schema, table_name;
	 
	 while num<=@rowNo do
			 select concat(sqltext,'\n','insert ignore into ',table_name,' (',group_concat(column_name),') \n','select',lpad(' ',length(table_name)+15,' '),group_concat(COLUMN_NAME),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
			 set num=num+1;
	 end while;
	 drop table if exists temp;
	 select sqltext;
end $$
delimiter ;

-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;

-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');

新增insert库名

delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin 
   declare sqltext longtext default '';
   declare num,dataNum int default 0;
   set num=1;
	 
   set @rowNo=0;
	 drop table if exists temp;
	 create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and  table_schema=databaseName ORDER BY table_schema, table_name;
	 
	 while num<=@rowNo do
			 select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(column_name),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(COLUMN_NAME),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
			 set num=num+1;
	 end while;
	 drop table if exists temp;
	 select sqltext;
end $$
delimiter ;

-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;

-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');

call pro('zaip_xman_multi_tenant_new','zaip_xman_activity');

每个字段用`标志，防止字段名是关键字

delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin 
   declare sqltext longtext default '';
   declare num,dataNum int default 0;
   set num=1;
	 
   set @rowNo=0;
	 drop table if exists temp;
	 create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and  table_schema=databaseName ORDER BY table_schema, table_name;
	 
	 while num<=@rowNo do
			 select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(concat('`',column_name,'`')),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(concat('`',column_name,'`')),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
			 set num=num+1;
	 end while;
	 drop table if exists temp;
	 select sqltext;
end $$
delimiter ;

-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;

-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');

call pro('zaip_xman_multi_tenant_new','zaip_xman_activity');

将datetime转换成时间戳，自动生成语句

delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin 
   declare sqltext longtext default '';
   declare num,dataNum int default 0;
   set num=1;
	 
   set @rowNo=0;
	 drop table if exists temp;
	 create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and  table_schema=databaseName ORDER BY table_schema, table_name;
	 
	 while num<=@rowNo do
			 select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(concat('`',column_name,'`')),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',concat('`',column_name,'`'),')*1000') else concat('`',column_name,'`') end),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
			 set num=num+1;
	 end while;
	 drop table if exists temp;
	 select sqltext;
end $$
delimiter ;

-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;

-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');

call pro('zaip_xman_multi_tenant_new','zaip_xman_activity');

call pro('zaip_xman_xflow_new','zaip_xman_xflow');

生成的sql语句：

insert ignore into zaip_xman_xflow_new.zatlas_request_log (`id`,`time`,`email`,`appkey`,`remote_addr`,`method`,`uri`,`response_time`,`token`,`status`,`gmt_created`,`gmt_modified`,`creator`,`modifier`,`is_deleted`) 
select                                                     `id`,UNIX_TIMESTAMP(`time`)*1000,`email`,`appkey`,`remote_addr`,`method`,`uri`,`response_time`,`token`,`status`,UNIX_TIMESTAMP(`gmt_created`)*1000,UNIX_TIMESTAMP(`gmt_modified`)*1000,`creator`,`modifier`,`is_deleted` from zaip_xman_xflow.zatlas_request_log;

根据字段名判断类型，自动生成语句

delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin 
   declare sqltext longtext default '';
   declare num,dataNum int default 0;
   set num=1;
	 
   set @rowNo=0;
	 drop table if exists temp;
	 create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and  table_schema=databaseName ORDER BY table_schema, table_name;
	 
	 while num<=@rowNo do
			 select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(concat('`',column_name,'`')),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(case when column_name in ('gmt_created','gmt_modified','attribution_begin_date','attribution_end_date','last_warning_time','effective_time','begin_date','end_date','analyze_begin_date','analyze_end_date','funnel_begin_date','funnel_end_date','bizdate_date') then concat('UNIX_TIMESTAMP(',concat('`',column_name,'`'),')*1000') else concat('`',column_name,'`') end),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
			 set num=num+1;
	 end while;
	 drop table if exists temp;
	 select sqltext;
end $$
delimiter ;

-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;

call pro('zaip_xman_xflow_new','zaip_xman_xflow');

将字段名'gmt_created','gmt_modified','attribution_begin_date','attribution_end_date','last_warning_time','effective_time','begin_date','end_date','analyze_begin_date','analyze_end_date','funnel_begin_date','funnel_end_date','bizdate_date'字段当成时间戳类型查出

校验mysql

校验mysql库里面表数量

SELECT table_schema,count(1) as num from information_schema.tables
WHERE table_schema in ('zaip_xman_activity_new','zaip_xman_avatar_new','zaip_xman_cms_new','zaip_xman_common_mgt_new','zaip_xman_lang_new','zaip_xman_markting_new','zaip_xman_multi_tenant_new','zaip_xman_push_new','zaip_xman_xflow_new')
GROUP BY table_schema;

校验对比迁移数据量是否一致

delimiter $$
drop procedure if exists testMigrate $$
create procedure testMigrate(databaseName varchar(200),originDataBaseName varchar(200))
begin 
	 SELECT 
    a.table_name, 
		a.table_rows as newTableRows,
    b.table_rows as oldTableRows
FROM
    information_schema.tables a 
		join information_schema.tables b 
		on a.table_name=b.table_name
WHERE
    a.table_schema = databaseName
		and b.table_schema = originDataBaseName
		and a.table_type = 'BASE TABLE'
		and b.table_type = 'BASE TABLE'
		and a.table_rows<>b.table_rows;
end $$
delimiter ;

call testMigrate('zaip_xman_xflow_new','zaip_xman_xflow');

为什么mysql没有nolock

SQLSERVER 默认情况下,一个事务修改了某个值，在这个事务提交前，是阻塞其他连接来读取这个修改中的值的，如果加nolock，那么读取到的可能是修改后未提交的值(也就是脏读，因为可能这个值最终会回滚)
MySQL 一个事务修改了某个值，在这个事务提交前，不阻塞其他连接来读取这个修改中的值，并且读取到的是修改前的值。