php - 如何在全文搜索SQL中获取与列值匹配的字符串

Question

就像下面的查询，它显示分数。我想要与

匹配的字符串或单词相同

SELECT Opinion, MATCH(Opinion) AGAINST('nice language' IN BOOLEAN MODE) AS Score 
FROM opinions 
WHERE MATCH(Opinion) AGAINST ('nice language' IN BOOLEAN MODE);

输出:

+---------------------------------------+-------+
| Opinion                               | Score |
+---------------------------------------+-------+
| PHP is a very nice language           |     2 |
| Spain is a nice country to visit      |     1 |
| Perl is not as nice a language as PHP |     2 |
+---------------------------------------+-------+

但想要以下输出:

+---------------------------------------+--------------+
| Opinion                               | String       |
+---------------------------------------+--------------+
| PHP is a very nice language           | nice language|
| Spain is a nice country to visit      | nice         |
| Perl is not as nice a language as PHP | nice language|
+---------------------------------------+-------+------+

Answer 1

如果您正在寻找如下所示的结果，

然后使用下面的查询

select Opinion, 
      case 
        when Opinion like '%nice language%' or (Opinion like '% nice %' and Opinion like '% language %') then 'nice language' 
        when Opinion like '% nice %' then 'nice' 
        when Opinion like '% language %' then 'language' 
      end string  
from table1
where 
    Opinion like '%nice language%' 
      OR (Opinion like '% nice %' and Opinion like '% language %') 
      OR Opinion like '% nice %' 
      OR Opinion like '% language %'

希望这对您有帮助。