本文汇总了mysql导出所有index 和 约束的方法，提供给大家以方便大家查询使用。具体如下： 1. 导出创建自增字段语句： selectconcat('alter table `',table_name,'` ','modify column `',column_name,'` ',if(upper(data_type) = 'int',replace(substring_in
本文汇总了mysql导出所有index 和 约束的方法，提供给大家以方便大家查询使用。具体如下：
1. 导出创建自增字段语句：
selectconcat('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.columnswhere table_schema = 'source_database_name' andextra = upper('auto_increment')order by table_name asc
2. 导出所有索引：
selectconcat('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.statisticswhere table_schema = 'pbq'group by table_name, index_nameorder by table_name asc, index_name asc
3. 创建删除所有自增字段：
selectconcat('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.columnswhere table_schema = 'destination_database_name' andextra = upper('auto_increment')order by table_name asc
4. 删除库所有索引：
selectconcat('alter table `',table_name,'` ',group_concat(distinctconcat('drop ',if(upper(index_name) = 'primary','primary key',concat('index `', index_name, '`')))separator ', '),';')from information_schema.statisticswhere table_schema = 'destination_database_name'group by table_nameorder by table_name asc
希望本文所述示例能够对大家有所帮助。