MySQL多个Id查找 [英] MySQL multiple Id lookups

问题描述

我正在尝试将全文搜索添加到系统中。我想写的查询需要涉及多个查找，然后搜索（如果甚至可能）。

我有一张教师表和一个主题表。

pre $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ [int]
subjectThreeId [int]
teacherBiography [text]

subject
subjectId [int]
subjectName [text]
< code $
$ b $ p
$ b $ p $所以最终我想要一个结果集沿着..

<$ p
（subjectTwoName [text]）
（subjectThreeName [text]）
teacherId [int]
teacherBiography [text]
（subjectOneName [text] ）

所以括号中的最后三个字段是不存在的，但是我想要执行一个文本搜索他们是否需要设置一个foriegn关键约束（如果对现有系统有进一步的影响，我宁愿不要这么做），还是我能做些更有说服力的事情？

解决方案 MySQL 不能索引视图，所以你想在 MySQL 。

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

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

  CREATE TABLE ftsearch 
（
 teacherId INT PRIMARY KEY，
 teacherBiography TEXT，
 subject1 TEXT，
 subject2 TEXT，
 subject3 TEXT， 
）
 ENGINE = MyISAM 
  

并用下面的查询填充：选择teacherId，teacherBiography，
s1.name，s2.name，

  INSERT 
 INTO ftsearch 
 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 ，那么可以将全文搜索查询（以布尔模式）应用于连接如果你正在寻找'+琼斯+数学+物理学'，那么你必须创建一个全文索引。

，其中 Jones 是教师姓氏， math 和 physics 是主题，你可以做这个查询：

$ p $ 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模式）
AND MATCH （t.teacherBiography，s1.name， s2.name，s3.name）AGAINST（'+ Jones + math + physics'IN BOOLEAN MODE）


MATCH（teacherBiography）AGAINST（'+ Jones'）将在 FULLTEXT >老师，如果有的话;第二个 MATCH 将对结果进行精细过滤。

涉及 OR code>条件或相关性排序更复杂，但是。


I'm trying to add a full text search to a system. The query I want to write needs to involve multiple lookups followed by the search (if that's even possible).

I've got a table of teachers and a table of subjects.

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

subjects
subjectId [int]
subjectName [text]

So ultimately I want a resultset along the lines of..

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

So these last three fields in brackets are non existant but I do want to perform a text search upon them, do I need to setup a foriegn key constraint (which I'd rather not to do in case of further impacts on the existing system) or is there something more eloquent I can do?

解决方案

MySQL cannot index views so what you want is impossible in MySQL.

You can use an external fulltext indexing engine like Sphinx and load data there using a query with JOINS

Alternatively, you can create a denormalized table:

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

and fill it with this query:

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 

on a timely basis.

Actually, if all your tables are MyISAM, you can apply fulltext search queries (in boolean mode) to a join, without having to create a fulltext index.

Say, if you are searching for '+Jones +math +physics', where Jones is a teacher's surname and math and physics are subjects, you can do this query:

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)

The MATCH(teacherBiography) AGAINST ('+Jones') will use a FULLTEXT index on teacher, if any; the second MATCH will fine-filter the results.

Queries involving the OR conditions or relevance sorting are more complex, however.

这篇关于MySQL多个Id查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！