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)
According to MySQL docs: http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like
%\\\\%
is the right operand, but why it yields no result?
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
There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
MySQL WildcardsA wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
MySQL provides two wildcard characters for constructing patterns: percentage % and underscore _ . The percentage ( % ) wildcard matches any string of zero or more characters. The underscore ( _ ) wildcard matches any single character.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With