批量修改mysql数据库、表、字段的编码格式及排序规则

直接上代码吧，可以通过这些代码获取到修改所需库、表、字段的sql代码，然后执行这些代码，即可批量修改编码格式和排序规则。本例中以全部修改为 ‘utf8’ 为例，如果需要修改为 ‘utf8mb4’ 等其他格式，请按照需求酌情修改；

-- 获取修改所有数据库编码的sql
SELECT CONCAT('ALTER DATABASE ',SCHEMA_NAME,' CHARACTER SET utf8 COLLATE utf8_general_ci;') as 修改数据库编码格式语句
FROM information_schema.`SCHEMATA`
WHERE DEFAULT_CHARACTER_SET_NAME <> 'utf8'
    AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys');

-- 获取修改所有数据库排序规则不是'utf8_general_ci'的表编码的sql
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET utf8 COLLATE utf8_general_ci;') AS 修改表编码格式语句
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA not IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys')
    AND TABLE_TYPE="BASE TABLE"
   and TABLE_COLLATION <> 'utf8_general_ci';

-- 获取修改所有数据库所有表所有字段编码的sql
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_general_ci', ' ', IF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), IF(ISNULL(COLUMN_DEFAULT), '', CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\'')), IF(ISNULL(COLUMN_COMMENT), '', CONCAT(' COMMENT \'', COLUMN_COMMENT, '\';'))) AS 修改字段编码格式语句
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA  NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys')
    and COLLATION_NAME <> 'utf8_general_ci'
    and COLLATION_NAME is not null;                 -- 可以不用，上面'<>'的条件可以过滤空值
    

如果是只想修改 ‘utf8’ 的数据库下的字段编码，可以用以下sql:

-- 获取只修改字符集编码格式为'utf8'的数据库下的表编码格式的sql
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET utf8 COLLATE utf8_general_ci;') AS 修改表编码格式语句
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA IN (
    SELECT SCHEMA_NAME
    FROM information_schema.`SCHEMATA`
    WHERE DEFAULT_CHARACTER_SET_NAME = 'utf8'
        AND SCHEMA_NAME NOT IN ('admindb', 'mysql', 'information_schema', 'performance_schema', 'sys'))
    AND TABLE_TYPE="BASE TABLE";