INSERT INTO test VALUES

(1, 'php is a good language.'),

(2, 'Java is a good language'),

(3, 'All language are good'),

(4, 'Php,Java both good language'),

(5, 'Php is good but Java is not'),

(6, 'Everything is bad');

SELECT * FROM test;

SELECT @criteria;

FROM test

LEFT JOIN JSON_TABLE(@criteria,

"$[*]" COLUMNS (word VARCHAR(255) PATH "$")) jsontable ON LOCATE (jsontable.word, test.tittle)

GROUP BY test.id, test.tittle

ORDER BY SUM(jsontable.word IS NOT NULL) DESC

SELECT @criteria;

cte AS (SELECT test.id,

test.tittle,

0 amount,

TRIM(TRIM(LEADING SUBSTRING_INDEX(@criteria, ' ', 1) FROM @criteria)) criteria,

SUBSTRING_INDEX(@criteria, ' ', 1) token

FROM test

UNION ALL

SELECT id,

tittle,

amount + (LOCATE(token, tittle) > 0),

TRIM(TRIM(LEADING token FROM criteria)),

SUBSTRING_INDEX(criteria, ' ', 1)

FROM cte

WHERE TRIM(criteria) != '' )

SELECT id, tittle

FROM cte

GROUP BY id, tittle

ORDER BY MAX(amount) DESC;