尝试从多列全文mysql搜索中对结果进行排序

我正在使用一个有10列的表 . 第一个是广泛的类别，接下来的9个概念随着您的上升而变得更加具体 . 所有行都有一个类别和至少1号属性，但从那里可能是空白 .

我正在尝试编写一个查询，命令关键字全文搜索结果首先按类别中的结果数量分组，然后按可能的最高属性排序 . 因此，具有30个实例的cat将在具有10个的实例之前出现 . 但是在该类别中，具有第9个属性的所有结果将在第9列为空的结果之前位于顶部 .

以下是显示我在哪里的两个主要代码：

$sql = "SELECT *, 
        MATCH (SubCat09Name) AGAINST ('".$keyword."') AS res9, 
        MATCH (SubCat08Name) AGAINST ('".$keyword."') AS res8,
        MATCH (SubCat07Name) AGAINST ('".$keyword."') AS res7,
        MATCH (SubCat06Name) AGAINST ('".$keyword."') AS res6,
        MATCH (SubCat05Name) AGAINST ('".$keyword."') AS res5,
        MATCH (SubCat04Name) AGAINST ('".$keyword."') AS res4,
        MATCH (SubCat03Name) AGAINST ('".$keyword."') AS res3,
        MATCH (SubCat02Name) AGAINST ('".$keyword."') AS res2, 
        MATCH (SubCat01Name) AGAINST ('".$keyword."') AS res1, 
        MATCH (Category) AGAINST ('".$keyword."') AS res15 
        FROM stufftaxonomy 
    WHERE 
     MATCH (SubCat09Name, SubCat08Name, SubCat07Name, SubCat06Name, SubCat05Name, SubCat04Name, SubCat03Name, SubCat02Name, SubCat01Name, Category) AGAINST ('".$keyword." IN BOOLEAN MODE')
        ORDER BY res15 DESC, res9 DESC, res8 DESC, res7 DESC, res6 DESC, res5 DESC, res4 DESC, id DESC LIMIT 25000";

并且：

foreach($mvres as $res){
    if(strlen($res->Category) > 1 || strlen($res->SubCat01Name) > 1 || strlen($res->SubCat02Name) > 1 || strlen($res->SubCat03Name) > 1 || strlen($res->SubCat04Name) > 1 || strlen($res->SubCat05Name) > 1 || strlen($res->SubCat06Name) > 1 || strlen($res->SubCat07Name) > 1 || strlen($res->SubCat08Name) > 1 || strlen($res->SubCat09Name) > 1){

    if($catHead != $res->Category){$response .= '';}
    if($catHead != $res->Category){$response .= 'id.'" class="categoryHead">'.$res->Category.' ';}

    $response .= '';
    $response .= 'id.'" class="taxID">id: id.'">'.$res->id.'';
    if(strlen($res->SubCat01Name) > 0){$response .= ''.$res->Category.'';}
    if(strlen($res->SubCat09Name) > 0){$response .= ' 09:'.$res->SubCat09Name.'';}
    if(strlen($res->SubCat08Name) > 0){$response .= ' 08:'.$res->SubCat08Name.'';}
    if(strlen($res->SubCat07Name) > 0){$response .= ' 07:'.$res->SubCat07Name.'';}
    if(strlen($res->SubCat06Name) > 0){$response .= ' 06:'.$res->SubCat06Name.'';}
    if(strlen($res->SubCat05Name) > 0){$response .= ' 05:'.$res->SubCat05Name.'';}
    if(strlen($res->SubCat04Name) > 0){$response .= ' 04:'.$res->SubCat04Name.'';}
    if(strlen($res->SubCat03Name) > 0){$response .= ' 03:'.$res->SubCat03Name.'';}
    if(strlen($res->SubCat02Name) > 0){$response .= ' 02:'.$res->SubCat02Name.'';}
    if(strlen($res->SubCat01Name) > 0){$response .= ' 01:'.$res->SubCat01Name.'';}
    $response .= '';

    $catHead = $res->Category;
    if($catHead != $mvres[$i+1]->Category){$response .= '';}
    $i++;
    }
    }

我知道这个数据库设计太简陋了 . 这也是我提问的一部分;是否值得中断类别表，并索引原始？它是84,000行，这就是我得到它的方式 .

非常感谢任何专家的见解！

另外，我发现在我的 $keyword 中添加 + 或 - 没有所需的布尔效果 . 我也喜欢这样工作......

更新：

好吧，我对以下内容感到非常满意：

SELECT * FROM stufftaxonomy 
    WHERE 
     MATCH (Category) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat01Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat02Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat03Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat04Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat05Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat06Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat07Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat08Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) || 
     MATCH (SubCat09Name) AGAINST ('".$keyword."' IN BOOLEAN MODE) 
        ORDER BY Category ASC, 
        length(SubCat09Name) + length(SubCat08Name) + length(SubCat07Name) + length(SubCat06Name) + length(SubCat05Name) + length(SubCat04Name) + length(SubCat03Name) + length(SubCat02Name) + length(SubCat01Name) DESC, 
        SubCat09Name ASC, SubCat08Name ASC, SubCat07Name ASC, SubCat06Name ASC, SubCat05Name ASC, SubCat04Name ASC, SubCat03Name ASC, SubCat02Name ASC, SubCat01Name ASC 
        LIMIT 25000

如果单级属性按字母顺序排列，我会更高兴 .