GNU Info

(mysql.info)String syntax

---

Next: Number syntax Prev: Literals Up: Literals

---

Enter node , (file) or (file)node

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 recognizes 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 go 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
     wild-card character. Note: String comparison functions.

`\_'
     A `_' character. This is used to search for literal instances of
     `_' in contexts where `_' would otherwise be interpreted as a
     wild-card character. Note: 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 below 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 `BLOB' column, 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_escape_string()' to escape characters for the `INSERT'
statement.  Note: 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.  Note: Perl `DBI' Class.

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