如何在 MySQL 中转义特殊字符?

例如:

select * from tablename where fields like "%string "hi" %";

错误:

<块引用>

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 'hi" "' 附近使用的正确语法

如何构建此查询?

解决方案

此答案中提供的信息可能会导致不安全的编程实践.

此处提供的信息高度依赖于 MySQL 配置，包括(但不限于)程序版本、数据库客户端和使用的字符编码.

见http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

<前>MySQL 识别以下转义序列.\0 一个 ASCII NUL (0x00) 字符.\' 单引号('")字符.\" 双引号 (""") 字符.\b 退格字符.\n 换行(换行)字符.\r 回车符.\t 一个制表符.\Z ASCII 26 (Control-Z).请参阅表格后面的注释.\\ 反斜杠(\")字符.\％ 一个角色.请参阅表格后面的注释.\_ 一个角色.请参阅表格后面的注释.

所以你需要

select * from tablename where fields like "%string \"hi\";%";

尽管 Bill Karwin 在下面说明，对字符串分隔符使用双引号不是标准 SQL，因此使用单引号是一种很好的做法.这简化了事情:

select * from tablename where '%string hi"之类的字段%';

For example:

select * from tablename where fields like "%string "hi"  %";

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hi" "' at line 1

How do I build this query?

解决方案

The information provided in this answer can lead to insecure programming practices.

The information provided here depends highly on MySQL configuration, including (but not limited to) the program version, the database client and character-encoding used.

See http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

MySQL recognizes the following escape sequences.
\0     An ASCII NUL (0x00) character.
\'     A single quote ("'") character.
\"     A double quote (""") character.
\b     A backspace character.
\n     A newline (linefeed) character.
\r     A carriage return character.
\t     A tab character.
\Z     ASCII 26 (Control-Z). See note following the table.
\\     A backslash ("\") character.
\%     A "%" character. See note following the table.
\_     A "_" character. See note following the table.

So you need

select * from tablename where fields like "%string \"hi\" %";

Although as Bill Karwin notes below, using double quotes for string delimiters isn't standard SQL, so it's good practice to use single quotes. This simplifies things:

select * from tablename where fields like '%string "hi" %';