调用带命名参数的MySQL 8存储过程时排序规则冲突问题解决

问题场景 #

创建了带多个命名参数的MySQL 8存储过程，调用时触发排序规则冲突错误：

[HY000][1267] Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

已尝试重建数据库并设为utf8mb4_unicode_ci、修改@@local.collation_server后重建存储过程等操作，但仍报错。

存储过程创建代码：

create definer = lardev@localhost procedure sp_getFilteredProductsWithDiscounts(IN in_status varchar(1), IN in_discountPriceAllowed tinyint unsigned, IN in_in_stock varchar(1), IN in_stock_qty mediumint, IN in_discounts_qty mediumint)
BEGIN
SELECT products.id, products.title, products.sale_price,
    GROUP_CONCAT(CONCAT(discounts.name, ': ', discounts.min_qty, ': ', discounts.max_qty, ': ', discounts.percent)) AS discount_info
FROM products
    LEFT JOIN discount_product ON discount_product.product_id = products.id
    LEFT JOIN discounts on discounts.id = discount_product.discount_id
    WHERE ( products.status = in_status OR ISNULL(in_status) ) AND
      ( products.discount_price_allowed = in_discountPriceAllowed OR ISNULL(in_discountPriceAllowed)) AND
      ( products.in_stock = 1 OR ISNULL(in_in_stock) ) AND
      ( products.stock_qty >= in_stock_qty OR ISNULL(in_stock_qty) ) AND
      ( in_discounts_qty BETWEEN discounts.min_qty  AND discounts.max_qty OR ISNULL(in_discounts_qty))
    GROUP BY products.id, products.title, products.sale_price;
END;

调用语句：

CALL sp_getFilteredProductsWithDiscounts(@in_status := 'A', @in_discountPriceAllowed := 1, @in_in_stock := 1, @in_stock_qty := 2, @in_discounts_qty := 3 );

解决方法 #

冲突根源是字符串类型的存储过程参数（in_status、in_in_stock）继承了MySQL 8默认的utf8mb4_0900_ai_ci排序规则，和表字段的utf8mb4_unicode_ci不匹配，可通过以下方式解决：

1. 显式指定存储过程参数的排序规则

修改存储过程定义，给字符串参数加上COLLATE utf8mb4_unicode_ci，确保和表字段排序规则一致：

create definer = lardev@localhost procedure sp_getFilteredProductsWithDiscounts(
    IN in_status varchar(1) COLLATE utf8mb4_unicode_ci, 
    IN in_discountPriceAllowed tinyint unsigned, 
    IN in_in_stock varchar(1) COLLATE utf8mb4_unicode_ci, 
    IN in_stock_qty mediumint, 
    IN in_discounts_qty mediumint
)
BEGIN
SELECT products.id, products.title, products.sale_price,
    GROUP_CONCAT(CONCAT(discounts.name, ': ', discounts.min_qty, ': ', discounts.max_qty, ': ', discounts.percent)) AS discount_info
FROM products
    LEFT JOIN discount_product ON discount_product.product_id = products.id
    LEFT JOIN discounts on discounts.id = discount_product.discount_id
    WHERE ( products.status = in_status OR ISNULL(in_status) ) AND
      ( products.discount_price_allowed = in_discountPriceAllowed OR ISNULL(in_discountPriceAllowed)) AND
      ( products.in_stock = 1 OR ISNULL(in_in_stock) ) AND
      ( products.stock_qty >= in_stock_qty OR ISNULL(in_stock_qty) ) AND
      ( in_discounts_qty BETWEEN discounts.min_qty  AND discounts.max_qty OR ISNULL(in_discounts_qty))
    GROUP BY products.id, products.title, products.sale_price;
END;

2. 调用时给参数指定排序规则

如果不想修改存储过程，可在调用时为字符串参数显式指定排序规则：

CALL sp_getFilteredProductsWithDiscounts(
    @in_status := 'A' COLLATE utf8mb4_unicode_ci, 
    @in_discountPriceAllowed := 1, 
    @in_in_stock := '1' COLLATE utf8mb4_unicode_ci, 
    @in_stock_qty := 2, 
    @in_discounts_qty := 3 
);

3. 全局统一排序规则

修改MySQL配置文件（my.cnf/my.ini），设置全局默认排序规则：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

保存后重启MySQL服务，然后重新创建存储过程，确保所有表、字段的排序规则也统一为utf8mb4_unicode_ci。

验证 #

完成上述操作后，重新调用存储过程，即可解决排序规则冲突问题。

内容的提问来源于stack exchange，提问作者mstdmstd