A string is a sequence of characters, surrounded by either single quote (‘'’) or double quote (‘"’) characters. Examples:

'a string'
"another string"

If the server SQL mode has ANSI_QUOTES enabled, string literals can be quoted only with single quotes. A string quoted with double quotes will be interpreted as an identifier.

As of MySQL 4.1.1, 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 the section called “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 ‘\%’ 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 section called “String Comparison Functions”. Note that if you use ‘\%’ or ‘\_’ in other contexts, they return the strings ‘\%’ and ‘\_’ and not ‘%’ and ‘_’.

In all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped.

There are several ways to include quotes within a string:

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), 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:

Integers are represented as a sequence of digits. Floats use ‘.’ as a decimal separator. Either type of number may be preceded by ‘-’ to indicate a negative value.

Examples of valid integers:

1221
0
-32

Examples of valid floating-point numbers:

294.42
-32032.6809e+10
148.00

An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number.

MySQL supports hexadecimal values. In numeric contexts, these act like integers (64-bit precision). In string contexts, these act like binary strings, where each pair of hex digits is converted to a character:

mysql> SELECT x'4D7953514C';
        -> 'MySQL'
mysql> SELECT 0xa+0;
        -> 10
mysql> SELECT 0x5061756c;
        -> 'Paul'

In MySQL 4.1 (and in MySQL 4.0 when using the --new option), the default type of a hexadecimal value is a string. If you want to ensure that the value is treated as a number, you can use CAST(... AS UNSIGNED):

mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
        -> 'A', 65

The 0x syntax is based on ODBC. Hexadecimal strings are often used by ODBC to supply values for BLOB columns. The x'hexstring' syntax is new in 4.0 and is based on standard SQL.

Beginning with MySQL 4.0.1, you can convert a string or a number to a string in hexadecimal format with the HEX() function:

mysql> SELECT HEX('cat');
        -> '636174'
mysql> SELECT 0x636174;
        -> 'cat'

Beginning with MySQL 4.1, the constant TRUE evaluates to 1 and the constant FALSE evaluates to 0. The constant names can be written in any lettercase.

mysql> SELECT TRUE, true, FALSE, false;
        -> 1, 1, 0, 0

The NULL value means “no data.” NULL can be written in any lettercase.

Be aware that the NULL value is different from values such as 0 for numeric types or the empty string for string types. See Problems with NULL.

For text file import or export operations performed with LOAD DATA INFILE or SELECT ... INTO OUTFILE, NULL is represented by the \N sequence. See LOAD DATA.