A string is a sequence of bytes or characters, enclosed within
either single quote (‘'’) or
double quote (‘"’) characters.
Examples:
'a string'
"another string"
If the ANSI_QUOTES SQL mode is enabled,
string literals can be quoted only within single quotes. A
string quoted within double quotes is interpreted as an
identifier.
A binary string is a string of bytes that
has no character set or collation. A non-binary
string is a string of characters that has a
character set and collation. For both types of strings,
comparisons are based on the numeric values of the string unit.
For binary strings, the unit is the byte. For non-binary strings
the unit is the character and some character sets allow
multi-byte characters.
String literals may have an optional character set introducer
and COLLATE clause:
[_charset_name]'string' [COLLATE collation_name]
Examples:
SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;
For more information about these forms of string syntax, see
Section 10.3.5, “Character String Literal Character Set and Collation”.
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:
These sequences are case sensitive. For example,
‘\b’ is interpreted as a
backspace, but ‘\B’ is
interpreted as ‘B’.
The ASCII 26 character can be encoded as
‘\Z’ to enable you to work around
the problem that ASCII 26 stands for END-OF-FILE on Windows.
ASCII 26 within a file causes problems if you try to use
mysql db_name <
file_name.
The ‘\%’ and
‘\_’ sequences are used to search
for literal instances of ‘%’ and
‘_’ in pattern-matching contexts
where they would otherwise be interpreted as wildcard
characters. See the description of the LIKE
operator in Section 12.3.1, “String Comparison Functions”. If
you use ‘\%’ or
‘\_’ in non-pattern-matching
contexts, they evaluate to the strings
‘\%’ and
‘\_’, not to
‘%’ and
‘_’.
For all other escape sequences, backslash is ignored. That is,
the escaped character is interpreted as if it was not escaped.
For example, ‘\x’ is just
‘x’.
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 string
quoted with ‘'’ needs no
special treatment.
The following SELECT statements 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 |
+--------------------+
mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+
If you want to insert binary data into a string column (such as
a BLOB column), the following characters must
be represented by escape sequences:
When writing application programs, any string that might contain
any of these special characters must be properly escaped before
the string is used as a data value in an SQL statement that is
sent to the MySQL server. You can do this in two ways:
Process the string with a function that escapes the special
characters. In a C program, you can use the
mysql_real_escape_string() C API function
to escape characters. See
Section 25.2.3.52, “mysql_real_escape_string()”. The Perl DBI
interface provides a quote method to
convert special characters to the proper escape sequences.
See Section 25.4, “MySQL Perl API”. Other language interfaces may
provide a similar capability.
As an alternative to explicitly escaping special characters,
many MySQL APIs provide a placeholder capability that
enables you to insert special markers into a statement
string, and then bind data values to them when you issue the
statement. In this case, the API takes care of escaping
special characters in the values for you.
