Удаление дубликатов по двум полям. Игнорируется ORDER BY в операторе DELETE
v. 10.11.5-MariaDB.
Имеется таблица t1:
CREATE TABLE `t1` (
`str` TEXT DEFAULT '',
`cnt` INT(10) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB
Для примера заполнена следующими данными:
+-----+-----+
| str | cnt |
+-----+-----+
| aaa | 1 |
| aaa | 2 |
| aaa | 3 |
| aaa | 7 |
| aaa | 4 |
| bbb | 1 |
| bbb | 2 |
| bbb | 8 |
| bbb | 3 |
+-----+-----+
Задача удалить дубликаты по полю str, оставив только записи соответствующие максимальным значениям в поле cnt. Для приведенного примера это - ('ааа',7) и ('bbb',8).
Делаю проверку SELECT-ом:
SET @lastStr = '';
SELECT @lastStr, IF(str = @lastStr, 1, (@lastStr:=str) AND 0) AS toDelete, t1.* FROM t1
ORDER BY str ASC, cnt DESC;
+----------+----------+-----+-----+
| @lastStr | toDelete | str | cnt |
+----------+----------+-----+-----+
| | 0 | aaa | 7 |
| aaa | 1 | aaa | 4 |
| aaa | 1 | aaa | 3 |
| aaa | 1 | aaa | 2 |
| aaa | 1 | aaa | 1 |
| aaa | 0 | bbb | 8 |
| bbb | 1 | bbb | 3 |
| bbb | 1 | bbb | 2 |
| bbb | 1 | bbb | 1 |
+----------+----------+-----+-----+
Видно что должны остаться 2 записи с максимальными значениями cnt (поле toDelete в выборке равно нулю). Пробуем удалить дубликаты тем же подходом:
SET @lastStr = '';
DELETE FROM t1 WHERE IF(str = @lastStr, 1, (@lastStr:=str) AND 0)
ORDER BY str ASC, cnt DESC;
SELECT * FROM t1;
+-----+-----+
| str | cnt |
+-----+-----+
| aaa | 1 |
| bbb | 1 |
+-----+-----+
Остаются только первые записи. ORDER BY в операторе DELETE игнорируется? почему?
Аналогичная ситуация с UPDATE, если дубликатам в поле str присвоить значение NULL:
SET @lastStr = '';
UPDATE t1 SET str=null WHERE IF(str = @lastStr, 1, (@lastStr:=str) AND 0)
ORDER BY str ASC, cnt DESC;
SELECT * FROM t1;
+------+-----+
| str | cnt |
+------+-----+
| aaa | 1 |
| NULL | 2 |
| NULL | 3 |
| NULL | 7 |
| NULL | 4 |
| bbb | 1 |
| NULL | 2 |
| NULL | 8 |
| NULL | 3 |
+------+-----+
Ответы (2 шт):
CREATE TABLE `t1` (
`str` TEXT NOT NULL DEFAULT '',
`cnt` INT(10) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB;
INSERT INTO t1 (str, cnt ) VALUES ('aaa' , 1 ),
('aaa' , 2 ),
('aaa' , 3 ),
('aaa' , 7 ),
('aaa' , 4 ),
('bbb' , 1 ),
('bbb' , 2 ),
('bbb' , 8 ),
('bbb' , 3 );
DELETE t1 FROM t1
INNER JOIN (
SELECT `str`, MAX(`cnt`) as max_cnt
FROM t1
GROUP BY `str`
) t2 ON t1.`str` = t2.`str`
WHERE t1.`cnt` < t2.max_cnt;
select * from t1;
Output:
+-----+-----+
| str | cnt |
+-----+-----+
| aaa | 7 |
| bbb | 8 |
+-----+-----+
Explain
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 9 | Using temporary; Using filesort |
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
Я плохо читаю Explain, как я понял тут написано один раз проходим по всем данным 9 строк, второй раз проходим по всем данным Using where, помещаем во временную таблицу, третий раз проходим по всем данным извлекая из временной таблицы. На других данных нужно проверять .
DELETE t11.*
FROM t1 AS t11
JOIN t1 AS t12 USING (str)
WHERE t11.cnt < t12.cnt;
Но если две или более записей для одного str имеют одинаковый и при этом максимальный cnt - останутся обе записи.