mysql中正则表达式匹配中的十六进制字符 [英] Hex characters in regexp matching in mysql

问题描述

我发现mysql的行为很奇怪. 下面的选择返回0:

I've spot very odd behavior of mysql. The select below returns 0:

SELECT CONVERT('a' USING BINARY) REGEXP '[\x61]'

但是下面的语义相同的select返回1:

However semantically identical select below returns 1:

SELECT CONVERT('a' USING BINARY) REGEXP '[\x61-\x61]'

您知道这里发生了什么吗? 我已经在mysql 5.0.0.3031和4.1.22中进行了测试

Do you know what is happening here? I've tested that in mysql 5.0.0.3031 and 4.1.22

我需要十六进制字符来创建一个正则表达式，该正则表达式在utf8中编码二进制字符串时会匹配.可以在 w3c网站.它看起来如下:

I need the hex characters to create a regexp that match when a binary string is encoded in utf8. A perl version of such regexp can be found on w3c site. It looks as follow:

$field =~
      m/\A(
         [\x09\x0A\x0D\x20-\x7E]            # ASCII
       | [\xC2-\xDF][\x80-\xBF]             # non-overlong 2-byte
       |  \xE0[\xA0-\xBF][\x80-\xBF]        # excluding overlongs
       | [\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}  # straight 3-byte
       |  \xED[\x80-\x9F][\x80-\xBF]        # excluding surrogates
       |  \xF0[\x90-\xBF][\x80-\xBF]{2}     # planes 1-3
       | [\xF1-\xF3][\x80-\xBF]{3}          # planes 4-15
       |  \xF4[\x80-\x8F][\x80-\xBF]{2}     # plane 16
      )*\z/x;

推荐答案

这也匹配:

SELECT CONVERT('a' USING BINARY) REGEXP '[1-\x]'

原因是\x被插入为x，而a介于1和x之间.正则表达式的其余部分只是普通字符，在这里不相关，因为它们已经在[1-x]范围内.

The reason is that \x is interpeted as x and a comes between 1 and x. The rest of your regex is just ordinary characters that aren't relevant here because they're already inside the [1-x] range.

SELECT CONVERT('0' USING BINARY) REGEXP '[\x61-\x61]' -- Fails, because 0 < 1.
SELECT CONVERT('1' USING BINARY) REGEXP '[\x61-\x61]' -- Succeeds: inside [1-x].
SELECT CONVERT('2' USING BINARY) REGEXP '[\x61-\x61]' -- Succeeds: inside [1-x].
...
SELECT CONVERT('w' USING BINARY) REGEXP '[\x61-\x61]' -- Succeeds: inside [1-x].
SELECT CONVERT('x' USING BINARY) REGEXP '[\x61-\x61]' -- Succeeds: inside [1-x].
SELECT CONVERT('y' USING BINARY) REGEXP '[\x61-\x61]' -- Fails, because y > x.

我不确定您要实现的目标，但是如果要使用十六进制字符，可以使用hex函数:

I'm not sure what you're trying to achieve, but if you want hex characters, you can use the hex function:

SELECT HEX('a')
61

这篇关于mysql中正则表达式匹配中的十六进制字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！