我有一个这样的表结构
mysql> SELECT id, name, parent_id FROM categories;
+-------+------------+-----------+
| id | name | parent_id |
+-------+------------+-----------+
| 15790 | Test | 0 |
| 15791 | Test2 | 0 |
| 16079 | Subtest | 15790 |
| 16080 | Subtest 2 | 15790 |
| 16081 | Subsubtest | 16079 |
+-------+------------+-----------+
现在我想查找每个孩子和兄弟姐妹的父级,并按正确的顺序将其返回以进行删除。
所以在这种情况下,我的输出将是:
Array
(
16081,
16080,
16079,
15791,
15790
)
我不能仅仅通过反转父 id 来删除,因为这应该是可靠的走回树。
此外,我不能/不允许更改表的结构。因此,构建索引是必要的。
假设你无法访问TRUNCATE
、SET
(所以你可以做SET FOREIGN_KEY_CHECKS=0;
)、ALTER
等,并且绝对必须使用脚本:
由于问题被标记为 php
,这应该可以解决问题:
function reversetree($src_arr, $currentid = 0)
{
$cats = array();
foreach($src_arr as $id => $parent)
{
if($parent == $currentid)
{
$cats[] = $id;
$cats = array_merge($cats, reversetree($src_arr, $id));
}
}
return !$currentid ? array_reverse($cats) : $cats;
}
$rs = array();
foreach($pdo->query('SELECT id, parent_id FROM categories') as $row)
$rs[$row['id']] = $row['parent_id'];
$stmt = $pdo->prepare('DELETE FROM categories WHERE id = ?');
$pdo->beginTransaction();
foreach(reversetree($rs) as $v)
$stmt->execute(array($v));
$pdo->commit();
我不
明白为什么您需要按特定顺序排列 ID。 您可以通过事务删除它们,它们将同时被删除。
DELETE FROM categories WHERE ID IN (15790,15791,16079,16080,16081);
您可以在删除时添加带有级联的外键约束。外键将指向父 id 字段上的同一表。
删除父项时,将自动删除所有子项(无论级别如何)。
<?php
// housekeeping
$pdo = new PDO($dsn, $user, $password);
$select = $pdo->prepare(
"SELECT parent.id AS parent_id, child.id AS child_id
FROM categories AS parent
JOIN categories AS child ON parent.id = child.parent_id
WHERE parent.id = ?"
);
$delete = $pdo->prepare('DELETE FROM categories WHERE id = ?');
// deletes $node_id, deletes its children first if required
function delete_node($node_id){
$select->execute( array($node_id) );
$children = $select->fetchAll(PDO::FETCH_NUM);
if (count($children) !== 0) { // if 0, then the category does not exist, or it has no child
foreach ($children as $child) { // call delete_node() recursively on each child
delete_node ($child[1]);
}
}
$delete->execute( array($node_id) ); // then delete this node (or do nothing if $node_id does not exist)
}
// to delete one category and all its sub-categories
delete_node(15790);
// to delete all contents
$allTopLevel = $pdo->exec('SELECT id FROM categories WHERE parent_id = 0')->fetchAll(PDO::FETCH_NUM);
foreach ($allTopLevel as $node) {
delete_node($node[0]);
}
没有测试,甚至不确定它是否"编译",但你明白了。确保在调用 delete_node()
之前锁定表(或启动事务)。
对不起,我帮不上什么忙,因为SQL不是我的菜。但也许有人可以将 java 伪代码转移到解决方案中
delete(table.getFirstRow().get(id));
delete(id_a){
for(data_set : table)
if(data_set.get(parent_id) == id_a)delete(data_set.get(id));
}
table.remove(id_a);
}
编辑:没有关于元素的迭代?所以像这样的事情?
delete(list){
if(list.size() == 0)return;
idList = list.getAll(id);
plist = table.getAllWhichEquals(parent_id, idList);
delete(plist);
table.remove(idList);
}
啊,算了,我不是同时删除所有,只是尝试^^