6.1.7 Is MySQL Picky About Reserved Words?

A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as a column name). However, by default, in function invocations no whitespace is allowed between the function name and the following `(' character, so that a function call can be distinguished from a reference to a column name.

If you start the server with the --ansi or --sql-mode=IGNORE_SPACE option, 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, column names that are the same as function names must be quoted as described in section 6.1.2 Database, Table, Index, Column, and Alias Names.

The following words are explicitly reserved in MySQL. Most of them are forbidden by SQL-92 as column and/or table names (for example, GROUP). A few are reserved because MySQL needs them and is (currently) using a yacc parser:

The following symbols (from the table above) are disallowed by SQL-99 but allowed by MySQL as column/table names. This is because some of these names are very natural names and a lot of people have already used them.

• ACTION
• BIT
• DATE
• ENUM
• NO
• TEXT
• TIME
• TIMESTAMP

User Comments

The way around this is to place backticks (`name`) around the table name and relevant column names, for example:

CREATE TABLE `group` (
ID CHAR(13) NOT NULL,
createdByID CHAR(13) NOT NULL,
`read` CHAR(1) NULL,
PRIMARY KEY (ID)
)

As a rule, I use backticks on all table names and all column references anyway to avoid any suprises.

The better way around it is to stick with the ANSI standard double-quotes, not backticks.

Also, if you have a double-quote in the variable name, you just use two double-quotes, e.g. Annoying "variable name" becomes "Annoying ""variable name""".

Backticks are the MySQL style of quoting table and column names. Only if you are running in ANSI mode should you use double-quotes.

The underscore ("_") is a 'reserved character' since there is support for using it as a wildcard charcter for database names. Here is an example:

mysql> SHOW DATABASES LIKE 'm_sql'\G
*************************** 1. row ***************************
Database (m_sql): mysql
1 row in set (0.00 sec)

Wildcard characters are discussed here:
http://www.mysql.com/doc/en/String_comparison_functions.html

Add your own comment.