MySQL导出所有Index和约束的方法_Mysql

MySQL导出所有Index和约束的方法

2020-04-17 14:56shichen2014 Mysql

这篇文章主要介绍了MySQL导出所有Index和约束的方法,非常实用的技巧,需要的朋友可以参考下

本文汇总了MySQL导出所有Index 和约束的方法，提供给大家以方便大家查询使用。具体如下：

1. 导出创建自增字段语句：

				?

									SELECT

									CONCAT(

									'ALTER TABLE `',

									TABLE_NAME,

									'` ',

									'MODIFY COLUMN `',

									COLUMN_NAME,

									'` ',

									IF(UPPER(DATA_TYPE) = 'INT',

									REPLACE(

									SUBSTRING_INDEX(

									UPPER(COLUMN_TYPE),

									')',

									1

									),

									'INT',

									'INTEGER'

									),

									UPPER(COLUMN_TYPE)

									),

									') UNSIGNED NOT NULL AUTO_INCREMENT;'

									)

									FROM information_schema.COLUMNS

									WHERE TABLE_SCHEMA = 'source_database_name' AND

									EXTRA = UPPER('AUTO_INCREMENT')

									ORDER BY TABLE_NAME ASC

2. 导出所有索引：

				?

									SELECT

									CONCAT('ALTER TABLE `',TABLE_NAME,'` ', 'ADD ', 

									 IF(NON_UNIQUE = 1,

									 CASE UPPER(INDEX_TYPE)

									 WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'

									 WHEN 'SPATIAL' THEN 'SPATIAL INDEX'

									 ELSE CONCAT('INDEX `',

									  INDEX_NAME,

									  '` USING ',

									  INDEX_TYPE

									 )

									END,

									IF(UPPER(INDEX_NAME) = 'PRIMARY',

									 CONCAT('PRIMARY KEY USING ',

									 INDEX_TYPE

									 ),

									CONCAT('UNIQUE INDEX `',

									 INDEX_NAME,

									 '` USING ',

									 INDEX_TYPE

									)

									)

									),'(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes'

									FROM information_schema.STATISTICS

									WHERE TABLE_SCHEMA = 'pbq'

									GROUP BY TABLE_NAME, INDEX_NAME

									ORDER BY TABLE_NAME ASC, INDEX_NAME ASC

3. 创建删除所有自增字段：

				?

									SELECT

									CONCAT(

									'ALTER TABLE `',

									TABLE_NAME,

									'` ',

									'MODIFY COLUMN `',

									COLUMN_NAME,

									'` ',

									IF(UPPER(DATA_TYPE) = 'INT',

									REPLACE(

									SUBSTRING_INDEX(

									UPPER(COLUMN_TYPE),

									')',

									1

									),

									'INT',

									'INTEGER'

									),

									UPPER(COLUMN_TYPE)

									),

									') UNSIGNED NOT NULL;'

									)

									FROM information_schema.COLUMNS

									WHERE TABLE_SCHEMA = 'destination_database_name' AND

									EXTRA = UPPER('AUTO_INCREMENT')

									ORDER BY TABLE_NAME ASC

4. 删除库所有索引：

				?

									SELECT

									CONCAT(

									'ALTER TABLE `',

									TABLE_NAME,

									'` ',

									GROUP_CONCAT(

									DISTINCT

									CONCAT(

									'DROP ',

									IF(UPPER(INDEX_NAME) = 'PRIMARY',

									'PRIMARY KEY',

									CONCAT('INDEX `', INDEX_NAME, '`')

									)

									)

									SEPARATOR ', '

									),

									';'

									)

									FROM information_schema.STATISTICS

									WHERE TABLE_SCHEMA = 'destination_database_name'

									GROUP BY TABLE_NAME

									ORDER BY TABLE_NAME ASC