mysql使用LIKE查找二进制字段有问题

发布于03月19日

当使用LIKE查询时，我对Binary(16)类型(引擎盖下的ULID)和特定的ULID 01HRS49374C1EAGTPKTJ0JVT1P(十六进制018e32448ce4605ca86ad3d4812de836)有问题.LIKE查询适用于其他ULID，但不适用于这个特定的ULID.

因为它是一个ULID，长度总是16字节，所以它不是一个问题的字段的大小.

Last 100 statement from the code bellow returns empty result! Why?

CREATE TABLE IF NOT EXISTS new_table (
    id BINARY(16) PRIMARY KEY NOT NULL,
    name VARCHAR(255)
);

INSERT INTO new_table SET id = UNHEX("018e321f579997e1f2a907a72b98f965"), name = "this works fine";
INSERT INTO new_table SET id = UNHEX("018e32448ce4605ca86ad3d4812de836"), name = "try find this using LIKE";

SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e321f579997e1f2a907a72b98f965");
SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e32448ce4605ca86ad3d4812de836");

推荐答案

如果我们改变了Escape字符，它就可以工作:

mysql> SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e32448ce4605ca86ad3d4812de836") ESCAPE '@';
+----------------------------------+--------------------------+
| HEX(id)                          | name                     |
+----------------------------------+--------------------------+
| 018E32448CE4605CA86AD3D4812DE836 | try find this using LIKE |
+----------------------------------+--------------------------+

如果我们禁用反斜杠作为元字符，它也可以工作:

mysql> set sql_mode='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e32448ce4605ca86ad3d4812de836");
+----------------------------------+--------------------------+
| HEX(id)                          | name                     |
+----------------------------------+--------------------------+
| 018E32448CE4605CA86AD3D4812DE836 | try find this using LIKE |
+----------------------------------+--------------------------+

因此，我try 查看客户端中的二进制字符串:

% mysql --skip-binary-as-hex test

mysql> select UNHEX("018e32448ce4605ca86ad3d4812de836");
+-------------------------------------------+
| UNHEX("018e32448ce4605ca86ad3d4812de836") |
+-------------------------------------------+
| ?2D??`\?j?ԁ-?6                                 |
+-------------------------------------------+
        ^

这是反斜杠.

关键是LIKE对字符串中的某些字符应用了一些特殊的含义，如反斜杠和_和%.

如果你取消一个随机的十六进制字符串，你不知道字节是否会碰巧成为那些特殊的字符之一，LIKE.

结论:这不是bug.

LIKE不是适合您搜索的比较运算符.

在您的示例中，使用=会很好，因为您判断的是字符串是否相等，而不是使用LIKE的模式匹配.

这是有效的:

mysql> SELECT HEX(id), name FROM new_table WHERE id = UNHEX("018e32448ce4605ca86ad3d4812de836");
+----------------------------------+--------------------------+
| HEX(id)                          | name                     |
+----------------------------------+--------------------------+
| 018E32448CE4605CA86AD3D4812DE836 | try find this using LIKE |
+----------------------------------+--------------------------+