mysql 用 sql 语法统计单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12156970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql count word in sql syntax
提问by andrew
Lets have a simple query:
让我们做一个简单的查询：
SELECT myfield
FROM mytable
WHERE criteria
The above query gives us 10 rows of results for example. The field myFieldis a text field.
例如，上面的查询为我们提供了 10 行结果。该字段myField是一个文本字段。
My question is this: is it possible to alter the above query and get the total word count of the myFieldfield?
我的问题是：是否可以更改上述查询并获取该myField字段的总字数？
Edited:By total word count I mean the total word count of all selected fields in query, in all rows
编辑：总字数是指查询中所有选定字段的总字数，在所有行中
Example
例子
+------------------------------+-------+
| 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 |
+------------------------------+-------+
回答by Burhan Khalid
Use the excellent function from this questionby @otis in your query:
在您的查询中使用@otis这个问题中的优秀功能：
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)
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 中执行该函数的声明。就像执行任何其他查询一样：
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 ;
