为什么我需要双重转义(使用 4 )才能在纯 SQL 中找到反斜杠 ()? [英] Why I need to double-escape (use 4 ) to find a backslash ( ) in pure 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.

这篇关于为什么我需要双重转义(使用 4 )才能在纯 SQL 中找到反斜杠 ()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！