mysql去重

最新推荐文章于 2022-08-02 01:05:19 发布

jarjune_

最新推荐文章于 2022-08-02 01:05:19 发布

阅读量124

点赞数

本文链接：https://blog.youkuaiyun.com/weixin_42054277/article/details/90285857

版权

mysql去重

原因：由于表是各地归集过来的，通过前置机数据交换，很多数据会重复。
目的：删除数据库某张表的重复数据

方法一(将查询出无重复的数据导入临时表，再将原表删除，再将临时表的数据导回原表) 适用于无主键，所有字段完全匹配，数据量小的情况：

DELIMITER //
CREATE PROCEDURE delete_rows(IN tablename VARCHAR(50))
BEGIN
SET @tablename = tablename;
SET @tmp_table = CONCAT('tmp_table_',@tablename);

SET @create_table = CONCAT('DROP TABLE IF EXISTS ',@tmp_table);
PREPARE create_table FROM @create_table;
EXECUTE create_table;

SET @create_table = CONCAT('CREATE TEMPORARY TABLE ',@tmp_table,' SELECT DISTINCT * from ',@tablename);
PREPARE create_table FROM @create_table;
EXECUTE create_table;

SET @create_table = CONCAT('TRUNCATE TABLE ',@tablename);
PREPARE create_table FROM @create_table;
EXECUTE create_table;

SET @create_table = CONCAT('INSERT INTO ',@tablename,' SELECT * from ',@tmp_table);
PREPARE create_table FROM @create_table;
EXECUTE create_table;
END//
DELIMITER ;

CALL delete_rows('表名');

方法二(给表加自增主键) 适用于根据某几个字段去重，有主键：

DELIMITER //
CREATE PROCEDURE delete_rows_1(IN tablename VARCHAR(50),IN fieldnames VARCHAR(100),IN autofield VARCHAR(50))
BEGIN
SET @tablename = tablename;
SET @fieldnames = fieldnames;
SET @autofield = autofield;

SET @create_table = CONCAT('DELETE FROM ', @tablename ,' WHERE ', @autofield ,' not in (SELECT id FROM (SELECT max(', @autofield ,') id FROM ', @tablename ,' GROUP BY ', @fieldnames ,') t)');
PREPARE create_table FROM @create_table;
EXECUTE create_table;
END//
DELIMITER ;

CALL delete_rows_1('表名', '字段1,字段2,字段3...', '主键字段');