MySQL 5.0 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.3, “Function Name Parsing and Resolution”.

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

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.0

ACTION ADD (R) AFTER
AGAINST AGGREGATE ALGORITHM
ALL (R) ALTER (R) ANALYZE (R)
AND (R) ANY AS (R)
ASC (R) ASCII ASENSITIVE (R)
AUTO_INCREMENT AVG AVG_ROW_LENGTH
BACKUP BDB BEFORE (R)
BEGIN BERKELEYDB 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) CHAIN
CHANGE (R) CHANGED CHAR (R)
CHARACTER (R) CHARSET CHECK (R)
CHECKSUM CIPHER CLIENT
CLOSE CODE COLLATE (R)
COLLATION COLUMN (R) COLUMNS
COMMENT COMMIT COMMITTED
COMPACT COMPRESSED CONCURRENT
CONDITION (R) CONNECTION CONSISTENT
CONSTRAINT (R) CONTAINS CONTEXT
CONTINUE (R) CONVERT (R) CPU
CREATE (R) CROSS (R) CUBE
CURRENT_DATE (R) CURRENT_TIME (R) CURRENT_TIMESTAMP (R)
CURRENT_USER (R) CURSOR (R) DATA
DATABASE (R) DATABASES (R) 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 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 ENGINE
ENGINES ENUM ERRORS
ESCAPE ESCAPED (R) EVENTS
EXECUTE EXISTS (R) EXIT (R)
EXPANSION EXPLAIN (R) EXTENDED
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 FROM (R)
FULL FULLTEXT (R) FUNCTION
GEOMETRY GEOMETRYCOLLECTION GET_FORMAT
GLOBAL GRANT (R) GRANTS
GROUP (R) HANDLER HASH
HAVING (R) HELP HIGH_PRIORITY (R)
HOSTS HOUR HOUR_MICROSECOND (R)
HOUR_MINUTE (R) HOUR_SECOND (R) IDENTIFIED
IF (R) IGNORE (R) IMPORT
IN (R) INDEX (R) INDEXES
INFILE (R) INNER (R) INNOBASE
INNODB INOUT (R) INSENSITIVE (R)
INSERT (R) INSERT_METHOD 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) KILL (R) LANGUAGE
LAST LEADING (R) LEAVE (R)
LEAVES LEFT (R) LEVEL
LIKE (R) LIMIT (R) LINES (R)
LINESTRING LOAD (R) LOCAL
LOCALTIME (R) LOCALTIMESTAMP (R) LOCK (R)
LOCKS 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_USER MATCH (R) MAX_CONNECTIONS_PER_HOUR
MAX_QUERIES_PER_HOUR MAX_ROWS MAX_UPDATES_PER_HOUR
MAX_USER_CONNECTIONS MEDIUM MEDIUMBLOB (R)
MEDIUMINT (R) MEDIUMTEXT (R) MEMORY
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
NONE NOT (R) 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)
OR (R) ORDER (R) OUT (R)
OUTER (R) OUTFILE (R) PACK_KEYS
PAGE PARTIAL PASSWORD
PHASE POINT POLYGON
PRECISION (R) PREPARE PREV
PRIMARY (R) PRIVILEGES PROCEDURE (R)
PROCESSLIST PROFILE PROFILES
PURGE (R) QUARTER QUERY
QUICK RAID0 RAID_CHUNKS
RAID_CHUNKSIZE RAID_TYPE READ (R)
READS (R) REAL (R) RECOVER
REDUNDANT REFERENCES (R) REGEXP (R)
RELAY_LOG_FILE RELAY_LOG_POS RELAY_THREAD
RELEASE (R) RELOAD RENAME (R)
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 SCHEMA (R)
SCHEMAS (R) SECOND SECOND_MICROSECOND (R)
SECURITY SELECT (R) SENSITIVE (R)
SEPARATOR (R) SERIAL SERIALIZABLE
SESSION SET (R) SHARE
SHOW (R) SHUTDOWN SIGNED
SIMPLE SLAVE SMALLINT (R)
SNAPSHOT SOME SONAME (R)
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 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)
STATUS STOP STORAGE
STRAIGHT_JOIN (R) STRING STRIPED
SUBJECT SUPER SUSPEND
SWAPS SWITCHES TABLE (R)
TABLES TABLESPACE TEMPORARY
TEMPTABLE TERMINATED (R) TEXT
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) UNICODE
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 WARNINGS WEEK
WHEN (R) WHERE (R) WHILE (R)
WITH (R) WORK WRITE (R)
X509 XA XOR (R)
YEAR YEAR_MONTH (R) ZEROFILL (R)

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

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

ALGORITHM ASENSITIVE (R) BLOCK
CALL (R) CASCADED CHAIN
CODE COMPACT CONDITION (R)
CONNECTION CONTAINS CONTEXT
CONTINUE (R) CPU CURSOR (R)
DECLARE (R) DEFINER DETERMINISTIC (R)
EACH (R) ELSEIF (R) EXIT (R)
FAULTS FETCH (R) FOUND
FRAC_SECOND INOUT (R) INSENSITIVE (R)
INVOKER IO IPC
ITERATE (R) LANGUAGE LEAVE (R)
LOOP (R) MAX_USER_CONNECTIONS MEMORY
MERGE MIGRATE MODIFIES (R)
MUTEX NAME ONE
OUT (R) PAGE PHASE
PROFILE PROFILES QUARTER
READS (R) RECOVER REDUNDANT
RELEASE (R) REPEAT (R) RESUME
RETURN (R) ROUTINE SCHEMA (R)
SCHEMAS (R) SECURITY SENSITIVE (R)
SOURCE SPECIFIC (R) SQL (R)
SQLEXCEPTION (R) SQLSTATE (R) SQLWARNING (R)
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
SUSPEND SWAPS SWITCHES
TEMPTABLE TIMESTAMPADD TIMESTAMPDIFF
TRIGGER (R) TRIGGERS UNDEFINED
UNDO (R) UNKNOWN UPGRADE
VIEW WEEK WHILE (R)
XA


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