对列值进行MySQL全文搜索?

时间:2022-09-19 19:10:40

I need to do a Fulltext search for a whole bunch of values out of a column in another table. Since MATCH() requires a value in the AGAINST() part, a straightforward: "SELECT a.id FROM a,b WHERE MATCH(b.content) AGAINST(a.name)" fails with "Incorrect arguments to AGAINST".

我需要对另一个表中的一列中的所有值进行全文搜索。因为MATCH()需要在AGAINST()部分中包含一个值,所以可以直接使用:“SELECT a”。当匹配(b.content)与(a.name)匹配失败时,“错误的参数反对”。

Now, I know I could write a script to query for a list of names and then search for them, but I'd much rather work out a more complex query that can handle it all at once. It doesn't need to be speedy, either.

现在,我知道我可以编写一个脚本来查询一个名称列表,然后搜索它们,但是我更希望找到一个更复杂的查询,它可以同时处理所有这些问题。它也不需要很快。

Ideas?

想法吗?

thanks

谢谢

1 个解决方案

#1


3  

Unfortunately, http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html says:

不幸的是,http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html表示:

The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

搜索字符串必须是查询求值期间的常量。例如,这排除了表列,因为每一行都可能有不同。

Looks like you'll have to search for the patterns one at a time if you use MySQL's FULLTEXT index as your search solution.

如果你使用MySQL的全文索引作为你的搜索解决方案,看起来你必须一次搜索一个模式。

The only alternative I can think of to allow searching for many patterns like you describe is an Inverted Index. Though this isn't as flexible or scalable as a true full-text search technology.

我能想到的唯一的选择,允许搜索许多模式,如你所描述的是反向索引。虽然这并不像真正的全文搜索技术那样灵活和可伸缩。

See my presentation http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

看到我的演示http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

#1


3  

Unfortunately, http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html says:

不幸的是,http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html表示:

The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

搜索字符串必须是查询求值期间的常量。例如,这排除了表列,因为每一行都可能有不同。

Looks like you'll have to search for the patterns one at a time if you use MySQL's FULLTEXT index as your search solution.

如果你使用MySQL的全文索引作为你的搜索解决方案,看起来你必须一次搜索一个模式。

The only alternative I can think of to allow searching for many patterns like you describe is an Inverted Index. Though this isn't as flexible or scalable as a true full-text search technology.

我能想到的唯一的选择,允许搜索许多模式,如你所描述的是反向索引。虽然这并不像真正的全文搜索技术那样灵活和可伸缩。

See my presentation http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

看到我的演示http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql