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

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

在SQL语法中有一个错误;检查与MySQL服务器版本对应的手册，找到在第1行“hi”附近使用的正确语法

How do I build this query?

如何构建这个查询?

8 个解决方案

#1

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.

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

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

参见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:

尽管正如Bill Karwin在下文中指出的，对字符串分隔符使用双引号不是标准的SQL，因此使用单引号是很好的实践。这个简化了的事情:

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

#2

You should use single-quotes for string delimiters. The single-quote is the standard SQL string delimiter, and double-quotes are identifier delimiters (so you can use special words or characters in the names of tables or columns).

您应该对字符串分隔符使用单引号。单引号是标准的SQL字符串分隔符，双引号是标识符分隔符(因此可以在表或列的名称中使用特殊的单词或字符)。

In MySQL, double-quotes work (nonstandardly) as a string delimiter by default (unless you set ANSI SQL mode). If you ever use another brand of SQL database, you'll benefit from getting into the habit of using quotes standardly.

在MySQL中，双引号作为字符串分隔符(除非设置ANSI SQL模式)工作(非标准)。如果您曾经使用过另一个品牌的SQL数据库，您将从养成使用引号的习惯中获益。

Another handy benefit of using single-quotes is that the literal double-quote characters within your string don't need to be escaped:

使用单引号的另一个好处是，字符串中的双引号字符不需要转义:

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

#3

I've developed my own MySQL escape method in Java (if useful for anyone).

我用Java开发了自己的MySQL转义方法(如果对任何人都有用的话)。

See class code below.

请参阅下面的类代码。

Warning: wrong if NO_BACKSLASH_ESCAPES SQL mode is enabled.

警告:如果启用了no_backslash_exit SQL模式，则错误。

private static final HashMap<String,String> sqlTokens;
private static Pattern sqlTokenPattern;

static
{           
    //MySQL escape sequences: http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html
    String[][] search_regex_replacement = new String[][]
    {
                //search string     search regex        sql replacement regex
            {   "\u0000"    ,       "\\x00"     ,       "\\\\0"     },
            {   "'"         ,       "'"         ,       "\\\\'"     },
            {   "\""        ,       "\""        ,       "\\\\\""    },
            {   "\b"        ,       "\\x08"     ,       "\\\\b"     },
            {   "\n"        ,       "\\n"       ,       "\\\\n"     },
            {   "\r"        ,       "\\r"       ,       "\\\\r"     },
            {   "\t"        ,       "\\t"       ,       "\\\\t"     },
            {   "\u001A"    ,       "\\x1A"     ,       "\\\\Z"     },
            {   "\\"        ,       "\\\\"      ,       "\\\\\\\\"  }
    };

    sqlTokens = new HashMap<String,String>();
    String patternStr = "";
    for (String[] srr : search_regex_replacement)
    {
        sqlTokens.put(srr[0], srr[2]);
        patternStr += (patternStr.isEmpty() ? "" : "|") + srr[1];            
    }
    sqlTokenPattern = Pattern.compile('(' + patternStr + ')');
}


public static String escape(String s)
{
    Matcher matcher = sqlTokenPattern.matcher(s);
    StringBuffer sb = new StringBuffer();
    while(matcher.find())
    {
        matcher.appendReplacement(sb, sqlTokens.get(matcher.group(1)));
    }
    matcher.appendTail(sb);
    return sb.toString();
}

#4

MySQL has the string function QUOTE, and it should solve this problem:

MySQL有字符串函数引用，它应该解决这个问题:

#5

You can use mysql_real_escape_string. mysql_real_escape_string() does not escape % and _, so you should escape MySQL wildcards (% and _) separately.

您可以使用mysql_real_escape_string。mysql_real_escape_string()没有转义%和_，所以应该分别转义MySQL通配符(%和_)。

#6

For strings like that, for me the most comfortable way to do it is doubling the ' or ", as explained in the MySQL manual:

对于这样的字符串，对我来说最舒服的方法是将' or '加倍，就像MySQL手册中解释的那样:

There are several ways to include quote characters within a string:

有几种方法可以在字符串中包含引用字符:
A “'” inside a string quoted with “'” may be written as “''”.

A “"” inside a string quoted with “"” may be written as “""”.

Precede the quote character by an escape character (“\”).

A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a
Strings quoted with “'” need no special treatment.

引用的字符串不需要特殊处理。

It is from http://dev.mysql.com/doc/refman/5.0/en/string-literals.html.

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

#7

If you're using a variable when searching in a string, mysql_real_escape_string() is good for you. Just my suggestion:

如果在字符串中搜索时使用变量，mysql_real_escape_string()对您有好处。我的建议:

$char = "and way's 'hihi'";
$myvar = mysql_real_escape_string($char);

select * from tablename where fields like "%string $myvar  %";

#8

For testing how to insert the double quotes in MYSQL using Terminal you can use following way.

要测试如何使用终端在MYSQL中插入双引号，可以使用以下方法。

TableName(Name,DString) - > Schema
insert into TableName values("Name","My QQDoubleQuotedStringQQ")

TableName(Name,DString)——>模式插入到TableName值(“Name”，“My QQDoubleQuotedStringQQ”)

after inserting the value you can update the value in the db with double quotes or single quotes

插入值之后，您可以使用双引号或单引号来更新db中的值

update table TableName replace(Dstring,"QQ","\"")

更新表的表替换(下游,“QQ”,“\”)

#1