死钻牛角尖之 模式匹配中 LIKE 子句转义符的使用
死钻牛角尖之 模式匹配中 LIKE 子句转义符的使用
因为 MySQL 在字符串中使用的是 C 的转义句法(例如 “\n”),
所以在 LIKE 字符串中使用的任何一个 “\” 必须被双写。
例如,为了查找 “\n”,必须以 “\\n” 形式指定它。为了查找 “\”,必须指定它为 “\\\\”
(反斜线被语法分析器剥离一次,另一次在模式匹配时完成,留下一条单独的反斜线被匹配)。
示例(译者注):
CREATE TABLE `ta` (
`id` int(3) unsigned NOT NULL,
`memo` char(6) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;
INSERT INTO `ta` VALUES("1", "a\\");
INSERT INTO `ta` VALUES("2", "a\\\\");
INSERT INTO `ta` VALUES("3", "a\\n");
INSERT INTO `ta` VALUES("4", "a\n");
INSERT INTO `ta` VALUES("5", "a\\%");
mysql> SELECT * FROM `ta`;
+----+------+
| id | memo |
+----+------+
| 1 | a\ |
| 2 | a\\ |
| 3 | a\n |
| 4 | a
|
| 5 | a\% |
+----+------+
mysql> SELECT * FROM `ta` WHERE `memo` = 'a\\';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\';
+----+------+
| id | memo |
+----+------+
| 1 | a\ |
+----+------+
# 在 LIKE 子句中,为了查找 “\”,必须指定它为 “\\\\”
mysql> SELECT * FROM `ta` WHERE `memo` = 'a\\\\';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\\';
+----+------+
| id | memo |
+----+------+
| 2 | a\\ |
+----+------+
# 在 LIKE 子句中,为了查找 “\”,必须指定它为 “\\\\”
mysql> SELECT * FROM `ta` WHERE `memo` = 'a\\n';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\n';
+----+------+
| id | memo |
+----+------+
| 3 | a\n |
+----+------+
# 在 LIKE 子句中,为了查找 “\”,必须指定它为 “\\\\”
=========================================================
# 通过以上三个示例我的理解为 “\\\\”在 LIKE 子句中用于匹配一个“\”字符
# (反斜线被语法分析器剥离一次,另一次在模式匹配时完成,留下一条单独的反斜线被匹配)
# 'a\\\\n' 被剥离了两个反斜线后为“a\\n”,
# 字串中的第一个反斜线是转义符,对第二个反斜线进行转义
=========================================================
mysql> SELECT * FROM `ta` WHERE `memo` = 'a\n';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\n';
+----+------+
| id | memo |
+----+------+
| 4 | a
|
+----+------+
# 'a\\\n' 经两次剥离后为“a\n”,字串中的反斜线是一个转义符。
# 字串 “a\n” 匹配字串 ASCII(0x610A)
mysql> SELECT * FROM `ta` WHERE `memo` = 'a\\%';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\%';
+----+------+
| id | memo |
+----+------+
| 5 | a\% |
+----+------+
# 'a\\\\\%' 经两次剥离后为“a\\\%”,字串中的第一个和第三个反斜线是转义符
# 分别转义一个 “\” 和 换行符,字串匹配 “a\%”。
===========================================================
# 我的疑惑:
# 手册中提到:
# 为了查找 “\n”,必须以 “\\n” 形式指定它。(to search for `\n', specify it as `\\n')
# 原文中的这个 “\n” 是什么?一个换行符(应该用 “\\\n” 表示)?
# 两个字符(也应该用 “a\\\\n” 表示)??
# 就此如止,手册中对此描述还不算有什么太大的问题,
# (to search for `\n', specify it as `\\n'),
# (must double any `\' that you use in your LIKE strings)
# 我们假设这两个描述只不过是一个笔误!
# 下面还有更令人惊讶的!
===========================================================
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\';
+----+------+
| id | memo |
+----+------+
| 1 | a\ |
+----+------+
# 串 “a\\\\” 经两次剥离一次转义后匹配 “a\”
# 而 “a\\” 呢,第一个反斜线就是转义?该字串没有经两次剥离??
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\\';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\';
+----+------+
| id | memo |
+----+------+
| 2 | a\\ |
+----+------+
# 同样的,串 “a\\\\\\” 的前四个反斜线经两次剥离一次转义后匹配 “a\”
# 而后两个反斜线呢?怎么匹配到一个 “\” 了?
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\n';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\n';
+----+------+
| id | memo |
+----+------+
| 3 | a\n |
+----+------+
# 同样的,串 “a\\\\\\n” 的前四个反斜线经两次剥离一次转义后匹配 “a\”
# 而后两个反斜线呢?被剥离了??
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\n';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\n';
+----+------+
| id | memo |
+----+------+
| 4 | a
|
+----+------+
# 字串 “a\n” 没有经任何剥离?直接转义为一个换行符??
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\%';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\%';
+----+------+
| id | memo |
+----+------+
| 1 | a\ |
| 2 | a\\ |
| 3 | a\n |
| 5 | a\% |
+----+------+
# 既然 'a\\\n' 可以匹配 ASCII(0x610A)
# 为什么 'a\\\%' 就不是匹配 “a%” 而是匹配 “a\”+任意字符 呢
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\%';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\%';
+----+------+
| id | memo |
+----+------+
| 5 | a\% |
+----+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\%';
mysql> SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\\%';
+----+------+
| id | memo |
+----+------+
| 2 | a\\ |
+----+------+
1 row in set (0.00 sec)
* 以上结果在 MySQL 3.23.56 、MySQL 4.0.12 、MySQL 4.1 中测试通过
示例结束(译者注):
use test;
tee d:/test.txt;
CREATE TABLE `ta` (
`id` int(3) unsigned NOT NULL,
`memo` char(6) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;
INSERT INTO `ta` VALUES("1", "a\\");
INSERT INTO `ta` VALUES("2", "a\\\\");
INSERT INTO `ta` VALUES("3", "a\\n");
INSERT INTO `ta` VALUES("4", "a\n");
INSERT INTO `ta` VALUES("5", "a\\%");
SELECT * FROM `ta`;
SELECT * FROM `ta` WHERE `memo` = 'a\\';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\';
SELECT * FROM `ta` WHERE `memo` = 'a\\\\';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\\';
SELECT * FROM `ta` WHERE `memo` = 'a\\n';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\n';
SELECT * FROM `ta` WHERE `memo` = 'a\n';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\n';
SELECT * FROM `ta` WHERE `memo` = 'a\\%';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\%';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\\';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\n';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\n';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\n';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\n';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\%';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\%';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\%';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\%';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\%';
SELECT * FROM `ta` WHERE `memo` LIKE 'a\\\\\\\\%';