当前位置：首页 > news >正文

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

news 2026/5/23 16:31:49

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

查看全文

http://www.jsqmd.com/news/855250/