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

polhcujo 于 2023-02-15 发布在 Mysql

关注(0)|答案(1)|浏览(255)

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

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，另一个是文本。我如何将它们连接在一起？在这个查询中是否可以这样做？

mysql

来源：https://stackoverflow.com/questions/75431305/join-text-and-varchar-columns-together-in-mysql

1条答案

按热度按时间

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_id和INT的数据类型，并使用不同的排序规则来连接表。
Example fiddle using INT datatype for join

赞(0）回复(0）举报 2023-02-15

我来回答

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

1条答案

相关问题

热门标签

最新问答