處理 Null 值

發行項
03/08/2024

在關聯式資料庫中，當資料行中的值為未知或遺漏時，就會使用 Null 值。 Null 不是空字串 (針對字元或日期時間資料類型)，也不是零值 (針對數值資料類型)。 ANSI SQL-92 規格指出，所有資料類型的 Null 都必須相同，因此，所有 Null 都會以一致的方式來處理。 System.Data.SqlTypes 命名空間會藉由實作 INullable 介面來提供 Null 語意。 System.Data.SqlTypes 中的每個資料類型都有自己的 IsNull 屬性，以及可指派給該資料類型之執行個體的 Null 值。

注意

.NET Framework 2.0 版開始支援可為 Null 實值類型，這讓程式設計人員得以擴充實值類型，以表示基礎類型所有的值。這些 CLR 可為 Null 的實值類型代表 Nullable 結構的執行個體。已將數值類型 Boxed 和 Unboxed 時，此功能特別有用，可提供與物件類型的增強相容性。 CLR 可為 Null 的實值類型不適用於儲存資料庫 Null，因為 ANSI SQL Null 的行為方式與 null 參考不同 (在 Visual Basic 中為 Nothing)。若要使用資料庫 ANSI SQL Null 值，請使用 System.Data.SqlTypes Null，而不是 Nullable。如需在 Visual Basic 中使用 CLR 值可為 Null 類型的詳細資訊，請參閱可為 Null 實值類型，若為 C#，則請參閱可為 Null 實值類型。

Null 及三種值的邏輯

在資料行定義中允許 Null 值，會在您的應用程式中引進三值邏輯。比較可以評估為三個條件的其中一個：

True
False
Unknown

因為 Null 會被視為未知，所以不會將彼此相比較的兩個 Null 值視為相等。在使用算術運算子的運算式中，如果有任何運算元是 Null，結果也會是 Null。

Null 和 SqlBoolean

任何 System.Data.SqlTypes 間的比較都將傳回 SqlBoolean。每個 IsNull 的 SqlType 函數都會傳回 SqlBoolean，而且可用來檢查 Null 值。下列事實資料表示範 AND、OR 及 NOT 運算子如何在出現 Null 值的情況下運作 (T=true、F=false 及 U=unknown，或 Null)。

Truth Table

瞭解 ANSI_NULLS 選項

System.Data.SqlTypes 提供的語意與在 SQL Server 中設定 ANSI_NULLS 選項時相同。如果有任何運算元或引數為 Null (屬性 IsNull 除外)，則所有算術運算子 (+、-、*、/、%)、位元運算子 (~、&、|) 及大多數函式都會傳回 Null。

ANSI SQL-92 標準不支援 WHERE 子句中的 columnName = NULL。在 SQL Server 中，ANSI_NULLS 選項會控制資料庫中預設的可 NULL 性，以及對 Null 值的比較評估。如果 ANSI_NULLS 已開啟 (預設值)，則在測試 Null 值時，必須在運算式中使用 IS NULL 運算子。例如，當 ANSI_NULLS 是 ON 時，下列比較永遠都會產生未知：

colname > NULL

與包含 Null 值的變數進行比較也會產生未知：

colname > @MyVariable

使用 IS NULL 或 IS NOT NULL 述詞可以測試是否為 NULL 值。不過這樣會增加 WHERE 子句的複雜度。例如，AdventureWorks Customer 資料表中的 TerritoryID 資料行允許 Null 值。如果 SELECT 陳述式要測試其他資料行是否有 Null 值，必須包含 IS NULL 述詞：

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

如果您將 SQL Server 中的 ANSI_NULLS 設定為 off，則可建立使用等號比較運算子來比較 Null 的運算式。不過，您無法防止不同的連線來為該連線設定 Null 選項。不論連線的 ANSI_NULLS 設定為何，使用 IS NULL 來測試 Null 值一律可行。

DataSet 中不支援將 ANSI_NULLS 設定為 off，其始終會遵循 ANSI SQL-92 標準來處理 System.Data.SqlTypes 中的 Null 值。

指派 Null 值

Null 值是特殊的，它們的儲存和指派語意在不同類型系統和儲存系統之間各不相同。 Dataset 是設計來搭配不同的類型和儲存系統使用。

此節描述在不同類型系統間的 DataColumn 中，將 Null 值指派給 DataRow 的 Null 語意。

DBNull.Value
此指派適用於任何類型的 DataColumn。如果類型實作 INullable，就會將 DBNull.Value 強制轉型為適當的強型別 Null 值。

SqlType.Null
所有 System.Data.SqlTypes 資料類型都會實作 INullable。如果強型別 Null 值可以使用隱含轉換運算子轉換為資料行的資料類型，則指派應可順利完成。否則會擲回轉換無效的例外狀況。

null
如果 'null' 是指定 DataColumn 資料類型的合法值，就會強制轉型為適當的 DbNull.Value 或與 Null 類型 (INullable) 相關聯的 SqlType.Null。

derivedUdt.Null
如果是 UDT 資料行，一律會根據與 DataColumn 相關聯的類型來儲存 Null。請考慮與 DataColumn 相關聯的 UDT 案例，其不會實作 INullable，但其子類別會。在此案例中，如果已指派與衍生類別相關聯的強型別 Null 值，它就會儲存為不具類型的 DbNull.Value，因為 Null 儲存一律會與 DataColumn 的資料類型一致。

注意

Nullable<T> 中目前不支援 Nullable 或 DataSet 結構。

所有 System.Data.SqlTypes 執行個體的預設值均為 Null。

System.Data.SqlTypes 中的 Null 是類型專屬的，無法以單一值表示，例如 DbNull。使用 IsNull 屬性來檢查 Null。

Null 值可以指派給 DataColumn，如下列程式碼範例所示。您可以直接將 Null 值指派給 SqlTypes 變數，而不會觸發例外狀況。

範例

下列程式碼範例會建立 DataTable，其中包含兩個定義為 SqlInt32 和 SqlString 的資料行。程式碼會新增一個已知值的資料列、一個 Null 值的資料列，然後逐一查看 DataTable，將值指派給變數，並在主控台視窗中顯示輸出。

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

    // 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

此範例會顯示下列結果：

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

多個資料行 (資料列) 指派

DataTable.Add、DataTable.LoadDataRow 或其他接受對應到資料列之 ItemArray 的 API，會將 'null' 對應到 DataColumn 的預設值。如果陣列中的物件包含 DbNull.Value 或其強型別的對應項，即會套用前述的相同規則。

此外，下列規則適用於 DataRow.["columnName"] Null 指派的執行個體：

除了強類型 Null 資料行具有適當的強類型 Null 值之外，所有其他項目的「預設」值都是 DbNull.Value。
絕對不會在序列化到 XML 檔案期間寫出 Null 值 (就像 "xsi:nil")。
序列化到 XML 時，一律會寫出所有非 Null 值 (包括預設值)。這不同於 XSD/XML 語意，其中 Null 值 (xsi:nil) 是明確的，而預設值是隱含的 (如果未出現在 XML 中，驗證剖析器就能夠從相關聯的 XSD 結構描述中取得它)。如果是 DataTable，則情況恰恰相反：Null 值是隱含的，而預設值是明確的。
從 XML 輸入讀取之資料列的所有遺漏資料行值都會指派為 Null。使用 NewRow 或類似方法建立的資料列都會被指派 DataColumn 的預設值。
IsNull 方法會針對 true 和 DbNull.Value 傳回 INullable.Null。

將 Null 值與 SqlType 及 CLR 型別進行比較

比較 Null 值時，請務必了解 Equals 方法在 System.Data.SqlTypes 中評估 Null 值之方式間的差異 (相較於其使用 CLR 類型的方式)。所有 System.Data.SqlTypesEquals 方法都會使用資料庫語意來評估 Null 值：如果其中一個或兩個值都是 Null，則比較會產生 Null。另一方面，在兩個 Equals 上使用 CLR System.Data.SqlTypes 方法，將會在兩者均為 Null 時產生 True。這會反映使用執行個體方法 (例如 CLR String.Equals 方法) 和使用靜態/共用方法 (SqlString.Equals) 之間的差異。

下列範例示範在 SqlString.Equals 方法與 String.Equals 方法之間，分別傳遞一對 Null 值，然後傳遞一對空字串時的結果差異。

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

        // 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));
    }

    static string SqlStringEquals(SqlString string1, SqlString string2)
    {
        // SqlString.Equals uses database semantics for evaluating nulls.
        var returnValue = SqlString.Equals(string1, string2).ToString();
        return returnValue;
    }

    static string StringEquals(SqlString string1, SqlString string2)
    {
        // String.Equals uses CLR type semantics for evaluating nulls.
        var 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

此程式碼會產生下列輸出：

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

另請參閱

SQL Server 資料類型和 ADO.NET
ADO.NET 概觀 \(部分機器翻譯\)

共用方式為