MySQL查找子字符串匹配项和按匹配项全词分组 [英] MySQL Finding Substring Matches and Group by Match Full Word

问题描述

使用MySQL时，我找不到该表达式的正确术语组合.

Working with MySQL and I can't find the right combination of terms for this expression.

在我的语句中使用PHP用户输入变量，我需要找到部分子字符串匹配项并按完整匹配词分组

Using a PHP user input variable in my statement, I need to find a partial substring match and group by the full match word

以示例输入#bea" 为例，我想在全文中找到与#bea" 匹配的子字符串，然后进行分组并计算整个单词匹配的位置，例如#beat" ，#beauty" ，#beast"

Taking example input "#bea" I want to find matches for "#bea" as a substring in full text posts, then group by and count the full word where it matches such as "#beat", "#beauty", "#beast"

在数据库中带有示例帖子

With example posts in database

Testing Post #beat #beauty

Another test #beauty #beast

Testing #boom #beast

我要搜索一个子字符串，例如"#bea"

I want to search for a substring, example "#bea"

并获得输出

Match   | Count
#beast  | 2
#beauty | 2
#beat   | 1

这可以通过MySQL完成吗?还是我可以使用PHP算法更好地找到匹配项并计算完整单词?

Can this be done with MySQL or am I better finding matches and counting full words using a PHP algorithm?

推荐答案

您可以使用以下查询查找每个单词的计数.

You can use below query to find count of each word.

SELECT
sum(INSTR( '#beast' , TABLE.COL) > 0) '#beast',
sum(INSTR( '#beauty' , TABLE.COL) > 0) '#beauty'
FROM
TABLE

然后，如果需要，您可以透视表； MySQL数据透视表

Then you can pivot table if needed; MySQL pivot table

这篇关于MySQL查找子字符串匹配项和按匹配项全词分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！