MySQL 5.7升级 MySQL 8.0预检查及问题修复指南

SELECT table_schema, table_name, column_name, column_type 
FROM information_schema.columns 
WHERE column_type LIKE '%5.5 binary format%';

-- 对检测到的每张表执行重建
ALTER TABLE 数据库名.表名 FORCE;

-- 查找存在于 InnoDB 但不在 TABLES 表中的记录
SELECT t.NAME as table_name
FROM information_schema.innodb_sys_tables t
LEFT JOIN information_schema.tables it 
  ON CONCAT(it.TABLE_SCHEMA, '/', it.TABLE_NAME) = t.NAME
WHERE it.TABLE_NAME IS NULL
  AND t.NAME NOT LIKE 'SYS_%'
  AND t.NAME NOT LIKE 'mysql/%';

-- 查找受影响的表
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('point', 'geometry', 'polygon', 'linestring', 
                    'multipoint', 'multilinestring', 'multipolygon', 
                    'geometrycollection');

-- 重建表
ALTER TABLE 数据库名.表名 FORCE;

-- 手动执行检查
CHECK TABLE 数据库名.表名 FOR UPGRADE;

-- 根据返回的具体错误信息进行修复
-- 通常的修复方法：
REPAIR TABLE 数据库名.表名;
-- 或
ALTER TABLE 数据库名.表名 FORCE;

-- 1. 查看存储过程/函数定义
SHOW CREATE PROCEDURE 数据库名.过程名;
SHOW CREATE FUNCTION 数据库名.函数名;

-- 2. 为冲突的标识符添加反引号
-- 修改前
CREATE PROCEDURE test()
BEGIN
  SELECT rank FROM users;  -- rank 是 MySQL 8.0的保留字
END;

-- 修改后
CREATE PROCEDURE test()
BEGIN
  SELECT `rank` FROM users;
END;

-- 3. 重新创建存储过程
DROP PROCEDURE IF EXISTS 数据库名.过程名;
CREATE PROCEDURE 数据库名.过程名 ...

-- 1. 查找使用保留关键字的对象
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name IN ('RANK', 'ROWS', 'GROUPS', 'FUNCTION', 'SYSTEM', ...);

-- 2. 修改应用程序 SQL，为这些标识符添加反引号
-- 修改前
SELECT rank, rows FROM my_table;

-- 修改后
SELECT `rank`, `rows` FROM my_table;

-- 3. 或者重命名对象（如果可行）
ALTER TABLE my_table CHANGE rank ranking INT;

catalogs, character_sets, collations, column_type_elements, columns, 
dd_properties, events, foreign_key_column_usage, foreign_keys, 
index_column_usage, index_partitions, index_stats, indexes, 
parameter_type_elements, parameters, routines, schemata, 
st_spatial_reference_systems, table_partition_values, table_partitions, 
table_stats, tables, tablespace_files, tablespaces, triggers, 
view_routine_usage, view_table_usage, component, default_roles, 
global_grants, innodb_ddl_log, innodb_dynamic_metadata, 
password_history, role_edges

-- 1. 查找冲突的表
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'mysql' 
  AND table_name IN ('catalogs', 'character_sets', 'collations', ...);

-- 2. 重命名冲突的表
RENAME TABLE mysql.冲突表名 TO mysql.冲突表名_backup;

-- 或移动到其他数据库
RENAME TABLE mysql.冲突表名 TO other_db.表名;

-- 1. 查找超长约束名
SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_NAME, 
       LENGTH(i.CONSTRAINT_NAME) as name_length
FROM information_schema.TABLE_CONSTRAINTS i
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND LENGTH(i.CONSTRAINT_NAME) > 64;

-- 2. 重建外键约束
-- 先删除旧约束
ALTER TABLE 数据库名.表名 DROP FOREIGN KEY 旧约束名;

-- 再创建新约束（使用较短的名称）
ALTER TABLE 数据库名.表名 
ADD CONSTRAINT 新约束名 FOREIGN KEY (列名) REFERENCES 引用表(引用列);

-- 1. 查找使用 MAXDB 模式的对象
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE
FROM information_schema.ROUTINES
WHERE SQL_MODE LIKE '%MAXDB%';

-- 2. 重新创建存储过程，移除 MAXDB 模式
-- 导出存储过程定义
SHOW CREATE PROCEDURE 数据库名.过程名;

-- 修改 SQL_MODE，移除 MAXDB
-- 重新创建存储过程

-- 1. 查找使用过时模式的对象
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE
FROM information_schema.ROUTINES
WHERE SQL_MODE REGEXP 'DB2|MSSQL|MYSQL323|MYSQL40|NO_AUTO_CREATE_USER|ORACLE|POSTGRESQL';

-- 2. 重新创建对象，移除过时的 sql_mode 标志

-- 1. 查找使用已移除函数的视图
SELECT table_schema, table_name, view_definition
FROM information_schema.views
WHERE view_definition REGEXP 'ENCODE|DECODE|ENCRYPT|PASSWORD|\\\\bAREA\\\\b|\\\\bASBINARY\\\\b'
 and table_schema not in ('information_schema','mysql','performance_schema','sys');

-- 2. 修改视图定义，使用替代函数
CREATE OR REPLACE VIEW 视图名 AS
SELECT ST_AREA(geom_column) AS area  -- 替换 AREA()
FROM 表名;

-- 3. 类似地修改存储过程、触发器等

-- 修改前（错误）
SELECT category, COUNT(*) 
FROM products 
GROUP BY category DESC;

-- 修改后（正确）
SELECT category, COUNT(*) 
FROM products 
GROUP BY category 
ORDER BY category DESC;

-- 1. 查找包含 FTS 的表名
SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE table_name LIKE BINARY '%FTS%';

-- 2. 升级前临时重命名（将任一字母改为小写）
RENAME TABLE myFTStable TO myFtStable;

-- 3. 升级完成后可改回原名
RENAME TABLE myFtStable TO myFTStable;

-- 1. 使用 mysqlcheck 工具修复
mysqlcheck --check-upgrade --all-databases
mysqlcheck --fix-db-names --fix-table-names --all-databases

-- 2. 或使用 SQL 命令
ALTER DATABASE `#mysql50#旧名称` UPGRADE DATA DIRECTORY NAME;

-- 1. 查找孤立的存储过程
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, 'is orphaned' 
FROM information_schema.routines 
WHERE NOT EXISTS (
    SELECT SCHEMA_NAME 
    FROM information_schema.schemata 
    WHERE ROUTINE_SCHEMA = SCHEMA_NAME
);

-- 2. 删除孤立的存储过程
DROP PROCEDURE IF EXISTS 不存在的 schema.过程名;
DROP FUNCTION IF EXISTS 不存在的 schema.函数名;

-- 修改对象名称，使其以 $ 结尾
-- 例如：$example 改为 $example$ 或 example
RENAME TABLE `$mytable` TO `$mytable$`;
-- 或
RENAME TABLE `$mytable` TO `mytable`;

-- 1. 查找过大的索引
SELECT table_schema, table_name, index_name, 
       GROUP_CONCAT(column_name) as columns
FROM information_schema.statistics
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY table_schema, table_name, index_name
HAVING SUM(sub_part) > 767 OR (SUM(sub_part) IS NULL AND COUNT(*) > 1);

-- 2. 删除过大的索引
ALTER TABLE 表名 DROP INDEX 索引名;

-- 3. 考虑使用前缀索引或更改行格式
ALTER TABLE 表名 ROW_FORMAT=DYNAMIC;
ALTER TABLE 表名 ADD INDEX 索引名 (列名(191));

-- 修改存储过程，使用完整的 database.table 语法
-- 修改前
SELECT * FROM .mytable;

-- 修改后
SELECT * FROM mydb.mytable;

-- 1. 查找无效的外键
SELECT 
    fk.TABLE_SCHEMA, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
    fk.REFERENCED_TABLE_SCHEMA, fk.REFERENCED_TABLE_NAME,
    t1.ENGINE as source_engine, t2.ENGINE as target_engine
FROM information_schema.KEY_COLUMN_USAGE fk
JOIN information_schema.TABLES t1 
    ON fk.TABLE_SCHEMA = t1.TABLE_SCHEMA AND fk.TABLE_NAME = t1.TABLE_NAME
JOIN information_schema.TABLES t2 
    ON fk.REFERENCED_TABLE_SCHEMA = t2.TABLE_SCHEMA 
    AND fk.REFERENCED_TABLE_NAME = t2.TABLE_NAME
WHERE fk.REFERENCED_TABLE_NAME IS NOT NULL
    AND t1.ENGINE != t2.ENGINE;

-- 2. 删除无效的外键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

-- 3. 或统一存储引擎后重建外键
ALTER TABLE 表名 ENGINE=InnoDB;

-- 1. 查找使用非原生分区的表
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE create_options LIKE '%partitioned%'
  AND UPPER(engine) NOT IN ('INNODB', 'NDB', 'NDBCLUSTER');

-- 2. 转换为 InnoDB
ALTER TABLE 数据库名.表名 ENGINE=InnoDB;

-- 3. 或移除分区
ALTER TABLE 数据库名.表名 REMOVE PARTITIONING;

-- 复杂情况，需要多步骤处理：

-- 1. 重命名当前表
RENAME TABLE 问题表 TO 问题表_temp;

-- 2. 创建虚拟 InnoDB 表（使用相同结构）
CREATE TABLE 问题表 (id INT) ENGINE=InnoDB;

-- 3. 删除虚拟表（清理 InnoDB 数据字典）
DROP TABLE 问题表;

-- 4. 将临时表改回原名
RENAME TABLE 问题表_temp TO 问题表;