系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

Mysql中如何删除表重复数据

时间:2023-10-30来源:系统城装机大师作者:佚名

Mysql删除表重复数据

表里存在唯一主键

根据多个字段删除重复数据,只保留一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
DELETE
FROM
    table_name
WHERE
    (字段1, 字段2) IN (
        SELECT
            t.字段1,
            t.字段2
        FROM
            (
                SELECT
                    字段1,
                    字段2
                FROM
                    table_name
                GROUP BY
                    字段1,
                    字段2
                HAVING
                    count(1) > 1
            ) t
    )
AND id NOT IN (
    SELECT
        dt.id
    FROM
        (
            SELECT
                min(id) AS id
            FROM
                table_name
            GROUP BY
                字段1,
                字段2
            HAVING
                count(1) > 1
        ) dt
)

没有主键时删除重复数据

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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
/*
 Navicat Premium Data Transfer
 Source Server         : localmysql
 Source Server Type    : MySQL
 Source Server Version : 80030
 Source Host           : localhost:3306
 Source Schema         : nie_db
 Target Server Type    : MySQL
 Target Server Version : 80030
 File Encoding         : 65001
 Date: 17/08/2022 10:49:41
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message`  (
  `id` bigint(0) NOT NULL,
  `message_title` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `message_context` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `send_peo` bigint(0) NULL DEFAULT NULL,
  `receive_peo` bigint(0) NULL DEFAULT NULL,
  `scope` int(0) NULL DEFAULT 0,
  `del_flag` tinyint(0) NULL DEFAULT 0,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `creator` tinyint(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of message
-- ----------------------------
INSERT INTO `message` VALUES (1, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
INSERT INTO `message` VALUES (2, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
INSERT INTO `message` VALUES (3, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
INSERT INTO `message` VALUES (4, '测试消息', '消息内容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
SET FOREIGN_KEY_CHECKS = 1;

创建表并添加四条相同的数据

接下来是我们这次的重头,我封装了一个存储过程,具体的逻辑都在注释里了,你也可以分析然后单独拿出来分批次执行sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE DEFINER=`root`@`localhost` PROCEDURE `remove`()
BEGIN
DECLARE count_all INT DEFAULT 0;
DECLARE count_copy INT DEFAULT 1;
-- 创建一个临时复制表,并将目标表数据复制进来
DROP TABLE if exists message_01 ;
create TEMPORARY TABLE message_01 (SELECT * FROM message);
-- 查询去重后实际条数 并赋值给我们的变量 去重根据实际需求更改GROUP BY 后面条件
SELECT COUNT(1) into count_all from (select COUNT(1) FROM message WHERE del_flag = 0 GROUP BY message_title,message_context) t ;
/*删除复制表的重复数据并保留一条  保留哪条数据可以自己根据条件调节,
比如最小id等等,就是条件问题 还有就是去重根据实际需求更改GROUP BY 后面条件
这里如果使用了逻辑删除,有需要保留数据的可以改成修改逻辑删除字段
*/
DELETE FROM message_01 WHERE ID NOT IN (
select t.id FROM (select MAX(id) as id FROM message WHERE del_flag = 0 GROUP BY message_title,message_context) t
);
-- 再次不去重查询 如结果和查询结果一样则操作正确且完整
select COUNT(1) INTO count_copy FROM message_01 WHERE del_flag = 0 ;
-- 进行最后两次查询结果比对
IF count_all = count_copy THEN
TRUNCATE message;
INSERT INTO message (SELECT * FROM message_01);
SELECT 'success';
ELSE
SELECT '改造失败',count_all AS '原表条数',count_copy AS '复制表删除重复数据后统计的条数';
END IF;
END

另外创建存储过程,就是再函数那里右键 -》过程-》输入名字-》完成 ,你也可以百度下怎么创建的,我这里就不说太多啦。

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • MySQL清理数据并释放磁盘空间的实现示例

    在我们的生产环境中有一张表:courier_consume_fail_message,是存放消息消费失败的数据的,设计之初,这张表的数据量评估在万级别以下,因此没有建立索引。但目前发现,该表的数据量已...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载