创建表
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL COMMENT '姓名',
`age` tinyint(4) DEFAULT NULL COMMENT '年龄',
`mate` tinyint(4) DEFAULT '1' COMMENT '有无配偶(1-有 0-无)',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_age` (`age`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
现有记录:
insert into `test`(`id`,`name`,`age`,`mate`) values (2,'aaaaa',28,0),
(3,'bbbb',23,0),
(4,'cccc',25,1),
(5,'dddd',26,0),
(6,'eeee',24,0),
(7,'fffff',18,0),
(8,'eeee',40,1),
(9,'eeee',60,1);
想去掉name重名的记录,方法如下
create table `test_bak` like test;
alter table test_bak add unique idx_name (name);
insert ignore into `test_bak` select * from `test`
它会删除重复的记录(别怕,会保留一条),然后建立唯一索引,高效而且人性化。
方法二
SELECT * from test where id in(
SELECT max(id) from test GROUP BY name HAVING count(1)>1
) union SELECT * from test GROUP BY name HAVING count(1)=1