升级 MySQL 8.0 后将字符集从 utf8 改为 utf8mb4

因 MySQL 8 默认使用 utf8mb4 字符集,如果是从 MySQL 5-7 等低版本迁移的,那么仍然是 utf8(相当于 utf8mb3)。

这在保存字符串时有可能会报错:

An error occurred while saving the entity changes. See the inner exception for details.

Incorrect string value: '\xF0\xA1\x90\x93\xE6\x9D...' for column 'Html' at row 1

即使连接字符串上加上 CharSet=utf8 或 CharSet=utf8mb3 或 CharSet=utf8mb4 也没用。

那么只能把数据库的字符集改为 utf8mb4。


更改字符集和排序方式前必须先备份数据库!


  1. 更改现有数据库的字符集和排序规则

    ALTER DATABASE mydatabase 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;
  2. 更改现有表的字符集和排序规则

    ALTER TABLE mytable 
    CONVERT TO CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;
  3. 更改现有列的字符集和排序规则

    当列单独设置了字符集时执行,未设置的不需要执行,会继承表的字符集

    ALTER TABLE mytable 
    MODIFY mycolumn VARCHAR(255) 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci


数据库中可能存在许多表,数据表中可能存在许多列,使用下面的命令可以生成批量执行的命令:

  1. 更改所有表的默认字符集和排序规则

    USE database_name; -- 替换为你的数据库名
    
    SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') 
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'database_name'; -- 再次替换为你的数据库名
  2. 更改所有列的字符集和排序规则

    USE database_name; -- 使用你的数据库名
    
    SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = 'database_name'; -- 使用你的数据库名


另外给出几条查询语句:

  1. 查看特定数据库的字符集和排序规则

    SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
    FROM information_schema.SCHEMATA
    WHERE SCHEMA_NAME = 'your_database_name';
  2. 查看特定表的字符集和排序规则

    SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'your_database_name';
  3. 查看特定列的字符集和排序规则

    SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
xoyozo 7 个月前
转载请注明出处
可能相关的内容