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

希望本文所述示例能够对大家有所帮助。