mysql - MySQL：根据“关键字”位置搜索数据库(Mysql: Search database based on 'keyword' position)

Question

I have the following table, tags

(我有下表， 标签)

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

I would like to do a search for the keyword test and order the results by relevance according to the position the keyword is placed in the string in field tag_name .

(我想搜索关键字test并根据关键字在tag_name字段中字符串中的位置进行相关性排序。)

So the order of results would be 5, 1, 6, 2, 4, 3, 7 .

(这样结果的顺序将是5, 1, 6, 2, 4, 3, 7 。)

I've tried the code below, and it almost works, expect for the fact that LIKE 'test%' will return results that have the keyword test in the middle of the string in the order of the unique ID not the position of the keyword in the string

(我已经尝试过下面的代码，并且几乎可以正常运行，因为事实上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

The above code will return the following results:

(上面的代码将返回以下结果：)

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

The order becomes 5, 1, 6, 2, 4, 3, 7 instead of 5, 1, 6, 2, 3, 4, 7

(顺序变为5, 1, 6, 2, 4, 3, 7而不是5, 1, 6, 2, 3, 4, 7)

How do I return results based of the position of the keyword for LIKE 'test%', or is there a better way of achieving this?

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

Thank you!

(谢谢！)

  ask by Busker McGreen Brian translate from so

Answer 1

You can use the following solution using LOCATE on ORDER BY too:

(您也可以在ORDER BY上使用LOCATE使用以下解决方案：)

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

You can debug the above query using this query.

(您可以使用此查询调试上面的查询。)

There you can see the ORDER BY values:

(在这里，您可以看到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

demo on dbfiddle.uk

(dbfiddle.uk上的演示)