翻譯|使用教程|編輯:莫成敏|2020-03-25 14:25:56.343|閱讀 287 次
概述:?本文解釋了確定列是否允許空值的元素,如果您未在列定義中明確指定的話。如果您依靠連接設(shè)置所建立的默認(rèn)行為,那么您可能會遇到一些糟糕的意外。
# 界面/圖表報表/文檔/IDE等千款熱門軟控件火熱銷售中 >>
相關(guān)鏈接:
SQL Prompt是一款實用的SQL語法提示工具。根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進(jìn)行檢索,為用戶提供合適的代碼選擇。自動腳本設(shè)置使代碼簡單易讀--當(dāng)開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用,能大幅提高編碼效率。此外,用戶還可根據(jù)需要進(jìn)行自定義,使之以預(yù)想的方式工作。
在大多數(shù)情況下,需要指定列是否應(yīng)允許空條目。依靠默認(rèn)值并不是一個好主意,假設(shè),如果不使用NULL或NOT NULL顯式指定列的可為空性,則該列應(yīng)為可為空。如果您不為給定的數(shù)據(jù)類型選擇該選項,那么控制發(fā)生什么事情的規(guī)則很難解釋,即使您了解這些規(guī)則,您的團(tuán)隊或后繼者也會愿意這樣做嗎?
在SQL中,NOT NULL子句是“邏輯約束”,用于確保列永遠(yuǎn)不會獲得分配給它的空值。相反,NULL子句清楚表明希望該列接受空值。如果您的表規(guī)范不包含這些子句,則從數(shù)據(jù)庫屬性、連接設(shè)置或數(shù)據(jù)類型的默認(rèn)值確定是否存在空值。基本上,您并不總是知道,并且可以輕松地得到不允許為空的列。
如果在創(chuàng)建或更改表或聲明表變量時未能指定列的可為空性,則SQL Prompt的最佳實踐代碼分析規(guī)則BP014會警告您(每個表達(dá)式一次違反一次)。

為什么要關(guān)心列是否為空?
之所以需要指定此名稱,是因為關(guān)系數(shù)據(jù)庫旨在有效地防止不良數(shù)據(jù)進(jìn)入。約束是實現(xiàn)此目的的方法。因此,必須對不能合法包含null的所有列使用NOT NULL。 如果您指定一列為非空值,那么您將定義一個約束,以確保該列永遠(yuǎn)不會容納或接受空值,因此您不能意外的將該值保留下來。通過在列中允許空值,除非使用ISNULL()、IFNULL()和NULLIF()之類的函數(shù)來處理空值,否則還使聚合變得棘手,并使WHERE子句產(chǎn)生意外結(jié)果。
如果不指定該列,則不要假設(shè)該列將允許為空
本文旨在證明為什么您應(yīng)該始終指定在任何表中定義的列是否允許空值。不能深入討論NOT NULL約束是否是一件好事,而只是解釋了為什么需要聲明自己的偏好。
您可能會認(rèn)為,如果您在列的定義中未包含NOT NULL約束,那么該列將可以為空。不,錯了。它可以為空,但也可能不是。它取決于數(shù)據(jù)類型、數(shù)據(jù)庫設(shè)置和連接設(shè)置。除非您能記住所有規(guī)則并保證用于DDL腳本的連接類型,否則接受始終指定列為NULL或NOT NULL的單個規(guī)則要簡單得多。
現(xiàn)在,我們將研究各種因素,這些因素可以決定一列是否得到NOT NULL約束(如果您未指定的話)。
您的數(shù)據(jù)庫指定默認(rèn)值
如果程序員在創(chuàng)建或更改表時未指定列的可空性,則數(shù)據(jù)庫設(shè)置(特別是該ANSI_NULL_DEFAULT選項)將確定該列是NULL還是NOT NULL,除非您擁有覆蓋該列的SQL Server連接或其他因素,我會解釋,將其覆蓋。
ANSI_NULL_DEFAULT選項是sql_option設(shè)置之一,語法為:
ALTER Database SET { database_name | CURRENT } SET ANSI_NULL_DEFAULT { ON | OFF }
如果設(shè)置為ON,則在發(fā)出CREATE TABLE或ALTER TABLE語句時,允許所有未顯式定義為NOT NULL的用戶定義數(shù)據(jù)類型或列為空值。
您可以通過以下方式查看當(dāng)前數(shù)據(jù)庫的設(shè)置:
SELECT DATABASEPROPERTYEX(Db_Name(), 'IsAnsiNullDefault');
返回1或0。
您的連接指定默認(rèn)
您可以通過應(yīng)用程序的連接設(shè)置覆蓋數(shù)據(jù)庫設(shè)置。所有常用的設(shè)置都可以做到這一點。您使用的連接通常會指定默認(rèn)值應(yīng)為NULL。這樣做可能不是很明智,但它是ANSI標(biāo)準(zhǔn),這表明如果將更通用的ANSI_DEFAULTS設(shè)置為ON,也會發(fā)現(xiàn)它還將ANSI_NULL_DFLT_ON設(shè)置為ON。
SSMS允許您指定用于連接到SQL Server的默認(rèn)值,如果需要,可以覆蓋ANSI標(biāo)準(zhǔn)。存在集ANSI_NULL_DFLT_OFF和集ANSI_NULL_DFLT_ON,盡管它們不能同時設(shè)置為ON。您可以選擇將兩者都關(guān)閉,在這種情況下,您只是選擇繼承數(shù)據(jù)庫默認(rèn)值,或者可以通過將ANSI_NULL_DFLT_ON設(shè)置為ON來堅持默認(rèn)值是NULL。如果您愿意,可以通過將ANSI_NULL_DFLT_OFF設(shè)置為ON來覆蓋數(shù)據(jù)庫設(shè)置,以使默認(rèn)值為 NOT NULL。
SET ANSI_NULL_DFLT_ON { ON | OFF }
SET ANSI_NULL_DFLT_OFF { ON | OFF }
SQLCMD、BCP和SSMS略有不同,但通常它們是一致的。連接時,SQL Server的SQL Server本機客戶端ODBC驅(qū)動程序和SQL Server的SQL Server本機客戶端OLE DB提供程序會自動設(shè)置ANSI_NULL_DFLT_ON為ON。但是,對于來自遺留的db庫應(yīng)用程序的連接,SET ANSI_NULL_DFLT_ON的默認(rèn)設(shè)置是關(guān)閉的。
因此,如果我們想查看為連接啟用了哪些設(shè)置,則可以運行…
SELECT Setting FROM (VALUES (1 , 'DISABLE_DEF_CNST_CHK'), (2 , 'IMPLICIT_TRANSACTIONS'), (4 , 'CURSOR_CLOSE_ON_COMMIT'), (8 , 'ANSI_WARNINGS'), (16 , 'ANSI_PADDING'), (32 , 'ANSI_NULLS'), (64 , 'ARITHABORT'), (128 , 'ARITHIGNORE'), (256 , 'QUOTED_IDENTIFIER'), (512 , 'NOCOUNT'), (1024 , 'ANSI_NULL_DFLT_ON'), (2048 , 'ANSI_NULL_DFLT_OFF'), (4096 , 'CONCAT_NULL_YIELDS_NULL'), (8192 , 'NUMERIC_ROUNDABORT'), (16384 , 'XACT_ABORT'))f(Bit,Setting) WHERE bit & @@Options =bit
返回:

數(shù)據(jù)類型定義為NOT NULL
除非另外指定,否則Microsoft提供的幾個數(shù)據(jù)類型(timestamp和sysname)都不為空。您可以根據(jù)系統(tǒng)數(shù)據(jù)類型指定自己的別名數(shù)據(jù)類型,并指定它們是否默認(rèn)為可為空。要查看哪些數(shù)據(jù)類型不為空,可以使用如下查詢:…
SELECT name FROM sys.types WHERE is_nullable=0
要使它們可為空或不可為空,請使用以下語法
CREATE TYPE [ schema_name. ] type_name
{
[
FROM base_type
[ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ]
]
您會看到這對于處理具有特定維度、含義或用途的數(shù)據(jù)是多么的方便。您可以引用一個姓氏數(shù)據(jù)類型,并知道它不可能NULL(盡管它可以為'null'),并且它的最大值可以在數(shù)據(jù)庫中的一個地方更改,如果您突然發(fā)現(xiàn)它的長度不夠。
對于基本類型為數(shù)字或十進(jìn)制的數(shù)字?jǐn)?shù)據(jù),它也非常方便。這意味著出錯和意外截斷一個值要困難得多。除了精度和規(guī)模之外,您可以指定其默認(rèn)為空。
這意味著用戶別名類型是數(shù)據(jù)類型之一,通常最好不要通過覆蓋可空性規(guī)范(如果存在)來嘗試通過在基于此類型創(chuàng)建列時顯式指定NULL來覆蓋它。有人已經(jīng)決定類型必須為NULL或NOT NULL,這可能是一個很好的理由。
列參與主鍵
如果您為主鍵分配一列,則為NOT NULL。讓我們演示一下:
CREATE TABLE TestOutNullability ( MyTimestamp TIMESTAMP, ObjectName sysname, MyInt INT, MyCode NVARCHAR(120), CONSTRAINT myPK PRIMARY KEY (MyInt,MyCode) )
因此,讓我們看看所有未指定可空性的列是否都將允許空值:
SELECT c.name, c.is_nullable FROM sys.tables AS T INNER JOIN sys.columns AS C ON C.object_id = T.object_id WHERE t.name='TestOutNullability' ORDER BY c.column_id
這些列均不可為空。我們可以通過使SSMS使用生成的構(gòu)建腳本對表進(jìn)行反向工程來證明這一點
/****** Object: Table [dbo].[TestOutNullability] Script Date: 07/02/2020 19:39:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestOutNullability](
[MyTimestamp] [timestamp] NOT NULL,
[ObjectName] [sysname] NOT NULL,
[MyInt] [int] NOT NULL,
[MyCode] [nvarchar](120) NOT NULL,
CONSTRAINT [myPK] PRIMARY KEY CLUSTERED
(
[MyInt] ASC,
[MyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
看這里!所有列上的NOT NULL約束,即使您的連接設(shè)置指定允許它們?yōu)槟J(rèn)值也是如此。
結(jié)論
我不打算討論在表的列中使用允許空值是否是一個好主意。但是,可以肯定地說,通常應(yīng)明確指定一列是否應(yīng)允許它們。我會說“一般”,因為如果您使用的是用戶定義的別名類型,則有一個參數(shù)可以忽略該參數(shù),以便在數(shù)據(jù)庫中使用該類型是一致的。為了安全起見,您需要確保已在創(chuàng)建別名類型的代碼中指定了它!
您可能會在CREATE TABLE編寫代碼時理解該代碼,以及在執(zhí)行DDL代碼時的連接狀態(tài),但是它是否可重復(fù)?繼承您的代碼的可憐人或必須閱讀該代碼的團(tuán)隊成員,會得到什么啟發(fā)嗎?他們會想要得到線索嗎?
本文內(nèi)容到這里就完結(jié)了,希望對您有所幫助~感興趣的朋友可以下載SQL Prompt試用版免費體驗!或者關(guān)注我們慧都網(wǎng)了解更多產(chǎn)品相關(guān)資訊~
相關(guān)內(nèi)容推薦:
本站文章除注明轉(zhuǎn)載外,均為本站原創(chuàng)或翻譯。歡迎任何形式的轉(zhuǎn)載,但請務(wù)必注明出處、不得修改原文相關(guān)鏈接,如果存在內(nèi)容上的異議請郵件反饋至chenjj@ke049m.cn
文章轉(zhuǎn)載自: