从MySQL中删除COUNT（*） [英] DELETE FROM HAVING COUNT(*) in MySQL

问题描述

好的，这里有几个帖子已经在这里，更少仍然在网上。我真的试过每一个人，不能得到任何工作。希望有人在这里可以怜惜我：）

Ok so there are couple posts here already on this and fewer still out on the web. I've literally tried every one of them and can not get anything to work. Hopefully someone here can take pity on me :)

这里是我正在处理的数据。我要删除所有这些记录。

Here is the data I'm working with. I want to delete all these records.

SELECT
part_desc, count(*) as rec_num
FROM ag_master
GROUP BY part_desc HAVING COUNT(*) > 1000;

+--------------------------------------+---------+
| part_desc                            | rec_num |
+--------------------------------------+---------+
| SILICON DELAY LINE, TRUE OUTPUT      |    1092 |
| LOADABLE PLD                         |    1401 |
| 8-BIT, FLASH, 8 MHz, MICROCONTROLLER |    1411 |
| FPGA                                 |    1997 |
| 8-BIT, MROM, 8 MHz, MICROCONTROLLER  |    3425 |
+--------------------------------------+---------+
5 rows in set (0.00 sec)

来找到代码，它会做到如下所示。语法检查确定，它运行，但它似乎挂起数据库。

The closest I've come to finding code that would do it is shown below. The syntax checks ok and it runs, however it just seems to hang the database up. I've let it run for as long as 10 minutes and nothing ever happens so I abort it.

DELETE
FROM ag_master
WHERE part_id IN (
  SELECT part_id
  FROM ag_master
  GROUP BY part_desc
  HAVING COUNT(*) > 1000
);

这是tmp表上的解释计划

Here's the explain plan on the tmp table

mysql> EXPLAIN SELECT * FROM ag_master WHERE part_desc IN (SELECT part_desc FROM tmp);
+----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
| id | select_type        | table     | type   | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | ag_master | ALL    | NULL          | NULL | NULL    | NULL | 177266 | Using where |
|  2 | DEPENDENT SUBQUERY | tmp       | system | NULL          | NULL | NULL    | NULL |      1 |             |
+----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

推荐答案

如手册中所述：

目前，您不能从表中删除，也不能从子查询中的同一个表中进行选择。

Currently, you cannot delete from a table and select from the same table in a subquery.

我想你必须通过一个临时表来执行这个操作：

I think you'll have to perform this operation via a temporary table:

CREATE TEMPORARY TABLE temp
  SELECT   part_desc
  FROM     ag_master
  GROUP BY part_desc
  HAVING   COUNT(*) > 1000;

DELETE FROM ag_master WHERE part_desc IN (SELECT part_desc FROM temp);

DROP TEMPORARY TABLE temp;

这篇关于从MySQL中删除COUNT（*）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！