MySQL LIKE operator with wildcard and backslash

Question

This is frustrated by escaping the MySQL template used in the LIKE statement.

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 %\\\\% - the correct operand, but why doesn't it give a result?

EDIT: The database I'm testing in has character_set_database for utf8. To continue my research, I created the same setting in the database that has character_set_database set to latin1, and guess what works '%\\\\%'!

EDIT: The problem can be reproduced, and this is a field mapping problem. Details: http://bugs.mysql.com/bug.php?id=63829

Answer 1

MySQL 5.6.10 utf8mb4_unicode_520_ci 5 4, :

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

- , , . , , MySQL . , 5 , , , , MySQL - .

Answer 2

, MySQL: http://bugs.mysql.com/bug.php?id=46659

, mysql, --character-set-server ( latin1 latin1_swedish_ci) utf-8 . char , utf8 --character-set-server.

Answer 3

MySQL 5.0.12 dev Windows 10 ,

SELECT * FROM `foo` WHERE `name` LIKE '%http:\/\/%'

SELECT * FROM `foo` WHERE `name` LIKE '%http:\\\\\\\%'

, . , .