6.5 Using Reserved Words as Identifiers

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: 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, this statement fails because it uses a reserved word as an alias:


mysql> SELECT 1 AS INTEGER;

ERROR 1064: 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. Quoting 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 mode in which the server is running. 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 server was started with the --ansi or --sql-mode=ANSI_QUOTES option, 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 server mode doesn't make any difference; all three are legal in any mode.) To use INTEGER as an alias, you can write it like this:
```
mysql> SELECT 1 AS 'INTEGER';

+---------+

| INTEGER |

+---------+

|       1 |

+---------+
```
It can also be written in either of these ways, with the same result:
```
SELECT 1 AS "INTEGER";

SELECT 1 AS `INTEGER`;
```

Quoting is useful not only for identifiers that are reserved words; a given name might be illegal because it contains characters that normally are not allowed in names, such as dashes or spaces. Such names can be used as identifiers by applying the same quoting rules. If you aren't sure whether a name is legal, quote it. It's harmless to put quotes around a name that's legal without them. For a discussion of the legal syntax for identifiers, see Chapter 4, "Data Definition Language."

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 query parser to distinguish a name in a function invocation from the same name used for another purpose, such as an identifier. However, the server may be run in a mode that allows spaces between function names and the following parenthesis. (This happens if you start it with the --ansi or --sql-mode=IGNORE_SPACE option.) A side effect of running the server in this mode is that all function names become ambiguous in certain contexts because the query parser no longer can distinguish reliably whether a function name represents a function invocation or an identifier. Consider the following query:
```
INSERT INTO COUNT (id) VALUES(43);
```
In ignore-spaces mode, this query 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.

If you do want to use an identifier that's a function name, it should be quoted to prevent the possibility of it being interpreted as a reserved word.

< Day Day Up >