搜索“全字匹配"在 MySQL 中
我想编写一个在文本字段中搜索关键字的 SQL 查询，但前提是它是全词匹配"(例如，当我搜索rid"时，它不应匹配arid"，但它应该匹配arid".
I would like to write an SQL query that searches for a keyword in a text field, but only if it is a "whole word match" (e.g. when I search for "rid", it should not match "arid", but it should match "a rid".
我正在使用 MySQL.
I am using MySQL.
幸运的是，在这个应用程序中，性能并不重要，而且数据库大小和字符串大小都很小，但我更愿意在 SQL 中而不是在驱动它的 PHP 中进行.
Fortunately, performance is not critical in this application, and the database size and string size are both comfortably small, but I would prefer to do it in the SQL than in the PHP driving it.
推荐答案
您可以使用 REGEXP 和 [[:<:]] 和 [[:>:]] 字-边界标记:
You can use REGEXP and the [[:<:]] and [[:>:]] word-boundary markers:
SELECT *
FROM table
WHERE keywords REGEXP '[[:<:]]rid[[:>:]]'
2020 年更新:(实际上是 2018 年以上)
MySQL 在 8.0.4 版本更新了它的 RegExp-Engine，所以你 现在需要使用标准"字边界标记\b:
MySQL updated its RegExp-Engine in version 8.0.4, so you will now need to use the "standard" word boundary marker \b:
SELECT *
FROM table
WHERE keywords REGEXP '\\brid\\b'
另请注意，您需要通过放置第二个反斜杠来转义反斜杠.
Also be aware that you need to escape the backslash by putting a second backslash.
相关文章