dousi5358
dousi5358
2009-09-11 18:56

如何在MySQL中实现级联数据的级联删除？

已采纳

I'm working on a project that has categories/subcategories. The database table for this is unique, with the following structure:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL auto_increment,
  `publish` tinyint(1) NOT NULL default '0',
  `parent_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

So, in case the category is a "base" one, the parent_id is zero, and if the category has a parent, it herds the parent id. What I want to know is this: I need to delete everything above and related with a category when choosing that option, a cascade-like deletion, but I only have this table (no foreign keys). How do I do that? (Without a large amount of queries.)

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongluojiao6322 dongluojiao6322 12年前

    • You can write a trigger to do it.

      DELIMITER //
CREATE TRIGGER CatDelete AFTER DELETE ON categories
FOR EACH ROW BEGIN
  DELETE FROM categories WHERE parent_id = old.id;
END//
DELIMITER ;

    • You can ALTER your MyISAM tables to InnoDB, and then define foreign key constraints with the ON DELETE CASCADE option.

      ALTER TABLE categories ENGINE=InnoDB;
ALTER TABLE categories ADD CONSTRAINT 
  FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE CASCADE;

    Re your comment, the first thing I'd check is if you have some orphan categories, that is with parent_id pointing to a non-existant row. That would prevent you from creating the constraint.

    SELECT c1.*
FROM categories c1
LEFT OUTER JOIN categories c2
  ON (c1.parent_id = c2.id)
WHERE c2.id IS NULL;
    点赞 评论 复制链接分享
  • doushaqing7080 doushaqing7080 12年前

    Just my $0.02 - this not so trivial solution should require MVC to handle the cascade deletion.

    点赞 评论 复制链接分享
提交

为你推荐