翼度科技»论坛 编程开发 mysql 查看内容

Mysql中如何删除表重复数据

4

主题

4

帖子

12

积分

新手上路

Rank: 1

积分
12
Mysql删除表重复数据


表里存在唯一主键

根据多个字段删除重复数据,只保留一条数据
  1. DELETE
  2. FROM
  3.         table_name
  4. WHERE
  5.         (字段1, 字段2) IN (
  6.                 SELECT
  7.                         t.字段1,
  8.                         t.字段2
  9.                 FROM
  10.                         (
  11.                                 SELECT
  12.                                         字段1,
  13.                                         字段2
  14.                                 FROM
  15.                                         table_name
  16.                                 GROUP BY
  17.                                         字段1,
  18.                                         字段2
  19.                                 HAVING
  20.                                         count(1) > 1
  21.                         ) t
  22.         )
  23. AND id NOT IN (
  24.         SELECT
  25.                 dt.id
  26.         FROM
  27.                 (
  28.                         SELECT
  29.                                 min(id) AS id
  30.                         FROM
  31.                                 table_name
  32.                         GROUP BY
  33.                                 字段1,
  34.                                 字段2
  35.                         HAVING
  36.                                 count(1) > 1
  37.                 ) dt
  38. )
复制代码
没有主键时删除重复数据

1、创建新表
①创建一个新表与目标表结构字段保持一致
  1. create table new_table_temp
复制代码
②将过滤查询的统计的数据写入到新表
  1. insert into new_table_temp
复制代码
③将旧表table_name 删除
  1. delete from table_name 
复制代码
④将创建的新表名称修改为旧表名称
2、添加字段
①表结构添加一个自增且唯一字段
②按照存在唯一主键进行删除重复数据
③删除添加的自增字段

Mysql删除表中重复数据并保留一条

最近有个需求,给角色添加菜单权限,这是一个role_menu 表。
里面存放的是角色id和菜单id,是批量给一种类型角色添加,但有可能角色人为添加过,因为数据量还是比较大的,如果先查询这个有没有添加过再添加会很耗时,而统一不管有没有添加过一并添加则很快,这就需要后续给重复数据给删除掉,于是有了今天的分享。
我这里只做一个列子,工作代码安全底线大家谨记哈。

准备一张表 用的是mysql8 大家自行更改
  1. /*
  2. Navicat Premium Data Transfer
  3. Source Server         : localmysql
  4. Source Server Type    : MySQL
  5. Source Server Version : 80030
  6. Source Host           : localhost:3306
  7. Source Schema         : nie_db
  8. Target Server Type    : MySQL
  9. Target Server Version : 80030
  10. File Encoding         : 65001
  11. Date: 17/08/2022 10:49:41
  12. */
  13. SET NAMES utf8mb4;
  14. SET FOREIGN_KEY_CHECKS = 0;
  15. -- ----------------------------
  16. -- Table structure for message
  17. -- ----------------------------
  18. DROP TABLE IF EXISTS `message`;
  19. CREATE TABLE `message`  (
  20.   `id` bigint(0) NOT NULL,
  21.   `message_title` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  22.   `message_context` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  23.   `send_peo` bigint(0) NULL DEFAULT NULL,
  24.   `receive_peo` bigint(0) NULL DEFAULT NULL,
  25.   `scope` int(0) NULL DEFAULT 0,
  26.   `del_flag` tinyint(0) NULL DEFAULT 0,
  27.   `create_time` datetime(0) NULL DEFAULT NULL,
  28.   `creator` tinyint(0) NULL DEFAULT NULL,
  29.   `update_time` datetime(0) NULL DEFAULT NULL,
  30.   PRIMARY KEY (`id`) USING BTREE
  31. ) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
  32. -- ----------------------------
  33. -- Records of message
  34. -- ----------------------------
  35. INSERT INTO `message` VALUES (1, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
  36. INSERT INTO `message` VALUES (2, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
  37. INSERT INTO `message` VALUES (3, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
  38. INSERT INTO `message` VALUES (4, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
  39. SET FOREIGN_KEY_CHECKS = 1;
复制代码
创建表并添加四条相同的数据

接下来是我们这次的重头,我封装了一个存储过程,具体的逻辑都在注释里了,你也可以分析然后单独拿出来分批次执行sql
  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `remove`()
  2. BEGIN
  3. DECLARE count_all INT DEFAULT 0;
  4. DECLARE count_copy INT DEFAULT 1;
  5. -- 创建一个临时复制表,并将目标表数据复制进来
  6. DROP TABLE if exists message_01 ;
  7. create TEMPORARY TABLE message_01 (SELECT * FROM message);
  8. -- 查询去重后实际条数 并赋值给我们的变量 去重根据实际需求更改GROUP BY 后面条件
  9. SELECT COUNT(1) into count_all from (select COUNT(1) FROM message WHERE del_flag = 0 GROUP BY message_title,message_context) t ;
  10. /*删除复制表的重复数据并保留一条  保留哪条数据可以自己根据条件调节,
  11. 比如最小id等等,就是条件问题 还有就是去重根据实际需求更改GROUP BY 后面条件
  12. 这里如果使用了逻辑删除,有需要保留数据的可以改成修改逻辑删除字段
  13. */
  14. DELETE FROM message_01 WHERE ID NOT IN (
  15. select t.id FROM (select MAX(id) as id FROM message WHERE del_flag = 0 GROUP BY message_title,message_context) t
  16. );
  17. -- 再次不去重查询 如结果和查询结果一样则操作正确且完整
  18. select COUNT(1) INTO count_copy FROM message_01 WHERE del_flag = 0 ;
  19. -- 进行最后两次查询结果比对
  20. IF count_all = count_copy THEN
  21. TRUNCATE message;
  22. INSERT INTO message (SELECT * FROM message_01);
  23. SELECT 'success';
  24. ELSE
  25. SELECT '改造失败',count_all AS '原表条数',count_copy AS '复制表删除重复数据后统计的条数';
  26. END IF;
  27. END
复制代码
另外创建存储过程,就是再函数那里右键 -》过程-》输入名字-》完成 ,你也可以百度下怎么创建的,我这里就不说太多啦。

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/2921288rd.htm
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具