删除带有内部联接的行

Consider running DELETE...INNER JOIN and DELETE with subquery conditionals and avoid PHP query fetch looping with if/else as the logic seems to be the following: 如果评论者只有一个评论，则删除他/她的个人资料和评论仅在评论者具有多个(即，不止一个)评论的情况下删除评论.是的，所有三个DELETE可以在所有id上同时运行，因为互斥条件位于前两个和最后一个之间.因此，每次迭代前两个影响行或后一个影响行.不受影响的表将从任一表中删除零行. And yes, all three DELETE can be run at same time across all ids since mutually exclusive conditions are placed between the first two and last one. Therefore, either first two affects rows or last one affects rows per iteration. The unaffected one(s) will delete zero rows from either table. 此外，简单注释记录也首先被删除，因为该表由于一对多的关系可能具有注释的外键约束.最后，下面假设 comment id传递到循环中(不是 commentor id).Also, simplecomments records are deleted first since this table may have a foreign key constraint with commentor due to its one-to-many relationship. Finally, below assumes comment ids are passed into loop (not commentor id). PHP (使用参数化，假设$ conn是mysqli连接对象) foreach ($_POST["delete"] as $key => $value) { // DELETE COMMENTS AND THEN PROFILE FOR COMMENTORS WITH ONE POST $sql = "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $value); $stmt->execute(); $stmt->close(); $sql = "DELETE c.* FROM `simplecomments` c INNER JOIN `simplecomments` s ON s.commentorid = c.id WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $value); $stmt->execute(); $stmt->close(); // DELETE COMMENTS FOR COMMENTORS WITH MULTIPLE POSTS BUT KEEP PROFILE $sql = "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) > 1"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $value); $stmt->execute(); $stmt->close();}或者，对于DRY-er方法，在数组中循环SQL语句:Alternatively, for a DRY-er approach, loop SQL statements in an array:$sqls = array( 0 => "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1", 1 => "DELETE c.* FROM `simplecomments` c INNER JOIN `simplecomments` s ON s.commentorid = c.id WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1", 2 => "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) > 1" );foreach ($_POST["delete"] as $key => $value) { foreach($sqls as $sql) { $stmt = $conn->prepare($sql); $stmt->bind_param("i", $value); $stmt->execute(); $stmt->close(); }} 这篇关于删除带有内部联接的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！