MySQL、PHP 和 PDO 中的高效后代记录删除

Question

从给定记录 ID（表递归地指向自身）中删除后代记录的策略是什么？具体来说，我正在使用 PDO、PHP 和 MySQL 5.0+。

想象一个包含以下列的类别表：

• 如果
• 。
• idparent_idcategory_name

ID 为 0，则它是根类别 请注意，该 id 不是主键——可以有很多根类别。

想象一下它有几层深，比如食物和庇护所根类别，然后是这些类别的子类别，还有这些类别的子类别，依此类推。这些是后代。例如，如果有人要删除蔬菜，那么您可以预期食物和庇护所将作为根类别保留下来，但胡萝卜将消失，豆类也会消失。豪宅和小屋也会被留下，因为它们来自另一棵树。得到它？

编辑：我的错——忘记了一列——parent_id。这一点非常关键。

Answer 1

然而，在您的场景中可能不可以选择，用于存储分层数据的嵌套集模型可以使您所描述的操作非常高效。

另外这篇文章可能有用：

http://mikehillyer.com/articles/managing -hierarchical-data-in-mysql/

Answer 2

一个简单的级联引用完整性应该可以做到这一点 - 使用 ON DELETE CASCADE 声明您的外键。如果您对 parent_id 建立索引，它甚至应该相当高效（无论如何，这在 MySQL 中似乎是必需的；其他 DBMS 通常允许无索引 FK）。

例如：

CREATE TABLE your_table (
    id int PRIMARY KEY,
    parent_id int DEFAULT NULL,
    category_name varchar(45) NOT NULL,
    -- Will also create index on parent_id:
    CONSTRAINT your_table_fk1 FOREIGN KEY (parent_id) REFERENCES your_table (id)
        ON DELETE CASCADE
);

INSERT INTO your_table (id, category_name) VALUES (1, 'Food');
INSERT INTO your_table (id, category_name) VALUES (2, 'Shelter');
INSERT INTO your_table (id, parent_id, category_name) VALUES (3, 1, 'Vegetables');
INSERT INTO your_table (id, parent_id, category_name) VALUES (4, 3, 'Carrots');
INSERT INTO your_table (id, parent_id, category_name) VALUES (5, 3, 'Beans');
INSERT INTO your_table (id, parent_id, category_name) VALUES (7, 2, 'Mansions');
INSERT INTO your_table (id, parent_id, category_name) VALUES (8, 2, 'Cabins');

那么当你执行...

DELETE FROM your_table WHERE category_name = 'Vegetables'

...不仅'Vegetables'，而且'Carrots'和'Beans'都会被删除。

这甚至可以递归地工作，所以...

DELETE FROM your_table WHERE category_name = 'Food'

...删除第一级的“食物”，第二级的“蔬菜”，第三级的“胡萝卜”和“豆类”。

Answer 3

尽管嵌套集合模型更强大，但有时以下带有递归的示例就足够了。

public function deleteCategory($sCatID) {
  if (empty($sCatID)) {
    return FALSE;
  }
  // you can get your PDO database connection your own way -- this is my way for my framework
  $PDO = $this->data->mysql();
  // recursively find all the descendents of this category and delete those too
  $sSQL = "
  SELECT
    `id`
  FROM
    `categories`
  WHERE
    `parent_id` = :parent_id;
  ";
  $st = $PDO->prepare($sSQL);
  $st->bindValue(':parent_id',$sCatID);
  try {
    $st->execute();
    $rsRows = $st->fetchAll();
    foreach($rsRows as $rwRow) {
      $sChildCatID = $rwRow['id'];
      // note the recursion here!
      $this->deleteCategory($sChildCatID);
    }
  } catch (PDOException $e) {}
  unset($st);
  // now delete this category
  $sSQL = "
  DELETE FROM
    `categories`
  WHERE
    `id` = :id
  LIMIT 1;
  ";
  $st = $PDO->prepare($sSQL);
  $st->bindValue(':id',$sCatID);
  try {
    $st->execute();
  } catch (PDOException $e){}
}