mysql数据库按关键字搜索_mysql - MySQL：根据“关键字”位置搜索数据库 - 堆栈内存溢出...

我有下表， 标签

+---------+------------------------------+

| 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 。

我已经尝试过下面的代码，并且几乎可以正常运行，因为事实上LIKE'test％'将以唯一ID的顺序而不是关键字的位置返回在字符串中间具有关键字test结果。在字符串中

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

如何根据LIKE'test％'的关键字位置返回结果，还是有更好的方法来实现？

谢谢！