解决 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 实验设计

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 ;

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

2.2 实验过程

1. 在字符集 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)

1. 在字符集 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)

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

-- 在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)

1. 更改数据库 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)

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

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），再重新创建，才能使其使用新的数据库默认字符集和排序规则。