How to Use Reserved Word as Column Name in MySQL

Sometimes when you run a MySQL query, you may get an error message saying that there is syntax error, even though the query’s syntax is correct. In such cases, it might be that you are using a reserved word as column name or table name in your SQL query. MySQL has certain reserved words such as SELECT, INSERT, UPDATE, etc that have special meaning and are used to perform certain specific operations in MySQL. If you happen to use these words directly in your query’s column or tables names then you will get a syntax error. But sometimes you may have no option but to use these reserved words in queries. In this article, we will learn how to use reserved word as column name or table name in MySQL.

How to Use Reserved Word as Column Name in MySQL

In MySQL, you have some words that are reserved keywords meaning they have special meaning and cannot be used directly as column/table names or any other kind of identifier unless you enclose them in backticks. Here are some of the keywords.

ADD
AND
BEFORE
BY
CALL
CASE
CONDITION
DELETE
DESC
DESCRIBE
FROM
GROUP
IN
INDEX
INSERT
INTERVAL
IS
KEY
LIKE
LIMIT
LONG
MATCH
NOT
OPTION
OR
ORDER
PARTITION
RANK
REFERENCES
SELECT
TABLE
TO
UPDATE
WHERE

Here is the entire list of keywords in MySQL.

There are two ways to solve this problem.

1. It is not possible for everyone in your team to know and remember all MySQL keywords. So it is advisable to avoid using reserved words as column names, table names and any identifiers. Even if you use backticks to enclose column & table names, it may not be recognized by other SQL databases, who may require you to use double quotes instead of backticks. In such cases, you will face query portability issues. Also such errors can crop up if you are programmatically generating queries and even propagate throughout your application, wherever you are using such queries. If you completely avoid using reserved words in your identifiers, even other developers will avoid writing queries with syntax errors.

2. The second option is to use backticks to enclose your column & table names. Here is an example.

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)

In the above example, we are using INTERVAL keyword to name our table. The first query will give an error but second query doesn’t since you have enclosed the table name within back quotes.

If you are using dot format (table_name.column_name) in your query, then you need to separately enclose table name and column name within back quotes. Here is an example.

# the following query will give syntax error 
mysql> select interval.date from interval; 

# the following query will also give an error saying column not found
mysql> select `interval.date` from `interval`;

#the following query will work properly
mysql> select `interval`.`date` from `interval`.

In this article, we have learnt how to use reserved keywords in MySQL query. The best practice is to simply avoid using reserved keywords in your query. The next best thing is to enclose all table & column names in backticks so that even if the queries are generated programmatically, they will be error free.

How to Use Reserved Word as Column Name in MySQL

How to Use Reserved Word as Column Name in MySQL

Related posts:

Leave a Reply Cancel reply