| Reserved words are special. For example, function names cannot be used as identifiers such as table or column names, and an error occurs if you try to do so. The following statement fails because it attempts to create a column named order, which is erroneous because order is a reserved word (it's used in ORDER BY clauses): mysql> CREATE TABLE t (order INT NOT NULL UNIQUE, d DATE NOT NULL); ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order INT NOT NULL UNIQUE, d DATE NOT NULL)' at line 1
Similarly, the following statement fails because it uses a reserved word as an alias: mysql> SELECT 1 AS INTEGER; ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER' at line 1
The solution to these problems is to quote the identifiers properly. The rules depend on the type of identifier you're quoting: To use a reserved word as a database, table, column, or index identifier, there are either one or two allowable quoting styles, depending on the server SQL mode. By default, quoting a reserved word within backtick ('`') characters allows it to be used as an identifier: mysql> CREATE TABLE t (`order` INT NOT NULL UNIQUE, d DATE NOT NULL); Query OK, 0 rows affected (0.00 sec)
If the ANSI_QUOTES SQL mode is enabled, it's also allowable to quote using double quotes: mysql> CREATE TABLE t ("order" INT NOT NULL UNIQUE, d DATE NOT NULL); Query OK, 0 rows affected (0.00 sec)
If an identifier must be quoted in a CREATE TABLE statement, it's also necessary to quote it in any subsequent statements that refer to the identifier. To use a reserved word as an alias, quote it using either single quotes, double quotes, or backticks. The SQL mode makes no difference; it's legal to use any of the three quoting characters regardless. Thus, to use INTEGER as an alias, you can write it any of these ways: SELECT 1 AS 'INTEGER'; SELECT 1 AS "INTEGER"; SELECT 1 AS `INTEGER`;
It's a good idea to avoid using function names as identifiers. Normally, they aren't reserved, but there are circumstances under which this isn't true: Some functions have names that are also keywords and thus are reserved. CHAR() is one example. By default, a function name and the opening parenthesis that follows it must be written with no intervening space. This allows the statement parser to distinguish a name in a function invocation from the same name used for another purpose, such as an identifier. However, if the IGNORE_SPACE SQL mode is enabled, the server allows spaces between a function name and the following parenthesis. A side effect of running the server with this mode enabled is that all function names become ambiguous in certain contexts because the statement parser no longer can distinguish reliably whether a function name represents a function invocation or an identifier. Consider the following statement: INSERT INTO COUNT (id) VALUES(43);
In ignore-spaces mode, this statement might mean "create a new row in the COUNT table, setting the id column to 43," or it might simply be a malformed INSERT statement that has an invocation of the COUNT function where a table name ought to be. The parser cannot tell.
Reserved words are not case sensitive. They can be given in uppercase, lowercase, or even mixed case, and need not be written the same way throughout a query. The same is true for function names. |
