在MySQL中将TEXT和VARCHAR列连接在一起

polhcujo  于 2023-02-15  发布在  Mysql
关注(0)|答案(1)|浏览(253)

我收到了以下查询和错误消息:

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
    ORDER BY
    USER, TIMESTAMP, ORDER_LINE_ITEM
) SELECT * FROM full_table

错误:

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

这个错误是关于我连接pivoted_standards. DEPARTMENT = transactions_department.DEPARTMENT的位置。这两个变量有相同的排序规则,但是一个是VARCHAR,另一个是文本。我如何将它们连接在一起?在这个查询中是否可以这样做?

cqoc49vn

cqoc49vn1#

您应该能够通过在transactions_department.DEPARTMENT上添加COLLATE utf8mb4_general_ci来解决此问题,如下所示:

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                                                      /*here ^^^^^ */
    USER, TIMESTAMP, ORDER_LINE_ITEM
) SELECT * FROM full_table;

Here's a fiddle example
然而,这不是一个推荐的长期解决方案。我知道人们需要不同的排序规则是有原因的,所以考虑使用类似于dept_idINT的数据类型,并使用不同的排序规则来连接表。
Example fiddle using INT datatype for join

相关问题