MySQL 5.x版本升级到8.x版本系统后部分语句失效

问题描述：

公司内部系统，随着业务的扩展、技术的优化，需要进行数据库配套升级，mysql5.2版本升级到8.0版本，升级完成后，系统使用异常，出现sql查询错误。经排查发现，mysql5.x版本使用的语句中的字段，在mysql8.0及以上版本，成为了数据库系统的关键字或保留字。

举例：

mysql 5.x

select system * from systeminfo;
#执行成功

mysql 8.x

select system *from systeminfo;
#执行失败，报错

解决方案：

对sql进行优化，将关键字或保留字段进行更换

信息来源 官网参考Link：

参考内容截取：mysql5.x和8.x 关键字或保留字参考

9.3 Keywords and Reserved Words

Keywords are words that have significance in SQL. Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions.

Nonreserved keywords are permitted as identifiers without quoting. Reserved words are permitted as identifiers if you quote them as described in Section 9.2, “Schema Object Names”:

BEGIN and END are keywords but not reserved, so their use as identifiers does not require quoting. INTERVAL is a reserved keyword and must be quoted to be used as an identifier:Exception: A word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved:

Names of built-in functions are permitted as identifiers but may require care to be used as such. For example, COUNT is acceptable as a column name. However, by default, no whitespace is permitted in function invocations between the function name and the following ( character. This requirement enables the parser to distinguish whether the name is used in a function call or in nonfunction context. For further details on recognition of function names, see Section 9.2.5, “Function Name Parsing and Resolution”.

The INFORMATION_SCHEMA.KEYWORDS table lists the words considered keywords by MySQL and indicates whether they are reserved. See Section 26.3.17, “The INFORMATION_SCHEMA KEYWORDS Table”.

• MySQL 8.0 Keywords and Reserved Words
• MySQL 8.0 New Keywords and Reserved Words
• MySQL 8.0 Removed Keywords and Reserved Words

MySQL 8.0 Keywords and Reserved Words

The following list shows the keywords and reserved words in MySQL 8.0, along with changes to individual words from version to version. Reserved keywords are marked with (R). In addition, _FILENAME is reserved.

At some point, you might upgrade to a higher version, so it is a good idea to have a look at future reserved words, too. You can find these in the manuals that cover higher versions of MySQL. Most of the reserved words in the list are forbidden by standard SQL as column or table names (for example, GROUP). A few are reserved because MySQL needs them and uses a yacc parser.

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

A

• ACCESSIBLE (R)
• ACCOUNT
• ACTION
• ACTIVE; added in 8.0.14 (nonreserved)
• ADD (R)
• ADMIN; became nonreserved in 8.0.12
• AFTER
• AGAINST
• AGGREGATE
• ALGORITHM
• ALL (R)
• ALTER (R)
• ALWAYS
• ANALYSE; removed in 8.0.1
• ANALYZE (R)
• AND (R)
• ANY
• ARRAY; added in 8.0.17 (reserved); became nonreserved in 8.0.19
• AS (R)
• ASC (R)
• ASCII
• ASENSITIVE (R)
• AT
• ATTRIBUTE; added in 8.0.21 (nonreserved)
• AUTHENTICATION; added in 8.0.27 (nonreserved)
• AUTOEXTEND_SIZE
• AUTO_INCREMENT
• AVG
• AVG_ROW_LENGTH

B

• BACKUP
• BEFORE (R)
• BEGIN
• BETWEEN (R)
• BIGINT (R)
• BINARY (R)
• BINLOG
• BIT