外键
查询一个表的主键是哪些表的外键
select
table_name,
column_name,
constraint_name,
referenced_table_name,
referenced_column_name
from
information_schema.key_column_usage
where
table_schema = 'mydbname'
and referenced_table_name = '表名';
导出所有外键语句
select
concat('alter table ', table_name, ' add constraint ', constraint_name, ' foreign key (', column_name, ') references ', referenced_table_name, '(', referenced_column_name, ') on delete cascade on update cascade;')
from
information_schema.key_column_usage
where
table_schema = 'mydbname'
and referenced_table_name is not null;
删除所有外键语句
select
concat('alter table ', table_name, ' drop foreign key ', constraint_name, ';')
from
information_schema.key_column_usage
where
table_schema = 'mydbname'
and referenced_table_name is not null;
自增
导出创建自增字段的语句
select
concat( 'alter table `', table_name, '` ', 'modify column `', column_name, '` ', upper( column_type ), ' not null auto_increment comment "',column_comment,'";' ) as 'add_auto_increment'
from
information_schema.columns
where
table_schema = 'mydbname'
and extra = upper( 'auto_increment' )
order by
table_name asc;
创建删除所有自增字段
select
concat( 'alter table `', table_name, '` ', 'modify column `', column_name, '` ', upper( column_type ), ' not null;' ) as 'delete_auto_increment'
from
information_schema.columns
where
table_schema = 'mydbname'
and extra = upper( 'auto_increment' )
order by
table_name asc;
索引
导出所有索引
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 ))),
concat( '(`', column_name, '`)' ),
';'
) as 'add_all_index'
from
information_schema.statistics
where
table_schema = 'mydbname'
order by
table_name asc,
index_name asc;
删除所有索引
select
concat( 'alter table `', table_name, '` ', concat( 'drop ', if ( upper( index_name ) = 'primary', 'primary key', concat( 'index `', index_name, '`' ))), ';' ) as 'delete_all_index'
from
information_schema.statistics
where
table_schema = 'mydbname'
order by
table_name asc;
数据合并
在数据迁移合并的时候,比较棘手的是不同数据库主键重复,那么我们就要批量修改主键的值,为了避免重复我们可以把自增的数字改为字符串
步骤基本上有以下几步
取消主键自增
删除所有外键
修改主键字段为varchar
添加所有外键
修改主键的值
合并数据
修改主键值的时候要注意
如果包含id和pid这种自关联的情况下是不能直接修改值的,就需要先删除约束再添加。
比如
删除自约束
alter table `t_director` drop foreign key `fk_directorpid`;
修改值
update t_director set directorid=directorid+100000000;
update t_director set directorid=conv(directorid,10,36);
update t_director set directorpid=directorpid+100000000 where directorpid is not null;
update t_director set directorpid=conv(directorpid,10,36) where directorpid is not null;
添加自约束
alter table t_director add constraint fk_directorpid foreign key (directorpid) references t_director(directorid) on delete cascade on update cascade;
注意
conv(directorpid,10,36)后两个参数为原数字进制和要转换后的进制。
第一个参数只要内容是数字就算类型为varchar也可以转换。
以上就是mysql中索引和约束的示例语句的详细内容,更多关于mysql 索引和约束的资料请关注萬仟网其它相关文章!
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!