Удалить повторяющиеся строки в MySQL
У меня есть таблица со следующими полями:
id (Unique)
url (Unique)
title
company
site_id
Теперь мне нужно удалить строки, имеющие одинаковые title, company and site_id, Один из способов сделать это будет использовать следующий SQL вместе со скриптом (PHP):
SELECT title, site_id, location, id, count( * )
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1
После выполнения этого запроса я могу удалить дубликаты, используя скрипт на стороне сервера.
Но я хочу знать, если это можно сделать только с помощью SQL-запроса.
29 ответов
Действительно простой способ сделать это - добавить UNIQUE указатель по 3 колонкам. Когда вы пишете ALTER заявление, включите IGNORE ключевое слово. Вот так:
ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);
Это удалит все дубликаты строк. Как дополнительное преимущество, будущее INSERTs это дубликаты будут ошибка. Как всегда, вы можете сделать резервную копию, прежде чем запускать что-то вроде этого...
Если вы не хотите изменять свойства столбца, используйте запрос ниже.
Так как у вас есть столбец с уникальными идентификаторами (например, 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, если у вас очень большая база данных. Этот запрос может быть быстрее:
DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id
Удаление дубликатов в таблицах MySQL является распространенной проблемой, как правило, результатом отсутствующего ограничения, позволяющего избежать этих дубликатов заранее. Но эта общая проблема обычно связана с конкретными потребностями... которые требуют определенных подходов. Подход должен отличаться в зависимости, например, от размера данных, дублируемой записи, которая должна быть сохранена (обычно первая или последняя), от того, есть ли индексы, которые нужно сохранить, или от того, хотим ли мы выполнить какие-либо дополнительные действие на дублированные данные.
Есть также некоторые особенности самого MySQL, такие как невозможность ссылки на ту же таблицу по причине FROM при выполнении таблицы UPDATE (это вызовет ошибку MySQL #1093). Это ограничение может быть преодолено с помощью внутреннего запроса с временной таблицей (как предложено в некоторых подходах выше). Но этот внутренний запрос не будет особенно хорошо работать при работе с большими источниками данных.
Однако существует лучший подход для удаления дубликатов, он эффективен и надежен, и его можно легко адаптировать к различным потребностям.
Общая идея состоит в том, чтобы создать новую временную таблицу, обычно добавляя уникальное ограничение, чтобы избежать дальнейших дубликатов, и вставлять данные из прежней таблицы в новую, одновременно заботясь о дубликатах. Этот подход основан на простых запросах MySQL INSERT, создает новое ограничение, чтобы избежать дальнейших дубликатов, и пропускает необходимость использования внутреннего запроса для поиска дубликатов и временной таблицы, которая должна храниться в памяти (таким образом, подходя также для больших источников данных).
Вот как это может быть достигнуто. Учитывая, что у нас есть таблица сотрудников, со следующими столбцами:
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 с точно такой же структурой, что и таблица employee
- Строка #2 добавляет ограничение UNIQUE в новую таблицу tmp_eployee, чтобы избежать дальнейших дубликатов
- Строка № 3 просматривает исходную таблицу сотрудников по идентификатору, вставляя новые записи сотрудников в новую таблицу tmp_eployee, игнорируя при этом дублированные записи.
- Строка № 4 переименовывает таблицы, так что новая таблица сотрудников содержит все записи без дубликатов, а резервная копия прежних данных хранится в таблице backup_employee.
⇒ Используя этот подход, 1.6M регистры были преобразованы в 6 КБ менее чем за 200 с.
Chetan, следуя этому процессу, вы можете быстро и легко удалить все ваши дубликаты и создать УНИКАЛЬНОЕ ограничение, запустив:
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 предложение DESC ORDER BY id создает последние идентификаторы, которые получают приоритет над остальными.
✔ Вариант выполнения некоторых задач с дубликатами, например, ведение учета найденных дубликатов.
Иногда нам нужно выполнить некоторую дальнейшую обработку найденных дублированных записей (например, вести подсчет дубликатов).
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 запрос INSERT INTO ... ON DUPLICATE KEY UPDATE используется для выполнения дополнительного обновления при обнаружении дубликата (в этом случае увеличивается счетчик). Запрос INSERT INTO ... ON DUPLICATE KEY UPDATE может быть используется для выполнения различных типов обновлений для найденных дубликатов.
✔ Вариант для регенерации идентификатора автоинкрементного поля
Иногда мы используем автоинкрементное поле и, чтобы сохранить индекс как можно более компактным, мы можем воспользоваться удалением дубликатов для регенерации автоинкрементного поля в новой временной таблице.
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;
Если 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 ...
Решение, которое легко понять и работает без первичного ключа:
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 t1 FROM tablename t1
INNER JOIN tablename t2
WHERE
t1.id < t2.id AND
t1.title = t2.title AND
t1.company=t2.company AND
t1.site_ID=t2.site_ID;
Если у вас большая таблица с огромным количеством записей, то приведенные выше решения не будут работать или занимать слишком много времени. Тогда у нас есть другое решение
-- 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;
У меня есть этот фрагмент запроса для 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 t1 FROM tablename t1 INNER JOIN tablename t2
WHERE t1.id < t2.id AND t1.column1 = t2.column1 AND t1.column2 = t2.column2;
Более быстрый способ - вставить отдельные строки во временную таблицу. Используя delete, мне потребовалось несколько часов, чтобы удалить дубликаты из таблицы из 8 миллионов строк. Используя вставку и отчетливость, это заняло всего 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;
Удалите дублирующиеся строки с помощью инструкции DELETE JOIN MySQL предоставляет вам инструкцию DELETE JOIN, которую можно использовать для быстрого удаления дублирующихся строк.
Следующий оператор удаляет дублирующиеся строки и сохраняет самый высокий идентификатор:
DELETE t1 FROM contacts t1
INNER JOIN
contacts t2 WHERE
t1.id < t2.id AND t1.email = t2.email;
Начиная с версии 8.0 (2018), MySQL наконец поддерживает оконные функции.
Оконные функции удобны и эффективны. Вот решение, которое демонстрирует, как использовать их для решения этой задачи.
В подзапросе мы можем использовать ROW_NUMBER() назначить позицию каждой записи в таблице в column1/column2 группы, упорядоченные id, Если дубликатов нет, запись получит номер строки 1, Если дубликат существует, он будет пронумерован по возрастанию id (начинается с 1).
Как только записи будут правильно пронумерованы в подзапросе, внешний запрос просто удалит все записи, номер строки которых не равен 1.
Запрос:
DELETE FROM tablename
WHERE id IN (
SELECT id
FROM (
SELECT
id,
ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) rn
FROM output
) t
WHERE rn > 1
)
Я продолжаю посещать эту страницу каждый раз, когда я гуглю "удаляю дубликаты из mysql", но мои решения theIGNORE не работают, потому что у меня есть таблицы InnoDB mysql
этот код работает лучше в любое время
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 = временная таблица создана и удалена
Удалите повторяющиеся строки с помощью
DELETE JOIN заявление:
DELETE t1 FROM table_name t1
JOIN table_name t2
WHERE
t1.id < t2.id AND
t1.title = t2.title AND t1.company = t2.company AND t1.site_id = t2.site_id;
Это решение переместит дубликаты в одну таблицу, а уникальные в другую.
-- 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
-- Here is what I used, and it works:
create table temp_table like my_table;
-- t_id is my unique column
insert into temp_table (id) select id from my_table GROUP by t_id;
delete from my_table where id not in (select id from temp_table);
drop table temp_table;
Удалить дубликат записи в таблице.
delete from job s
where rowid < any
(select rowid from job k
where s.site_id = k.site_id and
s.title = k.title and
s.company = k.company);
или же
delete from job s
where rowid not in
(select max(rowid) from job k
where s.site_id = k.site_id and
s.title = k.title and
s.company = k.company);
это запрос, который я использую, и он работает как драгоценный камень,
вот запрос,
delete from products where products_id in ( select * from ( select products_id from products group by products_red having count(*) >1)_tmp)
это удаляет без каких-либо временных таблиц,
Преимущество: если имеется 10 000 записей с каждой от 2 до 5, вам нужно выполнить 5 раз, чтобы очистить все дубликаты. Недостаток: если данные содержат 10 000 дубликатов каждая, то вам нужно запустить 10 000 раз, чтобы каждый раз очистить одну,
используйте это после рассмотрения количества продуктов и количества дубликатов
CREATE TEMPORARY TABLE temp_table AS
SELECT max(option_id) AS max_option_id, title
FROM list_options
GROUP BY title;
DELETE FROM list_options
WHERE option_id NOT IN (
SELECT max_option_id
FROM temp_table
);
DROP TEMPORARY TABLE temp_table;
Для того, чтобы дублировать записи с уникальными столбцами, например, COL1,COL2, COL3 не должны быть реплицированы (предположим, что мы пропустили 3 уникальных столбца в структуре таблицы и несколько дублированных записей были внесены в таблицу)
DROP TABLE TABLE_NAME_copy;
CREATE TABLE TABLE_NAME_copy LIKE TABLE_NAME;
INSERT INTO TABLE_NAME_copy
SELECT * FROM TABLE_NAME
GROUP BY COLUMN1, COLUMN2, COLUMN3;
DROP TABLE TABLE_NAME;
ALTER TABLE TABLE_NAME_copy RENAME TO TABLE_NAME;
Надеюсь поможет дев.
У меня есть таблица, в которой забывают добавить первичный ключ в строку идентификатора. Хотя в id есть auto_increment. Но однажды один материал воспроизводит журнал bin mysql в базе данных, который вставляет несколько повторяющихся строк.
Я удаляю повторяющуюся строку
- выберите уникальные повторяющиеся строки и экспортируйте их
select T1.* from table_name T1 inner join (select count(*) as c,id from table_name group by id) T2 on T1.id = T2.id where T2.c > 1 group by T1.id;
удалить повторяющиеся строки по идентификатору
вставить строку из экспортированных данных.
Затем добавьте первичный ключ по id
TL;TR;
Очень описанный учебник для решения этой проблемы можно найти на сайте mysqltutorial.org:
Как удалить повторяющиеся строки в MySQL
Очень ясно показано, как удалять дублирующиеся строки тремя различными способами:
А) Использование DELETE JOIN заявление
Б) Использование промежуточной таблицы
В) Использование ROW_NUMBER() функция
Я надеюсь, что это кому-то поможет.
Это идеально, если вы пытаетесь удалить один из дубликатов и оставить другой. Обратите внимание, что без подзапросов вы получите ошибку #1093.
УДАЛИТЬ ИЗ имя_таблицы WHERE id IN (SELECT * FROM (SELECT n.id FROM table_name n WHERE n.column2! = "Value" GROUP BY n.column HAVING COUNT (n.column)> 1) x)
Мне нравится быть более точным в отношении того, какие записи я удаляю, поэтому вот мое решение:
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 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`);