避坑指南！「MySQL高版本数据库(8.0)转存sql文件并导入低版本数据库(5.7)」

高版本数据库(8.0)转存sql文件并导入低版本数据库(5.7)

今日在做数据迁移，由于之前使用的数据库版本是8.0.迁入的数据库为5.7，导致报错：Unknown collation: ‘utf8mb4_0900_ai_ci’

由于升级数据库会影响线上现有的业务，所以采用了方案二，对迁移的数据字符集和排序规则进行了调整

为了确保数据库中所有表及其列的字符集和排序规则都正确设置，需要针对每个表和列执行相应的修改操作。这里是一个更详细的步骤指南，包括如何修改表级和列级的字符集和排序规则：

1. 修改表级的字符集和排序规则
   首先，更改表的默认字符集和排序规则。可以通过以下SQL语句完成：

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

这条命令会将表 your_table_name 中的所有列转换为 utf8 字符集并使用 utf8_general_ci 排序规则。

2. 修改列级的字符集和排序规则
   如果某些列使用了不同的字符集或排序规则，并且想要保留这些差异，需要单独修改这些列。这可以通过以下SQL语句完成：

ALTER TABLE your_table_name MODIFY your_column_name YOUR_COLUMN_TYPE CHARACTER SET utf8 COLLATE utf8_general_ci;

在这里，your_column_name 是要修改的列的名称，YOUR_COLUMN_TYPE 是该列的数据类型（例如 VARCHAR(255)）。

3. 自动化脚本生成
   由于手动为每个表和列生成这些语句可能很繁琐，可以编写一个脚本来自动化这个过程。以下是一个简单的MySQL脚本示例，用于生成修改表级和列级字符集和排序规则的SQL语句：

-- 修改表级的字符集和排序规则
SELECT
CONCAT( 'ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) AS alter_tableFROM information_schema.tablesWHERE table_schema = 'your_database_name'
AND table_collation = 'utf8mb4_0900_ai_ci';

-- 修改列级的字符集和排序规则（如果需要）
SELECT
CONCAT( 'ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', column_type, ' CHARACTER SET utf8 COLLATE utf8_general_ci;' ) AS alter_columnFROM information_schema.columnsWHERE table_schema = 'your_database_name'
AND collation_name = 'utf8mb4_0900_ai_ci';

运行这些查询后，得到一系列 ALTER TABLE 和 ALTER COLUMN 语句，可以将它们复制到数据库管理工具中并执行，以更改字符集和排序规则。

4. 备份和测试
   在执行任何更改之前，请确保对数据库进行了完整备份。更改字符集和排序规则可能会影响数据的存储和检索方式，因此建议在生产环境应用更改之前，先在测试环境中进行彻底的测试。

5. 检查应用程序代码
   最后，不要忘记检查应用程序代码，确保它处理新字符集和排序规则的方式是正确的。特别是，如果您的应用程序依赖于特定的排序或字符比较逻辑，这些更改可能会影响这些逻辑。

执行这些更改时，请谨慎行事，并在进行任何更改之前彻底理解它们的含义和潜在影响。