MySQL keywords and reserved words

Keywords are meaningful words in SQL. It is the vocabulary of MySQL built-in rules. These keywords provide great convenience for MySQL grammar, function definition, and parsing grammar. Some keywords (such as SELECT, DELETE, or BIGINT) are reserved and require special handling when used as identifiers such as table names and column names.

The so-called keywords, look at the following example

Case 1:

  • Create table
mysql> CREATE TABLE int (begin INT, end 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 'int (begin INT, end INT)' at line 1mysql> mysql> CREATE TABLE `int` (begin INT, end INT);Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE db1.int (begin INT, end INT);Query OK, 0 rows affected (0.02 sec)
  • Field:
mysql> CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30),`ADD` VARCHAR(12),rank VARCHAR(30));mysql> INSERT INTO test(name,`ADD`,rank) VALUES('Jim','001','10'),('Tom','002','20'),('Him','003','5'); mysql>  SELECT ADD  FROM test;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  FROM test' at line 1

note:

  1. Syntax error mention: check the manual that corresponds to your MariaDB server version for the right syntax
  2. The int ADD type field is used as the table name or field name, and the syntax is incorrect. Use the following two methods:
    "`" interval to block keywords or specify the mysql library name

Case 2:

Comparison between version 5.7 and version 8.0:

image.png


Rank and load upgrade versions may have hidden dangers

The version upgrade prompts keywords error:

 

mysqlsh -- util checkForServerUpgrade root:[email protected]:3306 --target-version=8.0.25 --output-format=JSON --config-path=/etc/my5.7.cnf > upgrade.txt

image.png

Note: To upgrade version 8.0 from version 5.6 to version 5.7, checkForServerUpgrade must be verified.

What are the keywords

Because there are too many keywords, just list these fields.

  • ADD ALL ALTER ACTION
  • ANALYZE AND AS
  • ASC
    can see the official website:
image.png


https://dev.mysql.com/doc/refman/8.0/en/keywords.html

to sum up

Some common keywords in MySQL should be avoided as field names. Especially the 8.0 upgrade also needs attention.
The naming convention reference is provided below:

  • Use meaningful English vocabulary for naming, separated by underscores in the middle of the vocabulary.
  • Some database objects use lowercase letters.
  • Only English letters, numbers, and underscores can be used for naming.
  • The field name must not exceed 32 characters, and the name must be known. It is forbidden to mix Pinyin and English
  • Avoid using MySQL reserved words such as call, group, order rank, etc.
  • If used in special circumstances, "`" spacers can be used to block keywords.