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
- 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)
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
- Syntax error mention: check the manual that corresponds to your MariaDB server version for the right syntax
- 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
Comparison between version 5.7 and version 8.0:
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
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
can see the official website:
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.