为什么我需要双重转义(使用 4 )才能在纯 SQL 中找到反斜杠 ()?
我不理解这种 MySQL 行为:如果我想显示 a，我可以选择 "a\b" ，它可以正常工作:
I do not understand this MySQL behaviour : if I want to display a, I can just select "a\b" which work without problem :
mysql> select "a\b";
+-----+
| a |
+-----+
| a |
+-----+
1 row in set (0.05 sec)
但是如果我想使用 LIKE 在表中搜索包含 的字符串，我需要双重转义我的".为什么?
But if I wnat to search a string containing a in a table using LIKE, I need to double-escape my "". Why ?
这是一个例子.
我们准备了一张小桌子.
We prepare a small table.
create table test ( test varchar(255) );
insert into test values ( "a\b" ) , ( "a\b\c" ) , ( "abcd" );
mysql> select * from test;
+-------+
| test |
+-------+
| a |
| ac |
| abcd |
+-------+
3 rows in set (0.05 sec)
我们尝试获取以a"开头的条目......
We try to get entries beginning by "a" ...
mysql> select * from test where test LIKE "a\b%";
+------+
| test |
+------+
| abcd |
+------+
1 row in set (0.05 sec)
为什么 \ 在那里被忽略?为什么我需要双重转义 basckslash 才能得到预期的结果?
Why \ is just ignored there? Why I need to double-escape basckslash to get my expected result?
mysql> select * from test where test LIKE "a\\b%";
+-------+
| test |
+-------+
| a |
| ac |
+-------+
2 rows in set (0.04 sec)
推荐答案
你先转义字符串语法，然后转义 LIKE 语法.
You escape first for the string syntax, then for LIKE syntax.
LIKE 中的字符% 和_ 有特殊含义，所以如果要搜索字面量%，你需要使用 \%，如果你想搜索文字 \% 你需要像 \% 一样转义反斜杠.
In LIKE characters % and _ have special meaning, so if you want to search for literal %, you need to use \%, and if you want to search for literal \% you need to escape the backslash as in \%.
在字符串语法中 " 显然有特殊含义，所以如果你想在字符串中包含引号，你需要将它转义为 "，并包含文字 " 在字符串中，您必须像 \" 一样对反斜杠进行转义.
In string syntax " obviously has special meaning, so if you want to include quote in the string you need to escape it as ", and to include literal " in the string you have to escape the backslash as in \".
所以在这两种语法中你都必须转义 .
So in both syntaxes you have to escape .
如果不想使用 转义 LIKE 模式，可以使用 ESCAPE 关键字.例如:
If you don't want to use to escape the LIKE pattern , you can use ESCAPE keyword. For example:
... where test LIKE "a\b%" ESCAPE '|';
这样，您需要编写 |%、|_ 或 || 来转义这些特殊字符.
This way, you'll need to write |%, |_ or || to escape these special chars.
相关文章