MySQL查询重复记录的最新一条记录

2022-10-02 22:42:37

一、背景

最近在做一个黑名单的需求，但是由于历史原因，黑名单有大量的重复数据，比如一个user_id = 001 的用户，有效status=1,无效status=0,但是实际产线数据库有4条数据（3条无效，1条有效），故需要找出重复数据的最新一条数据，并将user_id设置为索引，不允许重复。

二、测试环境准备

新建表t_user，并插入测试数据

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户编码',
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '密码',
  `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 134 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '用户信息表' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (2, 'u_001', 'liudehua', '123456', 0, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (3, 'u_933', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (4, 'u_586', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (5, 'u_662', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (6, 'u_89', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (7, 'u_8476', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (8, 'u_967', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (124, 'u_001', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (125, 'u_933', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (126, 'u_586', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (127, 'u_662', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (128, 'u_89', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (130, 'u_96', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (131, 'u_449', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (132, 'u_4330', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');

三、基本SQL语句

查询数量

SELECT count(*) from t_user; -- 查询数量

查询所有重复数据的user_id以及重复条数

SELECT u.user_id, COUNT(*) from t_user u group by u.user_id HAVING COUNT(1) > 1; -- 查询重复的数据以及重复条数

拓展：查询所有不重复数据的user_id

SELECT u.user_id from t_user u group by u.user_id HAVING COUNT(1) = 1; -- 查询不重复的数据

查询所有重复数据

SELECT * from t_user u2 where u2.user_id in(
SELECT u.user_id from t_user u group by u.user_id HAVING COUNT(1) > 1); -- 查询所有重复的数据

查询最新一条数据

SELECT * from t_user where id = (SELECT MAX(id) from t_user); -- 查询最新一条数据

根据时间查询重复记录的最新一条数据（这个SQL有问题，因为如果update_time相同的话，将会导致结果仍旧有重复数据，我们这个demo就是这个情况）

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.update_time in (SELECT MAX(u2.update_time) from t_user u2 group by u2.user_id);
-- 根据时间查询最新的数据（查询结果为15条，实际为10条，所以有问题）

根据id查询记录的最新一条数据（因为id是主键，是不会重复的，所以没有问题）

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.id in (SELECT MAX(u2.id) from t_user u2 group by u2.user_id);
-- 根据id查询最新的数据（记录为10条）

拓展：查询重复数据的第一条记录

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.id in (SELECT min(u2.id) from t_user u2 group by u2.user_id);
-- 根据id查询第一条记录（记录为10条）

四、参考文献

1、mysql查询出所有重复的记录

https://www.cnblogs.com/phpfensi/p/10214487.html

码农公寓

一、背景

二、测试环境准备

三、基本SQL语句

四、参考文献

相关文章