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

Вот решение на месте (но не однострочное)

Узнать max id:

 select max(id) as maxid from shop; 

Помните это значение. Предположим, что он равен 1000;

Повторно вставьте уникальные значения со смещением:

 insert into shop (id, tax_id) select distinct id + 1000, tax_id from shop; 

Отбросить старые значения:

 delete from shop where id <= 1000; 

Восстановить нормальные идентификаторы:

 update shop set id = id - 1000; 

PROFIT!

Рабочее решение.

 //Sql query to find duplicates SELECT id, tax_id, count(*) - 1 AS cnt FROM shop GROUP BY id HAVING cnt > 1 --- res +------+--------+-----+ | id | tax_id | cnt | +------+--------+-----+ | 1 | 10 | 2 | | 2 | 10 | 3 | +------+--------+-----+ //Iterate through results with your language of choice DELETE FROM shop WHERE id=<res id> AND tax_id=<res tax_id> LIMIT <cnt - 1> ---res (iterated) +------+--------+ | id | tax_id | +------+--------+ | 1 | 10 | | 1 | 11 | | 2 | 12 | | 2 | 10 | +------+--------+ 

Для двух запросов потребуется небольшая часть php для выполнения удалений

 $res = mysql_query("SELECT id, tax_id, count(*) - 1 AS cnt FROM shop GROUP BY id HAVING cnt > 1") while($row = mysql_fetch_assoc($res)){ mysql_query("DELETE FROM shop WHERE id=".$row['id']." AND tax_id=". $row['tax_id']." LIMIT ".$row['cnt'] -1 . "); } 

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

 ALTER TABLE shop ADD COLUMN place INT; SET @i = 1 UPDATE shop SET place = @i:= @i + 1; DELETE FROM shop WHERE place NOT IN (SELECT place FROM items GROUP BY id, tax_id); ALTER TABLE shop DROP COLUMN place; 

Во-первых, вы можете предотвратить это, создав уникальный индекс для этих двух полей для дальнейшего использования.

Что касается решения, создайте новую таблицу shopnew с той же структурой в mysql или просто удалите каждую запись из таблицы при создании списка записей (убедитесь, что у вас есть резервная копия!):

 //Get every record from mysql $sSQL = "Select ID, tax_id from shop"; $oRes = mysql_query($sSQL); $aRecordList = array(); while($aRow = mysql_fetch_assoc($oRes)){ //If record is a duplicate, it will be 'overwritten' $aRecordList[$aRow['id'].".".$aRow['tax_id']] =1; } //You could delete every record from shop here, if you dont want an additional table //recordList now only contains unique records foreach($aRecordList as $sRecord=>$bSet){ $aExpRecord = explode(".",$sRecord); mysql_query("INSERT INTO shopnew set id=".$aExpRecord[0].", tax_id = ".$aExpRecord[1] } 

Может быть, это может помочь:

 $query="SELECT * FROM shop ORDER BY id"; $rez=$dbh->query($query); $multi=$rez->fetchAll(PDO::FETCH_ASSOC); foreach ($multi as $key=>$row){ $rest=array_slice($multi,$key+1); foreach ($rest as $rest){ if(($row['id']==$rest['id']) && ($row['tax_id']==$rest['tax_id'])){ $dbh->query("DELETE FROM shop WHERE id={$rest['id']} and tax_id= {$rest['tax_id']}"); } } 

}

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

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

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

Проблемы:

1. строки не имеют ключа идентификации или первичного ключа, поэтому нужно придумать способ ссылки на одну строку, которая должна оставаться
2. нам нужно будет как-то сгруппировать строки, то есть применить порядок, а затем условие, но форма DELETE которая поддерживает ORDER BY может иметь только WHERE и не поддерживает HAVING . Это порядок применяется после выполнения условия.
3. нам не нужно будет сортировать строки, если значения будут упорядочены с помощью кластерного первичного ключа, но у нас его нет.

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

 CREATE TABLE `tablename` ( `a_id` int(10) unsigned NOT NULL, `b_id` int(10) unsigned NOT NULL, KEY `Index_1` (`a_id`,`b_id`) ) ENGINE=InnoDB COLLATE utf8_bin; 

Я добавил ключ (не UNIQUE или PRIMARY), чтобы ускорить поиск и надеяться использовать его в группах.

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

 INSERT INTO tablename (a_id, b_id) VALUES (2, 3), (1, 1), (2, 2), (1,4); INSERT INTO tablename (a_id, b_id) VALUES (2, 3), (1, 1), (2, 2), (1,4); INSERT INTO tablename (a_id, b_id) VALUES (2, 3), (1, 1), (2, 2), (1,4); 

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

Теперь давайте рассмотрим следующий запрос:

 SELECT @c, @a_id as a, @b_id as b, a_id, b_id FROM tablename, (SELECT @a_id:=0, @b_id:=0, @c:=0) as init WHERE (@c:=IF(LEAST(@a_id=(@a_id:=a_id), @b_id=(@b_id:=b_id)), @c+1, 1)) >= 1 ; 

И его результат:

 @c, a, b, a_id, b_id 1, 1, 1, 1, 1 2, 1, 1, 1, 1 3, 1, 1, 1, 1 1, 1, 4, 1, 4 2, 1, 4, 1, 4 3, 1, 4, 1, 4 1, 2, 2, 2, 2 2, 2, 2, 2, 2 3, 2, 2, 2, 2 1, 2, 3, 2, 3 2, 2, 3, 2, 3 3, 2, 3, 2, 3 

Результаты автоматически сортируются с использованием Index_1 , а дублированные пары (a_id, b_id) перечисляются в столбце @c . Теперь наша задача – удалить все строки, где @c > 1 . Единственная проблема, с которой мы сталкиваемся, – заставить MySQL использовать Index_1 при удалении, что довольно сложно, не применяя дополнительные условия. Но мы можем сделать это, используя проверку равенства или несколько проверок равенства на a_id :

 DELETE FROM t USING tablename t FORCE INDEX (Index_1) JOIN (SELECT @a_id:=0, @b_id:=0, @c:=0) as init WHERE a_id IN (1) AND (@c:=IF(LEAST(@a_id=(@a_id:=a_id), @b_id=(@b_id:=b_id)), @c+1, 1)) > 1; DELETE FROM t USING tablename t FORCE INDEX (Index_1) JOIN (SELECT @a_id:=0, @b_id:=0, @c:=0) as init WHERE a_id IN (2) AND (@c:=IF(LEAST(@a_id=(@a_id:=a_id), @b_id=(@b_id:=b_id)), @c+1, 1)) > 1; SELECT * FROM tablename t; a_id, b_id 1, 1 1, 4 2, 2 2, 3 

Я не могу поместить все возможные a_id в IN() потому что MySQL поймет, что индекс бесполезен в этом случае, и запрос не удалит все дубликаты (только смежные), но, скажем, 10 разных a_id я могу удалить дубликаты в двух операторах DELETE , каждый IN будет иметь 5 явных идентификаторов.

Надеюсь, это может быть полезно кому-то =)