统一MySql各表字段字符集及校对规则（解决MySql联合查询时各表字段因该不同导致的错误1271 - illegal mix of collations for operation ‘UNION‘）

最新推荐文章于 2026-04-05 16:35:24 发布

原创最新推荐文章于 2026-04-05 16:35:24 发布 · 1.3k 阅读

19 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #数据库 #1271 #字符集 #校对规则

数据库专栏收录该内容

22 篇文章

订阅专栏

该文章已生成可运行项目，

出错分析

错误 1271 - illegal mix of collations for operation ‘UNION’

这是因为 UNION 的时候，不同表的 categorykid、title 等字段的字符集 / 校对规则（collation）不一致。

举个例子：

TableA.title 可能是 utf8_general_ci
TableB.title 可能是 utf8mb4_general_ci

当你用 UNION 合并时，MySQL 必须统一 collation，如果不一致就会报 1271 错误。

✅ 解决办法：在所有子查询里，把需要 UNION 的字符字段统一转换，比如用 CONVERT() 或 CAST()：

CONVERT(m.title USING utf8mb4) AS title,
CONVERT(m.fulltitle USING utf8mb4) AS fulltitle,
CONVERT(m.subtitle USING utf8mb4) AS subtitle,
CONVERT(m.remark USING utf8mb4) AS remark,

最好确认一下所有涉及 UNION 的字符串字段（title, fulltitle, subtitle, remark, url 等）在所有表中都是同一字符集 / collation，如果不是就统一修改表结构：

ALTER TABLE TableA MODIFY title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

上面是比较针对性的解决办法，下面是批量化的解决办法

批量查询所有字段

既然错误是 illegal mix of collations，那现在我们检查 哪些字段的 collation 不一致。

下面这个 SQL 可以找出 demo 数据库里所有字符类型字段（CHAR/VARCHAR/TEXT），并显示它们的 表名、字段名、字符集和排序规则：

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME,
    COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'demo'
  AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext')
ORDER BY COLLATION_NAME, TABLE_NAME, COLUMN_NAME;

用法

执行上面的 SQL。
结果里会显示：
- 表名
- 字段名
- 字符集（比如 utf8、utf8mb4、latin1）
- 排序规则（比如 utf8_general_ci、utf8mb4_unicode_ci）

这样就能一眼看到 哪些表/字段用的不是 utf8mb4_general_ci，它们就是 UNION 报错的元凶。

批量修改

好 👍 既然你已经确认有很多字段是 utf8mb3 / utf8mb3_general_ci，我们可以写一个 SQL 生成器，自动生成修改语句，把所有字段统一为 utf8mb4 / utf8mb4_general_ci。

⚠️ 强烈建议先备份数据库，因为修改字段字符集可能会重新编码数据。

一次性生成修改语句的 SQL

执行下面的 SQL，会输出一堆 ALTER TABLE ... MODIFY COLUMN ...，你复制结果再执行即可：

注意，这里需要先输出一堆语句，再复制后用这堆语句重新查询

SELECT 
    CONCAT(
        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
        '` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE,
        ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
        CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END,
        CASE WHEN COLUMN_DEFAULT IS NOT NULL 
             THEN CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)) 
             ELSE '' END,
        ';'
    ) AS alter_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'demo'
  AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext')
  AND (CHARACTER_SET_NAME = 'utf8mb3' OR COLLATION_NAME LIKE 'utf8mb3%');

脚本说明

它会扫描 demo 数据库里所有 字符型字段（CHAR, VARCHAR, TEXT 等）。
找出 utf8mb3 / utf8mb3_general_ci 的字段。
生成对应的 ALTER TABLE ... MODIFY COLUMN ... CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 语句。
保留原来的字段长度、是否允许 NULL、默认值。

示例输出

假设表 news 里有个字段：

title varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL

那么生成的 SQL 会是：

ALTER TABLE `demo`.`news` 
  MODIFY `title` varchar(255) 
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

👉 建议执行顺序：

先运行上面的 SQL，把结果导出来。
检查一下是否正确。
批量执行生成的 ALTER TABLE 语句。

批量解决漏网之鱼

有可能改完以后会发现有一大批的utf8mb4_0900_ai_ci，而你可能希望都是utf8mb4_general_ci

MySQL 8.0 里，默认的 utf8mb4 排序规则变成了 utf8mb4_0900_ai_ci（基于 Unicode 9.0 的规则）。
所以执行 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 或 ALTER COLUMN ... CHARACTER SET utf8mb4 时，如果不显式指定 COLLATE，MySQL 就会自动用这个 新的默认规则，而不是 utf8mb4_general_ci。

✅ 解决方法

如果你想让所有表、所有字段都统一成 utf8mb4_general_ci，需要在 ALTER 时强制指定 COLLATE utf8mb4_general_ci。

下面来一段 把所有非 utf8mb4_general_ci 的字段改成 utf8mb4_general_ci 的语句：

SELECT 
    CONCAT(
        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
        '` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE,
        ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
        CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END,
        CASE WHEN COLUMN_DEFAULT IS NOT NULL 
             THEN CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)) 
             ELSE '' END,
        ';'
    ) AS alter_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'demo'
  AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext')
  AND COLLATION_NAME <> 'utf8mb4_general_ci';

执行步骤

操作与上个部分是一样的，两步走：

运行上面的 SQL，它会生成一堆 ALTER TABLE ... MODIFY ... 语句。
把结果复制出来执行，就能把所有字段的排序规则改为 utf8mb4_general_ci。

整合成一段来执行

👌 那我给你一个完整的 MySQL 脚本，分三步走，确保数据库、表和字段都统一为 utf8mb4 + utf8mb4_general_ci。

⚠️ 强烈建议先完整备份数据库（mysqldump 或其它工具），因为 ALTER 会改动结构、可能重建索引。

🔹完整脚本

-- 第 1 步：修改数据库默认字符集
ALTER DATABASE `demo`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

-- 第 2 步：生成并执行所有表的 ALTER TABLE，统一表的默认字符集
SELECT CONCAT(
    'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
    '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
) AS alter_table_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'demo'
  AND TABLE_TYPE = 'BASE TABLE';

-- 👆 把结果复制出来执行，修改所有表

-- 第 3 步：生成所有需要修改字段的 SQL，把字段统一为 utf8mb4_general_ci
SELECT 
    CONCAT(
        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
        '` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE,
        ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
        CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END,
        CASE WHEN COLUMN_DEFAULT IS NOT NULL 
             THEN CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)) 
             ELSE '' END,
        ';'
    ) AS alter_column_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'demo'
  AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext')
  AND COLLATION_NAME <> 'utf8mb4_general_ci';

-- 👆 把结果复制出来执行，修改所有字段

🔹脚本执行顺序

运行 第 1 步 → 修改数据库默认字符集。
运行 第 2 步，拿到所有 ALTER TABLE ... CONVERT ...，复制执行。
运行 第 3 步，拿到所有 ALTER TABLE ... MODIFY COLUMN ...，复制执行。

🔹验证

执行完毕后，检查是否统一成功：

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'demo'
  AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext')
ORDER BY TABLE_NAME, COLUMN_NAME;

所有 COLLATION_NAME 应该都变成 utf8mb4_general_ci。

进一步整合

把 第 2 步和第 3 步的 SQL 自动拼接成一条 GROUP_CONCAT 的大语句，这样不用手动复制粘贴，可以直接执行一条

⚠️ 再次提示：执行前一定要备份数据库，因为所有表和字段的字符集都会被修改。

🔹完整自动脚本（MySQL 8.0）

-- 第 1 步：修改数据库默认字符集
ALTER DATABASE `demo`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

-- 第 2 + 3 步：修改所有表和字段
SET SESSION group_concat_max_len = 1000000;

SELECT GROUP_CONCAT(alter_sql SEPARATOR ' ')
INTO @ddl_sql
FROM (
    -- 表级别修改：把表默认字符集和字段转换成 utf8mb4_general_ci
    SELECT CONCAT(
        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
        '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
    ) AS alter_sql
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'demo'
      AND TABLE_TYPE = 'BASE TABLE'

    UNION ALL

    -- 字段级别修改：确保所有字符列强制为 utf8mb4_general_ci
    SELECT CONCAT(
        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
        '` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE,
        ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
        CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END,
        CASE WHEN COLUMN_DEFAULT IS NOT NULL 
             THEN CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)) 
             ELSE '' END,
        ';'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'demo'
      AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext')
      AND COLLATION_NAME <> 'utf8mb4_general_ci'
) t;

-- 执行拼接好的 SQL
PREPARE stmt FROM @ddl_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

🔹脚本做了什么

修改数据库默认字符集为 utf8mb4_general_ci。
自动生成 所有表的 CONVERT TO utf8mb4_general_ci 语句。
自动生成 所有非 utf8mb4_general_ci 的字段修改语句。
用 GROUP_CONCAT 拼成一个大 SQL，在一次 PREPARE ... EXECUTE 里跑掉。

🔹验证结果

执行完后，你可以跑这个检查：

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'demo'
  AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext')
ORDER BY TABLE_NAME, COLUMN_NAME;

所有的 COLLATION_NAME 应该都变成 utf8mb4_general_ci ✅

⚠️ 注意事项

如果你的表里有索引，某些排序规则切换时可能会触发索引重建，执行时间可能比较长。
一定要 备份数据库，避免因为 collation 变化导致某些字符比较行为发生变化。
utf8mb4_general_ci 对中文拼音排序支持一般，如果你后面需要精准的 Unicode 排序，可能要考虑 utf8mb4_unicode_ci 或 utf8mb4_zh_ci。

在这里插入图片描述