MySQL 脚本（最常用，支持 5.7/8.0）

原理：information_schema.COLUMNS 保存字段字符集、排序规则；只有 char/varchar/text/enum/set 才有字符集，数字、日期等是 NULL

1. 全量导出所有字符型字段字符集（清单）

SELECT
  TABLE_SCHEMA AS db_name,
  TABLE_NAME AS tbl_name,
  COLUMN_NAME AS col_name,
  DATA_TYPE,
  CHARACTER_SET_NAME,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY db_name, tbl_name, col_name;

2. 【重点】找出和目标标准字符集不一致的字段

例如：标准统一要求 utf8mb4，筛选异类：

SELECT
  TABLE_SCHEMA AS db_name,
  TABLE_NAME AS tbl_name,
  COLUMN_NAME AS col_name,
  DATA_TYPE,
  CHARACTER_SET_NAME,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4'  -- 这里改成你期望的标准字符集
ORDER BY CHARACTER_SET_NAME, db_name, tbl_name;

3. 检查表内字段字符集不统一（同一个表里面出现多种字符集）

SELECT * FROM (
  SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COUNT(DISTINCT CHARACTER_SET_NAME) AS charset_cnt,
    GROUP_CONCAT(DISTINCT CHARACTER_SET_NAME SEPARATOR ',') AS charset_list
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
    AND CHARACTER_SET_NAME IS NOT NULL
  GROUP BY TABLE_SCHEMA, TABLE_NAME
) t WHERE charset_cnt > 1;

4. 顺带检查表的默认字符集，对比字段（表默认 vs 字段）

SELECT
  c.TABLE_SCHEMA,
  c.TABLE_NAME,
  c.COLUMN_NAME,
  c.CHARACTER_SET_NAME col_charset,
  c.COLLATION_NAME col_collate,
  t.TABLE_COLLATION
FROM information_schema.COLUMNS c
JOIN information_schema.TABLES t
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND c.CHARACTER_SET_NAME IS NOT NULL
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME;

5. 直接生成修正 ALTER 语句（一键输出修改脚本）

SELECT CONCAT(
  'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
  '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
  ' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
) AS fix_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4';

⚠️ 注意：
MySQL 字段如果没有显式指定字符集，CHARACTER_SET_NAME 会继承表的字符集，查询也会正常显示出来，不是 NULL
join 报错、索引失效，很多时候是 collation 排序规则不一致，哪怕字符集一样，排查时最好同时看 COLLATION_NAME

openGauss（B 兼容模式，支持字段级 charset，信创常用）

openGauss 原生 PG 模式没有字段独立字符集，库级统一；B 模式（mysql 兼容）才支持字段 CHARSET

-- B模式查看字段collation（字符序）
SELECT
  table_schema,
  table_name,
  column_name,
  data_type,
  collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog','information_schema')
  AND collation_name IS NOT NULL;

PostgreSQL

PG 没有字段单独字符集，字符集是数据库级全局，只有 collation 排序规则在字段 / 列上可以不同：

SELECT
  nspname AS schema_name,
  relname AS table_name,
  attname AS col_name,
  pg_get_collation_name(attcollation) AS collate_name
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE attisdropped = false
  AND attcollation <> 0;

达梦 DM

达梦字符集是建库时全局固定，不支持单字段独立字符集，只能看库级：

SELECT UNICODE();
-- 0:GB18030  1:UTF-8

字段只区分排序规则，无独立 charset 属性

排查使用建议（踩坑经验）

优先跑【脚本 2】，锁定所有非标准字符集字段；
再跑【脚本 3】，定位 “同一个表里面多种字符集” 这种高危表（join 极易报错）；
核对 collation：utf8mb4_general_ci 和 utf8mb4_0900_ai_ci 字符集相同但 collation 不兼容，也会报 Illegal mix of collations；
导出 fix_sql 之后，先备份数据，再分批执行 alter，大表锁表风险很高。