6.1.1.1 Strings

A string is a sequence of characters, surrounded by either single quote (`'') or double quote (`"') characters (only the single quote if you run in ANSI mode). Examples:

'a string'
"another string"

Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (`\'), known as the escape character. MySQL recognises the following escape sequences:

\0

An ASCII 0 (NUL) character.

\'

A single quote (`'') character.

\"

A double quote (`"') character.

\b

A backspace character.

\n

A newline character.

\r

A carriage return character.

\t

A tab character.

\z

ASCII(26) (Control-Z). This character can be encoded to allow you to work around the problem that ASCII(26) stands for END-OF-FILE on Windows. (ASCII(26) will cause problems if you try to use mysql database < filename.)

\\

A backslash (`\') character.

\%

A `%' character. This is used to search for literal instances of `%' in contexts where `%' would otherwise be interpreted as a wildcard character. See section 6.3.2.1 String Comparison Functions.

\_

A `_' character. This is used to search for literal instances of `_' in contexts where `_' would otherwise be interpreted as a wildcard character. See section 6.3.2.1 String Comparison Functions.

Note that if you use `\%' or `\_' in some string contexts, these will return the strings `\%' and `\_' and not `%' and `_'.

There are several ways to include quotes within a string:

• A `'' inside a string quoted with `'' may be written as `'''.
• A `"' inside a string quoted with `"' may be written as `""'.
• You can precede the quote character with 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 string quoted with `'' needs no special treatment.

The SELECT statements shown here demonstrate how quoting and escaping work:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "This\nIs\nFour\nlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+

If you want to insert binary data into a string column (such as a BLOB), the following characters must be represented by escape sequences:

NUL

ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character).

\

ASCII 92, backslash. Represent this by `\\'.

'

ASCII 39, single quote. Represent this by `\''.

"

ASCII 34, double quote. Represent this by `\"'.

If you write C code, you can use the C API function mysql_real_escape_string() to escape characters for the INSERT statement. See section 9.1.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 9.5.2 The DBI Interface.

You should use an escape function on any string that might contain any of the special characters listed above!

Alternatively, many MySQL APIs provide some sort of placeholder capability that allows you to insert special markers into a query string, and then bind data values to them when you issue the query. In this case, the API takes case of escaping special characters in the values for you automatically.

User Comments

I encountered some problem with additional characters:

char: ?, replace with \?
char: &, replace with \&

I have no way found to escape a backslash at the end of a string. (Version 3.23.25)

'Hello \' gives an error
'Hello \\' gives an error too
'Hello \\\'' works, but results in "Hello \'"

As the only way I found to add a blank at the end of the line ('Hello \\ ').

This seems to be a problem.

I tried select "hello ";
and is able to add a blank to end of line.

How do I escape comment characters? i.e. #

Add your own comment.