解决 GreatSQL 报错：存储过程字符集排序规则不兼容问题

关注

原创最新推荐文章于 2026-11-10 15:47:34 发布 305 阅读 AI 写同款· GEO 优化 ›

SpringBoot+Vue+MySQL 高校学科竞赛平台完整可运行版（源码+文档）本项目基于SpringBoot+Vue+MySQL构建高校学科竞赛管理平台，覆盖学生、教师、领队教师、管理员四大角色，实现竞赛发布、报名、刷题、评分、公示等全流程闭环管理。系统架构清晰，技术主流，配套完整源码、数据库脚本、万字论文、开题报告、答辩PPT及部署文档，支持一键运行，适用于毕业设计、课程设计及院系二次开发，真正实现“开箱即用”。阅读详情

解决 GreatSQL 报错：存储过程字符集排序规则不兼容问题

1.问题来源

某用户的应用系统，在执行存储过程时，报如下错误：

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_bin,IMPLICIT) and (utf8mb4_bin,IMPLICIT) for operation '='

错误信息表示：在进行等号（=）运算时，等号前的排序规则是utf8mb4_0900_bin，等号之后的排序规则是utf8mb4_bin。

检查存储过程，确认错误发生在update语句的where条件中，如：

UPDATE customer.cc_od_subs 
SET ...
WHERE product_order_number=var_product_order_number;

即：customer.cc_od_subs的列product_order_number的排序规则是utf8mb4_0900_bin，变量var_product_order_number的排序规则utf8mb4_bin 。

那么，变量var_product_order_number的排序规则为什么是utf8mb4_bin？也就是问题：存储过程中字符串变量的排序规则来自于哪里？

2.实验验证

2.1 实验设计

编写存储过程check_collation，输出存储过程中定义的字符串变量的字符集和排序规则。

delimiter //
CREATE PROCEDURE check_collation()
BEGIN
    DECLARE my_var VARCHAR(255) DEFAULT 'Hello';
    SELECT CHARSET(my_var), COLLATION(my_var);
END;
//
delimiter ;

在不同字符集排序规则的数据库中，创建存储过程check_collation。运行存储过程check_collation，检查输出结果。

2.2 实验过程

在字符集gb18030排序规则gb18030_bin的数据库testdb_gb中创建存储过程。存储过程输出的字符集是gb18030、排序规则gb18030_bin 。

GreatSQL> CREATE DATABASE testdb_gb DEFAULT CHARACTER SET gb18030 COLLATE gb18030_bin;
Query OK, 1 row affected (0.00 sec)

GreatSQL> use testdb_gb;
Database changed
GreatSQL> source checkcollation.sql
Query OK, 0 rows affected (0.00 sec)

GreatSQL> CALL check_collation();
+-----------------+-------------------+
| CHARSET(my_var) | COLLATION(my_var) |
+-----------------+-------------------+
| gb18030         | gb18030_bin       |
+-----------------+-------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

检查存储过程的元数据信息，在information_schema.routines的列DATABASE_COLLATION中记录了存储过程创建时，数据库的排序规则（根据排序规则可知字符集）。

GreatSQL> SELECT * FROM information_schema.routines WHERE routine_schema='testdb_gb' and ROUTINE_name='check_collation'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: check_collation
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: testdb_gb
            ROUTINE_NAME: check_collation
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
    DECLARE my_var VARCHAR(255) DEFAULT 'Hello';
    SELECT CHARSET(my_var), COLLATION(my_var);
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2025-09-03 15:01:42
            LAST_ALTERED: 2025-09-03 15:01:42
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@%
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: gb18030_bin
1 row in set (0.01 sec)

在字符集utf8mb4排序规则utf8mb4_bin的数据库testdb_utf8中创建存储过程。存储过程输出的字符集是utf8mb4、排序规则utf8mb4_bin 。

GreatSQL> CREATE database testdb_utf8;
Query OK, 1 row affected (0.01 sec)

GreatSQL> SHOW CREATE DATABASE testdb_utf8;;
+----------+----------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                            |
+----------+----------------------------------------------------------------------------------------------------------------------------+
| testdb_utf8;  | CREATE DATABASE `testdb_utf8;` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GreatSQL> use testdb_utf8;;
Database changed

GreatSQL> source checkcollation.sql;
Query OK, 0 rows affected (0.01 sec)

GreatSQL> CALL check_collation();
+-----------------+-------------------+
| CHARSET(my_var) | COLLATION(my_var) |
+-----------------+-------------------+
| utf8mb4         | utf8mb4_bin       |
+-----------------+-------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

GreatSQL> SELECT * FROM information_schema.routines WHERE routine_schema='testdb_utf8;' AND ROUTINE_name='check_collation'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: check_collation
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: testdb_utf8;
            ROUTINE_NAME: check_collation
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
    DECLARE my_var VARCHAR(255) DEFAULT 'Hello';
    SELECT CHARSET(my_var), COLLATION(my_var);END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2025-09-03 13:45:02
            LAST_ALTERED: 2025-09-03 13:45:02
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@%
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_bin
1 row in set (0.00 sec)

存储过程输出的字符集和排序规则，与用户所在数据库无关；与存储过程运行时用户的字符集和排序规则变量无关。

-- 在testdb_gb数据库下，调用testdb_utf8.check_collation
GreatSQL> use testdb_gb;
Database changed
GreatSQL> CALL testdb_utf8.check_collation();
+-----------------+-------------------+
| CHARSET(my_var) | COLLATION(my_var) |
+-----------------+-------------------+
| utf8mb4         | utf8mb4_bin       |
+-----------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- 在testdb_utf8数据库下，调用testdb_gdb.check_collation
GreatSQL> use testdb_utf8;
Database changed
GreatSQL> CALL testdb_gb.check_collation();
+-----------------+-------------------+
| CHARSET(my_var) | COLLATION(my_var) |
+-----------------+-------------------+
| gb18030         | gb18030_bin       |
+-----------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

GreatSQL> SHOW variables LIKE '%character_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
+--------------------------+---------+
7 rows in set (0.01 sec)

GreatSQL> SHOW variables LIKE '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_bin        |
| collation_server              | utf8mb4_bin        |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

更改数据库testdb_gb的字符集和排序规则，存储过程check_collation输出仍然是编译时数据库的字符集和排序规则；删除后重新创建存储过程，check_collation输出是新的字符集和排序规则。

GreatSQL> ALTER database testdb_gb DEFAULT CHARACTER SET gb2312 collate gb2312_bin;
Query OK, 1 row affected (0.00 sec)

GreatSQL> CALL testdb_gb.check_collation();
+-----------------+-------------------+
| CHARSET(my_var) | COLLATION(my_var) |
+-----------------+-------------------+
| gb18030         | gb18030_bin       |
+-----------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

GreatSQL> DROP PROCEDURE check_collation;
Query OK, 0 rows affected (0.01 sec)

GreatSQL> source checkcollation.sql
Query OK, 0 rows affected (0.00 sec)

GreatSQL> CALL testdb_gb.check_collation();
+-----------------+-------------------+
| CHARSET(my_var) | COLLATION(my_var) |
+-----------------+-------------------+
| gb2312          | gb2312_bin        |
+-----------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

GreatSQL> SELECT * FROM information_schema.routines WHERE routine_schema='testdb_gb' and ROUTINE_name='check_collation'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: check_collation
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: testdb_gb
            ROUTINE_NAME: check_collation
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
    DECLARE my_var VARCHAR(255) DEFAULT 'Hello';
    SELECT CHARSET(my_var), COLLATION(my_var);END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2025-09-03 15:23:55
            LAST_ALTERED: 2025-09-03 15:23:55
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@%
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: gb2312_bin
1 row in set (0.00 sec)

在存储过程变量定义时，指定字符集和排序规则，则直接使用指定的字符集和排序规则。

delimiter //
CREATE PROCEDURE check_collation2()
BEGIN
    DECLARE my_var VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'Hello';
    SELECT CHARSET(my_var), COLLATION(my_var);
END;
//
delimiter ;
GreatSQL> SHOW CREATE DATABASE testdb_gb;
+----------+--------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                          |
+----------+--------------------------------------------------------------------------------------------------------------------------+
| testdb_gb  | CREATE DATABASE `testdb_gb` /*!40100 DEFAULT CHARACTER SET gb2312 COLLATE gb2312_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GreatSQL> use testdb_gb;
Database changed

GreatSQL> SOURCE checkcollation2.sql
Query OK, 0 rows affected (0.00 sec)

GreatSQL> CALL check_collation2();
+-----------------+-------------------+
| CHARSET(my_var) | COLLATION(my_var) |
+-----------------+-------------------+
| utf8mb4         | utf8mb4_bin       |
+-----------------+-------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

3.总结

存储过程中字符串变量的字符集及排序规则遵循下面原则：

对于字符数据类型，若声明中包含CHARACTER SET（字符集），则使用指定的字符集及其默认排序规则；若同时指定了COLLATE（排序规则）属性，则使用指定的排序规则，而非字符集的默认排序规则。
若未指定CHARACTER SET和COLLATE，则使用存储过程 / 函数（routine）创建时生效的数据库字符集与排序规则。若要避免使用数据库的字符集和排序规则，需为字符数据类型显式指定CHARACTER SET和COLLATE属性。
若修改了数据库的默认字符集或排序规则，需先删除依赖原默认设置的存储程序（stored routines），再重新创建，才能使其使用新的数据库默认字符集和排序规则。