mysql - SQL mediumtext 计数列表中的子字符串

Question

我正在尝试为 MyBB 论坛开发一种“坏词”插件，我想向您寻求有关 SQL 命令的帮助。

让我们将解决方案简化为仅针对相关列:

Table: words
Column1: number, recordID
Column2: varchar, badWord
-this table contains just a list of not permitted words

Table: posts
Column1: number, postID
Column2: number, userID
Column3:mediumtext, postBody
-here is id of post, who sent it and the text of post.

Table: users
Column1: number, userID
Column2: number, badWordCount
-basic usere table was expanded by my column containing number of bad words

现在。是否存在针对此请求的纯 SQL 解决方案？

对于“posts”中的每一行，选择 mediumtext 列“postBody”。一旦进入，循环遍历“单词”表。每次拦截任何坏词时，根据用户 ID 将 +1 添加到 users.badWordCount。

在一个帖子中，可以有许多不同(或相同)的坏词。我需要找到所有这些词并对其进行计数，然后将不良词的数量添加到创建该帖子的每个用户

演示:

words (id, badWord):
1, fck
2, btch

Posts (postID,userID,postBody):
1,1,Hello, fck you btch
2,2,no, fck you you fck
3,1,Aaah, fck you

执行后结果应该是

Users (userID,badwordCount)
1,3
2,2

Answer 1

有一种纯 SQL 方法，尽管这可能不是最好的方法。

select userId,
       sum(length(replace(concat(' ', w.postBody, ' '), concat(' ', w.badword, ' '), concat(w.word, '123'))) -
           length(concat(' ', w.postBody, ' '))
          )
from posts p join
     words w
     on concat(' ', w.postBody, ' ') like concat('% ', w.badword, '% ')
group by userId;

空格用于分隔文本中的单词。对于您的目的而言，这可能不是必需的。没有空格的逻辑看起来像:

select userId,
       sum(length(replace(w.postBody, w.word, concat(w.badword, ' '))) -
           length(w.postBody)
          )
from posts p join
     words w
     on w.postBody like concat('% ', w.badword, '% ')
group by userId;

想法是使用like 在帖子中查找单词。然后通过将单词替换为长度更长的单词来计算单词的数量。然后，测量更换后的长度变化。