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

Если вы не хотите изменять свойства столбца, то вы можете использовать запрос ниже.

Так как у вас есть столбец, который имеет уникальные идентификаторы (например,auto_increment столбцы), вы можете использовать его для удаления дубликатов:

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
    AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
    AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);

в MySQL, вы можете упростить его еще больше NULL-безопасный равный оператор (он же "оператор корабля"):

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND `a`.`title` <=> `b`.`title`
    AND `a`.`company` <=> `b`.`company`
    AND `a`.`site_id` <=> `b`.`site_id`;

MySQL имеет ограничения относительно ссылки на таблицу, из которой вы удаляете. Вы можете обойти это с помощью временной таблицы, как:

create temporary table tmpTable (id int);

insert  tmpTable
        (id)
select  id
from    YourTable yt
where   exists
        (
        select  *
        from    YourTabe yt2
        where   yt2.title = yt.title
                and yt2.company = yt.company
                and yt2.site_id = yt.site_id
                and yt2.id > yt.id
        );

delete  
from    YourTable
where   ID in (select id from tmpTable);

из предложения Костаноса в комментариях:
Единственный медленный запрос выше-Удалить, для случаев, когда у вас очень большая база данных. Этот запрос может быть быстрее:

DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id

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

CREATE TABLE your_table_deduped like your_table;
INSERT your_table_deduped SELECT * FROM your_table GROUP BY index1_id, index2_id;
RENAME TABLE your_table TO your_table_with_dupes;
RENAME TABLE your_table_deduped TO your_table;
#OPTIONAL
ALTER TABLE `your_table` ADD UNIQUE `unique_index` (`index1_id`, `index2_id`);
#OPTIONAL
DROP TABLE your_table_with_dupes;

есть и другое решение :

DELETE t1 FROM my_table t1, my_table t2 WHERE t1.id < t2.id AND t1.my_field = t2.my_field AND t1.my_field_2 = t2.my_field_2 AND ...

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

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

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

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

вот как это может быть достигнуто. Учитывая, что у нас есть таблица сотрудник, со следующими столбцами:

employee (id, first_name, last_name, start_date, ssn)

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

-- create a new tmp_eployee table
CREATE TABLE tmp_employee LIKE employee;

-- add a unique constraint
ALTER TABLE tmp_employee ADD UNIQUE(ssn);

-- scan over the employee table to insert employee entries
INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id;

-- rename tables
RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

технические объяснение

• строка #1 создает новый tmp_eployee таблица с точно такой же структурой, как сотрудник стол
• строка #2 добавляет уникальное ограничение к новому tmp_eployee таблица, чтобы избежать дальнейших повторений
• строка #3 сканирует оригинал сотрудник таблица по идентификатору, вставка новых записей сотрудников в новый tmp_eployee таблица, игнорируя дублируются записи
• строка #4 переименовывает таблицы, так что новый сотрудник таблица содержит все записи без дубликатов, и резервная копия прежних данных хранится на backup_employee стол

⇒ используя этот подход, 1,6 м регистров были преобразованы в 6k менее чем за 200 секунд.

Четан, после этого процесса, вы можете быстро и легко удалить все ваши дубликаты и создать Уникальное ограничение при запуске:

CREATE TABLE tmp_jobs LIKE jobs;

ALTER TABLE tmp_jobs ADD UNIQUE(site_id, title, company);

INSERT IGNORE INTO tmp_jobs SELECT * FROM jobs ORDER BY id;

RENAME TABLE jobs TO backup_jobs, tmp_jobs TO jobs;

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

✔ вариация для сохранения последней записи вместо первой

иногда нам нужно сохранить последнюю дублированную запись вместо первой.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id DESC;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• в строке № 3, в заказать по id DESC предложение делает последний идентификатор, чтобы получить приоритет над остальными

✔ вариация для выполнения некоторых задач на дубликатах, например ведение учета найденных дубликатов

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

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• в строке #3, новый столбец n_duplicates создана
• в строке № 4, в ВСТАВИТЬ ВНУТРЬ ... НА ДУБЛИКАТ КЛЮЧА Обновление запрос используется для выполнения дополнительного обновления при обнаружении дубликата (в этом случае увеличение счетчика) Элемент ВСТАВИТЬ ВНУТРЬ ... ПРИ ОБНОВЛЕНИИ ДУБЛИКАТА КЛЮЧА запрос может использоваться для выполнения различных типов обновлений для найденных дубликатов.

✔ вариация для регенерации автоматического инкрементного поля id

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

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT (first_name, last_name, start_date, ssn) FROM employee ORDER BY id;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• в строке #3 вместо выбора всех полей в таблице поле id пропускается, так что механизм БД автоматически генерирует новое

✔ дальнейшие вариации

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

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id DESC ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

CREATE TABLE tmp_employee2 LIKE tmp_employee;

INSERT INTO tmp_employee2 SELECT (first_name, last_name, start_date, ssn) FROM tmp_employee ORDER BY id;

DROP TABLE tmp_employee;

RENAME TABLE employee TO backup_employee, tmp_employee2 TO employee;

у меня есть этот запрос snipet для SQLServer, но я думаю, что он может быть использован в других СУБД с небольшими изменениями:

DELETE
FROM Table
WHERE Table.idTable IN  (  
    SELECT MAX(idTable)
    FROM idTable
    GROUP BY field1, field2, field3
    HAVING COUNT(*) > 1)

Я забыл сказать вам, что этот запрос не удаляет строку с самым низким идентификатором дублированных строк. Если это работает для вас попробуйте этот запрос:

DELETE
FROM jobs
WHERE jobs.id IN  (  
    SELECT MAX(id)
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING COUNT(*) > 1)

простой и быстрый для всех случаев:

CREATE TEMPORARY TABLE IF NOT EXISTS _temp_duplicates AS (SELECT dub.id FROM table_with_duplications dub GROUP BY dub.field_must_be_uniq_1, dub.field_must_be_uniq_2 HAVING COUNT(*)  > 1);

DELETE FROM table_with_duplications WHERE id IN (SELECT id FROM _temp_duplicates);

более быстрый способ-вставить отдельные строки во временную таблицу. Используя delete, мне потребовалось несколько часов, чтобы удалить дубликаты из таблицы из 8 миллионов строк. Используя insert и distinct, это заняло всего 13 минут.

CREATE TABLE tempTableName LIKE tableName;  
CREATE INDEX ix_all_id ON tableName(cellId,attributeId,entityRowId,value);  
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName;  
TRUNCATE TABLE tableName;
INSERT INTO tableName SELECT * FROM tempTableName; 
DROP TABLE tempTableName;  

Я продолжаю посещать эту страницу в любое время, когда я google "удаляю дубликаты из mysql", но для моих решений theIGNORE не работают, потому что у меня есть InnoDB mysql tables

этот код работает лучше в любое время

CREATE TABLE tableToclean_temp LIKE tableToclean;
ALTER TABLE tableToclean_temp ADD UNIQUE INDEX (fontsinuse_id);
INSERT IGNORE INTO tableToclean_temp SELECT * FROM tableToclean;
DROP TABLE tableToclean;
RENAME TABLE tableToclean_temp TO tableToclean;

tableToclean = имя таблицы, которую нужно очистить

tableToclean_temp = временная таблица создана и удалена

Это решение будет переместить дубликаты в одной таблице и uniques в другой.

-- speed up creating uniques table if dealing with many rows
CREATE INDEX temp_idx ON jobs(site_id, company, title, location);

-- create the table with unique rows
INSERT jobs_uniques SELECT * FROM
    (
    SELECT * 
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) > 1
    UNION
    SELECT *
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) = 1
) x

-- create the table with duplicate rows
INSERT jobs_dupes 
SELECT * 
FROM jobs
WHERE id NOT IN
(SELECT id FROM jobs_uniques)

-- confirm the difference between uniques and dupes tables
SELECT COUNT(1)
AS jobs, 
(SELECT COUNT(1) FROM jobs_dupes) + (SELECT COUNT(1) FROM jobs_uniques)
AS sum
FROM jobs

решение, которое просто понять и работает без первичного ключа:

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;

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

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

следующая инструкция удаляет повторяющиеся строки и сохраняет наивысший код:

DELETE t1 FROM contacts t1
    INNER JOIN
contacts t2 WHERE
t1.id < t2.id AND t1.email = t2.email;

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

-- Create temporary table

CREATE TABLE temp_table LIKE table1;

-- Add constraint
ALTER TABLE temp_table ADD UNIQUE(title, company,site_id);

-- Copy data
INSERT IGNORE INTO temp_table SELECT * FROM table1;

-- Rename and drop
RENAME TABLE table1 TO old_table1, temp_table TO table1;
DROP TABLE old_table1;

Мне нравится быть немного более конкретным в отношении того, какие записи я удаляю, поэтому вот мое решение:

delete
from jobs c1
where not c1.location = 'Paris'
and  c1.site_id > 64218
and exists 
(  
select * from jobs c2 
where c2.site_id = c1.site_id
and   c2.company = c1.company
and   c2.location = c1.location
and   c2.title = c1.title
and   c2.site_id > 63412
and   c2.site_id < 64219
)

вы можете легко удалить дубликаты записей из этого кода..

$qry = mysql_query("SELECT * from cities");
while($qry_row = mysql_fetch_array($qry))
{
$qry2 = mysql_query("SELECT * from cities2 where city = '".$qry_row['city']."'");

if(mysql_num_rows($qry2) > 1){
    while($row = mysql_fetch_array($qry2)){
        $city_arry[] = $row;

        }

    $total = sizeof($city_arry) - 1;
        for($i=1; $i<=$total; $i++){


            mysql_query( "delete from cities2 where town_id = '".$city_arry[$i][0]."'");

            }
    }
    //exit;
}

Я должен был сделать это с текстовыми полями и наткнулся на предел 100 байт на индекс.

Я решил это, добавив столбец, выполнив хэш md5 полей и выполнив alter.

ALTER TABLE table ADD `merged` VARCHAR( 40 ) NOT NULL ;
UPDATE TABLE SET merged` = MD5(CONCAT(`col1`, `col2`, `col3`))
ALTER IGNORE TABLE table ADD UNIQUE INDEX idx_name (`merged`);