MySQL删除重复数据实例

偏执的太偏执、 2022-11-18 01:27 231阅读 0赞

CREATE TABLE `product_comment` (
      `comment_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评论ID',
      `product_id` INT(10) UNSIGNED NOT NULL COMMENT '商品ID',
      `order_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单ID',
      `customer_id` INT(10) UNSIGNED NOT NULL COMMENT '用户ID',
      `title` VARCHAR(50) NOT NULL COMMENT '评论标题',
      `content` VARCHAR(300) NOT NULL COMMENT '评论内容',
      `audit_status` TINYINT(4) NOT NULL COMMENT '审核状态:0未审核1已审核',
      `audit_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '评论时间',
      `modified_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
      PRIMARY KEY (`comment_id`)
    )COMMENT='商品评论表';

业务场景：删除评论表中对同一订单同一商品的重复评论，只保留最早的一条。

1.查看是否存在对于同一订单同一商品的重复评论

SELECT order_id,product_id,COUNT(*) FROM product_comment GROUP BY order_id,product_id HAVING COUNT(*)>1;

2.备份product\_comment表，如果操作出错，可以通过备份数据进行还原

CREATE TABLE bak_product_comment LIKE product_comment;
    INSERT INTO bak_product_comment SELECT * FROM product_comment;

3.删除同一订单的重复评论

DELETE a
    FROM product_comment a
    JOIN(
        SELECT order_id,product_id,MIN(comment_id) AS comment_id
        FROM product_comment
        GROUP BY order_id,product_id
        HAVING COUNT(*) > 1
    ) b ON a.order_id=b.order_id AND a.product_id=b.product_id
    AND a.comment_id>b.comment_id