Detailed explanation of keywords and reserved words in MySQL 5.7

Preface

The keywords of MySQL and Oracle are not exactly the same. In the Oracle database, we define a large number of code fields in our data table to represent the primary key, but in MySQL, code is a keyword, and the previous processing method is somewhat "incompatible".

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

What are keywords and reserved words

Keywords are words that have meaning in SQL. Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special handling for use 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 need to be quoted. If you want to use a reserved word as an identifier, you must use quotes.

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 enclosed in quotes 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 can see that the table name in the first statement uses the reserved word interval, and the execution fails;

The second statement adds quotes to interval and is executed successfully.

It is important to note here that the quotes must be backticks, not single quotes. Otherwise, an error will be reported as follows:

mysql>
mysql> drop table `interval`; -- use backticks Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> create table 'interval' (begin INT, end INT); --Use single quotes, error ERROR 1064 (42000):

mysql>

There is one exception

If the identifier follows a period in a qualified name (database name), no quotes are required even for reserved keywords.

Let's take the hoegh database as an example. If the table name is written as hoegh.interval, there is no need to quote 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

Built-in function names are allowed as identifiers, but should be used with caution. For example, COUNT is a legal column name. However, by default, no whitespace is allowed in function calls between the function name and the following (. This restriction enables the parser to distinguish whether a name is used in a function call or in a non-function context.

appendix

At some point you may need to upgrade to a later 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. Most reserved words are prohibited in standard SQL as column or table names (for example, GROUP). Some of these are reserved words because MySQL requires them and uses a yacc parser.

The three tables are listed below:

The first table, 10.2, shows the keywords and reserved words in MySQL 5.7. Reserved keywords are marked with (R). Additionally, _FILENAME is reserved.

The second table 10.3 shows the reserved words added in MySQL 5.7 compared to MySQL 5.6.

The third table 10.4 shows the reserved words that were removed in MySQL 5.7 compared to 5.6.

Table 10.2 Keywords and Reserved Words in MySQL 5.7

Table 10.3 Keywords and Reserved Words Added in MySQL 5.7 Compared to MySQL 5.6

Table 10.4 Keywords and Reserved Words Removed in MySQL 5.7 Compared to MySQL 5.6

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.