Question

This question already has answers here:

How to delete from multiple tables in MySQL? (7 answers)

Closed 6 years ago.

我有4个表格,储存了不同用户信息。每个表格都有一个用户-id的外地,以确定哪些浏览属于用户。如果我想删除用户,这是从多个表格中删除用户信息的最佳方式? 我的目标是在一个问题上这样做。

学历:

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

Answer 1

您可以用<代码>界定表格中的外国关键制约因素。 ON DELETECASCADE.

然后从母体表中删除记录,从儿童表中删除记录。

查阅这一链接:。

Answer 2

显然,这是可能的。 http://dev.mysql.com/doc/refman/5.0/en/delete.html

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 t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

应适用1:1。

Answer 3

也可以使用以下询问:

<编码> DELETE from Student, Enroll USING Student INNER JOIN Enrollment ON Student.student Id = 入学率。 Id WHERE 学生:studentId=51;

Answer 4

在这种情况下,合并发言不一定是复杂的。最初的问题只是同时从多个表格中删除特定用户的记录。值得注意的是,你可能期望这样做的工作:

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

当然,它没有。但是,如果使用结合(子宫颈癌扩散)或外国钥匙(所有发动机或现有数据集都比较困难)而不是撰写多篇声明(累s和低效),那么你可以简化其代码,使其达到<><><>>>>。

作为使用购买力平价的基本例子(如果是你的联系,费用是美元)。

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

希望能帮助人们!

Answer 5

从与外国关键人物的两张表格中可以看出:

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

Answer 6

您可在询问后删除多个表格中的行文,

页: 1 INNER JOIN table2 INNER JOIN table3 WHERE table1.userid = table2.userid and table2.userid = table3.userid and table1.userid=3

Answer 7

The documentation for DELETE tells you the multi-table syntax.

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]

Answer 8

通常,除非你使用其他答复所示的初专干事。

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%

然后将它(炮弹)推到我方的指挥中,以便执行。

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;

更有针对性的例子包括:

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

如果你只想为此使用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.

Answer 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.

这种逻辑与你所写的相似。

友情链接