How can we use the MySQL reserved words as an identifier?

MySQLMySQLi Database

We must have to use quotes with reserved words to use them as an identifier. The quotes can be single or double depends upon ANSI_QUOTES SQL mode.

If this mode is disabled then the identifier quote character is the backtick (“`”). Consider the following example in which we created a table named ‘select’ −

mysql> create table `select`(id int);
Query OK, 0 rows affected (0.19 sec)

If this mode is enabled then we can use backtick (“`”) and double quotes (“”) both as identifier quote character. Consider the following example in which we created a table named ‘trigger’ −

mysql> Create table "trigger" (id int);
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 '"trigger" (id int)' at line 1

mysql> Set sql_mode = 'ANSI_Quotes';
Query OK, 0 rows affected (0.03 sec)

mysql> Create table "trigger" (id int);
Query OK, 0 rows affected (0.17 sec)

mysql> Create table `DESCRIBE`(id int);
Query OK, 0 rows affected (0.11 sec)

The queries above show that we can use both backtick (“`”) and double quotes (“”) both as identifier quote character after enabling ‘ANSI_QUOTES’ mode.

raja
Published on 20-Feb-2018 07:02:49
Advertisements