Mysql -用一个查询从多个表中删除[重复]

This question already has an answer here:

这个问题已经有了答案:

How to delete from multiple tables in MySQL? 6 answers
如何从MySQL的多个表中删除?6答案

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';";

9 个解决方案

#1

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

可以使用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- keys -constraints.html。

#2

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子句中的特定条件从一个或多个表中删除行。但是，不能在多表删除中使用ORDER BY或LIMIT。table_references子句列出了连接中涉及的表。它的语法在第12.2.8.1节“连接语法”中描述。

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。

#3

You can also use following query :

您还可以使用以下查询:

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

从学生中删除，使用学生内部加入注册的学生。studentId =招生。studentId学生的地方。studentId = 51;

#4

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:

在这种情况下，联接语句是不必要的复杂。最初的问题只处理同时从多个表中删除给定用户的记录。直觉上，你可能会期望这样的工作:

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!

当然,它不会。但是，与其编写多个语句(冗余且低效)、使用连接(新手很难)或外键(新手更难，在所有引擎或现有数据集中都不可用)，不如使用循环来简化代码!

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!

希望这可以帮助别人!

#5

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

将表1、表2、表3从表1内部连接表2内部连接表3中删除，其中表1。用户id =表二。标识和表二。用户id = table3。标识和table1.userid = 3

#6

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

#7

The documentation for DELETE tells 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]

#8

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 -删除/删除所有带有特定前缀的表。

#9

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.

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

this logic would be similar to what you have written.

这种逻辑与您所写的类似。

#1