跨两表ID与名称检索及产品关键词搜索优化技术问询

嘿，我来帮你搞定这两个需求——跨表ID/名称检索，还有优化那有点局限的关键词搜索逻辑，咱们一个个说：

根据两个表的关联情况，有两种常用实现方式：

• 关联表场景（通过ID关联）
  如果两个表是通过外键绑定的（比如product表和category表，用product.category_id关联category.id），可以用JOIN语句同时检索ID和名称匹配的内容。举个例子，要找产品ID/名称匹配，或者所属分类名称匹配的有效产品：

// 假设目标检索的ID和名称
$targetId = 123;
$targetName = "智能手机";

// 预处理查询，避免SQL注入
$stmt = $conn->prepare("
    SELECT p.*, c.category_name 
    FROM product p 
    LEFT JOIN category c ON p.category_id = c.id 
    WHERE (p.id = ? OR p.name LIKE ? OR c.category_name LIKE ?) 
    AND p.status = 1 
    ORDER BY p.id DESC
");
$likeName = "%$targetName%";
$stmt->bind_param("iss", $targetId, $likeName, $likeName);
$stmt->execute();
$results = $stmt->get_result();

• 独立表场景（无关联关系）
  如果两个表没有直接关联，只是需要分别根据ID/名称检索再合并结果，可以分开执行查询后处理：

$targetId = 123;
$targetName = "智能手机";

// 检索产品表
$stmtProduct = $conn->prepare("SELECT * FROM product WHERE id = ? OR name LIKE ? AND status=1");
$likeName = "%$targetName%";
$stmtProduct->bind_param("is", $targetId, $likeName);
$stmtProduct->execute();
$productResults = $stmtProduct->get_result()->fetch_all(MYSQLI_ASSOC);

// 检索另一个表（比如品牌表）
$stmtBrand = $conn->prepare("SELECT * FROM brand WHERE id = ? OR brand_name LIKE ?");
$stmtBrand->bind_param("is", $targetId, $likeName);
$stmtBrand->execute();
$brandResults = $stmtBrand->get_result()->fetch_all(MYSQLI_ASSOC);

// 合并结果（根据需求去重或保留所有）
$combinedResults = array_merge($productResults, $brandResults);

你原来的方案只能匹配包含所有关键词的产品，确实有局限，这里给你三个递进的改进思路：

1. 宽松匹配：满足任意关键词即可 #

把原来的AND改成OR，这样只要产品名称包含任意一个输入的关键词就会被检索到，适合需要扩大搜索范围的场景：

$keywords = array_filter(explode(' ', trim($psearch))); // 过滤空字符串，避免无效匹配
$searchTerms = [];
foreach ($keywords as $word) {
    $searchTerms[] = "name LIKE ?";
}

if (!empty($searchTerms)) {
    $qry = "SELECT * FROM product WHERE ".implode(' OR ', $searchTerms)." AND status=1 ORDER BY RAND() LIMIT 12";
    $stmt = $conn->prepare($qry);
    // 绑定所有关键词的模糊匹配参数
    $likeParams = array_map(fn($word) => "%$word%", $keywords);
    $stmt->bind_param(str_repeat('s', count($likeParams)), ...$likeParams);
    $stmt->execute();
    $results = $stmt->get_result();
}

2. 智能排序：优先展示匹配更多关键词的产品 #

如果想兼顾“精准匹配优先”，可以给每个产品的关键词匹配数量打分，排序时优先展示匹配更多关键词的产品，比随机排序更合理：

$keywords = array_filter(explode(' ', trim($psearch)));
$conditions = [];
$scoreParts = [];

foreach ($keywords as $word) {
    $conditions[] = "name LIKE ?";
    $scoreParts[] = "CASE WHEN name LIKE ? THEN 1 ELSE 0 END";
}

if (!empty($conditions)) {
    $qry = "SELECT *, (".implode(' + ', $scoreParts).") AS match_score 
            FROM product 
            WHERE ".implode(' OR ', $conditions)." AND status=1 
            ORDER BY match_score DESC, RAND() LIMIT 12";
    // 每个关键词要绑定两次（一次在WHERE，一次在分数计算）
    $bindParams = [];
    foreach ($keywords as $word) {
        $likeWord = "%$word%";
        $bindParams[] = $likeWord;
        $bindParams[] = $likeWord;
    }
    $stmt = $conn->prepare($qry);
    $stmt->bind_param(str_repeat('s', count($bindParams)), ...$bindParams);
    $stmt->execute();
    $results = $stmt->get_result();
}

这样匹配3个关键词的产品会排在匹配2个的前面，用户体验会好很多。

3. 高效检索：用全文索引替代LIKE（适合大数据量） #

如果你的产品数据量比较大，多个LIKE语句会导致性能问题，这时可以用MySQL的全文索引来实现更智能、高效的搜索：
首先给产品名称字段创建全文索引：

ALTER TABLE product ADD FULLTEXT INDEX idx_product_name (name);

然后用MATCH AGAINST语句查询（支持中文的话，需要确保MySQL字符集是utf8mb4，且配置了ngram分词插件）：

$searchQuery = trim($psearch);
$stmt = $conn->prepare("
    SELECT *, MATCH(name) AGAINST(? IN BOOLEAN MODE) AS relevance 
    FROM product 
    WHERE MATCH(name) AGAINST(? IN BOOLEAN MODE) AND status=1 
    ORDER BY relevance DESC LIMIT 12
");
$stmt->bind_param("ss", $searchQuery, $searchQuery);
$stmt->execute();
$results = $stmt->get_result();

这种方式支持更灵活的搜索规则，比如用+关键词表示必须包含，-关键词表示排除，而且性能远优于多个LIKE拼接的查询。

额外提醒 #

无论用哪种方案，一定要用预处理语句绑定参数，绝对不能直接把用户输入拼接到SQL里，避免SQL注入风险；另外记得过滤用户输入中的空字符串，避免生成无效的查询条件。

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