我必须编写一个mysql查询,用它我必须传递一条文本消息来检查消息中是否有单词存在于表中
Table
id words
1 arse
2 ball
3 sex
string="板球是一种由11名球员组成的两支球队在一个22码长的长方形球场上进行的击球游戏。许多国家有1.2亿球员参加板球比赛,使其成为世界第二大最受欢迎的运动。[1][2][3]每支球队轮流击球,试图得分,而另一支球队则在球场上。每一轮都被称为一局😁 😁 😁 😁 😁"
是否可以在"like"opt中传递整个字符串?表中大约有1000个单词需要屏蔽和检查。我想检查给定的字符串是否包含该表中的任何单词如果是,那么我想要与该表数据匹配的字数。
检查了许多帖子。但他们建议以下选项例如
select * from table where column like '%Cricket%' or column like '%is%'
...
这是缓慢且不可行的还有其他选择吗?
您可以使用JOIN ON LIKE
:
CREATE TABLE posts(id INT, post VARCHAR(1000));
INSERT INTO posts
SELECT 1, 'Cricket is a bat and ball game played between two teams of 11 players each on a field at the centre of which is a rectangular 22-yard-long pitch. The game is played by 120 million players in many countries, making it the world''s second most popular sport.[1][2][3] Each team takes its turn to bat, attempting to score runs, while the other team fields. Each turn is known as an innings ';
CREATE TABLE words(id INT, word VARCHAR(100));
INSERT INTO words
SELECT 1 , 'arse' UNION ALL SELECT 2,'ball' UNION ALL SELECT 3, 'sex';
查询:
SELECT w.word, COUNT(*) AS `occ`
FROM posts p
JOIN words w
ON p.post LIKE CONCAT('%', w.word, '%')
WHERE p.id = 1
GROUP BY w.word
ORDER BY occ DESC;
SqlFiddleDemo
输出:
╔═══════╦═════╗
║ word ║ occ ║
╠═══════╬═════╣
║ ball ║ 1 ║
╚═══════╩═════╝