A string is a sequence of bytes or characters, enclosed within either single quote (') or double quote (") characters. Examples:

'a string' "another string"

Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:

'a string' 'a' ' ' 'string'

If theANSI_QUOTESSQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier.

Abinary stringis a string of bytes. Every binary string has a character set and collation namedbinary。Anonbinary stringis a string of characters. It has a character set other thanbinaryand a collation that is compatible with the character set.

For both types of strings, comparisons are based on the numeric values of the string unit. For binary strings, the unit is the byte; comparisons use numeric byte values. For nonbinary strings, the unit is the character and some character sets support multibyte characters; comparisons use numeric character code values. Character code ordering is a function of the string collation. (For more information, seeSection 10.8.5, “The binary Collation Compared to _bin Collations”。)

A character string literal may have an optional character set introducer andCOLLATEclause, to designate it as a string that uses a particular character set and collation:

[_charset_name]'string' [COLLATEcollation_name]

Examples:

SELECT _latin1'string'; SELECT _binary'string'; SELECT _utf8'string' COLLATE utf8_danish_ci;

You can useN'literal'(orn'literal') to create a string in the national character set. These statements are equivalent:

SELECT N'some text'; SELECT n'some text'; SELECT _utf8'some text';

For information about these forms of string syntax, seeSection 10.3.7, “The National Character Set”, andSection 10.3.8, “Character Set Introducers”。

Within a string, certain sequences have special meaning unless theNO_BACKSLASH_ESCAPESSQL mode is enabled. Each of these sequences begins with a backslash (\), known as theescape character。MySQL recognizes the escape sequences shown inTable 9.1, “Special Character Escape Sequences”。对于所有其他转义序列,ign反斜杠ored. That is, the escaped character is interpreted as if it was not escaped. For example,\xis justx。These sequences are case-sensitive. For example,\bis interpreted as a backspace, but\Bis interpreted asB。Escape processing is done according to the character set indicated by thecharacter_set_connectionsystem variable. This is true even for strings that are preceded by an introducer that indicates a different character set, as discussed inSection 10.3.6, “Character String Literal Character Set and Collation”。

The ASCII 26 character can be encoded as\Zto 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 usemysqldb_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 theLIKEoperator inSection 12.8.1, “String Comparison Functions and Operators”。If you use\%or\_outside of pattern-matching contexts, they evaluate to the strings\%and\_, not to%and_。

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

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

To insert binary data into a string column (such as aBLOBcolumn), you should represent certain characters by escape sequences. Backslash (\) and the quote character used to quote the string must be escaped. In certain client environments, it may also be necessary to escapeNULor Control+Z. Themysqlclient truncates quoted strings containingNULcharacters if they are not escaped, and Control+Z may be taken for END-OF-FILE on Windows if not escaped. For the escape sequences that represent each of these characters, seeTable 9.1, “Special Character 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: