Mysql计算子字符串出现次数，按顺序排序

Question

我在mySQL中遇到了一个问题，如下所示：

• 计算mySQL数据库中字符串字段中子字符串的数量
• 按该子字符串出现次数排序结果（降序）

我从未做过比基础查询更复杂的查询...我找不到其他解决方案。

- Alan

Answer 1

SELECT (CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, ''))) / CHAR_LENGTH(substr) AS cnt
...
ORDER BY cnt DESC

是的，看起来很臃肿，但据我所知没有其他可能的解决方案。

mysql> select (CHAR_LENGTH('asd') - CHAR_LENGTH(REPLACE('asd', 's', ''))) / CHAR_LENGTH('s');
+-----------------------------------------------------------------+
| (CHAR_LENGTH('asd') - CHAR_LENGTH(REPLACE('asd', 's', ''))) / CHAR_LENGTH('s') |
+-----------------------------------------------------------------+
|                                                          1.0000 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> select host, (CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, 'l', ''))) / CHAR_LENGTH('l') AS cnt from user;
+-----------+--------+
| host      | cnt    |
+-----------+--------+
| 127.0.0.1 | 0.0000 |
| honeypot  | 0.0000 |
| honeypot  | 0.0000 |
| localhost | 2.0000 |
| localhost | 2.0000 |
+-----------+--------+
5 rows in set (0.00 sec)

- zerkms

---

@Alan：添加了一个更多的样例查询。 - zerkms

@Alan：尝试从头开始编写相同的查询，而不是从SO复制粘贴。似乎是一些无法打印的字符破坏了查询。 - zerkms

啊..这是一个超出了问题范围的无关问题。我相信现在它已经可以工作了。感谢你们的帮助！ - Alan

作为一名东亚用户，我想提醒大家，如果字段(服务器端)和substr(客户端/应用程序端)都包含多字节字符并且它们使用不同的编码，那么LENGTH将会得到相同字符串的不同长度，从而导致结果值成为十进制而非整数。你不能简单地将小数值向下取整或向上取整为整数，因为你不知道服务器端字段的编码。例如：在GBK编码中，LENGTH('中文')的长度为4，在UTF-8编码中，LENGTH('中文')的长度为6。因此，我会使用CHAR_LENGTH代替LENGTH来计算字符串的长度。 - LiuYan 刘研

@DanFare，如果你需要操作字符串，那么很可能你不会有更好的选择，因为你必须要操作字符串 :-) - zerkms

Answer 2

DELIMITER //
DROP FUNCTION IF EXISTS `subStringCount`//
CREATE FUNCTION `subStringCount` (sequence VARCHAR(1000), word VARCHAR(100)) RETURNS INT(4)
DETERMINISTIC
CONTAINS SQL
BEGIN
    DECLARE counter SMALLINT UNSIGNED DEFAULT 0;
    DECLARE word_length SMALLINT UNSIGNED;

    SET word_length = CHAR_LENGTH(word);

    WHILE (INSTR(sequence,word) != 0) DO
        SET counter = counter+1;
        SET sequence = SUBSTR(sequence, INSTR(sequence,word)+word_length);
    END WHILE; 

    RETURN counter;
END //
DELIMITER ;

可以通过调用以下命令来执行：

SELECT sum(subStringCount(fieldName,'subString')) FROM  `table` WHERE 1 

- FedeEDavide