mysql like c_mysql 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

'an';

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

| id | memo |

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

| 3 | a/n |

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

# 在 LIKE 子句中，为了查找 “/”，必须指定它为 “”

=========================================================

# 通过以上三个示例我的理解为 “”在 LIKE 子句中用于匹配一个“/”字符

# (反斜线被语法分析器剥离一次，另一次在模式匹配时完成，留下一条单独的反斜线被匹配)

# 'an' 被剥离了两个反斜线后为“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” 表示)？

# 两个字符(也应该用 “an” 表示)？？

# 就此如止，手册中对此描述还不算有什么太大的问题，

# (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

'an';

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 'an';

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 'an';

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%';