mysql自动生成卸数文件和导数脚本

mysql自动生成卸数文件和导数脚本

1、修改参数变量

show variables like 'group_concat_max_len';
set session group_concat_max_len=202400;

2、生成多表卸数语句程序

select group_concat('',B.UNDO_SQL order by B.TABLE_NAME separator '\r') from (
select  u.TABLE_SCHEMA,u.TABLE_NAME ,concat_ws('','select',
	(select group_concat(concat_ws('','\r\t\`',a.COLUMN_NAME,'\`')
			 order by a.ordinal_position
			separator ',')
		 as x
	FROM
		information_schema.`COLUMNS` a
	where a.table_schema=u.TABLE_SCHEMA
	   and a.TABLE_NAME =u.TABLE_NAME
	  group by a.TABLE_NAME
	  ),' \r\t from ',u.TABLE_NAME ,'  into outfile \'/home/mysqldata/mysql_dump/files/',u.TABLE_NAME,
	  '.txt\'',
	  '\r character set utf8mb4 fields terminated by x\'0f\'',' enclosed by \'\\"\'',' lines terminated by \'\\n\';\r') as 'UNDO_SQL'
  from information_schema.`TABLES` u where u.TABLE_SCHEMA  = 'sit'
 and u.TABLE_NAME  in ('info_change')
order by u.TABLE_SCHEMA,u.TABLE_NAME
) as B
group by B.TABLE_SCHEMA;

3、生成多表卸数语句程序

*** 包括删除DEL语句、加载INTO语句、统计COUNT语句***

select  u.TABLE_NAME ,concat_ws('','\r delete from ',u.TABLE_NAME ,';\r',' load data local infile \'/home/tmper/mysqlbackdump/files/',u.TABLE_NAME,
	  '.txt\'',
	  ' \r into table ',u.TABLE_NAME,' \r fields terminated by x\'0f\'',' enclosed by \'\\"\'',' lines terminated by \'\\n\';\r',
	  '\r select count(1) as ',u.TABLE_NAME,'_count from ',u.TABLE_NAME ,';\r') as ' ---------SQLS----- ---------------------------'
  from information_schema.`TABLES` u where u.TABLE_SCHEMA  = 'sit'
 and u.TABLE_NAME  in ('info_change')
order by u.TABLE_NAME;