如何使用mysql全文获取连续词的频率 [英] How to get frequency of a word in a row using mysql fulltext

问题描述

我有一个 MyISAM 表，其中包含超过200万条记录，其上有 FULLTEXT 索引在多列。

给定一个搜索词，我想知道它在每个记录的索引字段中出现多少次。

例如，当在下表中搜索<​​code>'test'时（其中有 FULLTEXT FREETEXT 和 Third_Col 栏）：

 
 + ---- + ----------------------------- --------------- + --------------------------- + 
 | ID | FREETEXT | Third_Col | 
 + ---- + ---------------------------------------- ---- + --------------------------- + 
 | 1 |这是测试示例中的第一个测试字符串。 |这是第一次测试Values | 
 | 2 |这是第二次测试。 |这是sec col | 
 + ---- + ---------------------------------------- ---- + --------------------------- + 
 

我希望得到如下结果：



 
 + ---- + ------- + 
 | ID | count | 
 + ---- + ------- + 
 | 1 | 3 | 
 | 2 | 1 | 
 + ---- + ------- + 
 

我知道在 FULLTEXT index MySQL使用dtf（术语在文档中出现的次数）;如何获得这个？

解决方案

创建一个像这样的用户定义函数

  DELIMITER $$ 
 
 CREATE FUNCTION`getCount`（myStr VARCHAR（1000），myword VARCHAR（100））
 RETURNS INT 
 BEGIN 
 DECLARE cnt INT DEFAULT 0; 
 DECLARE结果INT DEFAULT 1; 
 
 WHILE（result> 0）DO 
 SET result = INSTR（myStr，myword）; 
 IF（result> 0）THEN 
 SET cnt = cnt + 1; 
 SET myStr = SUBSTRING（myStr，result + LENGTH（myword））; 
 END IF; 
 END WHILE; 
 RETURN cnt; 
 
 END $$ 
 
 DELIMITER; 
  

然后你可以在你的查询中使用这个如下

 选择id，getCount（concat（FREETEXT，Third_col），'test'）from yourtable 
  

希望它有帮助

I have a MyISAM table comprising over 2 million records, on which there is a FULLTEXT index over multiple columns.

Given a search term, I would like to know how many times it occurs within the indexed fields of each record.

For example, when searching for 'test' within the following table (in which there is a FULLTEXT index over both the FREETEXT and Third_Col columns):

+----+--------------------------------------------+---------------------------+
| ID | FREETEXT                                   | Third_Col                 |
+----+--------------------------------------------+---------------------------+
|  1 | This is first test string in test example. | This is first test Values |
|  2 | This is second test.                       | This is sec col           |
+----+--------------------------------------------+---------------------------+

I expect results like:

+----+-------+
| ID | count |
+----+-------+
|  1 |     3 |
|  2 |     1 |
+----+-------+

I know that in the FULLTEXT index MySQL uses dtf (the number of times the term appears in the document); how can one obtain this?

解决方案

Create a user defined function like this

DELIMITER $$

CREATE FUNCTION `getCount`(myStr VARCHAR(1000), myword VARCHAR(100))
    RETURNS INT
    BEGIN
    DECLARE cnt INT DEFAULT 0;
    DECLARE result INT DEFAULT 1;

    WHILE (result > 0) DO
    SET result = INSTR(myStr, myword);
    IF(result > 0) THEN 
        SET cnt = cnt + 1;
        SET myStr = SUBSTRING(myStr, result + LENGTH(myword));
    END IF;
    END WHILE;
    RETURN cnt;    

    END$$

DELIMITER ;

Then you can use this in your query as follows

select id, getCount(concat(FREETEXT, Third_col), 'test') from yourtable

Hope it helps

这篇关于如何使用mysql全文获取连续词的频率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！