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

原创已于 2023-04-26 20:19:35 修改 · 2.1k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #服务器

于 2022-09-23 11:35:20 首次发布

本文档详细介绍了如何在MySQL8.0环境中修改数据库、表和字段的字符集及排序规则。首先，由于初始设置与应用需求不符导致的问题，需要将排序规则从utf8mb4_0900_ai_ci更改为utf8mb4_general_ci。接着，提供了查看当前字符集和排序规则的命令。然后，展示了如何通过ALTER DATABASE语句修改数据库的字符集和排序规则。最后，给出了查询并生成修改表和字段字符集及排序规则的SQL语句，确保所有相关组件与新设置保持一致。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 = '库名字';