如何从表中递归删除项目? [英] How to recursively delete items from table?
问题描述
我有一个MySQL表文件夹":
I've a MySQL table "folders":
CREATE TABLE IF NOT EXISTS `folders` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`folder_key` varchar(40) NOT NULL,
`parent_key` varchar(40) NOT NULL,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
我不使用整数ID，而只使用键(字母数字哈希，为了方便起见，已将其替换为单词).因此，folder_key& parent_key是SHA-1哈希(在我的实际应用中).
I don't use integer IDs, only keys (alphanumeric hashes, which I've replaced with words to make things more clear). So, folder_key & parent_key are SHA-1 hashes (in my real application).
INSERT INTO `folders` (`id`, `folder_key`, `parent_key`, `name`) VALUES
(1, 'sun', 'root', '1'),
(2, 'moon', 'sun', '1.1'),
(3, 'jupiter', 'moon', '1.1.1'),
(4, 'mars', 'root', '2');
如您所见，第一项也有一个parent_key，它是一个根密钥.
As you can see the first item has a parent_key too, it's a root key.
测试用例:
如果我想删除带有folder_key === moon(1.1)的项目，它也应该删除其子元素，在这种情况下，它是带有folder_key === jupiter(1.1的项目) .1)等...
The test case:
If I wish to delete an item with folder_key === moon (1.1), it should also delete its children element(s), in this case it's an item with folder_key === jupiter (1.1.1) and so on...
让我说我想删除多个项目，所以我这样做:
Let say I wish to delete multiple items, so I do:
DELETE from folders WHERE folder_key IN('moon', 'mars');
执行后，该表应仅包含带有folder_key === sun
DELETE from folders WHERE folder_key IN('moon', 'mars');
After execution, the table should have only one item with folder_key === sun
因此，问题是: 如何使用MySQL触发器，ON DELETE CASCADE或...从表中删除具有一个或多个folder_key的项目(递归)?
谢谢.
推荐答案
使用此模型是不可能的
- 代替root使用NULL，因此您可以使用InnoDB外键+级联删除.
- 使用id(例如sun = 1，moon = 2)代替使用字符串parent_key
其他方法是更改数据模型，因此您可以轻松选择元素的任何后代-例如，请参见 http://www.sitepoint.com/hierarchical-data-database-2/
Other way is to change data model, so you can easly select any descendands of element - see this for example http://www.sitepoint.com/hierarchical-data-database-2/
这篇关于如何从表中递归删除项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
