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

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

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！