从多个mysql表中删除数据

Question

tableOk，所以我想做的是

我在mysql中的一个数据库上有多个表

database1

table1

table2

table3

table4

table5

每个表都有一个列id

对于每个匹配的id，我想删除该行。因此在每个表中删除954001行。什么是最好的方式来做到这一点，而不杀死我的数据库。顺便说一句，所有的ids都是完全匹配的。

<?php
// get value of id that sent from address bar
$customer_id=$_GET['id'];
$id=$_GET['id'];
// Delete data in mysql from row that has this id
$sql="DELETE FROM teable_users WHERE customer_id = $customer_id;
DELETE FROM table_parties WHERE id = $id;
DELETE FROM table_weddings WHERE id = $id;
DELETE FROM table WHERE id = $id;
DELETE FROM table_request_client WHERE id = $id;
DELETE FROM table_requests WHERE id = $id;
DELETE FROM table_users WHERE id = $id;";

$result=mysql_query($sql);

// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='text.php'>Back to Event    Manager</a>";
}

else {
echo "ERROR";
}

// close connection
mysql_close();

?>

Answer 1

每个表都需要一条DELETE语句。如果你担心一致性，你可以使用事务。

BEGIN TRANSACTION;
DELETE FROM table1 WHERE id = 1;
DELETE FROM table2 WHERE id = 1;
DELETE FROM table3 WHERE id = 1;
DELETE FROM table4 WHERE id = 1;
DELETE FROM table5 WHERE id = 1;
COMMIT;

使用PDO的PHP示例(未测试)

$id = (int) $_GET['id'];
$pdo->beginTransaction();
$st = $pdo->prepare('DELETE FROM table1 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table2 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table3 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table4 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table5 WHERE id = :id');
$st->execute(array(':id', $id));
$pdo->commit();

Answer 2

您可以编写触发器，在初始删除时执行删除操作

Answer 3

我认为从本质上讲，您必须为每个表分别执行语句。

您可能会对此论坛感兴趣：

http://forums.devshed.com/database-management-46/one-query-to-delete-from-multiple-tables-71959.html

我相信是这样的--尽管我的SQL很生疏。

delete from table1 where id='954001';