Как удалить дубликаты в таблице MySQL?

Ответ 1

удаляет дубликаты на месте, не создавая новую таблицу

ALTER IGNORE TABLE `table_name` ADD UNIQUE (title, SID)

Примечание: работает только хорошо, если индекс помещается в память

Ответ 2

Предположим, что у вас есть таблица employee со следующими столбцами:

employee (first_name, last_name, start_date)

Чтобы удалить строки с повторяющимся столбцом first_name:

delete
from employee using employee,
    employee e1
where employee.id > e1.id
    and employee.first_name = e1.first_name  

Ответ 3

После удаления дубликатов для всех SID-ов, а не только один.

С помощью таблицы temp

CREATE TABLE table_temp AS
SELECT * FROM table GROUP BY title, SID;

DROP TABLE table;
RENAME TABLE table_temp TO table;

Так как temp_table только что создан, он не имеет индексов. Вам нужно будет восстановить их после удаления дубликатов. Вы можете проверить, какие индексы у вас есть в таблице с помощью SHOW INDEXES IN table

Без таблицы temp:

DELETE FROM `table` WHERE id IN (
  SELECT all_duplicates.id FROM (
    SELECT id FROM `table` WHERE (`title`, `SID`) IN (
      SELECT `title`, `SID` FROM `table` GROUP BY `title`, `SID` having count(*) > 1
    )
  ) AS all_duplicates 
  LEFT JOIN (
    SELECT id FROM `table` GROUP BY `title`, `SID` having count(*) > 1
  ) AS grouped_duplicates 
  ON all_duplicates.id = grouped_duplicates.id 
  WHERE grouped_duplicates.id IS NULL
)

Ответ 4

Удаление повторяющихся строк в MySQL, прохождение

Создайте таблицу и вставьте несколько строк:

dev-db> create table penguins(foo int, bar varchar(15), baz datetime);
Query OK, 0 rows affected (0.07 sec)
dev-db> insert into penguins values(1, 'skipper', now());
dev-db> insert into penguins values(1, 'skipper', now());
dev-db> insert into penguins values(3, 'kowalski', now());
dev-db> insert into penguins values(3, 'kowalski', now());
dev-db> insert into penguins values(3, 'kowalski', now());
dev-db> insert into penguins values(4, 'rico', now());
Query OK, 6 rows affected (0.07 sec)
dev-db> select * from penguins;
+------+----------+---------------------+
| foo  | bar      | baz                 |
+------+----------+---------------------+
|    1 | skipper  | 2014-08-25 14:21:54 |
|    1 | skipper  | 2014-08-25 14:21:59 |
|    3 | kowalski | 2014-08-25 14:22:09 |
|    3 | kowalski | 2014-08-25 14:22:13 |
|    3 | kowalski | 2014-08-25 14:22:15 |
|    4 | rico     | 2014-08-25 14:22:22 |
+------+----------+---------------------+
6 rows in set (0.00 sec)

Затем удалите дубликаты:

dev-db> delete a
    -> from penguins a
    -> left join(
    -> select max(baz) maxtimestamp, foo, bar
    -> from penguins
    -> group by foo, bar) b
    -> on a.baz = maxtimestamp and
    -> a.foo = b.foo and
    -> a.bar = b.bar
    -> where b.maxtimestamp IS NULL;
Query OK, 3 rows affected (0.01 sec)

Результат:

dev-db> select * from penguins;
+------+----------+---------------------+
| foo  | bar      | baz                 |
+------+----------+---------------------+
|    1 | skipper  | 2014-08-25 14:21:59 |
|    3 | kowalski | 2014-08-25 14:22:15 |
|    4 | rico     | 2014-08-25 14:22:22 |
+------+----------+---------------------+
3 rows in set (0.00 sec)

Что делает этот оператор удаления

Pseudocode: группируйте строки по двум столбцам, которые вы хотите удалить дубликаты. Выберите одну строку каждой группы, чтобы сохранить ее с помощью максимального агрегата. Левое соединение возвращает все строки из левой таблицы с соответствующими строками в правой таблице. В этом случае левая таблица имеет все строки в таблице, а справа - только те строки, которые имеют NULL (не одну строку для каждой группы, которую вы хотите сохранить). Удаляя эти строки, вы остаетесь только уникальным для каждой группы.

Подробное техническое объяснение. Как вы должны прочитать инструкцию sql delete:

Табличные пингвины с псевдонимом 'a' оставлены соединенными на подмножестве табличных пингвинов под псевдонимом 'b'. Правая таблица "b", которая является подмножеством, находит максимальную временную метку, сгруппированную по foo и bar. Это сопоставляется с левой таблицей "a". (foo, bar, baz) слева имеет каждую строку в таблице. Правое подмножество "b" имеет (maxtimestamp, foo, bar), который сопоставляется слева только с тем, что IS max.

Каждая строка, которая не является таковой, имеет значение maxtimestamp из NULL. Отфильтруйте эти строки NULL, и у вас есть набор всех строк, сгруппированных по foo и bar, который не является последним timestamp baz. Удалите их.

Сделайте резервную копию таблицы перед ее запуском.

Предотвратить повторение этой проблемы в этой таблице:

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

Ответ 5

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

В операторе с одним запросом, без временной таблицы, это работало лучше всего для меня,

DELETE e.*
FROM employee e
WHERE id IN
 (SELECT id
   FROM (SELECT MIN(id) as id
          FROM employee e2
          GROUP BY first_name, last_name
          HAVING COUNT(*) > 1) x);

Единственное предостережение в том, что я должен запускать запрос несколько раз, но даже с этим я нашел, что он работал лучше для меня, чем другие параметры.

Ответ 6

Это всегда работает для меня:

CREATE TABLE NoDupeTable LIKE DupeTable; 
INSERT NoDupeTable SELECT * FROM DupeTable group by CommonField1,CommonFieldN;

Который хранит самый низкий идентификатор для каждого из дубликатов и остальных записей без дублирования.

Я также принял решение сделать следующее, чтобы проблема с дублированием больше не возникала после удаления:

CREATE TABLE NoDupeTable LIKE DupeTable; 
Alter table NoDupeTable Add Unique 'Unique' (CommonField1,CommonField2);
INSERT IGNORE NoDupeTable SELECT * FROM DupeTable;

Другими словами, я создаю дубликат первой таблицы, добавляю уникальный индекс в поля, которые я не хочу дублировать, а затем Insert IGNORE который имеет то преимущество, что не может, поскольку нормальная Insert будет в первый раз попытался добавить дублируемую запись на основе двух полей и, скорее, игнорировать любые такие записи.

При перемещении fwd становится невозможным создание дубликатов записей на основе этих двух полей.

Ответ 7

Вот простой ответ:

delete a from target_table a left JOIN (select max(id_field) as id, field_being_repeated  
    from target_table GROUP BY field_being_repeated) b 
    on a.field_being_repeated = b.field_being_repeated
      and a.id_field = b.id_field
    where b.id_field is null;

Ответ 8

Следующие работы для всех таблиц

CREATE TABLE 'noDup' LIKE 'Dup' ;
INSERT 'noDup' SELECT DISTINCT * FROM 'Dup' ;
DROP TABLE 'Dup' ;
ALTER TABLE 'noDup' RENAME 'Dup' ;

Ответ 9

Эта работа для меня, чтобы удалить старые записи:

delete from table where id in 
(select min(e.id)
    from (select * from table) e 
    group by column1, column2
    having count(*) > 1
); 

Вы можете заменить min (e.id) на max (e.id), чтобы удалить новейшие записи.

Ответ 10

DELETE FROM table WHERE Field IN
(SELECT Field FROM table GROUP BY Field HAVING COUNT(Field) > 1)
WHERE SID = "1"

EDIT:

В приведенном выше запросе будут удалены все повторяющиеся записи, чтобы сохранить один из них:

DELETE FROM table WHERE id NOT IN

 (SELECT id FROM
     (SELECT id, field 
     FROM table WHERE SID = "1" 
     GROUP BY field 
     HAVING COUNT(field) > 1) 
 as tmp)    

AND field in

 (SELECT DISTINCT field 
 FROM table 
 WHERE SID = "1" 
 GROUP BY field 
 HAVING COUNT(field) > 1)

Я не мог полностью протестировать его, но я думаю, что это работает.

Ответ 11

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

Надеюсь, что это кому-то полезно.

DROP TABLE IF EXISTS UniqueIDs;
CREATE Temporary table UniqueIDs (id Int(11));

INSERT INTO UniqueIDs
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields 
    AND T1.ID < T2.ID)
    WHERE T2.ID IS NULL);

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);

Ответ 12

delete p from 
product p
inner join (
    select max(id) as id, url from product 
    group by url 
    having count(*) > 1
) unik on unik.url = p.url and unik.id != p.id;

Ответ 13

Еще один простой способ... с помощью UPDATE IGNORE:

U должен использовать индекс для одного или нескольких столбцов (индекс типа). Создайте новый временный ссылочный столбец (не часть индекса). В этом столбце вы отмечаете uniques, обновляя его с помощью предложения ignore. Шаг за шагом:

Добавьте временный ссылочный столбец для отметки uniques:

ALTER TABLE `yourtable` ADD `unique` VARCHAR(3) NOT NULL AFTER `lastcolname`;

= > это добавит столбец в вашу таблицу.

Обновить таблицу, попытаться отметить все как уникальное, но игнорировать возможные ошибки из-за дублирования ключевой проблемы (записи будут пропущены):

UPDATE IGNORE `yourtable` SET `unique` = 'Yes' WHERE 1;

= > вы обнаружите, что ваши повторяющиеся записи не будут отмечены как уникальные = "Да", другими словами, только один из каждого набора повторяющихся записей будет отмечен как уникальный.

Удалить все, что не уникально:

DELETE * FROM `yourtable` WHERE `unique` <> 'Yes';

= > Это приведет к удалению всех повторяющихся записей.

Отбросьте столбец...

ALTER TABLE `yourtable` DROP `unique`;

Ответ 14

Я считаю, что решение Вернера выше, чтобы быть наиболее удобным, потому что оно работает независимо от наличия первичного ключа, не возится со столами, использует надежный простой sql, очень понятно.

Как я сказал в своем комментарии, это решение не было должным образом объяснено. Так что это мое, основанное на нем.

1) добавить новый логический столбец

alter table mytable add tokeep boolean;

2) добавьте ограничение на дублированные столбцы И новый столбец

alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep);

3) установите для булева колонки значение true. Это будет выполнено только на одной из дублированных строк из-за нового ограничения

update ignore mytable set tokeep = true;

4) удалите строки, которые не были помечены как tokeep

delete from mytable where tokeep is null;

5) удалить добавленный столбец

alter table mytable drop tokeep;

Я предлагаю вам сохранить добавленное ограничение, чтобы новые дубликаты были предотвращены в будущем.

Ответ 15

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

Ali, в вашем случае вы можете запустить что-то вроде этого:

-- create a new temporary table
CREATE TABLE tmp_table1 LIKE table1;

-- add a unique constraint    
ALTER TABLE tmp_table1 ADD UNIQUE(sid, title);

-- scan over the table to insert entries
INSERT IGNORE INTO tmp_table1 SELECT * FROM table1 ORDER BY sid;

-- rename tables
RENAME TABLE table1 TO backup_table1, tmp_table1 TO table1;

Ответ 16

DELETE T2
FROM   table_name T1
JOIN   same_table_name T2 ON (T1.title = T2.title AND T1.ID <> T2.ID)

Ответ 17

delete from `table` where `table`.`SID` in 
    (
    select t.SID from table t join table t1 on t.title = t1.title  where t.SID > t1.SID
)

Ответ 18

Love @eric ответ, но он не работает, если у вас действительно большая таблица (я получаю The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay, когда я пытаюсь запустить ее). Поэтому я ограничил запрос соединения только рассмотрением дубликатов строк, и я закончил с:

DELETE a FROM penguins a
    LEFT JOIN (SELECT COUNT(baz) AS num, MIN(baz) AS keepBaz, foo
        FROM penguins
        GROUP BY deviceId HAVING num > 1) b
        ON a.baz != b.keepBaz
        AND a.foo = b.foo
    WHERE b.foo IS NOT NULL

Предложение WHERE в этом случае позволяет MySQL игнорировать любую строку, которая не имеет дубликата, и также будет игнорировать, если это первый экземпляр дубликата, поэтому последующие дубликаты будут проигнорированы. Измените MIN(baz) на MAX(baz), чтобы сохранить последний экземпляр вместо первого.

Ответ 19

Это работает для больших таблиц:

 CREATE Temporary table duplicates AS select max(id) as id, url from links group by url having count(*) > 1;

 DELETE l from links l inner join duplicates ld on ld.id = l.id WHERE ld.id IS NOT NULL;

Чтобы удалить старое изменение max(id) до min(id)

Ответ 20

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

ALTER IGNORE TABLE `table_name` ADD PRIMARY KEY (`column_name`);

Ответ 21

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

Создает копию вашей таблицы

создать таблицу temp_table, например oldtablename; Вставить temp_table select * from oldtablename;

Опорожняет исходную таблицу

DELETE * из oldtablename;

Копирует все отдельные значения из скопированной таблицы обратно в исходную таблицу

INSERT oldtablename SELECT * из группы temp_table по имени, фамилии, dob

Удаляет вашу временную таблицу.

Drop Table temp_table

Вам нужно сгруппировать по полям aLL, которые вы хотите сохранить.

Ответ 22

вот как я обычно устраняю дубликаты

1. добавьте временный столбец, назовите его как хотите (я буду называть его активным)
2. сгруппируйте по полям, которые, по вашему мнению, не должны дублироваться, и установите для них значение "1"; при группировании по этим столбцам будет выбрано только одно из дублированных значений (не будут выбраны дубликаты)
3. удалить те с активным нулем
4. активный столбец
5. по желанию (если подходит для ваших целей), добавьте уникальный индекс для этих столбцов, чтобы больше не иметь дубликатов

Ответ 23

Вы можете просто использовать предложение DISTINCT, чтобы выбрать "очищенный" список (и здесь - очень простой пример того, как это сделать).

Ответ 24

Может ли он работать, если вы их подсчитаете, а затем добавьте ограничение на ваш запрос на удаление, оставив только один?

Например, если у вас есть два или более, напишите свой запрос следующим образом:

DELETE FROM table WHERE SID = 1 LIMIT 1;

Ответ 25

При удалении повторяющихся данных из таблицы существует несколько основных шагов:

• Создайте резервную копию своего стола!
• Найти повторяющиеся строки
• Удалить повторяющиеся строки

Вот полный учебник: https://blog.teamsql.io/deleting-duplicate-data-3541485b3473