包含 '@' 的单词边界的 MySQL 正则表达式

Question

我正在尝试搜索示例短语:'@test123'使用像这样的正则表达式:

SELECT (...) WHERE x RLIKE '[[:<:]]@test123[[:>:]]'

运气不好。可能是词边界选择器 '[[:<:]]'不将“@”算作一个词。

如何实现？如何在 MySQL regex word boundary selector 中设置但有异常(exception)？

Answer 1

MySQL 5.7 Reference Manual / ... / Regular Expressions :

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

所以，@是一个单词边界，而不是一个单词字符。我们还需要扩展“单词字符”类以包含 @。最简单的方法就是直接枚举自定义单词字符a-z0-9_@:

SELECT * FROM
(
   SELECT '@test123'         AS x UNION ALL
   SELECT 'and @test123 too'      UNION ALL
   SELECT 'not@test123not'        UNION ALL
   SELECT 'not@test123'           UNION ALL
   SELECT '@test123not'           UNION ALL
   SELECT 'not @ test123'         UNION ALL
   SELECT 'test123'               UNION ALL 
   SELECT '@west123'
) t
WHERE x RLIKE '([^a-z0-9_@]|^)@test123([^a-z0-9_@]|$)';

结果:

x
----------------
@test123
and @test123 too