为什么我需要双重转义(使用4 \)在纯SQL中找到反斜杠(\)? [英] Why I need to double-escape (use 4 \) to find a backslash ( \ ) in pure SQL?
问题描述
a\\\b
哪些工作没有问题: mysql>选择a\\\b;
+ ----- +
| a\b |
+ ----- +
| a\b |
+ ----- +
1行集(0.05秒)
但是,如果我想使用LIKE在表中搜索包含\的字符串,我需要双重转义我的\。为什么?
这是一个例子。
我们准备一张小桌子。
create table test(test varchar(255));
插入到测试值(a\\\b),(a \\b\\c),(abcd);
mysql>从测试中选择*
+ ------- +
|测试|
+ ------- +
| a\b |
| a\b\c |
| abcd |
+ ------- +
3行集(0.05秒)
我们尝试以a\b开头的条目...
mysql> select * from test where test LIKEa\\b%;
+ ------ +
|测试|
+ ------ +
| abcd |
+ ------ +
1行集(0.05秒)
为什么 \\
在那里被忽略?为什么我需要双重转义basckslash来获得我的预期结果?
mysql> select * from test where test LIKEa\\\\\b%;
+ ------- +
|测试|
+ ------- +
| a\b |
| a\b\c |
+ ------- +
2行集(0.04秒)
您首先逃避字符串语法,然后为 LIKE
语法。
在 LIKE
字符%
和 _
有特殊的含义,所以如果你想搜索文字%
,你需要使用 \%
,如果您想要搜索文字 \%
,您需要转义反斜杠,如 \\%
。
在字符串语法中显然有特殊的含义,所以如果你想在你需要转义的字符串中加入引号它作为
\
,并在字符串中包含文字 \
,您必须转义为反斜杠,如 \\
。
所以在这两种语法中,你必须逃避 \
。
如果您不想使用 \
来转义LIKE模式,哟你可以使用ESCAPE关键字。例如:
...其中test LIKEa\\\b%ESCAPE'|';
这样,您需要写入 |%
, | _
或 ||
以转义这些特殊字符。
I do not understand this MySQL behaviour : if I want to display a\b, I can just select "a\\b"
which work without problem :
mysql> select "a\\b";
+-----+
| a\b |
+-----+
| a\b |
+-----+
1 row in set (0.05 sec)
But if I wnat to search a string containing a \ in a table using LIKE, I need to double-escape my "\". Why ?
Here is an example.
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\b |
| a\b\c |
| abcd |
+-------+
3 rows in set (0.05 sec)
We try to get entries beginning by "a\b" ...
mysql> select * from test where test LIKE "a\\b%";
+------+
| test |
+------+
| abcd |
+------+
1 row in set (0.05 sec)
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\b |
| a\b\c |
+-------+
2 rows in set (0.04 sec)
You escape first for the string syntax, then for LIKE
syntax.
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 \
.
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.
这篇关于为什么我需要双重转义(使用4 \)在纯SQL中找到反斜杠(\)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!