Detailed explanation of keywords and reserved words in MySQL5.7

Recently, during the process of migrating data from Oracle to MySQL, I encountered some problems, including keywords. The following article mainly introduces you to the relevant information about keywords and reserved words in MySQL 5.7. The introduction in the article is very detailed. Friends who need it can refer to it. Let’s take a look together.

Foreword

The keywords of MySQL and Oracle are still different. In the Oracle database, a large number of code fields are defined in our data tables to represent the primary key, but in MySQL code is the keyword , using the previous treatment methods is somewhat "acclimated".

Let’s take a look at the keywords and reserved words in MySQL.

What are keywords and reserved words

Keywords refer to words that are meaningful in SQL. Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special handling to be used as identifiers such as table and column names. This also applies to the names of built-in functions.

How to use keywords and reserved words

Non-reserved keywords are allowed as identifiers and do not require quotes. If you want to use a reserved word as an identifier, you must use quotation marks.

For example, BEGIN and END are keywords but not reserved words, so they do not require quotes when used as identifiers. INTERVAL is a reserved keyword and must be quoted to be used as an identifier.

mysql>
mysql> use hoegh;
Database changed
mysql>
mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000):
mysql>
mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.42 sec)

mysql>
mysql> show create table `interval`;
+----------+---------------------------------------------------------
| Table | Create Table
+----------+---------------------------------------------------------
| interval | CREATE TABLE `interval` (
 `begin` int(11) DEFAULT NULL,
 `end` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------
1 row in set (0.00 sec)

mysql>

We see that the table name in the first statement uses the reserved word interval, and the execution fails;

The second statement adds quotation marks to interval, and the execution succeeds.

It should be noted here that the quotation marks must be backticks, not single quotation marks. Otherwise, an error will be reported as follows:

mysql>
mysql> drop table `interval`;--使用反引号
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> create table 'interval' (begin INT, end INT);--使用单引号，报错
ERROR 1064 (42000):

mysql>

There is one exception

If the identifier comes after the period of the qualified name (database name), even reserved keywords do not need quotes.

Let’s take the hoegh database as an example. If the table name is written as hoegh.interval, there is no need to put quotation marks around the reserved word interval.

mysql>
mysql> create table hoegh.interval (begin INT, end INT);
Query OK, 0 rows affected (0.19 sec)

mysql>
mysql> show create table hoegh.interval;
+----------+---------------------------------------------------------
| Table | Create Table
+----------+---------------------------------------------------------
| interval | CREATE TABLE `interval` (
 `begin` int(11) DEFAULT NULL,
 `end` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------
1 row in set (0.00 sec)

mysql>

About using built-in function names

Allowed names of built-in functions can be used as identifiers, but it is best to use them with caution. For example, COUNT is legal as a column name. However, by default, no spaces are allowed in function calls between the function name and the following (. This restriction allows the parser to distinguish whether the name is used in a function call or in a non-function context.

Appendix

At some point you may need to upgrade to a higher version, so it's a good idea to take a look at future reserved words. You can find these in the manuals covering later versions of MySQL for most reservations. Words are prohibited in standard SQL as names of columns or tables (e.g., GROUP). Some of these are reserved words because MySQL requires them and uses a yacc parser.

Three tables are listed below:

The first table 10.2 shows the keywords and reserved words in MySQL 5.7. The reserved keywords are marked (R). In addition, _FILENAME is reserved. The second table 10.3 shows the new additions in MySQL 5.7.

The third table 10.4 shows the reserved words deleted in MySQL 5.7 compared to version 5.6

Table 10.2 Keywords and Reserved Words in MySQL 5.7