MySQL 多个 Id 查找

Question

我正在尝试向系统添加全文搜索。我要编写的查询需要涉及多个查找，然后进行搜索(如果可能的话)。

我有一张教师表和一张科目表。

teacherProfile
teacherId [int] - primary key
subjectOneId [int]
subjectTwoId [int]
subjectThreeId [int]
teacherBiography [text]

subjects
subjectId [int]
subjectName [text]

所以最终我想要一个结果集......

teacherId [int]
teacherBiography [text]
( subjectOneName [text] )
( subjectTwoName [text] )
( subjectThreeName [text] )

所以括号中的最后三个字段不存在，但我确实想对它们执行文本搜索，我是否需要设置外键约束(我宁愿不这样做，以防进一步影响现有系统)还是我可以做一些更有说服力的事情？

Answer 1

MySQL 无法索引 View ，因此您想要的在 MySQL 中是不可能的。

您可以使用像 Sphinx 这样的外部全文索引引擎，并使用带有 JOINS 的查询加载数据

或者，您可以创建一个非规范化表:

CREATE TABLE ftsearch
        (
        teacherId INT PRIMARY KEY,
        teacherBiography TEXT,
        subject1 TEXT,
        subject2 TEXT,
        subject3 TEXT,
        )
ENGINE=MyISAM

并用这个查询填充它:

INSERT
INTO    ftsearch
SELECT  teacherId, teacherBiography,
        s1.name, s2.name, s3.name
FROM    teacherProfile
LEFT JOIN
        subject s1
ON      s1.id = subjectOneId 
LEFT JOIN
        subject s2
ON      s2.id = subjectTwoId 
LEFT JOIN
        subject s3
ON      s3.id = subjectThreeId 

及时。

实际上，如果您的所有表都是 MyISAM，您可以将全文搜索查询(以 bool 模式)应用于连接，而无需创建全文索引。

例如，如果您要搜索 '+Jones +math +physics'，其中 Jones 是老师的姓氏，math 和 physics 是主题，你可以这样查询:

SELECT  teacherId, teacherBiography,
        s1.name, s2.name, s3.name
FROM    teacherProfile
LEFT JOIN
        subject s1
ON      s1.id = subjectOneId 
LEFT JOIN
        subject s2
ON      s2.id = subjectTwoId 
LEFT JOIN
        subject s3
ON      s3.id = subjectThreeId 
WHERE   MATCH(teacherBiography) AGAINST ('+Jones' IN BOOLEAN MODE)
        AND MATCH(t.teacherBiography, s1.name, s2.name, s3.name) AGAINST ('+Jones +math +physics' IN BOOLEAN MODE)

MATCH(teacherBiography) AGAINST ('+Jones') 将使用 teacher 上的 FULLTEXT 索引(如果有的话)；第二个 MATCH 将精细过滤结果。

但是，涉及OR 条件或相关性排序的查询更为复杂。