SQL匹配所有关键字的开头

Question

I will set up a scenario to best describe what I am trying to accomplish.

There is an autocomplete field. The autocomplete is for TV shows. The user inputs "The Wal" hoping to find "The Walking Dead".

The database:

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;

Where shows is the table for all of the TV shows and tags is the table for all of the tags tied to each TV show.

Each word in each show title is inserted as its own lowercase tag in the tags table.

In the shows table:

• (id: 1) (name: The Walking Dead)
• (id: 2) (name: The Wandering Penguin)

In the tags table:

• (sid: 1) (tag: the)
• (sid: 1) (tag: walking)
• (sid: 1) (tag: dead)
• (sid: 2) (tag: the)
• (sid: 2) (tag: wandering)
• (sid: 2) (tag: penguin)

Goal: User inputs, "The Wal", user gets: "The Walking Dead". The query should return all results that meet the criteria, not just one. So if "The Walking Alive" were also a show with the corresponding tags, it should appear as well.

My issue: User inputs, "The Wal", user gets both shows. This is due to the OR clauses for the LIKE statement. I don't know how to fix this after trying for 2 days.

My current query:

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

One method is to use and instead of or. However, you need to use aggregation to get what you want:

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;

However, I don't think that solves your problem, because you don't know that all keywords will match. Instead, order by the number of keywords that do match and choose the best matching one:

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;