Chapter 9. Language Structure

Table of Contents

9.1. Literal Values
9.1.1. Strings
9.1.2. Numbers
9.1.3. Hexadecimal Values
9.1.4. Boolean Values
9.1.5. Bit-Field Values
9.1.6. NULL Values
9.2. Database, Table, Index, Column, and Alias Names
9.2.1. Identifier Qualifiers
9.2.2. Identifier Case Sensitivity
9.3. User Variables
9.4. System Variables
9.4.1. Structured System Variables
9.5. Comment Syntax
9.6. Treatment of Reserved Words in MySQL

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

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

9.1.1. 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 is interpreted as an identifier.

In MySQL 5.0, 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.7, “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:

\0 An ASCII 0 (NUL) 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). 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 causes 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 Section 12.3.1, “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 Section 23.2.3.52, “mysql_real_escape_string(). The Perl DBI interface provides a quote method to convert special characters to the proper escape sequences. See Section 23.4, “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.

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

9.1.3. 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 5.0, 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 based on standard SQL.

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'

9.1.4. Boolean Values

In MySQL 5.0, 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

9.1.5. Bit-Field Values

Beginning with MySQL 5.0.3, bit-field values can be written using b'value' notation. value is a binary value written using zeroes 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';
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
+------+----------+----------+----------+

9.1.6. 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 Section A.5.3, “Problems with NULL Values”.

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

9.2. Database, Table, Index, Column, and Alias Names

Database, table, index, column, and alias names are identifiers. This section describes the allowable syntax for identifiers in MySQL.

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

IdentifierMaximum Length (bytes)Allowed Characters
Database64Any character that is allowed in a directory name, except ‘/’, ‘\’, or ‘.
Table64Any character that is allowed in a filename, except ‘/’, ‘\’, or ‘.
Column64All characters
Index64All characters
Alias255All characters

In addition to the restrictions noted in the table, no identifier can contain ASCII 0 or a byte with a value of 255. Database, table, and column names should not end with space characters. MySQL 5.0 allows the use of identifier quote characters in identifiers, although it is best to avoid doing so if possible.

In MySQL 5.0, identifiers are stored using Unicode (UTF8). This applies to identifiers in table definitions that stored in .frm files and to identifiers stored in the grant tables in the mysql database. The sizes of the string columns in the grant tables (and in any other tables) in MySQL 5.0 equate to numbers of characters; this means that (unlike the case for some earlier versions of MySQL) you can use multi-byte characters without reducing the number of characters allowed for values stored in these columns.

An identifier may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it. For a list of reserved words, see Section 9.6, “Treatment of Reserved Words in MySQL”. Special characters are those outside the set of alphanumeric characters from the current character set, ‘_’, and ‘$’.

The identifier quote character is the backtick (‘`’):

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

If the server SQL mode includes the ANSI_QUOTES mode option, it is also allowable to quote identifiers with 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)

See Section 5.3.2, “The Server SQL Mode”.

In MySQL 5.0, 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);

It is recommended that you do not use names of the pattern XeX, such as 1e or 2e2, because an expression like 1e+1 is ambiguous. It might be interpreted as the expression 1e + 1 or as the number 1e+1, depending on context.

Be careful when using MD5 to produce table names, as it can produce illegal table names such as the ones listed above.

9.2.1. Identifier Qualifiers

MySQL allows names that consist of a single identifier or multiple identifiers. The components of a multiple-part name should 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 column using any of the following forms:

Column ReferenceMeaning
col_nameThe column col_name from whichever table used in the query 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, `my-table`.`my-column` is legal, whereas `my-table.my-column` is not.

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.

A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word.

The syntax .tbl_name means the table tbl_name in the current database. This syntax is accepted for ODBC compatibility because some ODBC programs prefix table names with a ‘.’ character.

9.2.2. Identifier Case Sensitivity

In MySQL, databases correspond to directories within the data directory. Each tables within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in most varieties of Unix, and not case sensitive in Windows. One notable exception is Mac OS X, which is Unix-based but uses a default filesystem 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.8.4, “MySQL Extensions to Standard SQL”.

Note: Although database and table names are not case sensitive on some platforms, you should not refer to a given database or table using different cases within the same query. The following query 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 trigger names are not case sensitive on any platform, nor are column aliases.

By default, table aliases in MySQL 5.0 are case sensitive on Unix, but not so on Windows or Mac OS X. The following query 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 query is permitted on Windows. To avoid being caught out by such differences, 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 defined by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take one of the following values:

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. This is the default on Unix systems. Note that if you force this to 0 with --lower-case-table-names=0 on a case-insensitive filesystem and access MyISAM tablenames using different lettercases, this may lead to index corruption.
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. In MySQL 5.0, this behavior also applies to database names and to table aliases. This value is the default on Windows and Mac OS X systems.
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. Note: This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

In MySQL 5.0 on both Windows and Mac OS X, the default value of lower_case_table_names is 1.

If you are using MySQL on only one platform, you don't normally have to change the lower_case_table_names variable. However, you may encounter difficulties if you want to transfer tables between platforms that differ in filesystem 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 stemming from database or table name lettercase, 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 don't 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 queries always refer to your database and table names with the correct lettercase on Windows. If you transfer your queries to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

    Exception: If you are using InnoDB tables, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

Note that before setting lower_case_table_names to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld.

9.3. User Variables

MySQL 5.0 supports user variables in which you can store a value and then refer to it later; this allows you to pass values from one statement to another. User variables are connection-specific. That is, a variable defined by one client cannot be seen or used by other clients. All variables for a 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 cp1252 (Latin1). This may be changed with the --default-character-set option to mysqld. See Section 5.10.1, “The Character Set Used for Data and Sorting”. User variable names are not case sensitive in MySQL 5.0.

One way to set a user 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. The expr assigned to each variable can evaluate to an integer, real, string, or NULL value.

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 may be used 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 number LINES clause of a LOAD DATA statement.

If you refer to a variable that has not been initialized, its value is NULL.

In MySQL 5.0, if a user variable is assigned a string value, it also has the same character set and collation as the string. The coercibility of user variables is implicit as of MySQL 5.0.3. (This is the same coercibility as for table column values.)

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 does not contain the value of the current row, but the value of id from the previous selected row.

The general rule is to never assign a value to a user variable in one part of a statement and use the same variable in some other part 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.

An unassigned variable has a value of NULL with a type of string.

9.4. System Variables

MySQL provides access to many system and connection variables. Many variables can be changed dynamically while the server is running. This often allows you to modify server operation without having to stop and restart it.

The mysqld server maintains two kinds of variables. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections.

When the server starts, it initializes all global variables to their default values. These defaults may be changed by options specified in option files or on the command line. After the server starts, those global variables that are dynamic can be changed by connecting to the server and issuing a SET GLOBAL var_name statement. To change a global variable, you must have the SUPER privilege.

The server also maintains a set of session variables for each client that connects. The client's session variables are initialized at connect time using the current values of the corresponding global variables. For those session variables that are dynamic, the client can change them by issuing a SET SESSION var_name statement. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.

A change to a global variable is visible to any client that accesses that global variable. However, it affects the corresponding session variable that is initialized from the global variable only for clients that connect after the change. It does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

Global or session variables may be set or retrieved using several syntax forms. The following examples use sort_buffer_size as a sample variable name.

To set the value of a GLOBAL variable, use one of the following syntaxes:

mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;

To set the value of a SESSION variable, use one of the following syntaxes:

mysql> SET SESSION sort_buffer_size=value;
mysql> SET @@session.sort_buffer_size=value;
mysql> SET sort_buffer_size=value;

LOCAL is a synonym for SESSION.

If you don't specify GLOBAL, SESSION, or LOCAL when setting a variable, SESSION is the default. See Section 13.5.3, “SET Syntax”.

To retrieve the value of a GLOBAL variable, use one of the following statements:

mysql> SELECT @@global.sort_buffer_size;
mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';

To retrieve the value of a SESSION variable, use one of the following statements:

mysql> SELECT @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;
mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';

Here, too, LOCAL is a synonym for SESSION.

When you retrieve a variable with SELECT @@var_name (that is, you do not specify global., session., or local., MySQL returns the SESSION value if it exists and the GLOBAL value otherwise.

For SHOW VARIABLES, if you do not specify GLOBAL, SESSION, or LOCAL, MySQL returns the SESSION values.

The reason for requiring the GLOBAL keyword when setting GLOBAL-only variables but not when retrieving them is to prevent problems in the future. If we remove a SESSION variable with the same name as a GLOBAL variable, a client with the SUPER privilege might accidentally change the GLOBAL variable rather than just the SESSION variable for its own connection. If we add a SESSION variable with the same name as a GLOBAL variable, a client that intends to change the GLOBAL variable might find only its own SESSION variable changed.

Further information about system startup options and system variables can be found in Section 5.3.1, “mysqld Command-Line Options” and Section 5.3.3, “Server System Variables”. A list of the variables that can be set at runtime is given in Section 5.3.3.1, “Dynamic System Variables”.

9.4.1. Structured System Variables

MySQL 5.0 also supports structured system variables. A structured variable differs from a regular system variable in two respects:

  • Its value is a structure with components that specify server parameters considered to be closely related.

  • There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.

Currently, MySQL supports one structured variable type. It specifies parameters that govern the operation of key caches. A key cache structured variable has these components:

  • key_buffer_size

  • key_cache_block_size

  • key_cache_division_limit

  • key_cache_age_threshold

This section describes the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in Section 7.4.6, “The MyISAM Key Cache”.

To refer to a component of a structured variable instance, you can use a compound name in instance_name.component_name format. Examples:

hot_cache.key_buffer_size
hot_cache.key_cache_block_size
cold_cache.key_cache_block_size

For each structured system variable, an instance with the name of default is always predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus, default.key_buffer_size and key_buffer_size both refer to the same system variable.

The naming rules for structured variable instances and components are as follows:

  • For a given type of structured variable, each instance must have a name that is unique within variables of that type. However, instance names need not be unique across structured variable types. For example, each structured variable has an instance named default, so default is not unique across variable types.

  • The names of the components of each structured variable type must be unique across all system variable names. If this were not true (that is, if two different types of structured variables could share component member names), it would not be clear which default structured variable to use for references to member names that are not qualified by an instance name.

  • If a structured variable instance name is not legal as an unquoted identifier, refer to it as a quoted identifier using backticks. For example, hot-cache is not legal, but `hot-cache` is.

  • global, session, and local are not legal instance names. This avoids a conflict with notation such as @@global.var_name for referring to non-structured system variables.

Currently, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules assume greater significance if some other type of structured variable is created in the future.

With one exception, you can refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option:

shell> mysqld --hot_cache.key_buffer_size=64K

In an option file, use this:

[mysqld]
hot_cache.key_buffer_size=64K

If you start the server with this option, it creates a key cache named hot_cache with a size of 64KB in addition to the default key cache that has a default size of 8MB.

Suppose that you start the server as follows:

shell> mysqld --key_buffer_size=256K \
         --extra_cache.key_buffer_size=128K \
         --extra_cache.key_cache_block_size=2048

In this case, the server sets the size of the default key cache to 256KB. (You could also have written --default.key_buffer_size=256K.) In addition, the server creates a second key cache named extra_cache that has a size of 128KB, with the size of block buffers for caching table index blocks set to 2048 bytes.

The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:

shell> mysqld --key_buffer_size=6M \
         --hot_cache.key_buffer_size=2M \
         --cold_cache.key_buffer_size=2M

Structured variable values may be set and retrieved at runtime as well. For example, to set a key cache named hot_cache to a size of 10MB, use either of these statements:

mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;
mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;

To retrieve the cache size, do this:

mysql> SELECT @@global.hot_cache.key_buffer_size;

However, the following statement does not work. The variable is not interpreted as a compound name, but as a simple string for a LIKE pattern-matching operation:

mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';

This is the exception to being able to use structured variable names anywhere a simple variable name may occur.

9.5. Comment Syntax

The MySQL server supports three comment styles:

  • From a ‘#’ character to the end of the line.

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

  • From a /* sequence to the following */ sequence. The closing sequence need not be on the same line, so this syntax allows a comment to extend over multiple lines.

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;

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. (For example, it does this to determine statement boundaries within a multiple-statement input line.)

In MySQL 5.0, the only limitation on the way that mysql parses /* ... */ comments is that an exclamation point used with this style of comment delimiter marks portions of SQL statements for conditional execution. This applies both when you run mysql interactively and when you put commands in a file and use mysql in batch mode to process the file with mysql < file_name. For more information and examples, see Section 1.8.4, “MySQL Extensions to Standard SQL”.

9.6. Treatment of Reserved Words in MySQL

A common problem stems from trying to use an identifier such as a table or column name that is the name of a built-in MySQL data type or function, such as TIMESTAMP or GROUP. You are permitted to do this (for example, ABS 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 allows a function call to be distinguished from a reference to a column name.

A side effect of this behavior is that omitting a space in some contexts causes an identifier to be interpreted as a function name. For example, this statement is legal:

mysql> CREATE TABLE abs (val INT);

But omitting the space after abs causes a syntax error because the statement then appears to invoke the ABS() function:

mysql> CREATE TABLE abs(val INT);

If the server SQL mode includes the IGNORE_SPACE mode value, the server allows function invocations to have whitespace between a function name and the following ‘(’ character. This causes function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 9.2, “Database, Table, Index, Column, and Alias Names”. The server SQL mode is controlled as described in Section 5.3.2, “The Server SQL Mode”.

A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word.

The words in the following table are explicitly reserved in MySQL. Most of them are forbidden by standard SQL as column and/or table names (for example, GROUP). A few are reserved because MySQL needs them and (currently) uses a yacc parser. A reserved word can be used as an identifier if you quote it.

ACTIONADDAFTER
AGAINSTAGGREGATEALGORITHM
ALLALTERANALYZE
ANDANYAS
ASCASCIIASENSITIVE
AUTO_INCREMENTAVGAVG_ROW_LENGTH
BACKUPBDBBEFORE
BEGINBERKELEYDBBETWEEN
BIGINTBINARYBINLOG
BITBLOBBOOL
BOOLEANBOTHBTREE
BYBYTECACHE
CALLCASCADECASCADED
CASECHAINCHANGE
CHANGEDCHARCHARACTER
CHARSETCHECKCHECKSUM
CIPHERCLIENTCLOSE
COLLATECOLLATIONCOLUMN
COLUMNSCOMMENTCOMMIT
COMMITTEDCOMPACTCOMPRESSED
CONCURRENTCONDITIONCONNECTION
CONSISTENTCONSTRAINTCONTAINS
CONTINUECONVERTCREATE
CROSSCUBECURRENT_DATE
CURRENT_TIMECURRENT_TIMESTAMPCURRENT_USER
CURSORDATADATABASE
DATABASESDATEDATETIME
DAYDAY_HOURDAY_MICROSECOND
DAY_MINUTEDAY_SECONDDEALLOCATE
DECDECIMALDECLARE
DEFAULTDEFINERDELAYED
DELAY_KEY_WRITEDELETEDESC
DESCRIBEDES_KEY_FILEDETERMINISTIC
DIRECTORYDISABLEDISCARD
DISTINCTDISTINCTROWDIV
DODOUBLEDROP
DUALDUMPFILEDUPLICATE
DYNAMICEACHELSE
ELSEIFENABLEENCLOSED
ENDENGINEENGINES
ENUMERRORSESCAPE
ESCAPEDEVENTSEXECUTE
EXISTSEXITEXPANSION
EXPLAINEXTENDEDFALSE
FASTFETCHFIELDS
FILEFIRSTFIXED
FLOATFLOAT4FLOAT8
FLUSHFORFORCE
FOREIGNFOUNDFRAC_SECOND
FROMFULLFULLTEXT
FUNCTIONGEOMETRYGEOMETRYCOLLECTION
GET_FORMATGLOBALGOTO
GRANTGRANTSGROUP
HANDLERHASHHAVING
HELPHIGH_PRIORITYHOSTS
HOURHOUR_MICROSECONDHOUR_MINUTE
HOUR_SECONDIDENTIFIEDIF
IGNOREIMPORTIN
INDEXINDEXESINFILE
INNERINNOBASEINNODB
INOUTINSENSITIVEINSERT
INSERT_METHODINTINT1
INT2INT3INT4
INT8INTEGERINTERVAL
INTOINVOKERIO_THREAD
ISISOLATIONISSUER
ITERATEJOINKEY
KEYSKILLLABEL
LANGUAGELASTLEADING
LEAVELEAVESLEFT
LEVELLIKELIMIT
LINESLINESTRINGLOAD
LOCALLOCALTIMELOCALTIMESTAMP
LOCKLOCKSLOGS
LONGLONGBLOBLONGTEXT
LOOPLOW_PRIORITYMASTER
MASTER_CONNECT_RETRYMASTER_HOSTMASTER_LOG_FILE
MASTER_LOG_POSMASTER_PASSWORDMASTER_PORT
MASTER_SERVER_IDMASTER_SSLMASTER_SSL_CA
MASTER_SSL_CAPATHMASTER_SSL_CERTMASTER_SSL_CIPHER
MASTER_SSL_KEYMASTER_USERMATCH
MAX_CONNECTIONS_PER_HOURMAX_QUERIES_PER_HOURMAX_ROWS
MAX_UPDATES_PER_HOURMAX_USER_CONNECTIONSMEDIUM
MEDIUMBLOBMEDIUMINTMEDIUMTEXT
MERGEMICROSECONDMIDDLEINT
MIGRATEMINUTEMINUTE_MICROSECOND
MINUTE_SECONDMIN_ROWSMOD
MODEMODIFIESMODIFY
MONTHMULTILINESTRINGMULTIPOINT
MULTIPOLYGONMUTEXNAME
NAMESNATIONALNATURAL
NCHARNDBNDBCLUSTER
NEWNEXTNO
NONENOTNO_WRITE_TO_BINLOG
NULLNUMERICNVARCHAR
OFFSETOLD_PASSWORDON
ONEONE_SHOTOPEN
OPTIMIZEOPTIONOPTIONALLY
ORORDEROUT
OUTEROUTFILEPACK_KEYS
PARTIALPASSWORDPHASE
POINTPOLYGONPRECISION
PREPAREPREVPRIMARY
PRIVILEGESPROCEDUREPROCESSLIST
PURGEQUARTERQUERY
QUICKRAID0RAID_CHUNKS
RAID_CHUNKSIZERAID_TYPEREAD
READSREALRECOVER
REDUNDANTREFERENCESREGEXP
RELAY_LOG_FILERELAY_LOG_POSRELAY_THREAD
RELEASERELOADRENAME
REPAIRREPEATREPEATABLE
REPLACEREPLICATIONREQUIRE
RESETRESTORERESTRICT
RESUMERETURNRETURNS
REVOKERIGHTRLIKE
ROLLBACKROLLUPROUTINE
ROWROWSROW_FORMAT
RTREESAVEPOINTSCHEMA
SCHEMASSECONDSECOND_MICROSECOND
SECURITYSELECTSENSITIVE
SEPARATORSERIALSERIALIZABLE
SESSIONSETSHARE
SHOWSHUTDOWNSIGNED
SIMPLESLAVESMALLINT
SNAPSHOTSOMESONAME
SOUNDSSPATIALSPECIFIC
SQLSQLEXCEPTIONSQLSTATE
SQLWARNINGSQL_BIG_RESULTSQL_BUFFER_RESULT
SQL_CACHESQL_CALC_FOUND_ROWSSQL_NO_CACHE
SQL_SMALL_RESULTSQL_THREADSQL_TSI_DAY
SQL_TSI_FRAC_SECONDSQL_TSI_HOURSQL_TSI_MINUTE
SQL_TSI_MONTHSQL_TSI_QUARTERSQL_TSI_SECOND
SQL_TSI_WEEKSQL_TSI_YEARSSL
STARTSTARTINGSTATUS
STOPSTORAGESTRAIGHT_JOIN
STRINGSTRIPEDSUBJECT
SUPERSUSPENDTABLE
TABLESTABLESPACETEMPORARY
TEMPTABLETERMINATEDTEXT
THENTIMETIMESTAMP
TIMESTAMPADDTIMESTAMPDIFFTINYBLOB
TINYINTTINYTEXTTO
TRAILINGTRANSACTIONTRIGGER
TRIGGERSTRUETRUNCATE
TYPETYPESUNCOMMITTED
UNDEFINEDUNDOUNICODE
UNIONUNIQUEUNKNOWN
UNLOCKUNSIGNEDUNTIL
UPDATEUSAGEUSE
USERUSER_RESOURCESUSE_FRM
USINGUTC_DATEUTC_TIME
UTC_TIMESTAMPVALUEVALUES
VARBINARYVARCHARVARCHARACTER
VARIABLESVARYINGVIEW
WARNINGSWEEKWHEN
WHEREWHILEWITH
WORKWRITEX509
XAXORYEAR
YEAR_MONTHZEROFILL 

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

  • ACTION

  • BIT

  • DATE

  • ENUM

  • NO

  • TEXT

  • TIME

  • TIMESTAMP