Mysql:基于关键字位置的数据库搜索
Mysql:基于关键字位置的数据库搜索
提问于 2019-12-03 09:05:37
我有下表，标记
+---------+------------------------------+
| tag_id | tag_name |
+---------+------------------------------+
| 1 | test, subject |
+----------------------------------------+
| 2 | subject, test, this |
+----------------------------------------+
| 3 | how, is, subject, test, this |
+----------------------------------------+
| 4 | this, is, test, subject |
+---------+------------------------------+
| 5 | test |
+---------+------------------------------+
| 6 | testing, microphone |
+---------+------------------------------+
| 7 | microphone, this, is a, test |
+---------+------------------------------+我想搜索关键字test，并根据关键字放在tag_name.字段中的位置按相关性排序结果。
结果的顺序是5, 1, 6, 2, 4, 3, 7。
我已经尝试了下面的代码，并且它几乎是有效的，期望类似于'test%‘将返回在字符串中间的关键字test按照惟一ID的顺序而不是字符串中关键字的位置的结果。
SELECT *
FROM tags
WHERE `tag_name` LIKE '%test%'
ORDER BY
CASE
WHEN `tag_name` LIKE 'test' THEN 1
WHEN `tag_name` LIKE 'test%' THEN 2
WHEN `tag_name` LIKE '%test' THEN 4
ELSE 3
END以上代码将返回以下结果：
+---------+------------------------------+
| tag_id | tag_name |
+---------+------------------------------+
| 5 | test |
+---------+------------------------------+
| 1 | test, subject |
+----------------------------------------+
| 6 | testing, microphone |
+---------+------------------------------+
| 2 | subject, test, this |
+----------------------------------------+
| 3 | how, is, subject, test, this |
+----------------------------------------+
| 4 | this, is, test, subject |
+---------+------------------------------+
| 7 | microphone, this, is a, test |
+---------+------------------------------+订单变成5, 1, 6, 2, 4, 3, 7而不是5, 1, 6, 2, 3, 4, 7
如何根据关键字的位置返回类似“test%”的结果，或者有更好的方法来实现这一点？
谢谢!
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-12-03 10:08:13
您也可以使用LOCATE在ORDER BY上使用以下解决方案：
SELECT *
FROM tags
WHERE `tag_name` LIKE '%test%'
ORDER BY CAST(`tag_name` LIKE 'test' AS UNSIGNED) DESC,
CAST(`tag_name` LIKE '%test%' AS UNSIGNED) ASC,
LOCATE('test', `tag_name`) ASC可以使用此查询调试上述查询。在这里您可以看到ORDER BY值：
SELECT *,
CAST(`tag_name` LIKE 'test' AS UNSIGNED) AS KeywordOnly,
CAST(`tag_name` LIKE '%test%' AS UNSIGNED) AS KeywordExists,
LOCATE('test', `tag_name`) AS KeywordPosition
FROM tags
WHERE `tag_name` LIKE '%test%'
ORDER BY CAST(`tag_name` LIKE 'test' AS UNSIGNED) DESC,
CAST(`tag_name` LIKE '%test%' AS UNSIGNED) ASC,
LOCATE('test', `tag_name`) ASC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59153503
复制相关文章
相似问题
腾讯云开发者
