The primary key: id, the unique columns: col_1, col_2, col_3 .
You can use a temporary table, like:
create temporary table temp_table (id int); insert temp_table (id) select id from your_table t1 where exists ( select * from your_table t2 where t2.col_1 = t1.col_1 and t2.col_2 = t1.col_2 and t2.col_3 = t1.col_3 and t2.id > t1.id ); delete from your_table where id in (select id from temp_table);
Or you can add a UNIQUE index on the 3 columns. When you write the
ALTER IGNORE TABLE your_table ADD UNIQUE INDEX idx_name (col_1, col_2, col_3);
This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. And you’d better take a backup before running this statement.
Some MySQL tools you can try: https://www.withdata.com/mysql/