PHP多关键词搜索优化问题：实现全关键词匹配且无报错

我有一个基于PHP和AJAX的搜索框，需要实现多关键词全匹配搜索——输入多个关键词时，商品的name、description、tags字段中必须包含所有关键词（比如输入“pizza mozzarella salami”，要找到同时包含这三个词的商品，而非只含其中一个的）。以下是针对现有问题的解决方案：

现有问题回顾 #

问题1：固定数量关键词导致报错 #

最初硬编码LIKE查询，假设最多5个关键词，输入少于5个时会触发Undefined array key错误，因为数组索引不存在：

function searchResults(){
    $con = mysqli_connect("localhost", "root", "", "cookbook");

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);

    $sql = "SELECT * FROM product WHERE
    name LIKE '%$data[0]%' AND
    name LIKE '%$data[1]%' AND 
    name LIKE '%$data[2]%' AND 
    name LIKE '%$data[3]%' AND 
    name LIKE '%$data[4]%' AND 
    description LIKE '%$data[0]%' AND
    description LIKE '%$data[1]%' AND
    description LIKE '%$data[2]%' AND
    description LIKE '%$data[3]%' AND
    description LIKE '%$data[4]%' AND
    tags LIKE '%$data[0]%' AND
    tags LIKE '%$data[1]%' AND
    tags LIKE '%$data[2]%' AND
    tags LIKE '%$data[3]%' AND
    tags LIKE '%$data[4]%'
    ORDER BY name";
    
    $res = mysqli_query($con,$sql);
    
    $counter = 0;
    $limit = 6;
    
    echo "<ul class='search-list appear'>";
    
    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        echo "<li class='product' id='".$id."'>";
            CONTENT
        echo "</li>";
        
        $counter++;
    }
    
    echo "</ul>";

mysqli_close($con);
}

问题2：填充随机字符+OR逻辑，无法实现全匹配 #

为避免报错填充随机字符，但改用OR逻辑后，只要商品含任意一个关键词就会被匹配，不符合“所有关键词都要存在”的需求：

function searchResults(){
    $con = mysqli_connect("localhost", "root", "", "cookbook");

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);

    if(empty($data[1])){
        $data[1] = "q6Z%6fS8GG+2h6/q/";
    }    
    if(empty($data[2])){
        $data[2] = "q6Z%6fS8GG+2h6/q/";
    }    
    if(empty($data[3])){
        $data[3] = "q6Z%6fS8GG+2h6/q/";
    }    
    if(empty($data[4])){
        $data[4] = "q6Z%6fS8GG+2h6/q/";
    }

    $sql = "SELECT * FROM product WHERE
    name LIKE '%$data[0]%' OR 
    name LIKE '%$data[1]%' OR 
    name LIKE '%$data[2]%' OR 
    name LIKE '%$data[3]%' OR 
    name LIKE '%$data[4]%' OR 
    description LIKE '%$data[0]%' OR
    description LIKE '%$data[1]%' OR
    description LIKE '%$data[2]%' OR
    description LIKE '%$data[3]%' OR
    description LIKE '%$data[4]%' OR
    tags LIKE '%$data[0]%' OR
    tags LIKE '%$data[1]%' OR
    tags LIKE '%$data[2]%' OR
    tags LIKE '%$data[3]%' OR
    tags LIKE '%$data[4]%'
    ORDER BY name";
    
    $res = mysqli_query($con,$sql);
    
    $counter = 0;
    $limit = 6;
    
    echo "<ul class='search-list appear'>";
    
    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        echo "<li class='product' id='".$id."'>";
            CONTENT
        echo "</li>";
        
        $counter++;
    }
    
    echo "</ul>";

mysqli_close($con);
}

问题3：全文搜索的数组转字符串错误 #

使用MySQL全文搜索时，直接把数组$data代入SQL，触发Array to string conversion错误，且只搜索了name字段：

function searchResults(){
    $con = mysqli_connect("localhost", "root", "", "cookbook");

    $search = $_POST["search-bar"];
    $data = explode(' ',$search);

    $sql = "SELECT * 
            FROM product
            WHERE MATCH(name) AGAINST('$data' IN BOOLEAN MODE)
           ";

    $res = mysqli_query($con,$sql);
    
    $counter = 0;
    $limit = 6;
    
    echo "<ul class='search-list appear'>";
    
    while($row = mysqli_fetch_assoc($res) and ($counter < $limit)){
        echo "<li class='product' id='".$id."'>";
            CONTENT
        echo "</li>";
        
        $counter++;
    }
    
    echo "</ul>";

mysqli_close($con);
}

正确实现方案 #

方案1：动态构建安全的LIKE查询（适合小数据集） #

核心逻辑：

• 过滤关键词，去除空字符串与重复值
• 对每个关键词，要求name/description/tags中至少一个包含该关键词
• 所有关键词的匹配条件用AND连接，确保全匹配
• 使用预处理语句防止SQL注入

function searchResults(){
    $con = mysqli_connect("localhost", "root", "", "cookbook");
    if (!$con) {
        die("连接失败: " . mysqli_connect_error());
    }

    // 获取并处理搜索关键词
    $search = trim($_POST["search-bar"] ?? '');
    $keywords = array_filter(explode(' ', $search)); // 去除空元素（处理连续空格）
    $keywords = array_unique($keywords); // 去重重复关键词

    if(empty($keywords)){
        echo "<ul class='search-list appear'></ul>";
        mysqli_close($con);
        return;
    }

    // 动态构建WHERE条件
    $whereClauses = [];
    foreach($keywords as $keyword){
        // 每个关键词需匹配name/description/tags中的至少一个
        $whereClauses[] = "(name LIKE ? OR description LIKE ? OR tags LIKE ?)";
    }
    $whereSql = implode(" AND ", $whereClauses);

    // 准备SQL语句
    $sql = "SELECT * FROM product WHERE $whereSql ORDER BY name LIMIT 6";
    $stmt = mysqli_prepare($con, $sql);

    // 绑定参数：每个关键词对应三个LIKE参数（%keyword%）
    $paramTypes = str_repeat('sss', count($keywords));
    $params = [];
    foreach($keywords as $keyword){
        $likeKeyword = "%$keyword%";
        $params[] = $likeKeyword;
        $params[] = $likeKeyword;
        $params[] = $likeKeyword;
    }
    mysqli_stmt_bind_param($stmt, $paramTypes, ...$params);

    // 执行查询
    mysqli_stmt_execute($stmt);
    $res = mysqli_stmt_get_result($stmt);

    // 输出结果
    echo "<ul class='search-list appear'>";
    while($row = mysqli_fetch_assoc($res)){
        echo "<li class='product' id='".$row['id']."'>";
        echo htmlspecialchars($row['name']); // 替换为你的商品内容
        echo "</li>";
    }
    echo "</ul>";

    // 关闭资源
    mysqli_stmt_close($stmt);
    mysqli_close($con);
}

方案2：MySQL全文搜索（适合大数据集，性能更优） #

核心逻辑：

• 先给name、description、tags创建联合全文索引
• 将关键词转为布尔模式查询字符串（每个关键词前加+，表示必须匹配）
• 使用预处理语句防止注入

步骤1：创建全文索引（MySQL中执行）

ALTER TABLE product ADD FULLTEXT INDEX ft_product(name, description, tags);

步骤2：PHP代码实现

function searchResults(){
    $con = mysqli_connect("localhost", "root", "", "cookbook");
    if (!$con) {
        die("连接失败: " . mysqli_connect_error());
    }

    $search = trim($_POST["search-bar"] ?? '');
    $keywords = array_filter(explode(' ', $search));
    $keywords = array_unique($keywords);

    if(empty($keywords)){
        echo "<ul class='search-list appear'></ul>";
        mysqli_close($con);
        return;
    }

    // 构建布尔模式查询字符串：每个关键词前加+，表示必须匹配
    $fulltextQuery = implode(' ', array_map(function($kw){
        return "+".mysqli_real_escape_string($con, $kw);
    }, $keywords));

    // 准备SQL语句
    $sql = "SELECT * FROM product WHERE MATCH(name, description, tags) AGAINST(? IN BOOLEAN MODE) ORDER BY name LIMIT 6";
    $stmt = mysqli_prepare($con, $sql);
    mysqli_stmt_bind_param($stmt, 's', $fulltextQuery);

    mysqli_stmt_execute($stmt);
    $res = mysqli_stmt_get_result($stmt);

    echo "<ul class='search-list appear'>";
    while($row = mysqli_fetch_assoc($res)){
        echo "<li class='product' id='".$row['id']."'>";
        echo htmlspecialchars($row['name']); // 替换为你的商品内容
        echo "</li>";
    }
    echo "</ul>";

    // 关闭资源
    mysqli_stmt_close($stmt);
    mysqli_close($con);
}

方案对比 #

• 方案1：无需额外索引，适合小数据集；缺点是全表扫描，性能随数据量增长下降
• 方案2：性能远优于LIKE查询，支持复杂搜索逻辑；需要提前创建全文索引，注意MySQL默认停用词（短词可能被忽略，可通过配置调整）

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