JavaEar 专注于收集分享传播有价值的技术资料

having with match and group_concat in mysql

I' trying to write a MYSQL query which looks for a string in an aggregation of fields.

The following query finds all the concatenations where "io sono" is present:

SELECT chapter, GROUP_CONCAT(text_search) AS aggregated_chapters
FROM bible_it_cei_2008
GROUP BY chapter
HAVING aggregated_chapters LIKE '%io sono%';

However, trying to use MATCH... AGAINST instead of LIKE:

SELECT chapter, GROUP_CONCAT(text_search) AS aggregated_chapters
FROM bible_it_cei_2008
GROUP BY chapter
HAVING MATCH ( aggregated_chapters ) AGAINST ( '+"io sono"' IN BOOLEAN MODE);

returns the error:

#1210 - Incorrect arguments to MATCH

Isn't there any way to use MATCH AGAINST with GROUP_CONCAT?

1个回答

    最佳答案
  1. Isn't there any way to use MATCH AGAINST with GROUP_CONCAT?

    No. That's not the way FULLTEXT search works in MySQL.

    If your table contains the columns chapter and text_search, and you hope to find the values of chapter matching text search, you want something like this.

    SELECT chapter, 
           MATCH(text_search) AGAINST ('+"io sono"' IN NATURAL LANGUAGE MODE) AS score 
      FROM bible_it_cei_2008
    

    To get this to work you'll need to create an appropriate FULLTEXT index.