mysql 删除增加库中所有索引-蒲公英云

mysql 删除增加库中所有索引

青旅半醒 2022-12-27 07:24 253阅读 0赞

-- drop and and index
SELECT CONCAT('alter table ',i.TABLE_SCHEMA,'.',i.TABLE_NAME,' drop index  ',i.INDEX_NAME,' ;')  as exec
FROM INFORMATION_SCHEMA.STATISTICS i
#过滤主键索引
WHERE TABLE_SCHEMA = 'wise_base_acl' AND i.INDEX_NAME <> 'PRIMARY' union all
SELECT CONCAT('alter table ',i.TABLE_SCHEMA,'.',i.TABLE_NAME,' add index  ',i.INDEX_NAME,'(',i.INDEX_NAME,')',' ;') as exec
FROM INFORMATION_SCHEMA.STATISTICS i
#过滤主键索引
WHERE TABLE_SCHEMA = 'wise_base_acl' AND i.INDEX_NAME <> 'PRIMARY';
alter table wise_base_acl.resource drop index  merchant_id ;
-- ...
alter table wise_base_acl.resource add index  merchant_id(merchant_id) ;
-- ...
-- if mysql version support (if exists/if not exists)
SELECT CONCAT('alter table ',i.TABLE_SCHEMA,'.',i.TABLE_NAME,' drop index if exists ',i.INDEX_NAME,' ;')  as exec
FROM INFORMATION_SCHEMA.STATISTICS i
#过滤主键索引
WHERE TABLE_SCHEMA = 'wise_base_acl' AND i.INDEX_NAME <> 'PRIMARY' union all
SELECT CONCAT('alter table ',i.TABLE_SCHEMA,'.',i.TABLE_NAME,' add index if not exists ',i.INDEX_NAME,'(',i.INDEX_NAME,')',' ;') as exec
FROM INFORMATION_SCHEMA.STATISTICS i
#过滤主键索引
WHERE TABLE_SCHEMA = 'wise_base_acl' AND i.INDEX_NAME <> 'PRIMARY'