How to resolve the error that occurs while using a reserved word as a table or column name in MySQL?

MySQLMySQLi Database

This error occurs when you try to use a reserved word as a table or column name. It can occur due to −

Case 1: Whenever you use reserved word as a table name −

mysql> create table insert
−> (
−> Id int
−> );

The error is as follows −

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert
(
Id int
)' at line 1

The above error occurred because the word ‘insert’ is a keyword in MySQL.

Case 2 − Whenever you use reserved word as a column name in MySQL.

mysql> create table Customers
   −> (
   −> Add int
   −> );

The error is as follows −

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Add int
)' at line 3

The above error occurred because the column name ‘Add’ is a reserved word in MySQL.

To avoid the above error, you need to know about all the reserved words of MySQL

Some of the MySQL reserved words are as follows −

Insert
Add
Is
Key
Like etc.

The complete list of MySQL reserved keywords are as follows. This is the official website of MySQL − https://dev.mysql.com/doc/refman/5.7/en/keywords.html

Use a backtick with the reserved keyword to solve this issue.

Note - You cannot use a reserved keyword for table or column name. However, including them with backtick would be considered correct.

For Example −

create table `insert`

Demo of backtick with table as well as column name.

mysql> create table `Insert`
   −> (
   −> `Add` int
   −> );
Query OK, 0 rows affected (0.59 sec)

With the help of backtick, you will not get any error.

raja
Published on 11-Jan-2019 13:53:42
Advertisements