关于mysql数据库在没有主键情况下去除重复数据办法
约定
表名:mat
根据 cat 字段去重
新增加主键为 id
步骤
1、为mat新增一列自增主键
alter table mat add column id int(11) PRIMARY KEY AUTO_INCREMENT;
2、根据cat字段分组,找到数量大于1的,并保留id最小的行
DELETE FROM mat WHERE `cat` IN ( SELECT `cat` FROM ( SELECT `cat` FROM mat GROUP BY `cat` HAVING count( `cat` ) > 1 ) a ) AND id NOT IN ( SELECT id FROM ( SELECT min( id ) AS id FROM mat GROUP BY `cat` HAVING count( `cat` ) > 1 ) b );
3、去除掉第一步添加的主键id
ALTER table mat DROP column id;
4、定义cat为主键
ALTER table mat add PRIMARY KEY (`cat`);