批量修改MySQL表字符集和排序规则

目录

一、问题

二、修改字符集

三、修改库字符集和排序规则

四、修改表和字段字符集以及排序规则

1、查询并生成修改语句

2、生成简洁的修改表以及字段字符集和排序规则的语句

一、问题

        由于建库建表指定的数据库字符集和排序规则，和应用需求不一致，导致系统因字符集不一致异常。

二、修改字符集

        该生产库使用MySQL8.0版本，使用的默认字符集为:utf8mb4 ;使用的排序规则是: utf8mb4_0900_ai_ci。

        通过测试验证和评估，需要修改该生产库库、表和字段的排序规则为utf8mb4_general_ci和自字符集为utf8mb4。

        查看库、表以及字段的字符集和排序规则命令

-- 查看库、表以及字段的字符集和排序规则命令

 

-- 方法一

 

-- 1、查看指定库排序规则和字符集

 

show create database '库名字';

 

 

-- 2、查看指定表以及排序规则和字符集

 

show create table '表明字';

 

 

-- 方法二

 

select * from  information_schema.`TABLES` where TABLE_SCHEMA='库名字' and tables='表名字'

 

select * from  information_schema.`COLUMNS` where TABLE_SCHEMA='库名字' and tables='表名字'

三、修改库字符集和排序规则

-- 修改库的排序规则和字符集

ALTER database '库名' DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

四、修改表和字段字符集以及排序规则

1、查询并生成修改语句

-- 1、查看需要修改字符集和排序规则的表

SELECT

    TABLE_SCHEMA AS '数据库',

    TABLE_NAME AS '表',

    TABLE_COLLATION AS '原排序规则',

    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS '修正SQL'

FROM

    information_schema.`TABLES`

WHERE

    TABLE_COLLATION ='utf8mb4_0900_ai_ci' AND TABLE_SCHEMA = '库名字';

 

 

-- 2、查看需要修改字符集和排序规则的字段

SELECT

    TABLE_SCHEMA AS '数据库',

    TABLE_NAME AS '表',

    COLUMN_NAME AS '字段',

    CHARACTER_SET_NAME AS '原字符集',

    COLLATION_NAME AS '原排序规则',

    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS '修正SQL'

FROM

    information_schema.`COLUMNS`

WHERE

    CHARACTER_SET_NAME = 'utf8mb4' AND COLLATION_NAME != 'utf8mb4_general_ci' AND TABLE_SCHEMA = '库名字';

2、生成简洁的修改表以及字段字符集和排序规则的语句

-- 1、生成修改表 的字符集和排序规则的SQL语句

 

SELECT    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS '修正SQL'

FROM

    information_schema.`TABLES`

WHERE

    TABLE_COLLATION ='utf8mb4_0900_ai_ci' AND TABLE_SCHEMA = '库名字';

 

 

-- 2、生成修改字段 的字符集和排序规则的SQL语句

SELECT    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS '修正SQL'

FROM

    information_schema.`COLUMNS`

WHERE

    CHARACTER_SET_NAME = 'utf8mb4' AND COLLATION_NAME != 'utf8mb4_general_ci' AND TABLE_SCHEMA = '库名字';

文章知识

————————————————

版权声明：本文为CSDN博主「@土豆」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/weixin_44770684/article/details/127006877