数据库为utf8mb4_unicode_ci，视图计算字段sku_class排序规则异常求助

问题背景 #

数据库及所有WooCommerce关联表、字段均已设置为utf8mb4_unicode_ci排序规则，但创建视图后，通过CASE生成的计算字段sku_class排序规则自动变成了utf8mb4_general_ci。此前为解决「illegal mix of collations」错误，已经添加了SET character_set_connection = 'utf8mb4';和COLLATE 'utf8mb4_unicode_ci'语句，创建视图的简化SQL如下：

SET character_set_connection = 'utf8mb4';

CREATE VIEW vw_wc_product_details AS 

SELECT
        pm.sku,
        CASE
            -- manual fixes 
            WHEN pm.sku LIKE 'AB1404/%' THEN 'c'    
            -- default rules
            WHEN pm.sku LIKE 'ABs__-_%' THEN 's'    
            WHEN pm.sku LIKE 'ABt__-_%' THEN 't'
            WHEN pm.sku LIKE 'AB____/%' THEN 'pc'  
            WHEN ( SELECT COUNT(sku) FROM wp_wc_product_meta_lookup WHERE sku LIKE CONCAT(pm.sku, '/_%') ) >= 1 THEN 'p' 
            WHEN pm.sku LIKE 'AB____%' AND LENGTH(pm.sku) = 6 THEN 'c' 
            WHEN pm.sku LIKE 'AX%' AND LENGTH(pm.sku) >= 6 THEN 'm'
            ELSE 'x'
        END as sku_class
    
FROM    wp_wc_product_attributes_lookup AS pa
LEFT    JOIN wp_terms AS t ON pa.term_id = t.term_id
LEFT    JOIN wp_wc_product_meta_lookup AS pm ON pa.product_id = pm.product_id
LEFT    JOIN wp_posts AS p ON pa.product_id = p.ID

COLLATE 'utf8mb4_unicode_ci'

原因分析 #

• CASE语句中返回的字符串常量（如'c'、's'），其排序规则默认继承自当前数据库连接的collation_connection参数，而utf8mb4字符集的默认排序规则就是utf8mb4_general_ci，不受表或全局排序规则的影响。
• 你添加的COLLATE 'utf8mb4_unicode_ci'仅作用于查询的表连接和字段比较环节，不会自动应用到CASE生成的计算字段上。

解决方法 #

方法一：为CASE结果显式指定排序规则 #

直接在CASE语句的END后添加COLLATE utf8mb4_unicode_ci，强制指定计算字段的排序规则：

SET character_set_connection = 'utf8mb4';

CREATE VIEW vw_wc_product_details AS 

SELECT
        pm.sku,
        CASE
            -- manual fixes 
            WHEN pm.sku LIKE 'AB1404/%' THEN 'c'    
            -- default rules
            WHEN pm.sku LIKE 'ABs__-_%' THEN 's'    
            WHEN pm.sku LIKE 'ABt__-_%' THEN 't'
            WHEN pm.sku LIKE 'AB____/%' THEN 'pc'  
            WHEN ( SELECT COUNT(sku) FROM wp_wc_product_meta_lookup WHERE sku LIKE CONCAT(pm.sku, '/_%') ) >= 1 THEN 'p' 
            WHEN pm.sku LIKE 'AB____%' AND LENGTH(pm.sku) = 6 THEN 'c' 
            WHEN pm.sku LIKE 'AX%' AND LENGTH(pm.sku) >= 6 THEN 'm'
            ELSE 'x'
        END COLLATE utf8mb4_unicode_ci as sku_class
    
FROM    wp_wc_product_attributes_lookup AS pa
LEFT    JOIN wp_terms AS t ON pa.term_id = t.term_id
LEFT    JOIN wp_wc_product_meta_lookup AS pm ON pa.product_id = pm.product_id
LEFT    JOIN wp_posts AS p ON pa.product_id = p.ID

COLLATE 'utf8mb4_unicode_ci'

方法二：修改连接的默认排序规则 #

在创建视图前，除了设置character_set_connection，额外设置collation_connection为utf8mb4_unicode_ci，让所有字符串常量默认使用该排序规则：

SET character_set_connection = 'utf8mb4';
SET collation_connection = 'utf8mb4_unicode_ci';

CREATE VIEW vw_wc_product_details AS 

SELECT
        pm.sku,
        CASE
            -- manual fixes 
            WHEN pm.sku LIKE 'AB1404/%' THEN 'c'    
            -- default rules
            WHEN pm.sku LIKE 'ABs__-_%' THEN 's'    
            WHEN pm.sku LIKE 'ABt__-_%' THEN 't'
            WHEN pm.sku LIKE 'AB____/%' THEN 'pc'  
            WHEN ( SELECT COUNT(sku) FROM wp_wc_product_meta_lookup WHERE sku LIKE CONCAT(pm.sku, '/_%') ) >= 1 THEN 'p' 
            WHEN pm.sku LIKE 'AB____%' AND LENGTH(pm.sku) = 6 THEN 'c' 
            WHEN pm.sku LIKE 'AX%' AND LENGTH(pm.sku) >= 6 THEN 'm'
            ELSE 'x'
        END as sku_class
    
FROM    wp_wc_product_attributes_lookup AS pa
LEFT    JOIN wp_terms AS t ON pa.term_id = t.term_id
LEFT    JOIN wp_wc_product_meta_lookup AS pm ON pa.product_id = pm.product_id
LEFT    JOIN wp_posts AS p ON pa.product_id = p.ID

COLLATE 'utf8mb4_unicode_ci'

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