Chapter 10. Language Structure

Table of Contents

10.1. Literal Values
10.1.10.1.1. Strings
10.1.10.1.2. Numbers
10.1.10.1.3. Hexadecimal Values
10.1.10.1.4. Boolean Values
10.1.10.1.5. NULL Values
10.2. Database, Table, Index, Column, and Alias Names
10.2.10.2.1. Identifier Qualifiers
10.2.10.2.2. Identifier Case Sensitivity
10.3. User Variables
10.4. System Variables
10.4.10.4.1. Structured System Variables
10.5. Comment Syntax
10.6. Treatment of Reserved Words in MySQL

This chapter discusses the rules for writing the following elements of SQL statements when using MySQL:

Literal Values

This section describes how to write literal values in MySQL. These include strings, numbers, hexadecimal values, boolean values, and NULL. The section also covers the various nuances and “gotchas” that you may run into when dealing with these basic types in MySQL.

Strings

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:

\0An ASCII 0 (NUL) character.
\'A single quote (‘'’) character.
\"A double quote (‘"’) character.
\bA backspace character.
\nA newline (linefeed) character.
\rA carriage return character.
\tA tab character.
\ZASCII 26 (Control-Z). This character can be encoded as ‘\Z’ to allow you to work around the problem that ASCII 26 stands for END-OF-FILE on Windows. (ASCII 26 will cause problems if you try to use mysql db_name < file_name.)
\\A backslash (‘\’) character.
\%A ‘%’ character. See note following table.
\_A ‘_’ character. See note following table.

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:

  • 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 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:

NULNUL byte (ASCII 0). Represent this character by ‘\0’ (a backslash followed by an ASCII ‘0’ character).
\Backslash (ASCII 92). Represent this character by ‘\\’.
'Single quote (ASCII 39). Represent this character by ‘\'’.
"Double quote (ASCII 34). Represent this character by ‘\"’.

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. For example, in a C program, you can use the mysql_real_escape_string() C API function to escape characters. See mysql_real_escape_string(). The Perl DBI interface provides a quote method to convert special characters to the proper escape sequences. See the section called “MySQL Perl API”.

  • As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that allows you to insert special markers into a query string, and then bind data values to them when you issue the query. In this case, the API takes care of escaping special characters in the values for you.

Numbers

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.

Hexadecimal Values

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'

Boolean Values

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

NULL Values

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.