国槐种植,人体艺术绘画,图们天气预报
本文汇总了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
希望本文所述示例能够对大家有所帮助。
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
网友评论