php - SQL 匹配所有关键字的开头

Question

我将设置一个场景来最好地描述我想要完成的任务。

有一个自动完成字段。自动完成功能适用于电视节目。用户输入“The Wal”希望找到“行尸走肉”。

数据库:

CREATE TABLE `shows` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tags` (
  `tag` varchar(50) NOT NULL DEFAULT '',
  `sid` int(10) unsigned NOT NULL,
  KEY `sid` (`sid`),
  KEY `alphabetizer` (`tag`),
  CONSTRAINT `tags_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `shows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

其中 shows 是所有电视节目的表格，tags 是与每个电视节目相关的所有标签的表格。

每个节目标题中的每个单词都会作为其自己的小写标签插入 tags 表中。

在显示表中:

• (id:1)(名称:行尸走肉)
• (id:2)(名称:流浪企鹅)

在标签表中:

• (sid:1)(标签:the)
• (sid:1)(标签:步行)
• (sid:1)(标签:死亡)
• (sid:2)(标签:the)
• (sid:2)(标签:流浪)
• (sid:2)(标签:企鹅)

目标: 用户输入“The Wal”，用户得到:“The Walking Dead”。查询应返回满足条件的所有结果，而不仅仅是一个。所以如果《活着》也是一部带有相应标签的节目，它也应该出现。

我的问题:用户输入“The Wal”，用户会看到两个节目。这是由于 LIKE 语句的 OR 子句造成的。我尝试了两天后不知道如何解决这个问题。

我当前的查询:

SELECT name
    FROM shows s
    JOIN tags t ON s.id = t.sid
    WHERE t.tag LIKE "The%" OR t.tag LIKE "Wal%"

Answer 1

一种方法是使用and而不是or。但是，您需要使用聚合来获得您想要的:

SELECT name
FROM shows s JOIN
     tags t
     ON s.id = t.sid
WHERE t.tag LIKE 'The%' OR t.tag LIKE 'Wal%'
GROUP BY name
HAVING sum(t.tag LIKE 'The%') > 0 AND
       sum(t.tag LIKE 'Wal%') > 0;

但是，我认为这并不能解决您的问题，因为您不知道所有关键字都会匹配。相反，按匹配的关键字数量排序并选择最匹配的关键字:

SELECT name
FROM shows s JOIN
     tags t
     ON s.id = t.sid
WHERE t.tag LIKE 'The%' or t.tag LIKE 'Wal%'
GROUP BY name
ORDER BY (MAX(t.tag LIKE 'The%') +
          MAX(t.tag LIKE 'Wal%')
         ) DESC
LIMIT 1;