Handle innodb deadlocks
http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks.html
ALTER TABLE tb_domain ADD INDEX groupid_name_index (group_id, name);
ALTER TABLE tb_view_history ADD INDEX domainid_view_code (domain_id, view_code);
alter table t add constraint t_unique_key unique (g);
通过ignore删除表里面重复的数据,由于InnoDB有写版本有bug,所以先切换到MsISAM,然后修改。
ALTER TABLE tb_view ENGINE MyISAM;
ALTER IGNORE TABLE tb_view ADD UNIQUE INDEX domainid_view_code_unique (domain_id, view_code)
ALTER TABLE tb_view ENGINE InnoDB;
ALTER IGNORE TABLE tb_view ADD UNIQUE INDEX domainid_view_code_unique (domain_id, view_code)
ALTER TABLE tb_view ENGINE InnoDB;
限制Key的长度,MyISAM是1000, InnoDB是767,不兼容,需要手动限制。
ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );
查找表中重复的数据。
select a.domain_id,a.view_code from tb_view a inner join tb_view b where a.view_code=b.view_code and a.domain_id=b.domain_id and a.id!=b.id group by a.domain_id
select group_id,c.domain_id,c.view_code from tb_domain inner join (select a.domain_id,a.view_code from tb_view a inner join tb_view b where a.view_code=b.view_code and a.domain_id=b.domain_id and a.id!=b.id group by a.domain_id) c where id=c.domain_id;
删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY