mysql sql语法中的单词计数 [英] mysql count word in sql syntax

问题描述

让我们进行一个简单的查询:

Lets have a simple query:

SELECT myfield
FROM   mytable
WHERE  criteria

例如，上面的查询为我们提供了10行结果.字段myField是文本字段.

The above query gives us 10 rows of results for example. The field myField is a text field.

我的问题是:是否可以更改上述查询并获得myField字段的总字数?

My question is this: is it possible to alter the above query and get the total word count of the myField field?

已编辑:总字数"是指查询中所有行中所有选定字段的总字数

Edited: By total word count I mean the total word count of all selected fields in query, in all rows

示例

+------------------------------+-------+
| sentence                     | Words |
+------------------------------+-------+
| Hello World                  |     2 |
| Hello World                  |     2 |
| Mary had a little lamb       |     5 |
| Her fleece was white as snow |     6 |
| Everywhere that mary went    |     4 |
| Umm, sheep followed her      |     4 |
+------------------------------+-------+

推荐答案

使用

Use the excellent function from this question by @otis in your query:

mysql> select * from test;
+----+------------------------------+
| id | sentence                     |
+----+------------------------------+
|  0 | Hello World                  |
|  1 | Hello World                  |
|  2 | Mary had a little lamb       |
|  3 | Her fleece was white as snow |
|  4 | Everywhere that mary went    |
|  5 | Umm, sheep followed her      |
+----+------------------------------+
6 rows in set (0.00 sec)

mysql> SELECT sentence, wordcount(sentence) as "Words" from test;
+------------------------------+-------+
| sentence                     | Words |
+------------------------------+-------+
| Hello World                  |     2 |
| Hello World                  |     2 |
| Mary had a little lamb       |     5 |
| Her fleece was white as snow |     6 |
| Everywhere that mary went    |     4 |
| Umm, sheep followed her      |     4 |
+------------------------------+-------+
6 rows in set (0.02 sec)

要使函数正常工作，您需要在MySQL中执行函数的声明.就像执行任何其他查询一样:

To make the function work, you need to execute the declaration of the function in MySQL. It is just like executing any other query:

mysql> DELIMITER $$
mysql> CREATE FUNCTION wordcount(str TEXT)
            RETURNS INT
            DETERMINISTIC
            SQL SECURITY INVOKER
            NO SQL
       BEGIN
         DECLARE wordCnt, idx, maxIdx INT DEFAULT 0;
         DECLARE currChar, prevChar BOOL DEFAULT 0;
         SET maxIdx=char_length(str);
         WHILE idx < maxIdx DO
             SET currChar=SUBSTRING(str, idx, 1) RLIKE '[[:alnum:]]';
             IF NOT prevChar AND currChar THEN
                 SET wordCnt=wordCnt+1;
             END IF;
             SET prevChar=currChar;
             SET idx=idx+1;
         END WHILE;
         RETURN wordCnt;
       END
     $$
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;

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