用于日期/时间比较的操作的排序规则的非法混合 [英] Illegal mix of collations for operation for date/time comparison

问题描述

我收到此错误

操作的排序规则的非法混合 <=

Illegal mix of collations for operation <=

对于以下 WHERE 子句:

for the following WHERE clause:

time(table1.dt1)<=date_add(table1.tim,interval 60 second)

dt1 = datetime, tim = time

有人知道为什么吗?

推荐答案

1. 如DATE_ADD():

返回值取决于参数:

• DATETIME 如果第一个参数是 DATETIME(或 TIMESTAMP) 值，或者如果第一个参数是 DATE 并且单位值使用 HOURS、MINUTES 或 SECONDS.

其他字符串.

在你的情况下，因为 table1.tim 是一个 TIME 值，所以函数返回一个字符串.

In your case, because table1.tim is a TIME value, the function is returning a string.

字符串以给出的字符集和排序规则返回character_set_connection 和 collat​​ion_connection.

如的手册条目中所述TIME():

提取时间或日期时间表达式的时间部分expr，并以字符串形式返回.

Extracts the time part of the time or datetime expression expr and returns it as a string.

该字符串作为二进制字符串返回(为什么不使用连接字符集 & 排序规则超出我的范围——也许是一个错误?).

The string is returned as a binary string (why not using the connection character set & collation is beyond me—perhaps a bug?).

如表达式评估中的类型转换:

如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较.

If both arguments in a comparison operation are strings, they are compared as strings.

因此进行的比较是字符串比较，但 binary 字符串不能被强制转换为合适的排序规则进行比较(因为其编码未知).因此，您正在目睹错误.

Therefore the comparison undertaken is a string comparison, but the binary string cannot be coerced to a suitable collation for comparison (as its encoding is not known). Hence the error you are witnessing.

可以通过显式强制转换强制正确进行比较一个或两个操作数，但我很想重新修改子句，以便通过与 TIME 类型的比较隐式地进行转换:

One could force the comparison to be conducted correctly by explicitly casting one or both of the operands, but I'd be tempted just to rework the clause so that conversion takes place implicitly from comparison against a TIME type:

TIME(table1.dt1 - INTERVAL 1 MINUTE) <= table1.tim

这篇关于用于日期/时间比较的操作的排序规则的非法混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！