MySQL实现在线音频软件三条件搜索及无结果时相关结果展示

核心问题分析 #

• 原有查询逻辑仅支持二选一（流派/分类），无法同时匹配标题+流派+分类三个条件
• 直接拼接变量存在SQL注入风险，且针对固定下拉选项使用LIKE匹配不合理（应采用精确匹配）
• 无严格匹配结果时，缺乏自动降级到次优相关结果的逻辑
• 全文搜索与排序规则结合生硬，无法体现结果相关性优先级

改进后的MySQL查询方案 #

1. 数据库索引优化（前置准备） #

先给字段添加合适索引，提升查询效率：

-- 给标题添加全文索引，用于精准全文搜索
ALTER TABLE audiolovedb ADD FULLTEXT INDEX ft_title (`title`);
-- 给流派、分类添加普通索引，用于快速精确匹配
ALTER TABLE audiolovedb ADD INDEX idx_genre (`genre`);
ALTER TABLE audiolovedb ADD INDEX idx_category (`category`);

2. 动态多条件查询+权重排序逻辑 #

采用权重打分机制优先返回匹配条件最多的结果，同时支持无结果时自动降级：

// 初始化参数与查询片段
$params = [];
$whereClauses = [];
$scoreCalculation = [];

// 处理标题搜索
if (!empty(trim($search))) {
    $searchTerm = trim($search);
    // 全文搜索匹配，权重最高（×3）
    $whereClauses[] = "MATCH(`title`) AGAINST (? IN BOOLEAN MODE)";
    $params[] = $searchTerm;
    $scoreCalculation[] = "MATCH(`title`) AGAINST (? IN BOOLEAN MODE) * 3";
    $params[] = $searchTerm;
    // 标题前缀匹配，次优权重（×2）
    $scoreCalculation[] = "(`title` LIKE ?) * 2";
    $params[] = "$searchTerm%";
}

// 处理流派精确匹配
if (!empty(trim($genre))) {
    $genreTerm = trim($genre);
    $whereClauses[] = "`genre` = ?";
    $params[] = $genreTerm;
    $scoreCalculation[] = "(`genre` = ?) * 1";
    $params[] = $genreTerm;
}

// 处理分类精确匹配（修正原代码拼写错误：catagory → category）
if (!empty(trim($category))) {
    $categoryTerm = trim($category);
    $whereClauses[] = "`category` = ?";
    $params[] = $categoryTerm;
    $scoreCalculation[] = "(`category` = ?) * 1";
    $params[] = $categoryTerm;
}

// 构建基础查询
if (!empty($whereClauses)) {
    $baseQuery = "SELECT *, (" . implode(" + ", $scoreCalculation) . ") AS relevance 
                  FROM audiolovedb 
                  WHERE " . implode(" AND ", $whereClauses) . " 
                  ORDER BY relevance DESC, `title` 
                  LIMIT ?";
    $params[] = $amount;
} else {
    // 无任何搜索条件时返回默认排序结果
    $baseQuery = "SELECT * FROM audiolovedb ORDER BY `title` LIMIT ?";
    $params[] = $amount;
}

// 执行严格匹配查询
$stat = $db->prepare($baseQuery);
$stat->execute($params);
$results = $stat->fetchAll(PDO::FETCH_ASSOC);
$count = $stat->rowCount();

// 严格匹配无结果时，执行降级查询
if ($count === 0 && !empty(trim($search))) {
    // 第一步降级：仅匹配标题全文搜索，忽略流派/分类
    $fallbackQuery = "SELECT *, MATCH(`title`) AGAINST (? IN BOOLEAN MODE) AS relevance 
                      FROM audiolovedb 
                      WHERE MATCH(`title`) AGAINST (? IN BOOLEAN MODE) 
                      ORDER BY relevance DESC, `title` 
                      LIMIT ?";
    $fallbackParams = [trim($search), trim($search), $amount];
    $stat = $db->prepare($fallbackQuery);
    $stat->execute($fallbackParams);
    $results = $stat->fetchAll(PDO::FETCH_ASSOC);
    $count = $stat->rowCount();
    
    // 第二步降级：标题搜索也无结果时返回随机结果
    if ($count === 0) {
        $randomQuery = "SELECT * FROM audiolovedb ORDER BY RAND() LIMIT ?";
        $stat = $db->prepare($randomQuery);
        $stat->execute([$amount]);
        $results = $stat->fetchAll(PDO::FETCH_ASSOC);
    }
}

关键优化点说明 #

• SQL注入防护：全程使用预处理语句绑定参数，杜绝直接拼接变量的风险
• 多条件组合支持：同时兼容标题、流派、分类三个条件的任意组合查询
• 权重排序：通过打分机制让匹配条件更多的结果排在前面（标题匹配权重最高，流派/分类次之）
• 自动降级逻辑：严格匹配无结果时，依次降级为仅标题匹配→随机结果，确保用户始终有内容可看
• 字段拼写修正：统一修正原代码中catagory的拼写错误为category

表单代码小修正 #

原表单存在重复选项与标签未闭合问题，建议调整：

<form method="GET" action='index.php'>
<!-- 修正流派下拉框重复的House选项 -->
<select id='genregrab' name='genre' class='menusmall'>
   <optgroup label='Genre'>
   <option value="">All Genres</option>
   <option value="House">House</option>
   <option value="Hard House">Hard House</option>
   <option value="Tech House">Tech House</option>
   <option value="Deep House">Deep House</option>   
   <option value="Future House">Future House</option>
   <option value="Tribal House">Tribal House</option>
   <option value="Tropical House">Tropical House</option>
   <option value="EDM">EDM</option>
   <option value="DnB">DnB</option>
   <option value="Drum and Bass">Drum And Bass</option>
   </optgroup>
</select>

<!-- 修正分类下拉框的重复选项与未闭合optgroup，修正name为category -->
<select id='catagorygrab' name='category' class='menusmall'>
    <optgroup label='Software'>
    <option value="Windows">Windows Pc</option>
    <option value="Mac OSX">Mac OSX</option>
    </optgroup>

    <optgroup label='Audio'>
    <option value="Samples">Samples</option>
    <option value="Presets">Presets</option>
    <option value="Additional Libraries">Libraries</option>
    <option value="SFX">SFX</option>
    <option value="Vocals">Vocals</option>
    <option value="Film">Film/TV/Game</option>
    </optgroup>

    <optgroup label='Project'>
    <option value="MiDi">MiDi</option>
    <option value="Projects">Projects</option>
    <option value="Templates">Templates</option>
    </optgroup>

    <optgroup label='Learning'>
    <option value="Video">Video</option>
    <option value="eBooks">eBooks</option>
    </optgroup>

    <optgroup label='Addons'> 
    <option value="Skins">Skins</option>
    <option value="Extras">Extras</option> 
    </optgroup>
</select>

<?php $genre = implode(' ', array_slice(explode(' ', $genre), 0, 1)); ?>

<script type="text/javascript">
    document.getElementById('catagorygrab').value = '<?php echo $category ?>'
    document.getElementById('genregrab').value = '<?php echo $genre ?>'
</script>

<!-- 搜索栏 -->
<div class="search-input">
    <input id='search' type="text" name='search' placeholder="Random Products" value="<?php echo $search ?>">
</div>
<div class="search-btn">                                                                                    
    <button><i class="lni lni-search-alt"></i></button>                                                                                     
</div>                                                                                  
</form>

内容的提问来源于stack exchange，提问作者cathy maddox