我在mySQL中遇到了一个问题,如下所示:
- 计算mySQL数据库中字符串字段中子字符串的数量
- 按该子字符串出现次数排序结果(降序)
我从未做过比基础查询更复杂的查询...我找不到其他解决方案。
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)
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
LENGTH('中文')
的长度为4,在UTF-8编码中,LENGTH('中文')
的长度为6。因此,我会使用CHAR_LENGTH
代替LENGTH
来计算字符串的长度。 - LiuYan 刘研