Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  Language Structure  /  Keywords and Reserved Words

9.3 Keywords and Reserved Words

Keywords are words that have significance in SQL. Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions.

Nonreserved keywords are permitted as identifiers without quoting. Reserved words are permitted as identifiers if you quote them as described in Section 9.2, “Schema Object Names”:

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

BEGIN and END are keywords but not reserved, so their use as identifiers does not require quoting. INTERVAL is a reserved keyword and must be quoted to be used as an identifier:

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

Exception: A word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved:

mysql> CREATE TABLE mydb.interval (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

Names of built-in functions are permitted as identifiers but may require care to be used as such. For example, COUNT is acceptable as a column name. However, by default, no whitespace is permitted in function invocations between the function name and the following ( character. This requirement enables the parser to distinguish whether the name is used in a function call or in nonfunction context. For further details on recognition of function names, see Section 9.2.4, “Function Name Parsing and Resolution”.

The following table shows the keywords and reserved words in MySQL 5.5, along with changes to individual words from version to version. Reserved keywords are marked with (R). In addition, _FILENAME is reserved.

At some point, you might upgrade to a higher version, so it is 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 reserved 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 uses a yacc parser.

Table 9.2 Keywords and Reserved Words in MySQL 5.5

ACCESSIBLE (R) ACTION ADD (R)
AFTER AGAINST AGGREGATE
ALGORITHM ALL (R) ALTER (R)
ANALYZE (R) AND (R) ANY
AS (R) ASC (R) ASCII
ASENSITIVE (R) AT AUTHORS
AUTOEXTEND_SIZE AUTO_INCREMENT AVG
AVG_ROW_LENGTH BACKUP BEFORE (R)
BEGIN BETWEEN (R) BIGINT (R)
BINARY (R) BINLOG BIT
BLOB (R) BLOCK BOOL
BOOLEAN BOTH (R) BTREE
BY (R) BYTE CACHE
CALL (R) CASCADE (R) CASCADED
CASE (R) CATALOG_NAME CHAIN
CHANGE (R) CHANGED CHAR (R)
CHARACTER (R) CHARSET CHECK (R)
CHECKSUM CIPHER CLASS_ORIGIN
CLIENT CLOSE COALESCE
CODE COLLATE (R) COLLATION
COLUMN (R) COLUMNS COLUMN_NAME
COMMENT COMMIT COMMITTED
COMPACT COMPLETION COMPRESSED
CONCURRENT CONDITION (R) CONNECTION
CONSISTENT CONSTRAINT (R) CONSTRAINT_CATALOG
CONSTRAINT_NAME CONSTRAINT_SCHEMA CONTAINS
CONTEXT CONTINUE (R) CONTRIBUTORS
CONVERT (R) CPU CREATE (R)
CROSS (R) CUBE CURRENT_DATE (R)
CURRENT_TIME (R) CURRENT_TIMESTAMP (R) CURRENT_USER (R)
CURSOR (R) CURSOR_NAME DATA
DATABASE (R) DATABASES (R) DATAFILE
DATE DATETIME DAY
DAY_HOUR (R) DAY_MICROSECOND (R) DAY_MINUTE (R)
DAY_SECOND (R) DEALLOCATE DEC (R)
DECIMAL (R) DECLARE (R) DEFAULT (R)
DEFINER DELAYED (R) DELAY_KEY_WRITE
DELETE (R) DESC (R) DESCRIBE (R)
DES_KEY_FILE DETERMINISTIC (R) DIRECTORY
DISABLE DISCARD DISK
DISTINCT (R) DISTINCTROW (R) DIV (R)
DO DOUBLE (R) DROP (R)
DUAL (R) DUMPFILE DUPLICATE
DYNAMIC EACH (R) ELSE (R)
ELSEIF (R) ENABLE ENCLOSED (R)
END ENDS ENGINE
ENGINES ENUM ERROR[a]
ERRORS ESCAPE ESCAPED (R)
EVENT EVENTS EVERY
EXECUTE EXISTS (R) EXIT (R)
EXPANSION EXPLAIN (R) EXTENDED
EXTENT_SIZE FALSE (R) FAST
FAULTS FETCH (R) FIELDS
FILE FIRST FIXED
FLOAT (R) FLOAT4 (R) FLOAT8 (R)
FLUSH FOR (R) FORCE (R)
FOREIGN (R) FOUND FRAC_SECOND[b]
FROM (R) FULL FULLTEXT (R)
FUNCTION GENERAL[c] GEOMETRY
GEOMETRYCOLLECTION GET_FORMAT GLOBAL
GRANT (R) GRANTS GROUP (R)
HANDLER HASH HAVING (R)
HELP HIGH_PRIORITY (R) HOST
HOSTS HOUR HOUR_MICROSECOND (R)
HOUR_MINUTE (R) HOUR_SECOND (R) IDENTIFIED
IF (R) IGNORE (R) IGNORE_SERVER_IDS[d]
IMPORT IN (R) INDEX (R)
INDEXES INFILE (R) INITIAL_SIZE
INNER (R) INNOBASE[e] INNODB[f]
INOUT (R) INSENSITIVE (R) INSERT (R)
INSERT_METHOD INSTALL INT (R)
INT1 (R) INT2 (R) INT3 (R)
INT4 (R) INT8 (R) INTEGER (R)
INTERVAL (R) INTO (R) INVOKER
IO IO_THREAD IPC
IS (R) ISOLATION ISSUER
ITERATE (R) JOIN (R) KEY (R)
KEYS (R) KEY_BLOCK_SIZE KILL (R)
LANGUAGE LAST LEADING (R)
LEAVE (R) LEAVES LEFT (R)
LESS LEVEL LIKE (R)
LIMIT (R) LINEAR (R) LINES (R)
LINESTRING LIST LOAD (R)
LOCAL LOCALTIME (R) LOCALTIMESTAMP (R)
LOCK (R) LOCKS LOGFILE
LOGS LONG (R) LONGBLOB (R)
LONGTEXT (R) LOOP (R) LOW_PRIORITY (R)
MASTER MASTER_CONNECT_RETRY MASTER_HEARTBEAT_PERIOD[g]
MASTER_HOST MASTER_LOG_FILE MASTER_LOG_POS
MASTER_PASSWORD MASTER_PORT MASTER_SERVER_ID
MASTER_SSL MASTER_SSL_CA MASTER_SSL_CAPATH
MASTER_SSL_CERT MASTER_SSL_CIPHER MASTER_SSL_KEY
MASTER_SSL_VERIFY_SERVER_CERT (R) MASTER_USER MATCH (R)
MAXVALUE (R) MAX_CONNECTIONS_PER_HOUR MAX_QUERIES_PER_HOUR
MAX_ROWS MAX_SIZE MAX_UPDATES_PER_HOUR
MAX_USER_CONNECTIONS MEDIUM MEDIUMBLOB (R)
MEDIUMINT (R) MEDIUMTEXT (R) MEMORY
MERGE MESSAGE_TEXT MICROSECOND
MIDDLEINT (R) MIGRATE MINUTE
MINUTE_MICROSECOND (R) MINUTE_SECOND (R) MIN_ROWS
MOD (R) MODE MODIFIES (R)
MODIFY MONTH MULTILINESTRING
MULTIPOINT MULTIPOLYGON MUTEX
MYSQL_ERRNO NAME NAMES
NATIONAL NATURAL (R) NCHAR
NDB NDBCLUSTER NEW
NEXT NO NODEGROUP
NONE NOT (R) NO_WAIT
NO_WRITE_TO_BINLOG (R) NULL (R) NUMERIC (R)
NVARCHAR OFFSET OLD_PASSWORD
ON (R) ONE ONE_SHOT
OPEN OPTIMIZE (R) OPTION (R)
OPTIONALLY (R) OPTIONS OR (R)
ORDER (R) OUT (R) OUTER (R)
OUTFILE (R) OWNER PACK_KEYS
PAGE PARSER PARTIAL
PARTITION PARTITIONING PARTITIONS
PASSWORD PHASE PLUGIN
PLUGINS POINT POLYGON
PORT PRECISION (R) PREPARE
PRESERVE PREV PRIMARY (R)
PRIVILEGES PROCEDURE (R) PROCESSLIST
PROFILE PROFILES PROXY[h]
PURGE (R) QUARTER QUERY
QUICK RANGE (R) READ (R)
READS (R) READ_ONLY READ_WRITE (R)
REAL (R) REBUILD RECOVER
REDOFILE REDO_BUFFER_SIZE REDUNDANT
REFERENCES (R) REGEXP (R) RELAY[i]
RELAYLOG RELAY_LOG_FILE RELAY_LOG_POS
RELAY_THREAD RELEASE (R) RELOAD
REMOVE RENAME (R) REORGANIZE
REPAIR REPEAT (R) REPEATABLE
REPLACE (R) REPLICATION REQUIRE (R)
RESET RESIGNAL (R) RESTORE
RESTRICT (R) RESUME RETURN (R)
RETURNS REVOKE (R) RIGHT (R)
RLIKE (R) ROLLBACK ROLLUP
ROUTINE ROW ROWS
ROW_FORMAT RTREE SAVEPOINT
SCHEDULE SCHEMA (R) SCHEMAS (R)
SCHEMA_NAME SECOND SECOND_MICROSECOND (R)
SECURITY SELECT (R) SENSITIVE (R)
SEPARATOR (R) SERIAL SERIALIZABLE
SERVER SESSION SET (R)
SHARE SHOW (R) SHUTDOWN
SIGNAL (R) SIGNED SIMPLE
SLAVE SLOW[j] SMALLINT (R)
SNAPSHOT SOCKET SOME
SONAME SOUNDS SOURCE
SPATIAL (R) SPECIFIC (R) SQL (R)
SQLEXCEPTION (R) SQLSTATE (R) SQLWARNING (R)
SQL_BIG_RESULT (R) SQL_BUFFER_RESULT SQL_CACHE
SQL_CALC_FOUND_ROWS (R) SQL_NO_CACHE SQL_SMALL_RESULT (R)
SQL_THREAD SQL_TSI_DAY SQL_TSI_FRAC_SECOND[k]
SQL_TSI_HOUR SQL_TSI_MINUTE SQL_TSI_MONTH
SQL_TSI_QUARTER SQL_TSI_SECOND SQL_TSI_WEEK
SQL_TSI_YEAR SSL (R) START
STARTING (R) STARTS STATUS
STOP STORAGE STRAIGHT_JOIN (R)
STRING SUBCLASS_ORIGIN SUBJECT
SUBPARTITION SUBPARTITIONS SUPER
SUSPEND SWAPS SWITCHES
TABLE (R) TABLES TABLESPACE
TABLE_CHECKSUM TABLE_NAME TEMPORARY
TEMPTABLE TERMINATED (R) TEXT
THAN THEN (R) TIME
TIMESTAMP TIMESTAMPADD TIMESTAMPDIFF
TINYBLOB (R) TINYINT (R) TINYTEXT (R)
TO (R) TRAILING (R) TRANSACTION
TRIGGER (R) TRIGGERS TRUE (R)
TRUNCATE TYPE TYPES
UNCOMMITTED UNDEFINED UNDO (R)
UNDOFILE UNDO_BUFFER_SIZE UNICODE
UNINSTALL UNION (R) UNIQUE (R)
UNKNOWN UNLOCK (R) UNSIGNED (R)
UNTIL UPDATE (R) UPGRADE
USAGE (R) USE (R) USER
USER_RESOURCES USE_FRM USING (R)
UTC_DATE (R) UTC_TIME (R) UTC_TIMESTAMP (R)
VALUE VALUES (R) VARBINARY (R)
VARCHAR (R) VARCHARACTER (R) VARIABLES
VARYING (R) VIEW WAIT
WARNINGS WEEK WHEN (R)
WHERE (R) WHILE (R) WITH (R)
WORK WRAPPER WRITE (R)
X509 XA XML
XOR (R) YEAR YEAR_MONTH (R)
ZEROFILL (R)    

[a] ERROR: added in 5.5.3 (nonreserved)

[b] FRAC_SECOND: removed in 5.5.3

[c] GENERAL: added in 5.5.3 (reserved); became nonreserved in 5.5.8

[d] IGNORE_SERVER_IDS: became nonreserved in 5.5.8

[e] INNOBASE: removed in 5.5.3

[f] INNODB: removed in 5.5.3

[g] MASTER_HEARTBEAT_PERIOD: became nonreserved in 5.5.8

[h] PROXY: added in 5.5.7 (nonreserved)

[i] RELAY: added in 5.5.3 (nonreserved)

[j] SLOW: added in 5.5.3 (reserved); became nonreserved in 5.5.8

[k] SQL_TSI_FRAC_SECOND: removed in 5.5.3


The following table shows the keywords and reserved words that are added in MySQL 5.5. Reserved keywords are marked with (R).

Table 9.3 Keywords and Reserved Words Added in MySQL 5.5 Compared to MySQL 5.1

CATALOG_NAME CLASS_ORIGIN COLUMN_NAME
CONSTRAINT_CATALOG CONSTRAINT_NAME CONSTRAINT_SCHEMA
CURSOR_NAME ERROR GENERAL
IGNORE_SERVER_IDS MASTER_HEARTBEAT_PERIOD MESSAGE_TEXT
MYSQL_ERRNO PROXY RELAY
RELAYLOG RESIGNAL (R) SCHEMA_NAME
SIGNAL (R) SLOW SUBCLASS_ORIGIN
TABLE_NAME XML  

The following table shows the keywords and reserved words that are removed in MySQL 5.5. Reserved keywords are marked with (R).

Table 9.4 Keywords and Reserved Words Removed in MySQL 5.5 Compared to MySQL 5.1

FRAC_SECOND INNOBASE INNODB
SQL_TSI_FRAC_SECOND    


User Comments
Sign Up Login You must be logged in to post a comment.