为什么我的 MySQL MATCH() AGAINST() 查询对 LEFT JOIN 数据库中的列失败?
我有一个类似于以下内容的 MySQL 查询:
SELECT *
FROM products
LEFT JOIN descriptions ON products.DescriptionID = descriptions.ID
WHERE MATCH (name, overview, specs) AGAINST ('ram');
我尝试使用 MATCH() AGAINST()
搜索的所有列均为全文,但在 phpMyAdmin 中测试时出现以下错误:
#1210 - Incorrect arguments to MATCH
如果我只有 MATCH
一列不会出错并且工作正常,但是一旦我尝试 MATCH
多列,它就会失败并出现此错误。我正在运行 MySQL 5.0.45 和 MySQL 5.0 Full-Text搜索功能文档暗示我可以做到这一点。
是因为LEFT JOIN
吗?我是否需要将一堆 MATCH() AGAINST()
函数调用进行OR
组合在一起?
更新@Zak:我无法发布表创建语句,但我可以说在此示例中的列如下:products.name
、descriptions .概述
、描述.规格
。如果我以完整的 table.column
格式指定它们,它不会改变行为。
但是,如果我从 MATCH()
中丢失 products.name
,则会收到以下错误:
#1191 - Can't find FULLTEXT index matching the column list
但是,descriptions.overview
和 descriptions.specs
为全文。
I've got a MySQL query somewhat like the following:
SELECT *
FROM products
LEFT JOIN descriptions ON products.DescriptionID = descriptions.ID
WHERE MATCH (name, overview, specs) AGAINST ('ram');
All columns I'm trying to search with MATCH() AGAINST()
are FULLTEXT, but I get the following error when testing in phpMyAdmin:
#1210 - Incorrect arguments to MATCH
If I only MATCH
one column it doesn't error and works correctly, but as soon as I try to MATCH
multiple columns it fails with this error. I'm running MySQL 5.0.45 and the MySQL 5.0 Full-Text Search Functions documentation implies I can do this.
Is it because of the LEFT JOIN
? Do I need to OR
together a bunch of MATCH() AGAINST()
function calls?
Update @Zak: I can't post the table creation statements, but I can say that in this example the columns are as follows: products.name
, descriptions.overview
, descriptions.specs
. If I specify them in full table.column
format, it doesn't change the behavior.
However, if I lose products.name
from the MATCH()
I get the following error:
#1191 - Can't find FULLTEXT index matching the column list
But, both descriptions.overview
and descriptions.specs
are FULLTEXT.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
MATCH()
的参数必须与 FULLTEXT 索引定义中的列相同。它们的数量和位置必须相同。此外,由于您无法使用不同表中的多个列创建索引,因此无法在单个
MATCH()
调用中与不同表中的列进行匹配。如果要搜索不同表中的文本列,则必须在每个表中创建 FULLTEXT 索引,然后使用单独的
MATCH()
调用搜索每个索引。使用OR
表达式组合结果。The arguments to
MATCH()
must be the same columns in your FULLTEXT index definition. They must be the same in number as well as position.Also, since you can't make an index with multiple columns from different tables, you can't match against columns from different tables in a single
MATCH()
call.If you want to search text columns from different tables, you'll have to create a FULLTEXT index in each table, and then search each index with a separate
MATCH()
call. UseOR
expressions to combine the results.我遇到了同样的问题。问题是您无法匹配不同表中的列,因此您必须拆分查询。
尝试这样的事情(编辑以匹配列和表):
希望它有帮助!
I've run into that same problem. The thing is that you can't match columns from different tables so you have to split the query.
Try something like this (edited to match columns and tables):
Hope it helps!