处理 Null 值Handling Null Values

在列中的值未知或缺失时,在关系数据库中使用空值。A null value in a relational database is used when the value in a column is unknown or missing. 空既不是空字符串(对于 character 或 datetime 数据类型),也不是零值(对于 numeric 数据类型)。A null is neither an empty string (for character or datetime data types) nor a zero value (for numeric data types). ANSI SQL-92 规范规定,空必须对于所有数据类型均相同,以便以一致的方式处理所有空。The ANSI SQL-92 specification states that a null must be the same for all data types, so that all nulls are handled consistently. System.Data.SqlTypes 命名空间通过实现 INullable 接口,提供空语义。The System.Data.SqlTypes namespace provides null semantics by implementing the INullable interface. System.Data.SqlTypes 中的每种数据类型都有其自己的 IsNull 属性和可分配给该数据类型的实例的 Null 值。Each of the data types in System.Data.SqlTypes has its own IsNull property and a Null value that can be assigned to an instance of that data type.

备注

.NET Framework 2.0 版引入了对可以为 null 的类型的支持,这允许程序员扩展值类型以表示基础类型的所有值。The .NET Framework version 2.0 introduced support for nullable types, which allow programmers to extend a value type to represent all values of the underlying type. 这些 CLR 可以为 null 的类型表示 Nullable 结构的一个实例。These CLR nullable types represent an instance of the Nullable structure. 当值类型为装箱和未装箱,从而增强与对象类型的兼容性时,这个功能特别有用。This capability is especially useful when value types are boxed and unboxed, providing enhanced compatibility with object types. CLR 可以为 null 的类型不用于存储数据库 null 值,因为 ANSI SQL null 值的行为与 null 引用(或 Visual Basic 中的 Nothing)不同。CLR nullable types are not intended for storage of database nulls because an ANSI SQL null does not behave the same way as a null reference (or Nothing in Visual Basic). 为了使用数据库 ANSI SQL null 值,请使用 System.Data.SqlTypes null 值而不使用 NullableFor working with database ANSI SQL null values, use System.Data.SqlTypes nulls rather than Nullable. 有关如何使用 CLR 的详细信息请参阅在 Visual Basic 中为 null 的类型可以为 Null 的值类型,和有关 C#,请参阅使用可以为 Null 的类型For more information on working with CLR nullable types in Visual Basic see Nullable Value Types, and for C# see Using Nullable Types.

空和三值逻辑Nulls and Three-Valued Logic

在列定义中允许空值将三值逻辑引入您的应用程序。Allowing null values in column definitions introduces three-valued logic into your application. 可以将比较计算为以下三个条件之一:A comparison can evaluate to one of three conditions:

  • TrueTrue

  • FalseFalse

  • 未知Unknown

因为空被视作未知,所以,对两个空值进行彼此比较,其结果不被视为相等。Because null is considered to be unknown, two null values compared to each other are not considered to be equal. 在使用算术运算符的表达式中,如果任何操作数为空,结果也为空。In expressions using arithmetic operators, if any of the operands is null, the result is null as well.

空和 SqlBooleanNulls and SqlBoolean

任意 System.Data.SqlTypes 之间的比较都将返回 SqlBooleanComparison between any System.Data.SqlTypes will return a SqlBoolean. 每个 IsNullSqlType 函数都可返回一个 SqlBoolean 并可用于检查 null 值。The IsNull function for each SqlType returns a SqlBoolean and can be used to check for null values. 下面的真值表显示在存在空值时 AND、OR 和 NOT 这三个运算符的计算方式。The following truth tables show how the AND, OR, and NOT operators function in the presence of a null value. (T=true,F=false,U=unknown 或空。)(T=true, F=false, and U=unknown, or null.)

Truth TableTruth Table

理解 ANSI_NULLS 选项Understanding the ANSI_NULLS Option

System.Data.SqlTypes 提供与在 SQL Server 中设置 ANSI_NULLS 选项时相同的语义。System.Data.SqlTypes provides the same semantics as when the ANSI_NULLS option is set on in SQL Server. 所有算术运算符 (+、-,*、 /、 %),按位运算符 (~,&、 |),和大多数函数都返回 null,如果任何操作数或参数为 null,但该属性除外IsNullAll arithmetic operators (+, -, *, /, %), bitwise operators (~, &, |), and most functions return null if any of the operands or arguments is null, except for the property IsNull.

ANSI SQL-92 标准不支持columnName = NULL 在 WHERE 子句中。The ANSI SQL-92 standard does not support columnName = NULL in a WHERE clause. 在 SQL Server 中,ANSI_NULLS 选项既控制数据库中的默认可空性,也控制对空值的比较计算。In SQL Server, the ANSI_NULLS option controls both default nullability in the database and evaluation of comparisons against null values. 如果启用 ANSI_NULLS(这是默认设置),则在测试空值时在表达式中必须使用 IS NULL 运算符。If ANSI_NULLS is turned on (the default), the IS NULL operator must be used in expressions when testing for null values. 例如,在 ANSI_NULLS 为 on 时,以下比较始终生成 unknown:For example, the following comparison always yields unknown when ANSI_NULLS is on:

colname > NULL  

与包含空值的变量的比较也生成 unknown:Comparison to a variable containing a null value also yields unknown:

colname > @MyVariable  

使用 IS NULL 或 IS NOT NULL 谓词来测试是否有空值。Use the IS NULL or IS NOT NULL predicate to test for a null value. 这可能会增加 WHERE 子句的复杂性。This can add complexity to the WHERE clause. 例如,AdventureWorks 客户表中的 TerritoryID 列允许 null 值。For example, the TerritoryID column in the AdventureWorks Customer table allows null values. 如果 SELECT 语句用于测试是否有空值以及测试其他内容,则它必须包含 IS NULL 谓词:If a SELECT statement is to test for null values in addition to others, it must include an IS NULL predicate:

SELECT CustomerID, AccountNumber, TerritoryID  
FROM AdventureWorks.Sales.Customer  
WHERE TerritoryID IN (1, 2, 3)  
   OR TerritoryID IS NULL  

如果在 SQL Server 中将 ANSI_NULLS 设置为 off,则可以创建使用等于运算符来比较空值的表达式。If you set ANSI_NULLS off in SQL Server, you can create expressions that use the equality operator to compare to null. 但是,您无法阻止不同的连接为该连接设置空选项。However, you can't prevent different connections from setting null options for that connection. 不管连接的 ANSI_NULLS 设置如何,使用 IS NULL 测试是否有空值始终有效。Using IS NULL to test for null values always works, regardless of the ANSI_NULLS settings for a connection.

不支持在 DataSet 中将 ANSI_NULLS 设置为 off,因为前者总是遵守 ANSI SQL-92 标准来处理 System.Data.SqlTypes 中的 null 值。Setting ANSI_NULLS off is not supported in a DataSet, which always follows the ANSI SQL-92 standard for handling null values in System.Data.SqlTypes.

赋予 Null 值Assigning Null Values

空值是特殊的值类型,并且其存储和赋值语义在不同类型系统和存储系统中是不同的。Null values are special, and their storage and assignment semantics differ across different type systems and storage systems. Dataset 已设计为可与不同的类型和存储系统一起使用。A Dataset is designed to be used with different type and storage systems.

本节描述用于在不同类型系统中将空值赋给 DataColumn 中的 DataRow 的空语义。This section describes the null semantics for assigning null values to a DataColumn in a DataRow across the different type systems.

DBNull.Value
此赋值对于任何类型的 DataColumn 都有效。This assignment is valid for a DataColumn of any type. 如果该类型实现 INullable,则会将 DBNull.Value 强制为相应强类型的 Null 值。If the type implements INullable, DBNull.Value is coerced into the appropriate strongly typed Null value.

SqlType.Null
所有 System.Data.SqlTypes 数据类型均实现 INullableAll System.Data.SqlTypes data types implement INullable. 如果可以使用隐式强制转换运算符将强类型的空值转换为该列的数据类型,则应该进行赋值。If the strongly typed null value can be converted into the column's data type using implicit cast operators, the assignment should go through. 否则,将引发无效强制转换异常。Otherwise an invalid cast exception is thrown.

null
如果“null”是给定 DataColumn 数据类型的合法值,则会将其强制为相应的 DbNull.Value 或与 Null 类型关联的 INullable (SqlType.Null)。If 'null' is a legal value for the given DataColumn data type, it is coerced into the appropriate DbNull.Value or Null associated with the INullable type (SqlType.Null)

derivedUdt.Null
对于 UDT 列,null 值始终根据与 DataColumn 关联的类型来存储。For UDT columns, nulls are always stored based on the type associated with the DataColumn. 设想这样的情况:与 DataColumn 关联的 UDT 不实现 INullable,但其子类实现。Consider the case of a UDT associated with a DataColumn that does not implement INullable while its sub-class does. 在这种情况下,如果分配了与派生类关联的强类型 null 值,则它被存储为非类型化的 DbNull.Value,因为空存储始终与 DataColumn 的数据类型一致。In this case, if a strongly typed null value associated with the derived class is assigned, it is stored as an untyped DbNull.Value, because null storage is always consistent with the DataColumn's data type.

备注

Nullable<T> 中当前不支持 NullableDataSet 结构。The Nullable<T> or Nullable structure is not currently supported in the DataSet.

多列(行)赋值Multiple Column (Row) Assignment

DataTable.AddDataTable.LoadDataRow 或其他接受 ItemArray(映射到行)的 API 会将“null”映射到 DataColumn 的默认值。DataTable.Add, DataTable.LoadDataRow, or other APIs that accept an ItemArray that gets mapped to a row, map 'null' to the DataColumn's default value. 如果数组中的对象包含 DbNull.Value 或其强类型对应项,则上述规则同样适用。If an object in the array contains DbNull.Value or its strongly typed counterpart, the same rules as described above are applied.

此外,下面的规则适用于 DataRow.["columnName"] null 赋值的实例:In addition, the following rules apply for an instance of DataRow.["columnName"] null assignments:

  1. 默认值默认值是DbNull.Value所有除了强类型 null 列是相应强类型 null 值。The default default value is DbNull.Value for all except the strongly typed null columns where it is the appropriate strongly typed null value.

  2. 在序列化为 XML 文件(如在“xsi:nil”中)期间,永远不写出空值。Null values are never written out during serialization to XML files (as in "xsi:nil").

  3. 在序列化为 XML 时始终写出所有非空值,包括默认值。All non-null values, including defaults, are always written out while serializing to XML. 这与 XSD/XML 语义不同。在 XSD/XML 语义中,空值 (xsi:nil) 是显式的并且默认值是隐式的(如果在 XML 中不提供,则验证分析程序可以从关联的 XSD 架构获取它)。This is unlike XSD/XML semantics where a null value (xsi:nil) is explicit and the default value is implicit (if not present in XML, a validating parser can get it from an associated XSD schema). 对于 DataTable,反过来也成立:空值是隐式的,默认值是显式的。The opposite is true for a DataTable: a null value is implicit and the default value is explicit.

  4. 对于从 XML 输入读取的行的所有缺少的列值,都赋予 NULL。All missing column values for rows read from XML input are assigned NULL. 使用 NewRow 或类似方法创建的行被赋予 DataColumn 的默认值。Rows created using NewRow or similar methods are assigned the DataColumn's default value.

  5. 对于 IsNulltrueDbNull.Value 方法均返回 INullable.NullThe IsNull method returns true for both DbNull.Value and INullable.Null.

赋予 Null 值Assigning Null Values

任何 System.Data.SqlTypes 实例的默认值都是空。The default value for any System.Data.SqlTypes instance is null.

System.Data.SqlTypes 中的 null 值是类型特定的,不能由单个值(如 DbNull)来表示。Nulls in System.Data.SqlTypes are type-specific and cannot be represented by a single value, such as DbNull. 使用 IsNull 属性可以检查是否有空值。Use the IsNull property to check for nulls.

空值可被赋给 DataColumn,如以下代码示例所示。Null values can be assigned to a DataColumn as shown in the following code example. 您可以将空值直接赋给 SqlTypes 变量,而不会引发异常。You can directly assign null values to SqlTypes variables without triggering an exception.

示例Example

以下代码示例创建一个 DataTable,它具有两列,分别定义为 SqlInt32SqlStringThe following code example creates a DataTable with two columns defined as SqlInt32 and SqlString. 该代码添加一行已知值和一行空值,然后循环访问 DataTable,将这些值赋给变量并在控制台窗口中显示输出。The code adds one row of known values, one row of null values and then iterates through the DataTable, assigning the values to variables and displaying the output in the console window.

static private void WorkWithSqlNulls()
{
    DataTable table = new DataTable();

    // Specify the SqlType for each column.
    DataColumn idColumn =
        table.Columns.Add("ID", typeof(SqlInt32));
    DataColumn descColumn =
        table.Columns.Add("Description", typeof(SqlString));

    // Add some data.
    DataRow nRow = table.NewRow();
    nRow["ID"] = 123;
    nRow["Description"] = "Side Mirror";
    table.Rows.Add(nRow);

    // Add null values.
    nRow = table.NewRow();
    nRow["ID"] = SqlInt32.Null;
    nRow["Description"] = SqlString.Null;
    table.Rows.Add(nRow);

    // Initialize variables to use when
    // extracting the data.
    SqlBoolean isColumnNull = false;
    SqlInt32 idValue = SqlInt32.Zero;
    SqlString descriptionValue = SqlString.Null;

    // Iterate through the DataTable and display the values.
    foreach (DataRow row in table.Rows)
    {
        // Assign values to variables. Note that you 
        // do not have to test for null values.
        idValue = (SqlInt32)row["ID"];
        descriptionValue = (SqlString)row["Description"];

        // Test for null value in ID column.
        isColumnNull = idValue.IsNull;

        // Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}",
            isColumnNull, idValue, descriptionValue);
        Console.WriteLine();
    }
Private Sub WorkWithSqlNulls()
    Dim table As New DataTable()

    ' Specify the SqlType for each column.
    Dim idColumn As DataColumn = _
      table.Columns.Add("ID", GetType(SqlInt32))
    Dim descColumn As DataColumn = _
      table.Columns.Add("Description", GetType(SqlString))

    ' Add some data.
    Dim row As DataRow = table.NewRow()
    row("ID") = 123
    row("Description") = "Side Mirror"
    table.Rows.Add(row)

    ' Add null values.
    row = table.NewRow()
    row("ID") = SqlInt32.Null
    row("Description") = SqlString.Null
    table.Rows.Add(row)

    ' Initialize variables to use when
    ' extracting the data.
    Dim isColumnNull As SqlBoolean = False
    Dim idValue As SqlInt32 = SqlInt32.Zero
    Dim descriptionValue As SqlString = SqlString.Null

    ' Iterate through the DataTable and display the values.
    For Each row In table.Rows
        ' Assign values to variables. Note that you 
        ' do not have to test for null values.
        idValue = CType(row("ID"), SqlInt32)
        descriptionValue = CType(row("Description"), SqlString)

        ' Test for null value with ID column
        isColumnNull = idValue.IsNull

        ' Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}", _
          isColumnNull, idValue, descriptionValue)
        Console.WriteLine()
    Next row
End Sub

此示例显示以下结果:This example displays the following results:

isColumnNull=False, ID=123, Description=Side Mirror  
isColumnNull=True, ID=Null, Description=Null  

将空值与 SqlTypes 和 CLR 类型进行比较Comparing Null Values with SqlTypes and CLR Types

比较 null 值时,必须了解 Equals 方法在 System.Data.SqlTypes 中计算 null 值的方式与处理 CLR 类型的方式之间的差别。When comparing null values, it is important to understand the difference between the way the Equals method evaluates null values in System.Data.SqlTypes as compared with the way it works with CLR types. 所有 System.Data.SqlTypesEquals 方法都使用数据库语义计算 null 值:如果其中任何一个值为空或两个值都为空,则比较结果将为空。All of the System.Data.SqlTypesEquals methods use database semantics for evaluating null values: if either or both of the values is null, the comparison yields null. 另一方面,如果两个 Equals 都为 null,则对其使用 CLR System.Data.SqlTypes 方法将生成 true。On the other hand, using the CLR Equals method on two System.Data.SqlTypes will yield true if both are null. 这反映了使用实例方法(如 CLR String.Equals 方法)和使用静态/共享方法 SqlString.Equals 之间的差别。This reflects the difference between using an instance method such as the CLR String.Equals method, and using the static/shared method, SqlString.Equals.

下面的示例演示为 SqlString.Equals 方法和 String.Equals 方法传递一对 null 值,然后传递一对空字符串时,这两种方法生成的结果之间存在的差异。The following example demonstrates the difference in results between the SqlString.Equals method and the String.Equals method when each is passed a pair of null values and then a pair of empty strings.

    private static void CompareNulls()
    {
        // Create two new null strings.
        SqlString a = new SqlString();
        SqlString b = new SqlString();

        // Compare nulls using static/shared SqlString.Equals.
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b));

        // Compare nulls using instance method String.Equals.
        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two nulls={0}", StringEquals(a, b));

        // Make them empty strings.
        a = "";
        b = "";

        // When comparing two empty strings (""), both the shared/static and
        // the instance Equals methods evaluate to true.
        Console.WriteLine();
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b));

        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two empty strings={0}", StringEquals(a, b));
    }
    
    private static string SqlStringEquals(SqlString string1, SqlString string2)
    {
        // SqlString.Equals uses database semantics for evaluating nulls.
        string returnValue = SqlString.Equals(string1, string2).ToString();
        return returnValue;
    }

    private static string StringEquals(SqlString string1, SqlString string2)
    {
        // String.Equals uses CLR type semantics for evaluating nulls.
        string returnValue = string1.Equals(string2).ToString();
        return returnValue;
    }
}
Private Sub CompareNulls()
    ' Create two new null strings.
    Dim a As New SqlString
    Dim b As New SqlString

    ' Compare nulls using static/shared SqlString.Equals.
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b))

    ' Compare nulls using instance method String.Equals.
    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two nulls={0}", StringEquals(a, b))

    ' Make them empty strings.
    a = ""
    b = ""

    ' When comparing two empty strings (""), both the shared/static and
    ' the instance Equals methods evaluate to true.
    Console.WriteLine()
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b))

    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two empty strings={0}", StringEquals(a, b))
End Sub

Private Function SqlStringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' SqlString.Equals uses database semantics for evaluating nulls.
    Dim returnValue As String = SqlString.Equals(string1, string2).ToString()
    Return returnValue
End Function

Private Function StringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' String.Equals uses CLR type semantics for evaluating nulls.
    Dim returnValue As String = string1.Equals(string2).ToString()
    Return returnValue
End Function

此代码生成以下输出内容:The code produces the following output:

SqlString.Equals shared/static method:  
  Two nulls=Null  
  
String.Equals instance method:  
  Two nulls=True  
  
SqlString.Equals shared/static method:  
  Two empty strings=True  
  
String.Equals instance method:  
  Two empty strings=True   

请参阅See also