时间戳

首页 > 解决方案 > 如何优化从（针对）多个表（合二为一）的删除查询？

mysql - 如何优化从（针对）多个表（合二为一）的删除查询？

问题描述

好的，我目前正在从多个表中成功删除，其中三个查询选择存在 category_id 的行，除了一个，我使用子查询通过 category_id 获取 post_id。怎样才能更有效、更全面地做到这一点？谢谢 ！

/**
 * delete category by id
 * @param $id
 * @return bool
 */
public function delete_category($id){

    $this->db->query("DELETE FROM categories WHERE categories.id = $id");
    $this->db->query("DELETE FROM comments
                      WHERE comments.post_id = ANY (SELECT post_id
                                                    FROM posts
                                                    WHERE category_id = $id)");
    $this->db->query("DELETE FROM posts
                      WHERE posts.category_id = $id
                      AND posts.category_id IS NOT NULL");
}

代码工作正常，它给出了预期的结果，但看起来有点恶心，我该如何优化它？

标签： mysqlcodeignitersql-delete

解决方案

MySQL 有一个多表 DELETE 语法。

您基本上采用 SELECT 查询来选择要删除的行，然后将其更改SELECT * FROM为DELETE <table_name>, <table_name> FROM，并<table_name>, <table_name>作为要从中删除的表。

例如，如果您有以下 DELETE 查询：

DELETE FROM comments WHERE post_id IN (SELECT post_id FROM posts WHERE category_id = 1);
DELETE FROM posts WHERE category_id = 1;
DELETE FROM categories WHERE category_id = 1;

您可以将它们重写为选择要删除的所有行的 SELECT：

SELECT *
FROM categories
JOIN posts USING (category_id)
JOIN comments USING (post_id)
WHERE category_id = 1

然后你可以用SELECTmulti-table替换DELETE：

DELETE categories, posts, comments
FROM categories
JOIN posts USING (category_id)
JOIN comments USING (post_id)
WHERE category_id = 1

推荐阅读