MySQL计数匹配词 [英] MySQL count matching words

问题描述

如何查询以获取字段中匹配单词的数量，特别是在MySQL中. 只是我需要获取搜索字词"出现在字段值中的次数.

How to query to get count of matching words in a field, specifically in MySQL. simply i need to get how many times a "search terms"appear in the field value.

例如，值是一二一二"，所以当我搜索单词一"时，它应该给我3

for example, the value is "one two one onetwo" so when i search for word "one" it should give me 3

有可能吗?因为当前我只是从数据库中提取值，并使用服务器端语言进行计数.

is it possible? because currently i just extract the value out of database and do the counting with server side language.

谢谢

推荐答案

您可以创建一个直接在SQL中使用的函数，以便一步一步完成所有工作.

You could create a function to be used directly within SQL, in order to do it all in one step.

这是我在MySQL网站上找到的函数:

delimiter ||
DROP FUNCTION IF EXISTS substrCount||
CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE count TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET count = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET count = count + 1;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN count;
END;

||

delimiter ;

您应该像这样使用它:

SELECT substrCount('one two one onetwo', 'one') `count`; // Returns 3

这篇关于MySQL计数匹配词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！