使用SQL来确定文本字段的单词统计数据

Question

我最近一直在开发数据库搜索功能，想要获取一些信息，例如每个文档中的平均单词数（例如数据库中的文本字段）。到目前为止，我所找到的唯一一种方法（不需要在数据库外使用自己选择的语言进行处理）是：

SELECT AVG(LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1)
FROM documents

这似乎有效*，但您是否有其他建议？我目前正在使用MySQL 4（希望尽快为此应用程序升级到版本5），但也对一般解决方案感兴趣。

谢谢！

* 我可以想象，这可能是确定此内容的相当粗略的方法，因为它不考虑内容中的HTML等。对于这个特定项目来说，这还可以接受，但是否有更好的方法呢？

更新：我所说的“更好”是指更准确，性能更高或更“正确”（易于维护，良好的实践等）。对于我可用的内容，上面的查询已经足够快，并且对于这个项目而言是准确的，但是我将来可能需要类似的东西（所以我问了出来）。

- Rob

---

这帮助我解决了我的问题，但我发现我的一些字段有尾随空格，所以我使用了LENGTH(TRIM(content))而不是LENGTH(content)。 - Dave Radcliffe

Answer 1

MySQL的文本处理能力不足以满足你的要求。存储函数是一个选择，但可能会很慢。在MySQL中处理数据的最佳方法是添加用户定义函数。如果你打算构建一个新版本的MySQL，你也可以添加本地函数。

“正确”的方式是在DB之外处理数据，因为DB是用于存储而不是处理数据，任何重度处理都可能对DBMS造成太大负担。此外，在MySQL之外计算单词数使得更改单词定义更容易。将单词计数存储在DB中，并在文档更改时更新它如何？

存储函数示例：

DELIMITER $$
CREATE FUNCTION wordcount(str LONGTEXT)
       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);
    SET idx = 1;
    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
$$
DELIMITER ;

- outis

---

你的函数解决方案很棒，我很喜欢。它可以为我计算带有 ' 的单词（例如 haven't 计为 2）。我已经发布了对你的函数的更新。 - Pavel Jiri Strnad

Answer 2

这样速度会快得多，尽管准确度略微降低。我发现计算结果较少了4％，对于“估算”场景来说还可以接受。

SELECT
    ROUND (   
        (
            CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE (content, " ", "")) 
        ) 
        / CHAR_LENGTH(" ")        
    ) AS count    
FROM documents

- Jules

Answer 3

对于一些类似情况的简单解决方案（MySQL）：

SELECT *, (CHAR_LENGTH(student)-CHAR_LENGTH(REPLACE(student,' ','')))+1 as 'count'
FROM documents;

（该查询语句可用于计算文档中包含的单词数量）

- system64

Answer 4

您可以使用 https://github.com/spachev/mysql_udf_bundle 中的 word_count() UDF。我从被接受的答案中移植了逻辑，但我的代码只支持 latin1 字符集。该逻辑需要重新设计以支持其他字符集。此外，这两种实现方法始终将非字母数字字符视为分隔符，这可能并不总是理想的-例如，“teacher's book”被两种实现都视为三个单词。

当然，UDF 版本的速度要快得多。为了进行快速测试，我在 Project Guttenberg 的数据集上尝试了两种方法，该数据集由 9751 条记录组成，总计约 3 GB。UDF 在 18 秒内完成了所有操作，而存储函数则需要 63 秒才能处理仅 30 条记录（而 UDF 在 0.05 秒内完成）。因此，在这种情况下，UDF 大约快了 1000 倍。

如果不涉及修改 MySQL 源代码，则 UDF 将击败任何其他速度方法。这是因为它可以访问内存中的字符串字节，并直接对字节进行操作，而无需将它们移动。它还编译成机器代码，并直接在 CPU 上运行。

- Sasha Pachev

Answer 5

我尝试使用上面定义的函数，效果很好，但有一个情况除外。

在英语中，单引号经常作为单词的一部分。对我来说，上面的函数至少将“haven't”算作两个单词。

因此，这是我的小修正：

DELIMITER $$
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:]]' OR SUBSTRING(str, idx, 1) RLIKE "'";
             IF NOT prevChar AND currChar THEN
                 SET wordCnt=wordCnt+1;
             END IF;
             SET prevChar=currChar;
             SET idx=idx+1;
         END WHILE;
         RETURN wordCnt;
       END
     $$

- Pavel Jiri Strnad