處理 Null 值Handling Null Values

當資料行中的值未知或遺失時,便會使用關聯式資料庫中的 Null 值。A null value in a relational database is used when the value in a column is unknown or missing. Null 既不是空字串 (針對字元或 datetime 資料型別),也不是零值 (針對數值資料型別)。A null is neither an empty string (for character or datetime data types) nor a zero value (for numeric data types). 根據 ANSI SQL-92 規格的內容,對所有的資料型別而言,Null 必須都是相同的,以便可一致處理所有的 Null。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 命名空間可以提供 Null 語意。The System.Data.SqlTypes namespace provides null semantics by implementing the INullable interface. System.Data.SqlTypes 中的每個資料型別都具有自己的 IsNull 屬性及 Null 值,而該值可以指派給該資料型別的執行個體 (Instance)。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. 當實質型別為 boxed 和 unboxed 時,這項功能特別有用,可強化與物件型別的相容性。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 可為 null 的類型,在 Visual Basic 中,請參閱可為 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.

Null 及三種值的邏輯Nulls and Three-Valued Logic

在資料行定義中允許 Null 值會將三種值的邏輯引進應用程式。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

因為 Null 會視為 Unknown,所以比較兩個 Null 值時,並不會視為相等的。Because null is considered to be unknown, two null values compared to each other are not considered to be equal. 在使用算術運算子的運算式中,如果有任何運算元為 Null,其結果也會是 Null。In expressions using arithmetic operators, if any of the operands is null, the result is null as well.

Null 及 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. 下列 True 值資料表顯示存在 Null 值時,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 或 Null)。(T=true, F=false, and U=unknown, or null.)

事實資料表Truth 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 選項可控制資料庫中的預設 Null 屬性及針對 Null 值的比較評估。In SQL Server, the ANSI_NULLS option controls both default nullability in the database and evaluation of comparisons against null values. 如果啟用 ANSI_NULLS (預設值),則當測試 Null 值時,必須在運算式中使用 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 時,下列比較永遠會產生 Unknown:For example, the following comparison always yields unknown when ANSI_NULLS is on:

colname > NULL  

對包含 Null 值的變數進行比較也會產生 Unknown:Comparison to a variable containing a null value also yields unknown:

colname > @MyVariable  

使用 IS NULL 或 IS NOT NULL 述詞來測試 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 Customer 資料表中的 TerritoryID 資料行允許 Null 值。For example, the TerritoryID column in the AdventureWorks Customer table allows null values. 如果 SELECT 陳述式除測試其他項目之外,還要測試 Null 值,則其必須包括 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 設為停用,則可以建立使用相等運算子的運算式,以與 Null 進行比較。If you set ANSI_NULLS off in SQL Server, you can create expressions that use the equality operator to compare to null. 不過,您無法阻止不同連接設定該連接的 Null 選項。However, you can't prevent different connections from setting null options for that connection. 不論連接的 ANSI_NULLS 設定為何,都可以使用 IS NULL 來測試 Null 值。Using IS NULL to test for null values always works, regardless of the ANSI_NULLS settings for a connection.

DataSet 中不支援將 ANSI_NULLS 設為停用,因為它永遠會遵循 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 值較特殊,其儲存及指派語意在不同類型系統及儲存系統之間會有所不同。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.

本節將說明 Null 語意,其可指派 Null 值給跨不同類型系統之 DataColumnDataRowThis 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 會強制轉型為適當的強型別 (Strongly Typed) 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. 如果使用隱含轉換運算子,將強型別 Null 值轉換成資料行的資料型別,指派就應該能夠順利完成。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) 相關聯的適當 INullableSqlType.NullIf '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 資料行,則永遠會依據與 DataColumn 相關聯的型別來儲存 Null。For UDT columns, nulls are always stored based on the type associated with the DataColumn. 請考量下列情況:UDT 與不實作 DataColumnINullable (而其子類別實作) 相關聯。Consider the case of a UDT associated with a DataColumn that does not implement INullable while its sub-class does. 在此情況下,如果指派了與衍生類別相關聯的強型別 Null 值,它就會儲存為不具型別的 DbNull.Value,因為 Null 儲存永遠會與 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 檔案期間永遠不會寫出 Null 值 (如同在 xsi:nil 中)。Null values are never written out during serialization to XML files (as in "xsi:nil").

  3. 在序列化為 XML 時,永遠會寫出所有非 Null 值 (包括預設值)。All non-null values, including defaults, are always written out while serializing to XML. 這與 XSD/XML 語意不同,在 XSD/XML 語意中 Null 值 (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 的情況則相反:Null 值是隱含的,而預設值則是明確的。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. IsNull 方法會針對 trueDbNull.Value 傳回 INullable.NullThe IsNull method returns true for both DbNull.Value and INullable.Null.

指派 Null 值Assigning Null Values

任何 System.Data.SqlTypes 執行個體的預設值都為 Null。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 屬性來檢查 Null。Use the IsNull property to check for nulls.

Null 值可指派給 DataColumn,如下列程式碼範例中所示:Null values can be assigned to a DataColumn as shown in the following code example. 您可直接將 Null 值指派給 SqlTypes 變數,而不會觸發例外狀況。You can directly assign null values to SqlTypes variables without triggering an exception.

範例Example

下列程式碼範例會建立 DataTable,其包含兩個定義為 SqlInt32SqlString 的資料行。The following code example creates a DataTable with two columns defined as SqlInt32 and SqlString. 程式碼會加入一個已知值的資料列及一個 Null 值的資料列,然後在 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  

將 Null 值與 SqlType 及 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 值: 如果其中一個或兩個值是 null,則該比較會產生 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 使用 CLR System.Data.SqlTypes 方法時,如果二者都為 Null,則會產生 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.

下列範例示範當針對每個方法傳遞一對 Null 值,然後傳遞一對空字串時,SqlString.Equals 方法及 String.Equals 方法之間結果的差異。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

SQL Server 資料類型和 ADO.NETSQL Server Data Types and ADO.NET
ADO.NET Managed 提供者和 DataSet 開發人員中心ADO.NET Managed Providers and DataSet Developer Center