低版本迁移至MySQL 8.0，应该注意哪些问题
MySQL 8.0较MySQL 5.7增加了一些新的特性，并在性能表现上存在差异。迁移前，需要做兼容性分析并给出解决方案。可以从兼容性、系统变量等方面考虑。
- 兼容性分析：
针对MySQL8.0社区版与MySQL5.7社区版进行分析，包括以下两方面：
- 不影响迁移，但使用方法出现差异。
兼容性
检查项
作用
状态
解决方案
数据类型或函数
ENCODE()函数
加密
移除
AES_ENCRYPT()函数代替
DECODE()函数
解密
移除
AES_DECRYPT()函数代替
ENCRYPT()函数
加密
移除
SHA2()函数代替
DES_ENCRYPT()函数
加密
移除
AES_ENCRYPT()函数代替
DES_DECRYPT()函数
解密
移除
AES_DECRYPT()函数代替
JSON_APPEND()函数
增加json元素
移除
JSON_ARRAY_APPEND()函数代替
PASSWORD()函数
修改用户密码
移除
ALTER USER user IDENTIFIED BY 'auth_string';
JSON_MERGE()函数
将多个json合并为一个
废弃
JSON_MERGE_PERSERVE()函数代替
SQL MODE
NO_AUTO_CREATE_USER、DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS
-
移除
-
外键约束长度
外键约束名称不能超过64个字符
-
-
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1), INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1) FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
使用ALTER TABLE调整长度
features
GRANT创建用户
-
移除
CREATE USER
GRANT修改用户信息
-
移除
ALTER USER
IDENTIFIED BY PASSWORD 'auth_string'
设置密码
移除
IDENTIFIED WITH auth_plugin AS 'auth_string'
SQL语句中的\N
NULL
移除
NULL代替
PROCEDURE ANALYSE()语法
对MySQL字段值进行统计分析后给出建议的字段类型
移除
-
空间函数
-
-
-
mysql_install_db
初始化
移除
mysqld --initialize或--initialize-insecure
- 影响迁移，需要提前做检查。
兼容性
检查项
作用
状态
解决方案
原始用法
保留关键字
cume_dist、dense_rank、empty、except、first_value、grouping、groups、json_table、lag、last_value、lateral、lead、nth_value、ntile、of、over、percent_rank、rank、recursive、row_number、system、window
-
新增
SET sql_mode = 'ANSI_QUOTES'
名称：数据库、表、索引、列、alias、view、存储过程、分区、表空间
字符集
UTF8MB3
-
废弃
使用UTF8MB4代替
-
分区表
不得出现不支持本地分区的存储引擎的分区表
-
移除
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';
可按照下述两种方式解决：
（1）ALTER TABLE table_name ENGINE=INNODB;
（2）ALTER TABLE table_name REMOVE PARTITIONING;
不支持MyISAM
语法
group by … asc/desc
升序/降序
移除
使用order by子句代替
view、function等
名称长度
view的列名称不能超过64个字符
-
-
alter处理
最多255个字符
enum或set元素的总长度不能超过255个字符
-
-
用户处理
最大64K
大小写
lower_case_table_names
MySQL设置字母大小写是否敏感
-
升级过程中，如果设置该参数为1，则必须确保schema和table名称必须是小写的
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != LOWER(SCHEMA_NAME);
-
触发器
是否有空定义或者无效的创建上下文
-
-
show triggers查看，检测character_set_client、 collation_connection、Database Collation属性
-
- 不影响迁移，但使用方法出现差异。
- 系统变量默认值变更
针对社区版MySQL5.7与8.0版本的默认值作对比，默认值不影响迁移，但对迁移后的业务会产生影响。
序号
parameter/option
community
作用
备注
原默认值
新默认值
Server
1
character_set_server
latin1
utf8mb4
-
和源保持一致
2
collation_server
latin1_swedish_ci
utf8mb4_0900_ai_ci
-
和源保持一致
3
explicit_defaults_for_timestamp
OFF
ON
更新某一行时是否更新timestamp列
和源保持一致
4
optimizer_trace_max_mem_size
16KB
1MB
-
和源保持一致
5
validate_password_check_user_name
OFF
ON
-
和源保持一致
6
back_log
-1 (autosize) changed from : back_log = 50 + (max_connections / 5)
-1 (autosize) changed to : back_log = max_connections
在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
和源保持一致
7
max_allowed_packet
4194304 (4MB)
67108864 (64MB)
限制Server接受的数据包大小
按默认值
8
max_error_count
64
1024
控制显示告警的个数
和源保持一致
9
event_scheduler
OFF
ON
-
和源保持一致
10
table_open_cache
2000
4000
-
和源保持一致
11
log_error_verbosity
3 (Notes)
2 (Warning)
-
按默认值
INNODB
1
innodb_undo_tablespaces
0
2
-
按默认值
2
innodb_undo_log_truncate
OFF
ON
-
按默认值
3
innodb_flush_method
NULL
fsync (Unix),
unbuffered (Windows)
控制innodb数据文件及redo log的打开、刷写模式
按SQL默认值O_DIRECT
4
innodb_autoinc_lock_mode
1 (consecutive)
2 (interleaved)
控制着在向有auto_increment 列的表插入数据时，相关锁的行为；
和源保持一致
5
innodb_flush_neighbors
1 (enable)
0 (disable)
从缓冲池刷新页面是否也刷新相同范围内的其他脏页。
和源保持一致
6
innodb_max_dirty_pages_pct_lwm
0 (%)
10 (%)
影响innodb刷新脏页行为
按默认值
7
innodb_max_dirty_pages_pct
75 (%)
90 (%)
影响innodb刷新脏页行为
按默认值
PERFORMANCE SCHEMA
整体是不是开的
-
-
-
和源保持一致
REPLICATION
1
log_bin
OFF
ON
-
默认打开
2
server_id
0
1
-
如果是0，则设为1
3
log-slave-updates
OFF
ON
-
默认打开
4
expire_log_days
0
30
-
按默认值1
5
master-info-repository
FILE
TABLE
-
默认TABLE
6
relay-log-info-repository
FILE
TABLE
-
默认TABLE
7
transaction-write-set-extraction
OFF
XXHASH64
-
按默认值
8
slave_rows_search_algorithms
INDEX_SCAN, TABLE_SCAN
INDEX_SCAN, HASH_SCAN
-
按默认值
- 移除系统变量
针对社区版MySQL 5.7与8.0进行分析，移除系统变量不影响迁移。
移除变量
innodb_locks_unsafe_for_binlog
log_builtin_as_identified_by_password
old_passwords
query_cache_limit
query_cache_min_res_unit
query_cache_size
query_cache_type
query_cache_wlock_invalidate
ndb_cache_check_time
ignore_db_dirs
tx_isolation
tx_read_only
sync_frm
secure_auth
multi_range_count
log_error_verbosity
sql_log_bin
metadata_locks_cache_size
metadata_locks_hash_instances
date_format
datetime_format
time_format
max_tmp_tables
ignore_builtin_innodb
innodb_support_xa
innodb_undo_logs
innodb_undo_tablespaces
internal_tmp_disk_storage_engine
