MySQL中TEXT与VARCHAR列连接时排序规则冲突的解决方法

问题原因 #

连接的两列pivoted_standards.DEPARTMENT和transactions_department.DEPARTMENT排序规则不兼容（分别为utf8mb4_general_ci和utf8mb4_0900_ai_ci），即便列类型为TEXT和VARCHAR，只要统一排序规则就能正常执行JOIN操作。

解决方案 #

在JOIN的ON条件中，通过COLLATE子句显式将其中一列的排序规则转换为与另一列一致：

方法1：将pivoted_standards.DEPARTMENT转换为transactions_department.DEPARTMENT的排序规则

WITH full_table AS (
    SELECT TRANSACTION_ID AS Transaction, USER_ID AS USER, TRANSACTION_TIMESTAMP AS TIMESTAMP, 
           ORDER_LINE_ITEM, TIME_TAKEN, QUANTITY, TIME_STANDARD, 
           transactions_department.DEPARTMENT, (TIME_STANDARD*QUANTITY) AS NORMALIZED_TIME_STANDARD
    FROM pivoted_standards
    INNER JOIN transactions_department 
    ON pivoted_standards.DEPARTMENT COLLATE utf8mb4_0900_ai_ci = transactions_department.DEPARTMENT
    ORDER BY USER, TIMESTAMP, ORDER_LINE_ITEM
) SELECT * FROM full_table 

方法2：将transactions_department.DEPARTMENT转换为pivoted_standards.DEPARTMENT的排序规则

WITH full_table AS (
    SELECT TRANSACTION_ID AS Transaction, USER_ID AS USER, TRANSACTION_TIMESTAMP AS TIMESTAMP, 
           ORDER_LINE_ITEM, TIME_TAKEN, QUANTITY, TIME_STANDARD, 
           transactions_department.DEPARTMENT, (TIME_STANDARD*QUANTITY) AS NORMALIZED_TIME_STANDARD
    FROM pivoted_standards
    INNER JOIN transactions_department 
    ON pivoted_standards.DEPARTMENT = transactions_department.DEPARTMENT COLLATE utf8mb4_general_ci
    ORDER BY USER, TIMESTAMP, ORDER_LINE_ITEM
) SELECT * FROM full_table 

注意事项 #

utf8mb4_0900_ai_ci是基于Unicode 9.0的现代排序规则，排序准确性优于旧版的utf8mb4_general_ci，建议优先选择方法1。

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