like mysql 相反,具有通配符和反斜杠的MySQL LIKE运算符

It's frustrated with MySQL's pattern escaping used in LIKE operator.

root@dev> create table foo(name varchar(255));

Query OK, 0 rows affected (0.02 sec)

root@dev> insert into foo values('with\\slash');

Query OK, 1 row affected (0.00 sec)

root@dev> insert into foo values('\\slash');

Query OK, 1 row affected (0.00 sec)

root@dev> select * from foo where name like '%\\\\%';

Empty set (0.01 sec)

root@dev> select * from foo;

+------------+

| name |

+------------+

| with\slash |

| \slash |

+------------+

2 rows in set (0.00 sec)

root@dev> select * from foo where name like '%\\\\%';

Empty set (0.00 sec)

root@dev> select * from foo where name like binary '%\\\\%';

+------------+

| name |

+------------+

| with\slash |

| \slash |

+------------+

2 rows in set (0.00 sec)

EDIT:

The database I'm testing that in has character_set_database set to utf8. To further my investigation, I created the same setup in a database that has character_set_database set to latin1, and guess what, '%\\\\%' works!

EDIT:

The problem can be reproduced and it's the field collation problem. Details: http://bugs.mysql.com/bug.php?id=63829

解决方案

In MySQL 5.6.10, with the text field collation utf8mb4_unicode_520_ci this can be achieved by using 5 backslash characters instead of 4, i.e:

select * from foo where name like binary '%\\\\\%';

Somehow, against all expectations, this properly finds all rows with backslashes.

At least this should work until the MySQL field collation bug above is fixed. Considering it's been more than 5 years since the bug is discovered, any app designed with this may outlive its usefulness before MySQL is even fixed - so should be a pretty reliable workaround.