COALESCE Extrage在日期和字符串上的结果 [英] COALESCE Extrage results on dates and strings

问题描述

首先，我想澄清一下，我已经阅读了这个问题.其中描述的问题与我的问题非常相似，但是与一个错误相关，该错误已得到解决.

First of all, I want to clarify that I have already read this question. The problem described there is quite similar to my problem but related with a bug, which has been already solved.

通过Windows上运行的MySQL WorkBench 5.2.47 C查询MySQL服务器(在Linux上运行14.14版)后，我得到了奇怪的结果.

I have been getting strange results after querying a MySQL Server (Ver 14.14 running on Linux) through MySQL WorkBench 5.2.47 C running on Windows.

问题在于日期和字符串字段列表上的COALESCE上的选择将返回BLOBS而不是日期.例如

The problem is that selections on COALESCE over a list of date and string fields return BLOBS instead dates. For example

SET @dat='20130812';
SELECT COALESCE(dateA, @dat) FROM table1;        

我知道可以通过将COALESCE转换为DATE类型来解决此问题:

I know the problem can be fixed by casting COALESCE into DATE type:

SELECT CAST(COALESCE(dateA,$dat) AS DATE) FROM table1;

也可以通过投射@dat来解决:

It can also be solved just casting @dat:

SELECT COALESCE(dateA,CAST(@dat AS DATE)) FROM table1;

在我看来，这与内部类型转换有关，但是当客户端是mysql控制台客户端时，为什么我的第一个查询有效? (正如上面链接的问题所述).

It seems to me that it is related to internal type conversion but, why does my first query work when the client is the mysql console client? (just as described on the question linked above).

客户端是否执行某种类型的查询预处理?在那种情况下，不是所有客户都普遍吗?

Does the client perform some type of query preprocessing? In that case, shouldn't it be common to all clients?

我曾经认为SQL Server从其客户端接收到原始查询(文本)，现在我意识到这根本不是真的.在客户端执行什么级别的预处理?

I used to think that SQL servers received raw queries (text) from their clients, now I realise that isn't true at all. What level of preprocessing is performed at client side?

推荐答案

最后，看来我是正确的，认为在客户端不进行任何困难的预处理.

At the end, it seems I was right thinking that not hard preprocessing is done at the client side.

区别在于数据可视化:隐式规则类型转换适用于COALESCE，因此，当其输入列表的某些元素是日期而其他元素是字符串时，这些规则使所有元素都隐式转换为包含每个日期字符串表示形式的BLOB.

The difference comes at data visualization: The rules for implicit type conversion apply on COALESCE so, when some elements of its input list are dates and others are strings, these rules make all elements to be implicitly cast to BLOBs containing each date string representation.

如果其中一个参数是TIMESTAMP或DATETIME列，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳.这样做是为了使ODBC更友好.请注意，对于IN()的参数，此操作未完成！为了安全起见，在进行比较时，请始终使用完整的日期时间，日期或时间字符串.例如，为了在将BETWEEN与日期或时间值一起使用时获得最佳结果，请使用CAST()将这些值显式转换为所需的数据类型.

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

另一方面，mysql控制台客户端自动将此斑点显示为文本，而MySQL工作台使用其BLOB表示形式.

On the other hand, the mysql console client automatically shows this blobs as text whereas MySQL workbench uses its BLOB representation.

如果打开:

可以将其读为文本(默认情况下，这是mysql控制台客户端所做的事情):

Which can be read as text (that is what mysql console client does by default):

在我看来，根据我在上面引用的MySQL参考文本，解决此问题的最佳方法是将date元素转换为date.这样，不会进行任何隐式转换，并且COALESCE返回日期类型值.

In my opinion and based on the MySQL reference text I quoted above, the best way to cope with this problem is to cast the date element to date. This way, no implicit conversions are made and COALESCE returns a date type value.

这篇关于COALESCE Extrage在日期和字符串上的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！