Удаление повторяющихся строк из таблицы MySql

У вас есть несколько вариантов.

Пусть БД выполняет работу

Создайте копию таблицы с уникальным индексом – и затем вставьте в нее данные из исходной таблицы:

 CREATE TABLE clean LIKE pst_nw; ALTER IGNORE TABLE clean ADD UNIQUE INDEX (add1, add2, add3, add4); INSERT IGNORE INTO clean SELECT * FROM pst_nw; DROP TABLE pst_nw; RENAME TABLE clean pst_nw; 

Преимущество таких действий заключается в том, что вы можете убедиться, что ваша новая таблица верна, прежде чем отбрасывать исходную таблицу. Недостатком является то, что он занимает в два раза больше места и (относительно) медленный для выполнения.

Пусть БД выполняет работу №2

Вы также можете добиться желаемого результата:

 set session old_alter_table=1; ALTER IGNORE TABLE pst_nw ADD UNIQUE INDEX (add1, add2, add3, add4); 

Первая команда требуется в качестве обходного пути для игнорирования флагом игнорирования.

Преимущество здесь в том, что нет бесполезной работы с временной таблицей – недостатком является то, что вы не можете проверить, что ваше обновление делает именно то, что вы ожидаете, прежде чем запускать его.

Пример:

  CREATE TABLE `foo` ( `id` int(10) NOT NULL AUTO_INCREMENT, `one` int(10) DEFAULT NULL, `two` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) insert into foo values (null, 1, 1); insert into foo values (null, 1, 1); insert into foo values (null, 1, 1); select * from foo; +----+------+------+ | id | one | two | +----+------+------+ | 1 | 1 | 1 | | 2 | 1 | 1 | | 3 | 1 | 1 | +----+------+------+ 3 row in set (0.00 sec) set session old_alter_table=1; ALTER IGNORE TABLE foo ADD UNIQUE INDEX (one, two); select * from foo; +----+------+------+ | id | one | two | +----+------+------+ | 1 | 1 | 1 | +----+------+------+ 1 row in set (0.00 sec) 

Не делайте такого рода вещи вне БД

Особенно с 40 миллионами строк, которые делают что-то подобное вне db, вероятно, потребуется огромное количество времени, и может не закончиться вообще. Любое решение, которое остается в db, будет быстрее и более надежным.

Обычно в таких вопросах проблема заключается в том, что «у меня есть повторяющиеся строки, вы хотите сохранить только одну строку, любую».

Но, судя по коду, вы хотите: «если набор add1, add2, add3, add4 дублируется, УДАЛИТЕ ВСЕ КОПИИ С ID <1000». В этом случае копирование из таблицы в другую с помощью INSERT IGNORE не будет делать то, что вы хотите – может даже содержать строки с более низкими идентификаторами и отбрасывать последующие.

Я считаю, что вам нужно запустить что-то вроде этого, чтобы собрать все «плохие идентификаторы» (идентификаторы с дубликатом, дубликат выше 1000, в этом коде я использовал «AND bad.ID <good.ID», поэтому, если у вас есть ID 777 который дублирует ID 888, ID 777 по-прежнему будет удален. Если это не то, что вы хотите, вы можете изменить это в «И bad.ID <1000 AND good.ID> 1000» или что-то в этом роде).

 CREATE TABLE bad_ids AS SELECT bad.ID FROM pst_nw AS bad JOIN pst_nw AS good ON ( bad.ID < 1000 AND bad.ID < good.ID AND bad.add1 = good.add1 AND bad.add2 = good.add2 AND bad.add3 = good.add3 AND bad.add4 = good.add4 ); 

Затем, когда у вас есть все плохие идентификаторы в таблице,

 DELETE pst_nw.* FROM pst_nw JOIN bad_ids ON (pst_nw.ID = bad_ids.ID); 

Выступления в значительной степени выиграют от (non_unique, возможно только временного) индекса на add1, add2, add3, add4 и ID в этом порядке.

Конечно, есть. Обратите внимание, однако, что с 40 миллионами записей вы, скорее всего, превысите максимальное время выполнения php. Попробуйте следующее

 Create table temp_pst_nw like pst_nw; Insert into temp_pst_nw select * from pst_nw group by add1,add2,add3,add4; 

Подтвердите, что все в порядке!

 Drop table pat_nw; Rename table temp_pst_nw to pst_nw; 

Получите дубликаты строк, используя оператор «Группировать по». Вот пример, который вы можете попробовать:

 select id from table group by matching_field1,matching_field2.... having count(id) > 1 

Итак, вы получаете все дубликаты идентификаторов. Теперь удалите их с помощью запроса на удаление. Вместо использования оператора IN пользователя OR, поскольку IN очень медленный по сравнению с OR.

Попробуйте создать новую таблицу с теми же определениями. т.е. «my_table_two», тогда выполните:

SELECT DISTINCT unique_col1, col2, col3 […] FROM my_table INTO my_table_two;

Может быть, это разобратся.

Ваш код будет лучше, если вы не используете select * , выберите только столбцы (4 адреса), которые вы хотите сравнить. Он должен иметь ограничение в моем sql. Он может избегать состояния не реагировать, когда у вас слишком много таких строк.