请教一个 mysql 去重的问题

This topic created in 3287 days ago, the information mentioned may be changed or developed.

数据如下 id domain port email type name value ttl route def remark 2390 test.cn 80 [email protected] 1 www 123.123.123.123 0 0 0 2523 test.cn 80 [email protected] 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', 'fgwgxj.gov.cn', '80', '[email protected]', '1', 'www', '218.16.143.93', '0', '0', '0', NULL);
INSERT INTO test.test (`id`, `domain`, `port`, `email`, `type`, `name`, `value`, `ttl`, `route`, `def`, `remark`) VALUES ('2523', 'fgwgxj.gov.cn', '80', '[email protected]', '1', 'www', '218.16.143.93', '1800', '0', '0', NULL);

No Comments Yet

null default collate varchar