tableOk所以我想做的是以下
我在mysql 中的一个数据库上有多个表
数据库1
table1
table2
table3
table4
table5
每个表都有一个列id
对于每个匹配的id,我都想删除该行。因此删除每个表中的954001行。在不破坏数据库的情况下,最好的方法是什么。顺便说一句,所有id都匹配。
<?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();
?>
每个表都需要一个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();
您可以编写一个触发器,其中删除发生在初始删除上
我认为基本上您必须为每个表执行单独的语句。
这个论坛可能会引起你的兴趣:
http://forums.devshed.com/database-management-46/one-query-to-delete-from-multiple-tables-71959.html
我相信是这样的——尽管我的SQL已经生疏了。
delete from table1 where id='954001';