Mysql - 用一个查询从多个表中删除

Mysql - delete from multiple tables with one query

提问by Jason

I have 4 tables that stores different information about a user in each. Each table has a field with user_id to identify which row belongs to which user. If I want to delete the user is this the best way to delete that users information from multiple tables? My objective is to do it in one query.

我有 4 个表，每个表存储有关用户的不同信息。每个表都有一个带有 user_id 的字段，用于标识哪一行属于哪个用户。如果我想删除用户，这是从多个表中删除该用户信息的最佳方法吗？我的目标是在一个查询中完成。

Query:

询问：

"DELETE FROM table1 WHERE user_id='$user_id';
DELETE FROM table2 WHERE user_id='$user_id';
DELETE FROM table3 WHERE user_id='$user_id';
DELETE FROM table4 WHERE user_id='$user_id';";

采纳答案by Chandu

You can define foreign key constraints on the tables with ON DELETE CASCADEoption.

您可以使用ON DELETE CASCADE选项在表上定义外键约束。

Then deleting the record from parent table removes the records from child tables.

然后从父表中删除记录会从子表中删除记录。

Check this link : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

检查此链接：http: //dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

回答by Pekka

Apparently, it is possible. From the manual:

显然，这是可能的。从手册：

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.8.1, “JOIN Syntax”.

您可以在 DELETE 语句中指定多个表以根据 WHERE 子句中的特定条件从一个或多个表中删除行。但是，您不能在多表 DELETE 中使用 ORDER BY 或 LIMIT。table_references 子句列出了连接中涉及的表。其语法在第 12.2.8.1 节，“JOIN 语法”中描述。

The example in the manual is:

手册中的例子是：

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

should be applicable 1:1.

应适用 1:1。

回答by Rodel Sarate

You can also use following query :

您还可以使用以下查询：

DELETE FROM Student, Enrollment USING Student INNER JOIN Enrollment ON Student.studentId = Enrollment.studentId WHERE Student.studentId= 51;

DELETE FROM Student, Enrollment USING Student INNER JOIN Enrollment ON Student.studentId = Enrollment.studentId WHERE Student.studentId= 51;

回答by JoshR

A join statement is unnecessarily complicated in this situation. The original question only deals with deleting records for a given user from multiple tables at the same time. Intuitively, you might expect something like this to work:

在这种情况下，join 语句不必要地复杂化。原始问题仅涉及同时从多个表中删除给定用户的记录。直觉上，您可能会期望这样的事情能够奏效：

DELETE FROM table1,table2,table3,table4 WHERE user_id='$user_id'

Of course, it doesn't. But rather than writing multiple statements (redundant and inefficient), using joins (difficult for novices), or foreign keys (even more difficult for novices and not available in all engines or existing datasets) you could simplify your code with a LOOP!

当然，它没有。但是，与其编写多个语句（冗余且低效）、使用连接（新手很难）或外键（新手更难，而且并非在所有引擎或现有数据集中都可用），您可以使用LOOP来简化您的代码！

As a basic example using PHP (where $db is your connection handle):

作为使用 PHP 的基本示例（其中 $db 是您的连接句柄）：

$tables = array("table1","table2","table3","table4");
foreach($tables as $table) {
  $query = "DELETE FROM $table WHERE user_id='$user_id'";
  mysqli_query($db,$query);
}

Hope this helps someone!

希望这可以帮助某人！

回答by Abhijeet Kasurde

You can use following query to delete rows from multiple tables,

您可以使用以下查询从多个表中删除行，

DELETE table1, table2, table3 FROM table1 INNER JOIN table2 INNER JOIN table3 WHERE table1.userid = table2.userid AND table2.userid = table3.userid AND table1.userid=3

DELETE table1, table2, table3 FROM table1 INNER JOIN table2 INNER JOIN table3 WHERE table1.userid = table2.userid AND table2.userid = table3.userid AND table1.userid=3

回答by Niraj patel

from two tables with foreign key you can try this Query:

从带有外键的两个表中，您可以尝试此查询：

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition

回答by Lightness Races in Orbit

The documentation for DELETEtells you the multi-table syntax.

DELETE的文档告诉您多表语法。

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

或者：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

回答by kenorb

Normally you can't DELETE from multiple tables at once, unless you'll use JOINs as shown in other answers.

通常，您不能一次从多个表中删除，除非您将使用其他答案中所示的 JOIN。

However if all yours tables starts with certain name, then this query will generate query which would do that task:

但是，如果您的所有表都以某个名称开头，则此查询将生成执行该任务的查询：

SELECT CONCAT('DELETE FROM ', GROUP_CONCAT(TABLE_NAME SEPARATOR ' WHERE user_id=123;DELETE FROM ') , 'FROM table1;' ) AS statement FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'table%'

then pipe it (in shell) into mysql command for execution.

然后将它（在 shell 中）通过管道传输到 mysql 命令中执行。

For example it'll generate something like:

例如，它会生成如下内容：

DELETE FROM table1 WHERE user_id=123;
DELETE FROM table2 WHERE user_id=123;
DELETE FROM table3 WHERE user_id=123;

More shell oriented example would be:

更多面向外壳的示例是：

echo "SHOW TABLES LIKE 'table%'" | mysql | tail -n +2 | xargs -L1 -I% echo "DELETE FROM % WHERE user_id=123;" | mysql -v

If you want to use only MySQL for that, you can think of more advanced query, such as this:

如果您只想使用 MySQL，您可以考虑更高级的查询，例如：

SET @TABLES = (SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'table%');
PREPARE drop_statement FROM 'DELETE FROM @tables';
EXECUTE drop_statement USING @TABLES;
DEALLOCATE PREPARE drop_statement;

The above example is based on: MySQL – Delete/Drop all tables with specific prefix.

上面的示例基于：MySQL – 删除/删除所有具有特定前缀的表。

回答by Randy

usually, i would expect this as a 'cascading delete' enforced in a trigger, you would only need to delete the main record, then all the depepndent records would be deleted by the trigger logic.

通常，我希望这是在触发器中强制执行的“级联删除”，您只需要删除主记录，然后触发器逻辑将删除所有依赖记录。

this logic would be similar to what you have written.

这个逻辑类似于你写的。