课程网站关键词搜索工具开发及SQL查询实现技术问询

看起来你已经搭好了基础的数据库结构，不过你的SQL语句还没完成，而且要实现按关键词出现次数排序的需求，得调整一下写法。下面是针对你需求的完整解决方案：

基础实现：准确统计关键词出现次数并排序 #

如果只是需要精准统计关键词在课程文本中的出现次数，然后按次数降序排列，可以用这个SQL语句：

SELECT 
    `coursnumero`,
    `liencours`,
    `textcours`,
    -- 计算关键词出现的次数：原长度减去替换空后的长度，除以关键词本身长度
    (LENGTH(`textcours`) - LENGTH(REPLACE(`textcours`, 'super', ''))) / LENGTH('super') AS keyword_count
FROM `cours`
WHERE `textcours` LIKE '%super%' -- 过滤包含关键词的课程
ORDER BY keyword_count DESC; -- 按出现次数从多到少排序

代码解释： #

• (LENGTH(textcours) - LENGTH(REPLACE(textcours, 'super', '')))：算出替换掉所有关键词后文本缩短的长度
• 除以关键词'super'的长度，得到准确的出现次数（比如关键词是多字符时不会算错）
• 不需要用GROUP BY，因为我们是对每一条课程记录单独统计，直接排序即可

性能优化：用全文索引提升大数据量下的查询效率 #

如果你的课程数据量比较大，LIKE '%super%'这种模糊查询会非常慢（因为它无法利用索引）。这时候推荐使用MySQL的全文索引来优化：

1. 先给textcours字段创建全文索引：

ALTER TABLE `cours` ADD FULLTEXT INDEX idx_fulltext_textcours (`textcours`);

2. 使用全文索引查询并按相关度排序：

SELECT 
    `coursnumero`,
    `liencours`,
    `textcours`,
    MATCH(`textcours`) AGAINST('super') AS relevance_score
FROM `cours`
WHERE MATCH(`textcours`) AGAINST('super')
ORDER BY relevance_score DESC;

优势： #

• relevance_score是MySQL自动计算的相关度评分，和关键词出现次数、位置等因素正相关，排序结果更合理
• 全文索引的查询效率远高于LIKE模糊查询，适合大量数据的场景
• 还支持多关键词搜索（比如AGAINST('super python' IN BOOLEAN MODE)）

重要注意事项 #

• 防止SQL注入：如果关键词是用户输入的动态内容，一定要用参数化查询，绝对不要直接把用户输入拼接到SQL语句里
• 如果你需要支持不区分大小写的搜索，可以把textcours字段设置为不区分大小写的字符集（比如utf8mb4_general_ci）

内容的提问来源于stack exchange，提问作者Paul Tanné