Chapter 9. Language Structure

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 because 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. Character value ordering is a function of the string collation.

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:

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’.

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.

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 |
+------------------------+