Mysql子目录和父目录，包含posts [Database schema]

Question

我有一个简单的category表和一个单独的posts表但当用户发布帖子时，我不知道是否应该将子目录和父目录都存储在posts表中但这不会造成大量的数据重复所以我只存储sub_cat然后使用一些PHP函数在数据库中查询主目录及其名称

类别表

ID | cat_name | main_cat
1  | Dinner   | 0 
2  | Chicken  | 1  

posts表

ID | title | sub_cat | fields that are not related to Q
1  | test  | 2       | 

获取父(主)类别$sub_cat =来自一个获取帖子及其sub_cats的选择查询

function main_cat($sub_cat){
require("conn_posts.php");
$stmt = $conn_posts->prepare("SELECT `main_cat` FROM `cats` WHERE `ID` = ?");
$stmt->bind_param("s", $sub_cat);
$stmt->execute();
$stmt_results = $stmt->get_result(); // get result
while($row_get = $stmt_results->fetch_assoc()){
if($row_get['main_cat'] == 0){
return $sub_cat;
}elseif($row_get['main_cat'] !== ""){
return $row_get['main_cat'];
}
}
}

此函数获取任何类别名称，只要id是有效的

function cat_name($cat_number){
require("conn_posts.php");
$stmt = $conn_posts->prepare("SELECT `cat_name` FROM `cats` WHERE `ID` = ?");
$stmt->bind_param("s", $cat_number);
$stmt->execute();
$stmt_results = $stmt->get_result(); // get result
$row_get = $stmt_results->fetch_assoc(); 
if($stmt_results->num_rows <= 0){
return 0;
}elseif($stmt_results->num_rows == 1){
return $row_get['cat_name'];
}
}

我的问题是这是处理我的帖子子类别和父类别的好方法，是否有更好的方法来做我目前正在做的事情?如。我的数据库模式好吗(我说的好是指在posts表中只包含父cat id比做PHP服务器端处理更好吗)?

Answer 1

你的数据库模式很好:它不包括任何复制，我不会改变它。你在PHP中处理获取类别的方式并不是最优的:你应该总是以最小化查询的数量为目标，因为它(通常)会比查询的复杂性更影响性能。

如果你运行的是MySQL 8+，一个很好的方法是使用递归CTE;它将允许您通过一个查询获取所有父节点:

WITH RECURSIVE cte AS (
SELECT id, cat_name, main_cat, 0 as depth FROM categories WHERE ID=3
UNION ALL
SELECT categories.id, categories.cat_name, categories.main_cat, cte.depth+1 as depth
FROM cte inner join categories
ON cte.main_cat = categories.id
)
SELECT cat_name FROM cte order by depth ASC

查询中的数字'3'可以用您试图检索的类别替换。您可以查看这个DB小提琴以获得一个实际示例。如果我看到您的代码，将其合并到PHP中应该是相当简单的。如果没有，请留下评论，我会尽量展开。