当前位置：首页 > news >正文

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

news 2025/6/14 6:15:03

完整操作步骤

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))
BEGINDECLARE 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: LOOPFETCH cur_tables INTO tbl_name;IF done THENLEAVE 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: LOOPFETCH cur_columns INTO tbl_name, col_name, col_type, is_null, col_default, col_comment;IF done THENLEAVE 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');

重要注意事项

备份数据库：执行前务必备份整个数据库
停机维护：大型数据库转换可能需要较长时间，建议在维护窗口期操作
连接设置：确保应用程序连接MySQL时也使用utf8mb4字符集

sql
```
SET NAMES utf8mb4;
```
索引限制：utf8mb4字符集的索引长度限制是utf8的3/4，可能需要调整某些索引
存储空间：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字符集。

# 查询编码不一致的表
SELECTtable_schema,table_name,table_collation 
FROMinformation_schema.TABLES 
WHEREtable_schema = 'daysurgery_restructure' AND table_collation != ( SELECT DEFAULT_COLLATION_NAME FROM information_schema.schemata WHERE schema_name = 'daysurgery_restructure' ) 
ORDER BYtable_collation;# 修改数据库编码
ALTER DATABASE `daysurgery_restructure` CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci;# 生成批量修改所有表的SQL语句
SELECTCONCAT( 'ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) AS alter_statement 
FROMinformation_schema.TABLES 
WHEREtable_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))
BEGINDECLARE 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: LOOPFETCH cur_tables INTO tbl_name;IF done THENLEAVE 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: LOOPFETCH cur_columns INTO tbl_name, col_name, col_type, is_null, col_default, col_comment;IF done THENLEAVE 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');

查看全文

http://www.xdnf.cn/news/1013671.html