Certain words 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.
Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Database, Table, Index, Column, and Alias 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)' 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 allowed 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 detail on
recognition of function names, see
Section 8.2.3, “Function Name Parsing and Resolution”.
The words in the following table are explicitly reserved in MySQL
4.1. 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 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. A reserved word
can be used as an identifier if you quote it.
ADD |
ALL |
ALTER |
ANALYZE |
AND |
AS |
ASC |
BEFORE |
BETWEEN |
BIGINT |
BINARY |
BLOB |
BOTH |
BY |
CASCADE |
CASE |
CHANGE |
CHAR |
CHARACTER |
CHECK |
COLLATE |
COLUMN |
COLUMNS |
CONSTRAINT |
CONVERT |
CREATE |
CROSS |
CURRENT_DATE |
CURRENT_TIME |
CURRENT_TIMESTAMP |
CURRENT_USER |
DATABASE |
DATABASES |
DAY_HOUR |
DAY_MICROSECOND |
DAY_MINUTE |
DAY_SECOND |
DEC |
DECIMAL |
DEFAULT |
DELAYED |
DELETE |
DESC |
DESCRIBE |
DISTINCT |
DISTINCTROW |
DIV |
DOUBLE |
DROP |
DUAL |
ELSE |
ENCLOSED |
ESCAPED |
EXISTS |
EXPLAIN |
FALSE |
FIELDS |
FLOAT |
FLOAT4 |
FLOAT8 |
FOR |
FORCE |
FOREIGN |
FROM |
FULLTEXT |
GRANT |
GROUP |
HAVING |
HIGH_PRIORITY |
HOUR_MICROSECOND |
HOUR_MINUTE |
HOUR_SECOND |
IF |
IGNORE |
IN |
INDEX |
INFILE |
INNER |
INSERT |
INT |
INT1 |
INT2 |
INT3 |
INT4 |
INT8 |
INTEGER |
INTERVAL |
INTO |
IS |
JOIN |
KEY |
KEYS |
KILL |
LEADING |
LEFT |
LIKE |
LIMIT |
LINES |
LOAD |
LOCALTIME |
LOCALTIMESTAMP |
LOCK |
LONG |
LONGBLOB |
LONGTEXT |
LOW_PRIORITY |
MATCH |
MEDIUMBLOB |
MEDIUMINT |
MEDIUMTEXT |
MIDDLEINT |
MINUTE_MICROSECOND |
MINUTE_SECOND |
MOD |
NATURAL |
NOT |
NO_WRITE_TO_BINLOG |
NULL |
NUMERIC |
ON |
OPTIMIZE |
OPTION |
OPTIONALLY |
OR |
ORDER |
OUTER |
OUTFILE |
PRECISION |
PRIMARY |
PRIVILEGES |
PROCEDURE |
PURGE |
READ |
REAL |
REFERENCES |
REGEXP |
RENAME |
REPLACE |
REQUIRE |
RESTRICT |
REVOKE |
RIGHT |
RLIKE |
SECOND_MICROSECOND |
SELECT |
SEPARATOR |
SET |
SHOW |
SMALLINT |
SONAME |
SPATIAL |
SQL_BIG_RESULT |
SQL_CALC_FOUND_ROWS |
SQL_SMALL_RESULT |
SSL |
STARTING |
STRAIGHT_JOIN |
TABLE |
TABLES |
TERMINATED |
THEN |
TINYBLOB |
TINYINT |
TINYTEXT |
TO |
TRAILING |
TRUE |
UNION |
UNIQUE |
UNLOCK |
UNSIGNED |
UPDATE |
USAGE |
USE |
USING |
UTC_DATE |
UTC_TIME |
UTC_TIMESTAMP |
VALUES |
VARBINARY |
VARCHAR |
VARCHARACTER |
VARYING |
WHEN |
WHERE |
WITH |
WRITE |
XOR |
YEAR_MONTH |
ZEROFILL |
The following are new reserved words in MySQL 4.0:
CHECK |
FORCE |
LOCALTIME |
LOCALTIMESTAMP |
REQUIRE |
SQL_CALC_FOUND_ROWS |
SSL |
XOR |
The following are new reserved words in MySQL 4.1:
BEFORE |
COLLATE |
CONVERT |
CURRENT_USER |
DAY_MICROSECOND |
DIV |
DUAL |
FALSE |
HOUR_MICROSECOND |
MINUTE_MICROSECOND |
MOD |
NO_WRITE_TO_BINLOG |
SECOND_MICROSECOND |
SEPARATOR |
SPATIAL |
TRUE |
UTC_DATE |
UTC_TIME |
UTC_TIMESTAMP |
VARCHARACTER |
MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:
User Comments
Note that escaping your identifiers in MySQL way using backticks decreases portability on a plain place.
I would either use double quotes with ANSI SQL mode enabled, or just give my variables names which are unlikely to be become reserved in future.
If you want to check if you have named your table or column with a reserved word here is a script/process that will get you there:
As root: First create a test database and a table in that database to save all the reserved words. (You could probably use an existing schema)
---------------
create database mytest;
use mytest;
create table reserved_words (reserved_word varchar(50));
create index reserved_words_1ix on reserved_words(reserved_word);
insert into reserved_words (reserved_word) values
('ACCESSIBLE'),
('ALTER'),
('AS'),
('BEFORE'),
('BINARY'),
('BY'),
('CASE'),
('CHARACTER'),
('COLUMN'),
('CONTINUE'),
('CROSS'),
('CURRENT_TIMESTAMP'),
('DATABASE'),
('DAY_MICROSECOND'),
('DEC'),
('DEFAULT'),
('DESC'),
('DISTINCT'),
('DOUBLE'),
('EACH'),
('ENCLOSED'),
('EXIT'),
('FETCH'),
('FLOAT8'),
('FOREIGN'),
('GRANT'),
('HIGH_PRIORITY'),
('HOUR_SECOND'),
('IN'),
('INNER'),
('INSERT'),
('INT2'),
('INT8'),
('INTO'),
('JOIN'),
('KILL'),
('LEFT'),
('LINEAR'),
('LOCALTIME'),
('LONG'),
('LOOP'),
('MATCH'),
('MEDIUMTEXT'),
('MINUTE_SECOND'),
('NATURAL'),
('NULL'),
('OPTIMIZE'),
('OR'),
('OUTER'),
('PRIMARY'),
('RANGE'),
('READ_WRITE'),
('REGEXP'),
('REPEAT'),
('RESTRICT'),
('RIGHT'),
('SCHEMAS'),
('SENSITIVE'),
('SHOW'),
('SPECIFIC'),
('SQLSTATE'),
('SQL_CALC_FOUND_ROWS'),
('STARTING'),
('TERMINATED'),
('TINYINT'),
('TRAILING'),
('UNDO'),
('UNLOCK'),
('USAGE'),
('UTC_DATE'),
('VALUES'),
('VARCHARACTER'),
('WHERE'),
('WRITE'),
('ZEROFILL'),
('ALL'),
('AND'),
('ASENSITIVE'),
('BIGINT'),
('BOTH'),
('CASCADE'),
('CHAR'),
('COLLATE'),
('CONSTRAINT'),
('CREATE'),
('CURRENT_TIME'),
('CURSOR'),
('DAY_HOUR'),
('DAY_SECOND'),
('DECLARE'),
('DELETE'),
('DETERMINISTIC'),
('DIV'),
('DUAL'),
('ELSEIF'),
('EXISTS'),
('FALSE'),
('FLOAT4'),
('FORCE'),
('FULLTEXT'),
('HAVING'),
('HOUR_MINUTE'),
('IGNORE'),
('INFILE'),
('INSENSITIVE'),
('INT1'),
('INT4'),
('INTERVAL'),
('ITERATE'),
('KEYS'),
('LEAVE'),
('LIMIT'),
('LOAD'),
('LOCK'),
('LONGTEXT'),
('MASTER_SSL_VERIFY_SERVER_CERT'),
('MEDIUMINT'),
('MINUTE_MICROSECOND'),
('MODIFIES'),
('NO_WRITE_TO_BINLOG'),
('ON'),
('OPTIONALLY'),
('OUT'),
('PRECISION'),
('PURGE'),
('READS'),
('REFERENCES'),
('RENAME'),
('REQUIRE'),
('REVOKE'),
('SCHEMA'),
('SELECT'),
('SET'),
('SPATIAL'),
('SQLEXCEPTION'),
('SQL_BIG_RESULT'),
('SSL'),
('TABLE'),
('TINYBLOB'),
('TO'),
('TRUE'),
('UNIQUE'),
('UPDATE'),
('USING'),
('UTC_TIMESTAMP'),
('VARCHAR'),
('WHEN'),
('WITH'),
('YEAR_MONTH'),
('ADD'),
('ANALYZE'),
('ASC'),
('BETWEEN'),
('BLOB'),
('CALL'),
('CHANGE'),
('CHECK'),
('CONDITION'),
('CONVERT'),
('CURRENT_DATE'),
('CURRENT_USER'),
('DATABASES'),
('DAY_MINUTE'),
('DECIMAL'),
('DELAYED'),
('DESCRIBE'),
('DISTINCTROW'),
('DROP'),
('ELSE'),
('ESCAPED'),
('EXPLAIN'),
('FLOAT'),
('FOR'),
('FROM'),
('GROUP'),
('HOUR_MICROSECOND'),
('IF'),
('INDEX'),
('INOUT'),
('INT'),
('INT3'),
('INTEGER'),
('IS'),
('KEY'),
('LEADING'),
('LIKE'),
('LINES'),
('LOCALTIMESTAMP'),
('LONGBLOB'),
('LOW_PRIORITY'),
('MEDIUMBLOB'),
('MIDDLEINT'),
('MOD'),
('NOT'),
('NUMERIC'),
('OPTION'),
('ORDER'),
('OUTFILE'),
('PROCEDURE'),
('READ'),
('REAL'),
('RELEASE'),
('REPLACE'),
('RETURN'),
('RLIKE'),
('SECOND_MICROSECOND'),
('SEPARATOR'),
('SMALLINT'),
('SQL'),
('SQLWARNING'),
('SQL_SMALL_RESULT'),
('STRAIGHT_JOIN'),
('THEN'),
('TINYTEXT'),
('TRIGGER'),
('UNION'),
('UNSIGNED'),
('USE'),
('UTC_TIME'),
('VARBINARY'),
('VARYING'),
('WHILE'),
('XOR')
;
---------------
Assuming you want to do this check for a specific database, in my case I have a database called "rfp"
---------------
use information_schema;
select table_name from TABLES where table_schema='rfp'
and upper(table_name) in (select reserved_word from mytest.reserved_words);
select table_name, column_name from columns where table_schema='rfp'
and upper(column_name) in (select reserved_word from mytest.reserved_words);
---------------
To do the check for the entire database:
---------------
select table_schema, table_name from TABLES where
upper(table_name) in (select reserved_word from mytest.reserved_words);
select table_schema, table_name, column_name from columns
where
upper(column_name) in (select reserved_word from mytest.reserved_words);
Add your own comment.