MySQL 5.1 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.1, 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.1

ACCESSIBLE[a] (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[b] AUTHORS
AUTOEXTEND_SIZE[c] AUTO_INCREMENT AVG
AVG_ROW_LENGTH BACKUP BDB[d]
BEFORE (R) BEGIN BERKELEYDB[e]
BETWEEN (R) BIGINT (R) BINARY (R)
BINLOG BIT BLOB (R)
BLOCK[f] BOOL BOOLEAN
BOTH (R) BTREE BY (R)
BYTE CACHE CALL (R)
CASCADE (R) CASCADED CASE (R)
CHAIN CHANGE (R) CHANGED
CHAR (R) CHARACTER (R) CHARSET
CHECK (R) CHECKSUM CIPHER
CLIENT CLOSE COALESCE
CODE COLLATE (R) COLLATION
COLUMN (R) COLUMNS COMMENT
COMMIT COMMITTED COMPACT
COMPLETION[g] COMPRESSED CONCURRENT
CONDITION (R) CONNECTION[h] CONSISTENT
CONSTRAINT (R) CONTAINS CONTEXT[i]
CONTINUE (R) CONTRIBUTORS[j] CONVERT (R)
CPU[k] CREATE (R) CROSS (R)
CUBE CURRENT_DATE (R) CURRENT_TIME (R)
CURRENT_TIMESTAMP (R) CURRENT_USER (R) CURSOR (R)
DATA DATABASE (R) DATABASES (R)
DATAFILE[l] 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[m] 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[n]
ENGINE ENGINES ENUM
ERRORS ESCAPE ESCAPED (R)
EVENT[o] EVENTS EVERY[p]
EXECUTE EXISTS (R) EXIT (R)
EXPANSION EXPLAIN (R) EXTENDED
EXTENT_SIZE[q] FALSE (R) FAST
FAULTS[r] FETCH (R) FIELDS
FILE FIRST FIXED
FLOAT (R) FLOAT4 (R) FLOAT8 (R)
FLUSH FOR (R) FORCE (R)
FOREIGN (R) FOUND FRAC_SECOND
FROM (R) FULL FULLTEXT (R)
FUNCTION GEOMETRY GEOMETRYCOLLECTION
GET_FORMAT GLOBAL GOTO[s]
GRANT (R) GRANTS GROUP (R)
HANDLER HASH HAVING (R)
HELP HIGH_PRIORITY (R) HOST[t]
HOSTS HOUR HOUR_MICROSECOND (R)
HOUR_MINUTE (R) HOUR_SECOND (R) IDENTIFIED
IF (R) IGNORE (R) IMPORT
IN (R) INDEX (R) INDEXES
INFILE (R) INITIAL_SIZE[u] INNER (R)
INNOBASE INNODB 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[v]
IO_THREAD IPC[w] IS (R)
ISOLATION ISSUER ITERATE (R)
JOIN (R) KEY (R) KEYS (R)
KEY_BLOCK_SIZE[x] KILL (R) LABEL[y]
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[z]
LOGS LONG (R) LONGBLOB (R)
LONGTEXT (R) LOOP (R) LOW_PRIORITY (R)
MASTER MASTER_CONNECT_RETRY 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[aa] (R)
MASTER_USER MATCH (R) MAXVALUE
MAX_CONNECTIONS_PER_HOUR MAX_QUERIES_PER_HOUR MAX_ROWS
MAX_SIZE[ab] MAX_UPDATES_PER_HOUR MAX_USER_CONNECTIONS
MEDIUM MEDIUMBLOB (R) MEDIUMINT (R)
MEDIUMTEXT (R) MEMORY[ac] MERGE
MICROSECOND MIDDLEINT (R) MIGRATE
MINUTE MINUTE_MICROSECOND (R) MINUTE_SECOND (R)
MIN_ROWS MOD (R) MODE
MODIFIES (R) MODIFY MONTH
MULTILINESTRING MULTIPOINT MULTIPOLYGON
MUTEX NAME NAMES
NATIONAL NATURAL (R) NCHAR
NDB NDBCLUSTER NEW
NEXT NO NODEGROUP
NONE NOT (R) NO_WAIT[ad]
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[ae] OR (R)
ORDER (R) OUT (R) OUTER (R)
OUTFILE (R) OWNER[af] PACK_KEYS
PAGE[ag] PAGE_CHECKSUM[ah] PARSER
PARTIAL PARTITION PARTITIONING[ai]
PARTITIONS PASSWORD PHASE
PLUGIN PLUGINS[aj] POINT
POLYGON PORT[ak] PRECISION (R)
PREPARE PRESERVE[al] PREV
PRIMARY (R) PRIVILEGES PROCEDURE (R)
PROCESSLIST PROFILE[am] PROFILES[an]
PURGE (R) QUARTER QUERY
QUICK RAID0[ao] RAID_CHUNKS[ap]
RAID_CHUNKSIZE[aq] RAID_TYPE[ar] RANGE (R)
READ (R) READS (R) READ_ONLY[as]
READ_WRITE[at] (R) REAL (R) REBUILD[au]
RECOVER REDOFILE[av] REDO_BUFFER_SIZE[aw]
REDUNDANT REFERENCES (R) REGEXP (R)
RELAY_LOG_FILE RELAY_LOG_POS RELAY_THREAD
RELEASE (R) RELOAD REMOVE[ax]
RENAME (R) REORGANISE[ay] REORGANIZE[az]
REPAIR REPEAT (R) REPEATABLE
REPLACE (R) REPLICATION REQUIRE (R)
RESET RESTORE RESTRICT (R)
RESUME RETURN (R) RETURNS
REVOKE (R) RIGHT (R) RLIKE (R)
ROLLBACK ROLLUP ROUTINE
ROW ROWS ROW_FORMAT
RTREE SAVEPOINT SCHEDULE[ba]
SCHEDULER[bb] SCHEMA (R) SCHEMAS (R)
SECOND SECOND_MICROSECOND (R) SECURITY
SELECT (R) SENSITIVE (R) SEPARATOR (R)
SERIAL SERIALIZABLE SERVER[bc]
SESSION SET (R) SHARE
SHOW (R) SHUTDOWN SIGNED
SIMPLE SLAVE SMALLINT (R)
SNAPSHOT SOCKET[bd] SOME
SONAME SOUNDS SOURCE[be]
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
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[bf] STATUS
STOP STORAGE STRAIGHT_JOIN (R)
STRING STRIPED[bg] SUBJECT
SUBPARTITION SUBPARTITIONS SUPER
SUSPEND SWAPS[bh] SWITCHES[bi]
TABLE (R) TABLES TABLESPACE
TABLE_CHECKSUM[bj] TEMPORARY TEMPTABLE
TERMINATED (R) TEXT THAN
THEN (R) TIME TIMESTAMP
TIMESTAMPADD TIMESTAMPDIFF TINYBLOB (R)
TINYINT (R) TINYTEXT (R) TO (R)
TRAILING (R) TRANSACTION TRANSACTIONAL[bk]
TRIGGER (R) TRIGGERS TRUE (R)
TRUNCATE TYPE TYPES
UNCOMMITTED UNDEFINED UNDO (R)
UNDOFILE[bl] UNDO_BUFFER_SIZE[bm] UNICODE
UNINSTALL UNION (R) UNIQUE (R)
UNKNOWN UNLOCK (R) UNSIGNED (R)
UNTIL UPDATE (R) UPGRADE[bn]
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[bo]
WARNINGS WEEK WHEN (R)
WHERE (R) WHILE (R) WITH (R)
WORK WRAPPER[bp] WRITE (R)
X509 XA XOR (R)
YEAR YEAR_MONTH (R) ZEROFILL (R)

[a] ACCESSIBLE: added in 5.1.6 (reserved)

[b] AT: added in 5.1.6 (nonreserved)

[c] AUTOEXTEND_SIZE: added in 5.1.6 (nonreserved)

[d] BDB: removed in 5.1.12

[e] BERKELEYDB: removed in 5.1.12

[f] BLOCK: added in 5.1.24 (nonreserved)

[g] COMPLETION: added in 5.1.6 (nonreserved)

[h] CONNECTION: became nonreserved in 5.1.16

[i] CONTEXT: added in 5.1.24 (nonreserved)

[j] CONTRIBUTORS: added in 5.1.12 (nonreserved)

[k] CPU: added in 5.1.24 (nonreserved)

[l] DATAFILE: added in 5.1.6 (nonreserved)

[m] DISK: added in 5.1.6 (nonreserved)

[n] ENDS: added in 5.1.6 (nonreserved)

[o] EVENT: added in 5.1.6 (nonreserved)

[p] EVERY: added in 5.1.6 (nonreserved)

[q] EXTENT_SIZE: added in 5.1.6 (nonreserved)

[r] FAULTS: added in 5.1.24 (nonreserved)

[s] GOTO: removed in 5.1.11

[t] HOST: added in 5.1.15 (nonreserved)

[u] INITIAL_SIZE: added in 5.1.6 (nonreserved)

[v] IO: added in 5.1.24 (nonreserved)

[w] IPC: added in 5.1.24 (nonreserved)

[x] KEY_BLOCK_SIZE: added in 5.1.11 (nonreserved)

[y] LABEL: removed in 5.1.11

[z] LOGFILE: added in 5.1.6 (nonreserved)

[aa] MASTER_SSL_VERIFY_SERVER_CERT: added in 5.1.18 (reserved)

[ab] MAX_SIZE: added in 5.1.6 (nonreserved)

[ac] MEMORY: added in 5.1.6 (nonreserved)

[ad] NO_WAIT: added in 5.1.6 (nonreserved)

[ae] OPTIONS: added in 5.1.15 (nonreserved)

[af] OWNER: added in 5.1.15 (nonreserved)

[ag] PAGE: added in 5.1.24 (nonreserved)

[ah] PAGE_CHECKSUM: added in 5.1.24 (nonreserved); removed in 5.1.38

[ai] PARTITIONING: added in 5.1.9 (nonreserved)

[aj] PLUGINS: added in 5.1.9 (nonreserved)

[ak] PORT: added in 5.1.15 (nonreserved)

[al] PRESERVE: added in 5.1.6 (nonreserved)

[am] PROFILE: added in 5.1.24 (nonreserved)

[an] PROFILES: added in 5.1.24 (nonreserved)

[ao] RAID0: removed in 5.1.7

[ap] RAID_CHUNKS: removed in 5.1.7

[aq] RAID_CHUNKSIZE: removed in 5.1.7

[ar] RAID_TYPE: removed in 5.1.7

[as] READ_ONLY: added in 5.1.6 (reserved); became nonreserved in 5.1.24

[at] READ_WRITE: added in 5.1.6 (reserved)

[au] REBUILD: added in 5.1.6 (nonreserved)

[av] REDOFILE: added in 5.1.6 (nonreserved)

[aw] REDO_BUFFER_SIZE: added in 5.1.6 (nonreserved)

[ax] REMOVE: added in 5.1.9 (nonreserved)

[ay] REORGANISE: removed in 5.1.6

[az] REORGANIZE: added in 5.1.6 (nonreserved)

[ba] SCHEDULE: added in 5.1.6 (nonreserved)

[bb] SCHEDULER: added in 5.1.11 (nonreserved); removed in 5.1.12

[bc] SERVER: added in 5.1.15 (nonreserved)

[bd] SOCKET: added in 5.1.15 (nonreserved)

[be] SOURCE: added in 5.1.24 (nonreserved)

[bf] STARTS: added in 5.1.6 (nonreserved)

[bg] STRIPED: removed in 5.1.7

[bh] SWAPS: added in 5.1.24 (nonreserved)

[bi] SWITCHES: added in 5.1.24 (nonreserved)

[bj] TABLE_CHECKSUM: added in 5.1.24 (nonreserved)

[bk] TRANSACTIONAL: added in 5.1.24 (nonreserved); removed in 5.1.38

[bl] UNDOFILE: added in 5.1.6 (nonreserved)

[bm] UNDO_BUFFER_SIZE: added in 5.1.6 (nonreserved)

[bn] UPGRADE: added in 5.1.7 (reserved); became nonreserved in 5.1.12

[bo] WAIT: added in 5.1.6 (nonreserved)

[bp] WRAPPER: added in 5.1.15 (nonreserved)


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

Table 9.3 New Keywords and Reserved Words in MySQL 5.1 compared to MySQL 5.0

ACCESSIBLE (R) AT AUTHORS
AUTOEXTEND_SIZE COALESCE COMPLETION
CONTRIBUTORS DATAFILE DISK
ENDS EVENT EVERY
EXTENT_SIZE HOST INITIAL_SIZE
INSTALL KEY_BLOCK_SIZE LESS
LINEAR (R) LIST LOGFILE
MASTER_SSL_VERIFY_SERVER_CERT (R) MAXVALUE MAX_SIZE
NODEGROUP NO_WAIT OPTIONS
OWNER PARSER PARTITION
PARTITIONING PARTITIONS PLUGIN
PLUGINS PORT PRESERVE
RANGE (R) READ_ONLY READ_WRITE (R)
REBUILD REDOFILE REDO_BUFFER_SIZE
REMOVE REORGANIZE SCHEDULE
SERVER SOCKET STARTS
SUBPARTITION SUBPARTITIONS TABLE_CHECKSUM
THAN UNDOFILE UNDO_BUFFER_SIZE
UNINSTALL WAIT WRAPPER


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