MySQL 5.6 : escaping consecutive percent signs in like query

Question

考虑 2 个查询:

SELECT * FROM T1 WHERE NAME LIKE '%\%%';

SELECT * FROM T1 WHERE NAME LIKE '%\%\%%';

假设T1有记录在哪里 NAME是% , %% , 或 %%% .

我希望第二个查询返回较少的结果，但它包括 T1.NAME = '%' 所在的记录!有没有办法使用类似查询过滤掉该记录？类似于 SELECT * FROM T1 WHERE NAME LIKE '%\%\%%' AND NAME <> '%';不是我要找的。

mysql> explain table1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| text  | varchar(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> explain select * from table1
    -> where text like '%\%%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | table1 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from table1
    -> where text like '%\%\%%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | table1 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

如果有帮助，这是我的配置:

[mysqld]
innodb_buffer_pool_size=402653184
innodb_log_file_size=262144000
innodb_log_buffer_size=8388608
max_allowed_packet=5241856
innodb_additional_mem_pool_size=20971520

我需要进一步调查，但问题似乎与数据库的创建方式有关:

create database testdb character set utf8 collate utf8_unicode_ci;

当我以不太具体的方式创建数据库时，我能够得到正确的结果:

create database testdb;

知道为什么吗？

Answer 1

据我所知，这似乎是 mysql 中的一个错误...有时无法使用 LIKE 搜索连续的百分号。 (我遇到了你描述的同样问题)

仍然不确定触发错误需要什么条件。

但是，一种解决方法是使用 REGEXP:

SELECT * FROM T1 WHERE NAME REGEXP "%%";