php - 这是一个很好的 sql 模式来支持现场搜索框吗？

Question

我正在制作一个网站，它是一个面向英语观众的日本戏剧节目数据库。有趣的是，这种特殊情况意味着每个页面都有多个(大约四个，但有时更多)搜索词，用户可以尝试使用这些搜索词来查找节目。

这是我想出的 MySQL 模式，用于支持具有预输入功能的站内搜索框。

CREATE TABLE `search` (
    `showid` INT(15) NOT NULL ,
    `displayname` VARCHAR(200) NOT NULL ,
    `altnames` VARCHAR(2000) NOT NULL
) ENGINE = InnoDB;

我打算通过将英文、赫本、罗马字和汉字的小写版本的名称放入“替代名称”列中来使用它，每个名称由换行符分隔。然后，为了执行搜索，我将首先使用 PHP 将搜索词全部设为小写，然后使用类似...的查询

SELECT showid, displayname FROM search WHERE altnames LIKE "%search term%\n"

这个模式是最好的吗？它是否可扩展(前面有 Redis 缓存)？我应该使用哪种排序规则才能同时存储拉丁字符和日文字符？

Answer 1

我个人会将模式拆分为两个表。这可能会增加一些开销，但会使其更具可扩展性和更直观(至少我会发现它更直观)。

我会保留您的第一个表，但删除 altnames 列。如果这变得很大，有几十个替代名称，您的搜索可能会变得非常缓慢。

CREATE TABLE `search` (
    `showid` INT(15) NOT NULL ,
    `displayname` VARCHAR(200) NOT NULL
) ENGINE = InnoDB;

然后我会用与搜索表具有多对一关系的 altnames 表替换该 altnames 列:

CREATE TABLE `search_altnames` (
    `id` INT(15) NOT NULL AUTOINCREMENT , 
    `displayname_id` INT(15) NOT NULL ,
    `altname` VARCHAR(200) NOT NULL ,
    FOREIGN KEY (`displayname_id`) REFERENCES search(`showid`)
)

要进行搜索，您可以执行以下操作:

SELECT s.`showid`, s.`displayname`
FROM `search_altnames` sa
INNER JOIN `search` s ON s.`showid` = sa.`displayname_id`
WHERE sa.`altname` LIKE 'search term'

就像我说的，我认为这会更具可扩展性。希望对您有所帮助，祝您好运!

编辑
Here是指向通配符搜索缓慢的页面的链接。