请教一个mysql去重取最新记录-PHP中文网问答

数据如下
id domain port email type name value ttl route def remark
2390 test.cn 80 123@qq.com 1 www 123.123.123.123 0 0 0
2523 test.cn 80 123@qq.com 1 www 123.123.123.123 1800 0 0

一个表中有 2 条相同的数据，（除了 ttl 和 id 不一样外），现在要保留最新的一条记录（如 2523 ），请教如何操作

下面是需要用到的测试语句；数据库是 mysql 5.7

use test;
CREATE TABLE test (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `port` varchar(6) COLLATE utf8_unicode_ci DEFAULT '80',
  `email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `type` tinyint(4) DEFAULT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `value` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ttl` int(11) DEFAULT '0',
  `route` tinyint(4) DEFAULT '0',
  `def` tinyint(1) DEFAULT '0',
  `remark` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2721 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


INSERT INTO test.test (`id`, `domain`, `port`, `email`, `type`, `name`, `value`, `ttl`, `route`, `def`, `remark`) VALUES ('2390', 'www.test.cn', '80', '123@qq.com', '1', 'www', '123.123.123.123', '0', '0', '0', NULL);
INSERT INTO test.test (`id`, `domain`, `port`, `email`, `type`, `name`, `value`, `ttl`, `route`, `def`, `remark`) VALUES ('2523', 'www.test.cn', '80', '123@qq.com', '1', 'www', '123.123.123.123', '1800', '0', '0', NULL);

全部回复(3)

天蓬老师2017-05-18 10:47:19 3楼

踩我的出来指出错误呀，让我学习学习哪里有问题。
delete from test
where (id,domain, port, email, type, name, value,route, def, remark) not in (
select * from (
select max(id) id,domain, port, email, type, name, value,route, def, remark
from test group by domain, port, email, type, name, value,route, def, remark) tmp)

赞 +0

添加回复

世界只因有你2017-05-18 10:47:19 2楼

select max(id),domain, port, email, type, name, value,route, def, remark from test GROUP BY domain, port, email, type, name, value,route, def, remark;

高洛峰2017-05-18 10:47:19 1楼

给你个生成删除脚本的sql

select concat("delete from test where id=",min(id),";") 
into outfile '/tmp/delete_test.sql' lines terminated by '\n' 
from test group by domain, port, email, type, name, value,route, def, remark;