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

问题描述

这来自

这是两种不同的空值吗?有什么区别

注:改编自

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

<cfset dateTimeNow = now()><cfset qTest.NonSpecifiedNull[1] = dateTimeNow><cfset qTest.DateTimeNull[1] = dateTimeNow><cfset qTest.VarcharNull[1] = dateTimeNow><cfdump var="#qTest#" label="查询值">

结果会很不一样.插入DateTimeNull"的值仍然是一个日期对象.但是，相同的值在插入VarcharNull"时将转换为字符串，而在插入NonSpecifiedNull"时将转换为整数.后两者几乎肯定不是您期望或希望处理日期对象的方式.这就是为什么最好在原始 SQL 查询中包含适当的 CAST.

Granted ColdFusion 非常宽容，而且相对无类型.因此，将日期对象存储为字符串或整数可能并不总是会导致硬错误.但是，它确实会强制进行隐式转换，这充其量是不必要的，最坏的情况是容易出错和/或可能产生错误的结果.例如，如果您将原始日期与NonSpecifiedNull"列进行比较，您可能会认为它们是相等的，但是......由于数据类型，它们不是:

dateCompare(dateTimeNow, qTest.NonSpecifiedNull[1]) eq 0 ?相等":不同"

同样，最好预先将 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屋！