批量修改MySQL数据库表字段字符集

存储过程：查询出MySQL数据库中不是utf8mb4_unicode_ci字符集

# 批量修改所有表字符集为utf8mb4_unicode_ci

DELIMITER //

DROP PROCEDURE IF EXISTS update_table_collation;
CREATE PROCEDURE update_table_collation(IN target_collation VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE table_name_var VARCHAR(255);

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_COLLATION <> target_collation;

    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 创建临时表
    DROP TABLE IF EXISTS tmp_table_collation_check;
    CREATE TEMPORARY TABLE tmp_table_collation_check (
        table_name VARCHAR(255)
    );

    -- 打开游标
    OPEN cur;

    read_loop: LOOP
        -- 获取游标数据
        FETCH cur INTO table_name_var;

        -- 如果游标结束，退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 插入不符合规则的表信息到临时表
        INSERT INTO tmp_table_collation_check (table_name)
        VALUES (table_name_var);

        -- 修改表的 COLLATE 为目标字符集
        SET @alter_sql = CONCAT('ALTER TABLE `', table_name_var, '` COLLATE = ', target_collation);
        PREPARE stmt FROM @alter_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    -- 关闭游标
    CLOSE cur;

END //

DELIMITER ;

-- 调用存储过程，传入目标字符集
CALL update_table_collation('utf8mb4_unicode_ci');
-- 查询结果
SELECT * FROM tmp_table_collation_check;

# 批量修改所有表的字段字符集为utf8mb4_unicode_ci

DELIMITER //
DROP PROCEDURE IF EXISTS check_collation;
CREATE PROCEDURE check_collation(IN target_collation VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE table_name_var VARCHAR(255);
    DECLARE column_name_var VARCHAR(255);
    DECLARE column_collation VARCHAR(255);

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND COLLATION_NAME IS NOT NULL
          AND COLLATION_NAME <> target_collation;

    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 创建临时表
    DROP TABLE IF EXISTS tmp_table_check;
    CREATE TEMPORARY TABLE tmp_table_check (
        table_name VARCHAR(255),
        table_field VARCHAR(255)
    );

    -- 打开游标
    OPEN cur;

    read_loop: LOOP
        -- 获取游标数据
        FETCH cur INTO table_name_var, column_name_var, column_collation;

        -- 如果游标结束，退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 插入不符合规则的字段信息到临时表
        INSERT INTO tmp_table_check (table_name, table_field)
        VALUES (table_name_var, column_name_var);

        -- 修改字段的字符集为目标字符集
        SET @alter_sql = CONCAT('ALTER TABLE `', table_name_var, '` MODIFY `', column_name_var, '` ', 
                                (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
                                 WHERE TABLE_SCHEMA = DATABASE() 
                                   AND TABLE_NAME = table_name_var 
                                   AND COLUMN_NAME = column_name_var), 
                                ' CHARACTER SET utf8mb4 COLLATE ', target_collation);
        PREPARE stmt FROM @alter_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    -- 关闭游标
    CLOSE cur;

END //

DELIMITER ;

-- 调用存储过程，传入目标字符集
CALL check_collation('utf8mb4_unicode_ci');
-- 查询结果
SELECT * FROM tmp_table_check;