Mysql全文搜索：到where子句，还是不到where子句？

发布于 2024-12-05 18:02:09 字数 427 浏览 0 评论 0原文

我有 2 个 mysql 全文搜索查询，它们返回一组不同的结果。

SELECT e.id AS e__id, 
MATCH(e.subtitle, e.summary, e.title, e.prtext) AGAINST ('lorem') 
FROM exhibitions e

将返回一组可预测的 7 行（表中总共有 10 行），每行都有包含单词“lorem”的记录的 id。

但是，当我引入 where 子句时，

SELECT e.id 
FROM exhibitions e 
WHERE MATCH(e.subtitle, e.summary, e.title, e.prtext) AGAINST ('lorem')

不会返回任何行。

这两个查询有什么区别？

原文

I have 2 mysql fulltext search queries that return a different set of results.

SELECT e.id AS e__id, 
MATCH(e.subtitle, e.summary, e.title, e.prtext) AGAINST ('lorem') 
FROM exhibitions e

will return a predictable set of 7 rows (I have 10 overall in the table), each having the id of a record that contains the word 'lorem'.

However, when I introduce a where clause

SELECT e.id 
FROM exhibitions e 
WHERE MATCH(e.subtitle, e.summary, e.title, e.prtext) AGAINST ('lorem')

No rows are returned.

What is the difference between these two queries?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笙痞 2024-12-12 18:02:09

尝试在布尔模式下添加 *

SELECT *,MATCH (ft_search_field) AGAINST ('lorem*' IN BOOLEAN MODE) AS Score
FROM table_name
WHERE MATCH (ft_search_field) AGAINST ('lorem*' IN BOOLEAN MODE)

try adding * IN BOOLEAN MODE

SELECT *,MATCH (ft_search_field) AGAINST ('lorem*' IN BOOLEAN MODE) AS Score
FROM table_name
WHERE MATCH (ft_search_field) AGAINST ('lorem*' IN BOOLEAN MODE)

回复收藏 0 原文

岛歌少女 2024-12-12 18:02:09

实际上，问题是我的表结构上有许多全文索引，而我实际上只需要一个索引。以下是带有 where 和 and 子句的正确查询：

SELECT  e.id AS e__id,
    e.enabled AS e__enabled,
    e.rewrite AS e__rewrite,
    e.title AS e__title,
    e.subtitle AS e__subtitle,
    e.summary AS e__summary,
    e.type AS e__type,
    e.feature_home AS e__feature_home,
    e.date_start AS e__date_start,
    e.date_end AS e__date_end,
    e.prtext AS e__prtext,
    e.last_update AS e__last_update,
    e.file_id1 AS e__file_id1,
    e.file_id2 AS e__file_id2,
    e.file_id3 AS e__file_id3,
    e.file_id4 AS e__file_id4,
    e.file_id5 AS e__file_id5
FROM exhibitions e
WHERE  MATCH(e.subtitle, e.summary, e.title, e.prtext) AGAINST ('lorem')
       AND e.enabled = 1

Actually, the issue was I had a number of fulltext indicies on my table structure, when I really only needed one. Here is a correct query with a where and an and clause:

SELECT  e.id AS e__id,
    e.enabled AS e__enabled,
    e.rewrite AS e__rewrite,
    e.title AS e__title,
    e.subtitle AS e__subtitle,
    e.summary AS e__summary,
    e.type AS e__type,
    e.feature_home AS e__feature_home,
    e.date_start AS e__date_start,
    e.date_end AS e__date_end,
    e.prtext AS e__prtext,
    e.last_update AS e__last_update,
    e.file_id1 AS e__file_id1,
    e.file_id2 AS e__file_id2,
    e.file_id3 AS e__file_id3,
    e.file_id4 AS e__file_id4,
    e.file_id5 AS e__file_id5
FROM exhibitions e
WHERE  MATCH(e.subtitle, e.summary, e.title, e.prtext) AGAINST ('lorem')
       AND e.enabled = 1

回复收藏 0 原文

~没有更多了~