Chapter 8. Language Structure

Table of Contents

8.1. Literal Values
8.1.1. Strings
8.1.2. Numbers
8.1.3. Hexadecimal Values
8.1.4. Boolean Values
8.1.5. Bit-Field Values
8.1.6. NULL Values
8.2. Schema Object Names
8.2.1. Identifier Qualifiers
8.2.2. Identifier Case Sensitivity
8.2.3. Mapping of Identifiers to File Names
8.2.4. Function Name Parsing and Resolution
8.3. Reserved Words
8.4. User-Defined Variables
8.5. Comment Syntax

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

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

8.1.1. Strings

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 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 nonbinary 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 nonbinary 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;

You can use N'literal' (or n'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 more information about these forms of string syntax, see Section 9.1.3.5, “Character String Literal Character Set and Collation”, and Section 9.1.3.6, “National Character Set”.

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the following escape sequences.

\0 An ASCII NUL (0x00) character.
\' A single quote (“'”) character.
\" A double quote (“"”) character.
\b A backspace character.
\n A newline (linefeed) character.
\r A carriage return character.
\t A tab character.
\Z ASCII 26 (Control-Z). See note following the table.
\\ A backslash (“\”) character.
\% A “%” character. See note following the table.
\_ A “_” character. See note following the table.

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.

Escape processing is done according to the character set indicated by the character_set_connection system variable. This is true even for strings that are preceded by an introducer that indicates a different character set, as discussed in Section 9.1.3.5, “Character String Literal Character Set and Collation”.

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 11.4.1, “String Comparison Functions”. 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:

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

NULNUL byte (0x00). 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. In a C program, you can use the mysql_real_escape_string() C API function to escape characters. See Section 21.10.3.53, “mysql_real_escape_string(). The Perl DBI interface provides a quote method to convert special characters to the proper escape sequences. See Section 21.12, “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.

8.1.2. Numbers

Integers are represented as a sequence of digits. Floats use “.” as a decimal separator. Either type of number may be preceded by “-” or “+” to indicate a negative or positive value, respectively

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.

8.1.3. Hexadecimal Values

MySQL supports hexadecimal values, written using X'val', x'val', or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. For values written using X'val' or x'val' format, val must contain an even number of digits. For values written using 0xval syntax, values that contain an odd number of digits are treated as having an extra leading 0. For example, 0x0a and 0xaaa are interpreted as 0x0a and 0x0aaa.

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

mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT 0x0a+0;
        -> 10
mysql> SELECT 0x5061756c;
        -> 'Paul'

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 X'hexstring' syntax is based on standard SQL. The 0x syntax is based on ODBC. Hexadecimal strings are often used by ODBC to supply values for BLOB columns.

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'

8.1.4. Boolean Values

The constants TRUE and FALSE evaluate to 1 and 0, respectively. The constant names can be written in any lettercase.

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

8.1.5. Bit-Field Values

Bit-field values can be written using b'value' or 0bvalue notation. value is a binary value written using zeros and ones.

Bit-field notation is convenient for specifying values to be assigned to BIT columns:

mysql> CREATE TABLE t (b BIT(8));
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
mysql> INSERT INTO t SET b = b'0101';

Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN(). High-order 0 bits are not displayed in the converted value.

mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
|    5 | 101      | 5        | 5        |
+------+----------+----------+----------+

Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to a user variable, use CAST() or +0:

mysql> SET @v1 = 0b1000001;
mysql> SET @v2 = CAST(0b1000001 AS UNSIGNED), @v3 = 0b1000001+0;
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+

8.1.6. NULL Values

The NULL value means “no data.NULL can be written in any lettercase. A synonym is \N (case sensitive).

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

Be aware that the NULL value is different from values such as 0 for numeric types or the empty string for string types. For more information, see Section B.1.5.3, “Problems with NULL Values”.

8.2. Schema Object Names

Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers. This section describes the allowable syntax for identifiers in MySQL. Section 8.2.2, “Identifier Case Sensitivity”, describes which types of identifiers are case sensitive and under what conditions.

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. The set of alphanumeric characters from the current character set, “_”, and “$” are not special. Reserved words are listed at Section 8.3, “Reserved Words”. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.)

The identifier quote character is the backtick (“`”):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the ANSI_QUOTES SQL mode is enabled, it is also allowable to quote identifiers within double quotes:

mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax...
mysql> SET sql_mode='ANSI_QUOTES';
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)

The ANSI_QUOTES mode causes the server to interpret double-quoted strings as identifiers. Consequently, when this mode is enabled, string literals must be enclosed within single quotes. They cannot be enclosed within double quotes. The server SQL mode is controlled as described in Section 5.1.8, “Server SQL Modes”.

Identifier quote characters can be included within an identifier if you quote the identifier. If the character to be included within the identifier is the same as that used to quote the identifier itself, then you need to double the character. The following statement creates a table named a`b that contains a column named c"d:

mysql> CREATE TABLE `a``b` (`c"d` INT);

Aliases may be quoted either as identifiers or as strings:

mysql> SELECT 1 AS `one`, 2 AS 'two';
+-----+-----+
| one | two |
+-----+-----+
|   1 |   2 |
+-----+-----+

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

It is recommended that you do not use names that begin with Me or MeN, where M and N are integers. For example, avoid using 1e as an identifier, because an expression such as 1e+3 is ambiguous. Depending on context, it might be interpreted as the expression 1e + 3 or as the number 1e+3.

Be careful when using MD5() to produce table names because it can produce names in illegal or ambiguous formats such as those just described.

A user variable cannot be used directly in an SQL statement as an identifier or as part of an identifier. See Section 8.4, “User-Defined Variables”, for more information and examples of workarounds.

There are some restrictions on the characters that may appear in identifiers:

  • No identifier can contain ASCII NUL (0x00).

  • Database, table, and column names should not end with space characters.

  • Before MySQL 5.1.6, database and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in file names.

As of MySQL 5.1.6, special characters in database and table names are encoded in the corresponding file system names as described in Section 8.2.3, “Mapping of Identifiers to File Names”. If you have databases or tables from an older version of MySQL that contain special characters and for which the underlying directory names or file names have not been updated to use the new encoding, the server displays their names with a prefix of #mysql50#. For information about referring to such names or converting them to the newer encoding, see that section.

The following table describes the maximum length for each type of identifier.

IdentifierMaximum Length (characters)
Database64
Table64
Column64
Index64
Constraint64
Stored Function or Procedure64
Trigger64
View64
Event64
Tablespace64
Log File Group64
Alias256 (see exception following table)

As of MySQL 5.1.23, aliases for column names in CREATE VIEW statements are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).

Identifiers are stored using Unicode (UTF-8). This applies to identifiers in table definitions that are stored in .frm files and to identifiers stored in the grant tables in the mysql database. The sizes of the identifier string columns in the grant tables are measured in characters. You can use multi-byte characters without reducing the number of characters allowed for values stored in these columns, something not true prior to MySQL 4.1. The allowable Unicode characters are those in the Basic Multilingual Plane (BMP). Supplementary characters are not allowed.

8.2.1. Identifier Qualifiers

MySQL allows names that consist of a single identifier or multiple identifiers. The components of a multiple-part name must be separated by period (“.”) characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.

In MySQL, you can refer to a table column using any of the following forms.

Column ReferenceMeaning
col_nameThe column col_name from whichever table used in the statement contains a column of that name.
tbl_name.col_nameThe column col_name from table tbl_name of the default database.
db_name.tbl_name.col_nameThe column col_name from table tbl_name of the database db_name.

If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write `my-table`.`my-column`, not `my-table.my-column`.

A reserved word that follows a period in a qualified name must be an identifier, so in that context it need not be quoted.

You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a statement unless the reference would be ambiguous. Suppose that tables t1 and t2 each contain a column c, and you retrieve c in a SELECT statement that uses both t1 and t2. In this case, c is ambiguous because it is not unique among the tables used in the statement. You must qualify it with a table name as t1.c or t2.c to indicate which table you mean. Similarly, to retrieve from a table t in database db1 and from a table t in database db2 in the same statement, you must refer to columns in those tables as db1.t.col_name and db2.t.col_name.

The syntax .tbl_name means the table tbl_name in the default database. This syntax is accepted for ODBC compatibility because some ODBC programs prefix table names with a “.” character.

8.2.2. Identifier Case Sensitivity

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See Section 1.7.4, “MySQL Extensions to Standard SQL”. The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

MySQL Enterprise lower_case_table_names is just one of the system variables monitored by the MySQL Enterprise Monitor. For information about subscribing to this service, see http://www.mysql.com/products/enterprise/advisors.html.

Note

Although database, table, and trigger names are not case sensitive on some platforms, you should not refer to one of these using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.

However, names of triggers and logfile groups are case sensitive. This differs from standard SQL.

By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would not work on Unix, because it refers to the alias both as a and as A:

mysql> SELECT col_name FROM tbl_name AS a
    -> WHERE a.col_name = 1 OR A.col_name = 2;

However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take the values shown in the following table. This variable does not affect case sensitivity of trigger identifiers. On Unix, the default value of lower_case_table_names is 0. On Windows the default value is 1. On Mac OS X, the default value is 2.

ValueMeaning
0Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. Note that if you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

  • Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

  • Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

    Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting.

Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, functions, procedures, savepoints, stored routine parameters, stored program local variables, and plugins. It is not true for names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables.

8.2.3. Mapping of Identifiers to File Names

There is a correspondence between database and table identifiers and names in the file system. For the basic structure, MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files (.FRM), the data is always stored in this structure and location.

For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the FRM files, or the information may be stored separate file. InnoDB data is stored in the InnoDB data files. If you are using tablespaces with InnoDB, then the specific tablespace files you create are used instead.

Before MySQL 5.1.6, there are some limitations on the characters that can be used in identifiers for database objects that correspond to file system objects. For example, path name separator characters are disallowed, and “.” is disallowed because it begins the extension for table files.

As of MySQL 5.1.6, any character is legal in database or table identifiers except ASCII NUL (0x00). MySQL encodes any characters that are problematic in the corresponding file system objects when it creates database directories or table files:

  • Basic Latin letters (a..zA..Z) and digits (0..9) are encoded as is. Consequently, their case sensitivity directly depends on file system features.

  • All other national letters from alphabets that have uppercase/lowercase mapping are encoded as follows:

    Code range Pattern            Number   Used Unused  Blocks
    -----------------------------------------------------------------------------
    00C0..017F [@][0..4][g..z] 5*20= 100   97     3  Latin1 Supplement + Ext A
    0370..03FF [@][5..9][g..z] 5*20= 100   88    12  Greek + Coptic
    0400..052F [@][g..z][0..6] 20*7= 140  140   137  Cyrillic
    0530..058F [@][g..z][7..8] 20*2=  40   38     2  Armenian
    2160..217F [@][g..z][9]    20*1=  20   16     4  Number Forms
    0180..02AF [@][g..z][a..k] 28*11=220  203    17  Latin Ext B + IPA
    1E00..0EFF [@][g..z][l..r] 20*7= 140  136     4  Latin Additional Extended
    1F00..1FFF [@][g..z][s..z] 20*8= 160  144    16  Greek Extended
    ....  .... [@][a..f][g..z] 6*20= 120    0   120  RESERVED
    24B6..24E9 [@][@][a..z]           26   26     0  Enclosed Alphanumerics
    FF21..FF5A [@][a..z][@]           26   26     0  Full Width forms
    

    One of the bytes in the sequence encodes lettercase. For example: LATIN CAPITAL LETTER A WITH GRAVE is encoded as @0G, whereas LATIN SMALL LETTER A WITH GRAVE is encoded as @0g. Here the third byte (G or g) indicates lettercase. (On a case-insensitive file system, both letters will be treated as the same.)

    For some blocks, such as Cyrillic, the second byte determines lettercase. For other blocks, such as Latin1 Supplement, the third byte determines lettercase. If two bytes in the sequence are letters (as in Greek Extended), the leftmost letter character stands for lettercase. All other letter bytes must be in lowercase.

  • All nonletter characters, as well as letters from alphabets that do not have uppercase/lowercase mapping (such as Hebrew) are encoded using hexadecimal representation using lowercase letters for hex digits a..f:

    0x003F -> @003f
    0xFFFF -> @ffff
    

    The hexadecimal values correspond to character values in the ucs2 double-byte character set.

On Windows, some names such as nul, prn, and aux cannot be used as file names because they are reserved as device names. As of MySQL 5.1.10, these are allowable names in MySQL. They are encoded by appending @@@ to the name when the server creates the corresponding file or directory. This occurs on all platforms for portability of the corresponding database object between platforms.

If you have databases or tables from a version of MySQL older than 5.1.6 that contain special characters and for which the underlying directory names or file names have not been updated to use the new encoding, the server displays their names with a prefix of #mysql50# in the output from INFORMATION_SCHEMA tables or SHOW statements. For example, if you have a table named a@b and its name encoding has not been updated, SHOW TABLES displays it like this:

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| #mysql50#a@b   |
+----------------+

To refer to such a name for which the encoding has not been updated, you must supply the #mysql50# prefix:

mysql> SHOW COLUMNS FROM `a@b`;
ERROR 1146 (42S02): Table 'test.a@b' doesn't exist

mysql> SHOW COLUMNS FROM `#mysql50#a@b`;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

To update old names to eliminate the need to use the special prefix to refer to them, re-encode them with mysqlcheck. The following command updates all names to the new encoding:

shell> mysqlcheck --check-upgrade --fix-db-names --fix-table-names --all-databases

To check only specific databases or tables, omit --all-databases and provide the appropriate database or table arguments. For information about mysqlcheck invocation syntax, see Section 4.5.3, “mysqlcheck — A Table Maintenance Program”.

Note

The #mysql50# prefix is intended only to be used internally by the server. You should not create databases or tables with names that use this prefix.

Also, mysqlcheck cannot fix names that contain literal instances of the @ character that is used for encoding special characters. If you have databases or tables that contain this character, use mysqldump to dump them before upgrading to MySQL 5.1.6 or later, and then reload the dump file after upgrading.

8.2.4. Function Name Parsing and Resolution

MySQL 5.1 supports built-in (native) functions, user-defined functions (UDFs), and stored functions. This section describes how the server recognizes whether the name of a built-in function is used as a function call or as an identifier, and how the server determines which function to use in cases when functions of different types exist with a given name.

Built-In Function Name Parsing

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode.

When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name. For example, in the following statements, the first reference to count is a function call, whereas the second reference is a table name:

SELECT COUNT(*) FROM mytable;
CREATE TABLE count (i INT);

The parser should recognize the name of a built-in function as indicating a function call only when parsing what is expected to be an expression. That is, in nonexpression context, function names are permitted as identifiers.

However, some built-in functions have special parsing or implementation considerations, so the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in nonexpression context:

  • To use the name as a function call in an expression, there must be no whitespace between the name and the following “(” parenthesis character.

  • Conversely, to use the function name as an identifier, it must not be followed immediately by a parenthesis.

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name. The exact list of function names for which following whitespace determines their interpretation are those listed in the sql_functions[] array of the sql/lex.h source file. Before MySQL 5.1, these are rather numerous (about 200), so you may find it easiest to treat the no-whitespace requirement as applying to all function calls. In MySQL 5.1, parser improvements reduce to about 30 the number of affected function names.

For functions not listed in the sql_functions[]) array, whitespace does not matter. They are interpreted as function calls only when used in expression context and may be used freely as identifiers otherwise. ASCII is one such name. However, for these nonaffected function names, interpretation may vary in expression context: func_name () is interpreted as a built-in function if there is one with the given name; if not, func_name () is interpreted as a user-defined function or stored function if one exists with that name.

The IGNORE_SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive:

  • With IGNORE_SPACE disabled, the parser interprets the name as a function call when there is no whitespace between the name and the following parenthesis. This occurs even when the function name is used in nonexpression context:

    mysql> CREATE TABLE count(i INT);
    ERROR 1064 (42000): You have an error in your SQL syntax ...
    near 'count(i INT)'
    

    To eliminate the error and cause the name to be treated as an identifier, either use whitespace following the name or write it as a quoted identifier (or both):

    CREATE TABLE count (i INT);
    CREATE TABLE `count`(i INT);
    CREATE TABLE `count` (i INT);
    
  • With IGNORE_SPACE enabled, the parser loosens the requirement that there be no whitespace between the function name and the following parenthesis. This provides more flexibility in writing function calls. For example, either of the following function calls are legal:

    SELECT COUNT(*) FROM mytable;
    SELECT COUNT (*) FROM mytable;
    

    However, enabling IGNORE_SPACE also has the side effect that the parser treats the affected function names as reserved words (see Section 8.3, “Reserved Words”). This means that a space following the name no longer signifies its use as an identifier. The name can be used in function calls with or without following whitespace, but causes a syntax error in nonexpression context unless it is quoted. For example, with IGNORE_SPACE enabled, both of the following statements fail with a syntax error because the parser interprets count as a reserved word:

    CREATE TABLE count(i INT);
    CREATE TABLE count (i INT);
    

    To use the function name in nonexpression context, write it as a quoted identifier:

    CREATE TABLE `count`(i INT);
    CREATE TABLE `count` (i INT);
    

To enable the IGNORE_SPACE SQL mode, use this statement:

SET sql_mode = 'IGNORE_SPACE';

IGNORE_SPACE is also enabled by certain other composite modes such as ANSI that include it in their value:

SET sql_mode = 'ANSI';

Check Section 5.1.8, “Server SQL Modes”, to see which composite modes enable IGNORE_SPACE.

To minimize the dependency of SQL code on the IGNORE_SPACE setting, use these guidelines:

  • Avoid creating UDFs or stored functions that have the same name as a built-in function.

  • Avoid using function names in nonexpression context. For example, these statements use count (one of the affected function names affected by IGNORE_SPACE), so they fail with or without whitespace following the name if IGNORE_SPACE is enabled:

    CREATE TABLE count(i INT);
    CREATE TABLE count (i INT);
    

    If you must use a function name in nonexpression context, write it as a quoted identifier:

    CREATE TABLE `count`(i INT);
    CREATE TABLE `count` (i INT);
    

The number of function names affected by IGNORE_SPACE was reduced significantly in MySQL 5.1.13, from about 200 to about 30. As of MySQL 5.1.13, only the following functions are still affected by the IGNORE_SPACE setting.

ADDDATEBIT_ANDBIT_ORBIT_XOR
CASTCOUNTCURDATECURTIME
DATE_ADDDATE_SUBEXTRACTGROUP_CONCAT
MAXMIDMINNOW
POSITIONSESSION_USERSTDSTDDEV
STDDEV_POPSTDDEV_SAMPSUBDATESUBSTR
SUBSTRINGSUMSYSDATESYSTEM_USER
TRIMVARIANCEVAR_POPVAR_SAMP

For earlier versions of MySQL, check the contents of the sql_functions[] array in the sql/lex.h source file to see which functions are affected by IGNORE_SPACE.

Incompatibility warning: The change in MySQL 5.1.13 that reduces the number of function names affected by IGNORE_SPACE improves the consistency of parser operation. However, it also introduces the possibility of incompatibility for old SQL code that relies on the following conditions:

  • IGNORE_SPACE is disabled.

  • The presence or absence of whitespace following a function name is used to distinguish between a built-in function and stored function that have the same name, such as PI() versus PI ().

For functions that are no longer affected by IGNORE_SPACE as of MySQL 5.1.13, that strategy no longer works. Either of the following approaches can be used if you have code that is subject to the preceding incompatibility:

  • If a stored function has a name that conflicts with a built-in function, refer to the stored function with a schema name qualifier, regardless of whether whitespace is present. For example, write schema_name.PI() or schema_name.PI ().

  • Alternatively, rename the stored function to use a nonconflicting name and change invocations of the function to use the new name.

Function Name Resolution

The following rules describe how the server resolves references to function names for function creation and invocation:

  • Built-in functions and user-defined functions

    As of MySQL 5.1.14, an error occurs if you try to create a UDF with the same name as a built-in function. Before 5.1.14, a UDF can be created with the same name as a built-in function but the UDF cannot be invoked because the parser resolves invocations of the function to refer to the built-in function. For example, if you create a UDF named ABS, references to ABS() invoke the built-in function.

  • Built-in functions and stored functions

    It is possible to create a stored function with the same name as a built-in function, but to invoke the stored function it is necessary to qualify it with a schema name. For example, if you create a stored function named PI in the test schema, you invoke it as test.PI() because the server resolves PI() as a reference to the built-in function. As of 5.1.14, the server creates a warning if the stored function name collides with a built-in function name. The warning can be displayed with SHOW WARNINGS.

  • User-defined functions and stored functions

    User-defined functions and stored functions share the same namespace, so you cannot create a UDF and a stored function with the same name.

The preceding function name resolution rules have implications for upgrading to versions of MySQL that implement new built-in functions:

  • If you have already created a user-defined function with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name.

  • If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a nonconflicting name, or change calls to the function so that they use a schema qualifier (that is, use schema_name.func_name() syntax).

8.3. Reserved Words

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions.

Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Schema Object Names”:

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

Exception: A word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved:

mysql> CREATE TABLE mydb.interval (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

Names of built-in functions are permitted as identifiers but may require care to be used as such. For example, COUNT is acceptable as a column name. However, by default, no whitespace is allowed in function invocations between the function name and the following “(” character. This requirement enables the parser to distinguish whether the name is used in a function call or in nonfunction context. For further detail on recognition of function names, see Section 8.2.4, “Function Name Parsing and Resolution”.

The words in the following table are explicitly reserved in MySQL 5.1. In addition, _FILENAME is reserved. At some point, you might upgrade to a higher version, so it is a good idea to have a look at future reserved words, too. You can find these in the manuals that cover higher versions of MySQL. Most of the words in the table are forbidden by standard SQL as column or table names (for example, GROUP). A few are reserved because MySQL needs them and uses a yacc parser. A reserved word can be used as an identifier if you quote it.

For a more detailed list of reserved words, including differences between versions, see Reserved Words in MySQL 5.1.

ACCESSIBLEADDALL
ALTERANALYZEAND
ASASCASENSITIVE
BEFOREBETWEENBIGINT
BINARYBLOBBOTH
BYCALLCASCADE
CASECHANGECHAR
CHARACTERCHECKCOLLATE
COLUMNCONDITIONCONSTRAINT
CONTINUECONVERTCREATE
CROSSCURRENT_DATECURRENT_TIME
CURRENT_TIMESTAMPCURRENT_USERCURSOR
DATABASEDATABASESDAY_HOUR
DAY_MICROSECONDDAY_MINUTEDAY_SECOND
DECDECIMALDECLARE
DEFAULTDELAYEDDELETE
DESCDESCRIBEDETERMINISTIC
DISTINCTDISTINCTROWDIV
DOUBLEDROPDUAL
EACHELSEELSEIF
ENCLOSEDESCAPEDEXISTS
EXITEXPLAINFALSE
FETCHFLOATFLOAT4
FLOAT8FORFORCE
FOREIGNFROMFULLTEXT
GRANTGROUPHAVING
HIGH_PRIORITYHOUR_MICROSECONDHOUR_MINUTE
HOUR_SECONDIFIGNORE
ININDEXINFILE
INNERINOUTINSENSITIVE
INSERTINTINT1
INT2INT3INT4
INT8INTEGERINTERVAL
INTOISITERATE
JOINKEYKEYS
KILLLEADINGLEAVE
LEFTLIKELIMIT
LINEARLINESLOAD
LOCALTIMELOCALTIMESTAMPLOCK
LONGLONGBLOBLONGTEXT
LOOPLOW_PRIORITYMASTER_SSL_VERIFY_SERVER_CERT
MATCHMEDIUMBLOBMEDIUMINT
MEDIUMTEXTMIDDLEINTMINUTE_MICROSECOND
MINUTE_SECONDMODMODIFIES
NATURALNOTNO_WRITE_TO_BINLOG
NULLNUMERICON
OPTIMIZEOPTIONOPTIONALLY
ORORDEROUT
OUTEROUTFILEPRECISION
PRIMARYPROCEDUREPURGE
RANGEREADREADS
READ_WRITEREALREFERENCES
REGEXPRELEASERENAME
REPEATREPLACEREQUIRE
RESTRICTRETURNREVOKE
RIGHTRLIKESCHEMA
SCHEMASSECOND_MICROSECONDSELECT
SENSITIVESEPARATORSET
SHOWSMALLINTSPATIAL
SPECIFICSQLSQLEXCEPTION
SQLSTATESQLWARNINGSQL_BIG_RESULT
SQL_CALC_FOUND_ROWSSQL_SMALL_RESULTSSL
STARTINGSTRAIGHT_JOINTABLE
TERMINATEDTHENTINYBLOB
TINYINTTINYTEXTTO
TRAILINGTRIGGERTRUE
UNDOUNIONUNIQUE
UNLOCKUNSIGNEDUPDATE
USAGEUSEUSING
UTC_DATEUTC_TIMEUTC_TIMESTAMP
VALUESVARBINARYVARCHAR
VARCHARACTERVARYINGWHEN
WHEREWHILEWITH
WRITEXORYEAR_MONTH
ZEROFILL  

The following are new reserved words in MySQL 5.1:

ACCESSIBLELINEARMASTER_SSL_VERIFY_SERVER_CERT
RANGEREAD_ONLYREAD_WRITE

MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

8.4. User-Defined Variables

You can store a value in a user-defined variable and then refer to it later. This enables you to pass values from one statement to another. User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.

User variables are written as @var_name, where the variable name var_name may consist of alphanumeric characters from the current character set, “.”, “_”, and “$”. The default character set is latin1 (cp1252 West European). This may be changed with the --character-set-server option to mysqld. See Section 9.2, “The Character Set Used for Data and Sorting”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @`my-var`).

User variable names not case sensitive in MySQL 5.0 and up.

One way to set a user-defined variable is by issuing a SET statement:

SET @var_name = expr [, @var_name = expr] ...

For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements:

mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the allowable types is converted to an allowable type. For example, a value having a temporal or spatial data type is converted to a binary string.

If a user variable is assigned a nonbinary (character) string value, it has the same character set and collation as the string. The coercibility of user variables is implicit. (This is the same coercibility as for table column values.)

Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to a user variable, use CAST() or +0:

mysql> SET @v1 = b'1000001';
mysql> SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0;
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+

If the value of a user variable is selected in a result set, it is returned to the client as a string.

User variables may be used in contexts where expressions are allowed. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

Note

In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement does not work as expected:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the SELECT list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.

The order of evaluation for user variables is undefined and may change based on the elements contained within a given query. In SELECT @a, @a := @a+1 ..., you might think that MySQL will evaluate @a first and then do an assignment second, but changing the query (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may change the order of evaluation.

The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed.

Another issue with setting a variable and using it in the same statement is that the default result type of a variable is based on the type of the variable at the start of the statement. The following example illustrates this:

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that column one is a string and converts all accesses of @a to strings, even though @a is set to a number for the second row. After the SELECT statement executes, @a is regarded as a number for the next statement.

To avoid problems with this behavior, either do not set and use the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it.

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT. This is true even if the variable is quoted, as shown in the following example:

mysql> SELECT c1 FROM t;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> SET @col = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| c1   |
+------+
1 row in set (0.00 sec)

mysql> SELECT `@col` FROM t;
ERROR 1054 (42S22): Unknown column '@col' in 'field list'

mysql> SET @col = "`c1`";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| `c1` |
+------+
1 row in set (0.00 sec)

An exception to this principle that user variables cannot be used to provide identifiers is that if you are constructing a string for use as a prepared statement to be executed later. In this case, user variables can be used to provide any part of the statement. The following example illustrates how this can be done:

mysql> SET @c = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE stmt;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

See Section 12.7, “SQL Syntax for Prepared Statements”, for more information.

A similar technique can be used in application programs to construct SQL statements using program variables, as shown here using PHP 5:

<?php
  $mysqli = new mysqli("localhost", "user", "pass", "test");

  if( mysqli_connect_errno() )
    die("Connection failed: %s\n", mysqli_connect_error());

  $col = "c1";

  $query = "SELECT $col FROM t";

  $result = $mysqli->query($query);

  while($row = $result->fetch_assoc())
  {
    echo "<p>" . $row["$col"] . "</p>\n";
  }

  $result->close();

  $mysqli->close();
?>

Assembling an SQL statement in this fashion is sometimes known as “Dynamic SQL”.

8.5. Comment Syntax

MySQL Server supports three comment styles:

  • From a “#” character to the end of the line.

  • From a “-- ” sequence to the end of the line. In MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.7.5.6, “'--' as the Start of a Comment”.

  • From a /* sequence to the following */ sequence, as in the C programming language. This syntax allows a comment to extend over multiple lines because the beginning and closing sequences need not be on the same line.

The following example demonstrates all three comment styles:

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;

Nested comments are not supported.

MySQL Server supports some variants of C-style comments. These enable you to write code that includes MySQL extensions, but is still portable, by using comments of the following form:

/*! MySQL-specific code */

In this case, MySQL Server parses and executes the code within the comment as it would any other SQL statement, but other SQL servers will ignore the extensions. For example, MySQL Server recognizes the STRAIGHT_JOIN keyword in the following statement, but other servers will not:

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

If you add a version number after the “!” character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The TEMPORARY keyword in the following comment is executed only by servers from MySQL 3.23.02 or higher:

CREATE /*!32302 TEMPORARY */ TABLE t (a INT);

The comment syntax just described applies to how the mysqld server parses SQL statements. The mysql client program also performs some parsing of statements before sending them to the server. (It does this to determine statement boundaries within a multiple-statement input line.)

The use of short-form mysql commands such as \C within multi-line /* ... */ comments is not supported.