mysql查询字段排序规则、数据库编码、表编码，修改排序规则

查询字段排序规则、数据库编码、表编码

SELECT 
TABLE_CATALOG, 
TABLE_SCHEMA, 
TABLE_NAME,
 COLUMN_NAME, 
COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS

 

表字段修复

#改变字段数据 字符集、排序规则
SELECT
    TABLE_SCHEMA '数据库',
    TABLE_NAME '表',
    COLUMN_NAME '字段',
    CHARACTER_SET_NAME '原字符集',
    COLLATION_NAME '原排序规则',
    CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL' 
FROM
    information_schema.`COLUMNS` 
WHERE
    TABLE_NAME = 'ud_dm_org_cas_stat'  and COLLATION_NAME RLIKE 'utf8_general_ci';

表修复

#改变表
SELECT
    TABLE_SCHEMA '数据库',
    TABLE_NAME '表',
    TABLE_COLLATION '原排序规则',
    CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;' ) '修正SQL' 
FROM
    information_schema.`TABLES` 
WHERE
    TABLE_NAME = 'ud_yqyc_ryb' 

数据库修复

#修改数据库
SELECT
    SCHEMA_NAME '数据库',
    DEFAULT_CHARACTER_SET_NAME '原字符集',
    DEFAULT_COLLATION_NAME '原排序规则',
    CONCAT( 'ALTER DATABASE ', SCHEMA_NAME, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL' 
FROM
    information_schema.`SCHEMATA` 
WHERE
    DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8';
 ;
 
 ALTER DATABASE 库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;