无自增字段mysql去重_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...', '主键字段');