创建表 :
DROP TABLE IF EXISTS `m_repeat`;
CREATE TABLE `m_repeat` (
`id` bigint(10) NOT NULL auto_increment,
`name` varchar(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
删除name相同的数据,且保留最小id的数据。
方法一:
复制无重复记录到新表格,删除旧表格,然后重命名新表格为旧表名称。
新建表:
create table m_repeat_1
select * from m_repeat where id in (select min(id) from m_repeat group by name )
删除并重命名:
drop table m_repeat
alter table m_repeat_1 rename m_repeat
alter table m_repeat modify id int(2) not null primary key auto_increment
方法二:
delete m_repeat as a from m_repeat a,(select * from m_repeat group by name having count(1)>1 ) as b
where a.name = b.name and a.id >b.id