Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

9.5. 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 a reserved word such as SELECT or the name of a built-in MySQL data type or function such as TIMESTAMP or GROUP.

If an identifier is a reserved word, you must quote it as described in Section 9.2, “Database, Table, Index, Column, and Alias Names”. Exception: 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.

You are permitted to use function names as identifiers. 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);
ERROR 1064 (42000) at line 2: You have an error in your SQL
syntax ... near 'abs(val INT)'

If the IGNORE_SPACE SQL mode is enabled, 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.2.5, “The Server SQL Mode”.

The words in the following table are explicitly reserved in MySQL 5.1. At some point, you might update to a higher version, so it's 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 (currently) uses a yacc parser. A reserved word can be used as an identifier if you quote it.

ACCESSIBLE ADD ALL
ALTER ANALYZE AND
AS ASC ASENSITIVE
BEFORE BETWEEN BIGINT
BINARY BLOB BOTH
BY CALL CASCADE
CASE CHANGE CHAR
CHARACTER CHECK COLLATE
COLUMN CONDITION CONNECTION
CONSTRAINT CONTINUE CONVERT
CREATE CROSS CURRENT_DATE
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
CURSOR DATABASE DATABASES
DAY_HOUR DAY_MICROSECOND DAY_MINUTE
DAY_SECOND DEC DECIMAL
DECLARE DEFAULT DELAYED
DELETE DESC DESCRIBE
DETERMINISTIC DISTINCT DISTINCTROW
DIV DOUBLE DROP
DUAL EACH ELSE
ELSEIF ENCLOSED ESCAPED
EXISTS EXIT EXPLAIN
FALSE FETCH FLOAT
FLOAT4 FLOAT8 FOR
FORCE FOREIGN FROM
FULLTEXT GOTO GRANT
GROUP HAVING HIGH_PRIORITY
HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND
IF IGNORE IN
INDEX INFILE INNER
INOUT INSENSITIVE INSERT
INT INT1 INT2
INT3 INT4 INT8
INTEGER INTERVAL INTO
IS ITERATE JOIN
KEY KEYS KILL
LABEL LEADING LEAVE
LEFT LIKE LIMIT
LINEAR LINES LOAD
LOCALTIME LOCALTIMESTAMP LOCK
LONG LONGBLOB LONGTEXT
LOOP LOW_PRIORITY MATCH
MEDIUMBLOB MEDIUMINT MEDIUMTEXT
MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND
MOD MODIFIES NATURAL
NOT NO_WRITE_TO_BINLOG NULL
NUMERIC ON OPTIMIZE
OPTION OPTIONALLY OR
ORDER OUT OUTER
OUTFILE PRECISION PRIMARY
PROCEDURE PURGE RANGE
READ READS READ_ONLY
READ_WRITE REAL REFERENCES
REGEXP RELEASE RENAME
REPEAT REPLACE REQUIRE
RESTRICT RETURN REVOKE
RIGHT RLIKE SCHEMA
SCHEMAS SECOND_MICROSECOND SELECT
SENSITIVE SEPARATOR SET
SHOW SMALLINT SPATIAL
SPECIFIC SQL SQLEXCEPTION
SQLSTATE SQLWARNING SQL_BIG_RESULT
SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SSL
STARTING STRAIGHT_JOIN TABLE
TERMINATED THEN TINYBLOB
TINYINT TINYTEXT TO
TRAILING TRIGGER TRUE
UNDO UNION UNIQUE
UNLOCK UNSIGNED UPDATE
UPGRADE USAGE USE
USING UTC_DATE UTC_TIME
UTC_TIMESTAMP VALUES VARBINARY
VARCHAR VARCHARACTER VARYING
WHEN WHERE WHILE
WITH WRITE X509
XOR YEAR_MONTH ZEROFILL

The following are new reserved words in MySQL 5.1: ACCESSIBLE, LINEAR, RANGE, READ_ONLY, READ_WRITE.

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


 
 
  Published under the terms of the GNU General Public License Design by Interspire