MySQL. Remove duplicate lines?

Question

MySQL

There is a table with 100.000 lines.
It is necessary to remove duplicate rows (namely rows and not cells)
, that is, so that from the table:
c1 c2
1 2
1 0
4 2
1 0
1 1
2 1
Remaining:
c1 c2
1 2
1 0
4 2
1 1
2 1
One line (1 0 ) needs to be deleted since it was 2.
Can this be done with a request or is it impossible to do without php?
Thank you.

Answer 1

CREATE TEMPORARY TABLE tmp_tab AS SELECT DISTINCT * FROM your_table;
DELETE FROM your_table;
INSERT INTO your_table SELECT * FROM tmp_tab;
DROP TABLE tmp_tab;

Answer 2

You can also ALTER IGNORE TABLE bzzz add unique index tmp(column1, column2)

Answer 3

This one helped me:
ALTER IGNORE TABLE table1 ADD UNIQUE KEY(Name2, Name3);

Answer 4

Add primary key (id) and then:
DELETE FROM table as me, table as clone WHERE me.c1 = clone.c1 AND me.c2 = clone.c2 AND me.id <
clone.id task.

Answer 5

I like this way:
Create a dst table with the same structure but add a numeric field DupCount
query:
INSERT INTO dst SELECT * FROM src ON DUPLICATE KEY UPDATE dst.DupCount = dst.DupCount +1
DupCount for each row will contain the number of duplicates encountered .
You can write a script that will create a dst table, copy the data there, delete the original table and rename dst to src.

Answer 6

www.administrating.ru/udalenie-povtoryayushhixsya-strok-v-tablicax-mysql/

Answer 7

I can say that DISCINCT is really a mistake of all databases and it is always necessary to look at it, but in such a simple but not standard task, this is the best option.

Answer 8

Create a composite primary (or simply unique) index on c1 and c2 fields.