欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-31 15:57:34
本文汇总了mysql导出所有index 和 约束的方法,提供给大家以方便大家查询使用。具体如下: 1. 导出创建自增字段语句: select concat(...

本文汇总了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

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