有不同种类的NULL吗？ [英] Are there different kinds of NULLs?

问题描述

这来自

 < cfquery name =data> 
 SELECT 
 NULL as plainNull，
 CAST（NULL AS datetime）AS Due_Date 
 
< / cfquery> 
  

这两种不同的null？有什么区别

注意：改编自

从ColdFusion的角度来看，数据类型会影响列值如何解释各种函数，比较，QoQ和甚至当修改查询对象本身时。例如，使用日期时间对象更新三个列中的每一个：

 < cfset dateTimeNow = now（）> 
< cfset qTest.NonSpecifiedNull [1] = dateTimeNow> 
< cfset qTest.DateTimeNull [1] = dateTimeNow> 
< cfset qTest.VarcharNull [1] = dateTimeNow> 
 
< cfdump var =＃qTest＃label =Query Values> 
  

结果会有很大的不同。插入到DateTimeNull中的值仍为日期对象。但是，当插入到VarcharNull中时，相同的值将被转换为字符串，并且当插入到NonSpecifiedNull中时，该值将被转换为整数。后两者几乎肯定不是你希望或希望的日期对象被处理。这就是为什么最好在原始SQL查询中包含适当的 CAST 。

授予的ColdFusion是非常原谅的，并且相对类型化。因此，将日期对象存储为字符串或整数可能不会总是导致硬错误。然而，它强制隐式转换，这是最多不必要的，在最坏的情况下容易出错和/或可能产生错误的结果。例如，如果将原始日期与NonSpecifiedNull列相比较，您可能希望它们相等，但是它们不是，因为数据类型：

  dateCompare（dateTimeNow，qTest.NonSpecifiedNull [1]）eq 0？ EQUAL：DIFFERENT
  

再次，最好 CAST 到正确的类型，以确保预期的结果。

This came from

<cfquery name="data">
     SELECT
        NULL                   AS plainNull,
        CAST(NULL AS datetime) AS Due_Date

</cfquery>

Are these two different kinds of nulls? What is the difference

Note: adapted from How can I call a function in another CFC file from within a query of a function in one cfc file?

解决方案

Yes, there is a difference - but not the way you may be thinking.

It is not that NULL has a data type per se, one will be associated with it (implicitly or explicitly) as part of evaluating the SQL. With the SELECT statement above, the data type applies to the column metadata. Without an explicit CAST, the data type defaults to INT.

Take this query:

<cfquery name="qTest" datasource="#variables.sqlServerDSN#">
    SELECT NULL AS NonSpecifiedNull
        , CAST(NULL AS datetime) AS DateTimeNull
        , CAST(NULL AS varchar(25)) AS VarcharNull
</cfquery>

<cfdump var="#getMetaData(qTest)#" label="Query MetaData">

If you examine the query metadata, the query contains columns with three distinct data types: integer, datetime and varchar:

From a ColdFusion standpoint, the data type impacts how the column values may be interpreted in various functions, comparisons, QoQ's and even when modifying the query object itself. For example, update each of the three columns with a date time object:

<cfset dateTimeNow = now()>
<cfset qTest.NonSpecifiedNull[1] = dateTimeNow>
<cfset qTest.DateTimeNull[1] = dateTimeNow>
<cfset qTest.VarcharNull[1] = dateTimeNow>

<cfdump var="#qTest#" label="Query Values">

The results will be very different. The value inserted into "DateTimeNull" remains a date object. However, the same value will be converted into a string when inserted into "VarcharNull" and an integer when inserted into "NonSpecifiedNull". The latter two are almost certainly not how you would expect, or want, date objects to be handled. That is why it is better to include the appropriate CAST in the original SQL query.

Granted ColdFusion is very forgiving, and relatively typeless. So storing date objects as a string or integer may not always cause hard errors. However, it does force implicit conversion, which is at best unnecessary, at worst prone to error and/or likely to produce the wrong result. For example, if you compared original date to the "NonSpecifiedNull" column, you would probably expect them to be equal, but .. they are not, due to the data type:

dateCompare(dateTimeNow, qTest.NonSpecifiedNull[1]) eq 0 ? "EQUAL" : "DIFFERENT"

So again, best to CAST to the correct type up front to ensure the expected results.

这篇关于有不同种类的NULL吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！