You could (if you have MySQL8.0 or newer) do:
SET @str = 'word1 word2 word3';
DROP TABLE IF EXISTS content;
CREATE TABLE content(i integer, str VARCHAR(200));
INSERT INTO content VALUES
(1,'word1 word2 word1 word1 word2'),
(2,'word9 word2 word1 word8 word2'),
(3,'word1 word2 word1 word1 word3');
WITH RECURSIVE abc AS (
SELECT
LEFT(@str,instr(@str,' ')-1) as a,
MID(CONCAT(@str,' '),instr(@str,' ')+1) as b
UNION ALL
SELECT LEFT(b,instr(b,' ')-1), MID(b,instr(b,' ')+1)
FROM abc
WHERE rtrim(b)<>'')
SELECT
x.i,
content.str,
sum(c)
FROM (
SELECT
a ,
content.str,
content.i,
(length(content.str)-length(replace(content.str,a,'')))/length(a) as c
FROM abc
CROSS JOIN content) x
INNER JOIN content on content.i=x.i
GROUP BY x.i;
output:
i |
str |
sum(c) |
1 |
word1 word2 word1 word1 word2 |
5.0000 |
2 |
word9 word2 word1 word8 word2 |
3.0000 |
3 |
word1 word2 word1 word1 word3 |
5.0000 |
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…