将MySQL数据库中所有表和字段编码统一改为utf8mb4_unicode_ci

发布于：2025-06-15 ⋅ 阅读:(18) ⋅ 点赞:(0)

完整操作步骤

1. 首先修改数据库默认字符集

sql

ALTER DATABASE `你的数据库名` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 生成批量修改所有表的SQL语句

sql

SELECT CONCAT(
    'ALTER TABLE `', table_schema, '`.`', table_name, 
    '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS alter_statement
FROM information_schema.tables
WHERE table_schema = '你的数据库名'
AND table_type = 'BASE TABLE';

3. 生成批量修改所有字段的SQL语句（确保字段也统一）

sql

SELECT CONCAT(
    'ALTER TABLE `', table_schema, '`.`', table_name, 
    '` MODIFY `', column_name, '` ', column_type, 
    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
    IF(is_nullable = 'NO', ' NOT NULL', ''),
    IF(column_default IS NOT NULL, CONCAT(' DEFAULT \'', column_default, '\''), ''),
    ' COMMENT \'', IFNULL(column_comment, ''), '\';'
) AS alter_column_statement
FROM information_schema.columns
WHERE table_schema = '你的数据库名'
AND data_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set')
AND collation_name != 'utf8mb4_unicode_ci';

4. 使用存储过程自动执行所有修改

sql

DELIMITER //
CREATE PROCEDURE convert_all_to_utf8mb4(IN db_name VARCHAR(64))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE col_name VARCHAR(64);
    DECLARE col_type TEXT;
    DECLARE is_null VARCHAR(3);
    DECLARE col_default TEXT;
    DECLARE col_comment TEXT;
    
    -- 声明游标获取所有表
    DECLARE cur_tables CURSOR FOR 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = db_name AND table_type = 'BASE TABLE';
    
    -- 声明游标获取所有需要修改的列
    DECLARE cur_columns CURSOR FOR 
        SELECT table_name, column_name, column_type, is_nullable, 
               column_default, column_comment 
        FROM information_schema.columns 
        WHERE table_schema = db_name 
        AND data_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set')
        AND collation_name != 'utf8mb4_unicode_ci';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 禁用外键检查
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- 修改所有表
    OPEN cur_tables;
    tables_loop: LOOP
        FETCH cur_tables INTO tbl_name;
        IF done THEN
            LEAVE tables_loop;
        END IF;
        
        SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', tbl_name, 
                         '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('Converted table: ', tbl_name) AS message;
    END LOOP;
    CLOSE cur_tables;
    
    -- 重置完成标志
    SET done = FALSE;
    
    -- 修改所有列
    OPEN cur_columns;
    columns_loop: LOOP
        FETCH cur_columns INTO tbl_name, col_name, col_type, is_null, col_default, col_comment;
        IF done THEN
            LEAVE columns_loop;
        END IF;
        
        SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', tbl_name, 
                         '` MODIFY `', col_name, '` ', col_type,
                         ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
                         IF(is_null = 'NO', ' NOT NULL', ''),
                         IF(col_default IS NOT NULL, CONCAT(' DEFAULT \'', col_default, '\''), ''),
                         IF(col_comment IS NOT NULL AND col_comment != '', CONCAT(' COMMENT \'', col_comment, '\''), ''));
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('Converted column: ', tbl_name, '.', col_name) AS message;
    END LOOP;
    CLOSE cur_columns;
    
    -- 恢复外键检查
    SET FOREIGN_KEY_CHECKS = 1;
    
    SELECT 'All tables and columns have been converted to utf8mb4_unicode_ci' AS result;
END //
DELIMITER ;

-- 调用存储过程
CALL convert_all_to_utf8mb4('你的数据库名');

-- 使用后可以删除存储过程
DROP PROCEDURE IF EXISTS convert_all_to_utf8mb4;

5. 验证修改结果

sql

-- 检查所有表的字符集
SELECT table_name, table_collation 
FROM information_schema.tables 
WHERE table_schema = '你的数据库名';

-- 检查所有列的字符集
SELECT table_name, column_name, collation_name 
FROM information_schema.columns 
WHERE table_schema = '你的数据库名'
AND data_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set');

重要注意事项

  1. 备份数据库：执行前务必备份整个数据库

  2. 停机维护：大型数据库转换可能需要较长时间，建议在维护窗口期操作

  3. 连接设置：确保应用程序连接MySQL时也使用utf8mb4字符集

    sql

    SET NAMES utf8mb4;

  4. 索引限制：utf8mb4字符集的索引长度限制是utf8的3/4，可能需要调整某些索引

  5. 存储空间：utf8mb4占用的存储空间可能比原来的字符集更多

后续配置

修改MySQL配置文件(my.cnf或my.ini)，确保新创建的表默认使用utf8mb4:

text

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

这样设置后，新创建的表和字段都会默认使用utf8mb4字符集。

# 查询编码不一致的表
SELECT
	table_schema,
	table_name,
	table_collation 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'daysurgery_restructure' 
	AND table_collation != ( SELECT DEFAULT_COLLATION_NAME FROM information_schema.schemata WHERE schema_name = 'daysurgery_restructure' ) 
ORDER BY
	table_collation;

# 修改数据库编码
ALTER DATABASE `daysurgery_restructure` CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 生成批量修改所有表的SQL语句
SELECT
	CONCAT( 'ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) AS alter_statement 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'daysurgery_restructure' 
	AND table_type = 'BASE TABLE';

# 生成批量修改所有字段的SQL语句（确保字段也统一）
SELECT CONCAT(
    'ALTER TABLE `', table_schema, '`.`', table_name, 
    '` MODIFY `', column_name, '` ', column_type, 
    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
    IF(is_nullable = 'NO', ' NOT NULL', ''),
    IF(column_default IS NOT NULL, CONCAT(' DEFAULT \'', column_default, '\''), ''),
    ' COMMENT \'', IFNULL(column_comment, ''), '\';'
) AS alter_column_statement
FROM information_schema.columns
WHERE table_schema = 'daysurgery_restructure'
AND data_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set')
AND collation_name != 'utf8mb4_unicode_ci';

# 使用存储过程自动执行所有修改
DELIMITER //
CREATE PROCEDURE convert_all_to_utf8mb4(IN db_name VARCHAR(64))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE col_name VARCHAR(64);
    DECLARE col_type TEXT;
    DECLARE is_null VARCHAR(3);
    DECLARE col_default TEXT;
    DECLARE col_comment TEXT;
    
    -- 声明游标获取所有表
    DECLARE cur_tables CURSOR FOR 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = db_name AND table_type = 'BASE TABLE';
    
    -- 声明游标获取所有需要修改的列
    DECLARE cur_columns CURSOR FOR 
        SELECT table_name, column_name, column_type, is_nullable, 
               column_default, column_comment 
        FROM information_schema.columns 
        WHERE table_schema = db_name 
        AND data_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set')
        AND collation_name != 'utf8mb4_unicode_ci';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 禁用外键检查
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- 修改所有表
    OPEN cur_tables;
    tables_loop: LOOP
        FETCH cur_tables INTO tbl_name;
        IF done THEN
            LEAVE tables_loop;
        END IF;
        
        SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', tbl_name, 
                         '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('Converted table: ', tbl_name) AS message;
    END LOOP;
    CLOSE cur_tables;
    
    -- 重置完成标志
    SET done = FALSE;
    
    -- 修改所有列
    OPEN cur_columns;
    columns_loop: LOOP
        FETCH cur_columns INTO tbl_name, col_name, col_type, is_null, col_default, col_comment;
        IF done THEN
            LEAVE columns_loop;
        END IF;
        
        SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', tbl_name, 
                         '` MODIFY `', col_name, '` ', col_type,
                         ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
                         IF(is_null = 'NO', ' NOT NULL', ''),
                         IF(col_default IS NOT NULL, CONCAT(' DEFAULT \'', col_default, '\''), ''),
                         IF(col_comment IS NOT NULL AND col_comment != '', CONCAT(' COMMENT \'', col_comment, '\''), ''));
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('Converted column: ', tbl_name, '.', col_name) AS message;
    END LOOP;
    CLOSE cur_columns;
    
    -- 恢复外键检查
    SET FOREIGN_KEY_CHECKS = 1;
    
    SELECT 'All tables and columns have been converted to utf8mb4_unicode_ci' AS result;
END //
DELIMITER ;

-- 调用存储过程
CALL convert_all_to_utf8mb4('daysurgery_restructure');

-- 使用后可以删除存储过程
DROP PROCEDURE IF EXISTS convert_all_to_utf8mb4;

5. 验证修改结果
sql
-- 检查所有表的字符集
SELECT table_name, table_collation 
FROM information_schema.tables 
WHERE table_schema = 'daysurgery_restructure';

-- 检查所有列的字符集
SELECT table_name, column_name, collation_name 
FROM information_schema.columns 
WHERE table_schema = 'daysurgery_restructure'
AND data_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set');

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布