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

原创已于 2024-04-21 22:42:52 修改 · 430 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #android #数据库

于 2024-02-23 12:56:39 首次发布

数据库专栏收录该内容

6 篇文章

订阅专栏

本文介绍了如何在MySQL中修改`group_concat_max_len`参数以处理大文本数据，然后提供了两个脚本，一个用于生成包含多表卸数语句（包括DEL、INTO和COUNT统计），另一个只包含删除和加载数据的语句，适用于sit数据库中的tinfo_change表。

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;