SQL語法提示工具SQL Prompt教程：插入不帶列列表的語句（BP004）

SQL Prompt是一款實用的SQL語法提示工具。其根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。

點擊下載SQL Prompt試用版

許多生產數據庫由于INSERT代碼省略了列列表而尷尬地失敗了，通常以神秘的方式進行，而且通常不會產生錯誤。本文演示了該問題，并提倡一種“深度防御”方法來編寫SQL，以避免這種情況。

您可以在不提供列列表的情況下將行插入表中，但這根本沒有有效的用途。通常，您應該避免所有事情，以免通過使事情變得多余而使您的SQL代碼更短。當然，每個規則總是有例外，但是INSERT列列表非常重要，因此我建議您在SQL Prompt（BP004）或使用的任何靜態代碼分析工具檢測到這種犯罪的情況下停止構建。同時，我建議您在編寫SQL代碼時應采取防御和悲觀的態度，這種態度自然會引起“機會”代碼的思想，而這種想法會忽略列列表。

盲插的危險

假設我們有一個Purchase表，然后將其存儲在數據庫代碼很遠的存儲過程中，其中一些代碼將派生表的結果插入其中。在這里，我們只插入歷經艱辛的AdventureWorks2016的一些數據。

USE business;
  IF Object_Id('dbo.purchase') IS NOT NULL DROP TABLE dbo.Purchase;
  CREATE TABLE Purchase
    (
    SubTotal NUMERIC(19, 4) NOT NULL,
    TaxAmt NUMERIC(19, 4) NOT NULL,
    Freight NUMERIC(19, 4) NOT NULL,
    total NUMERIC(19, 4) NOT NULL,
    OrderDate DATETIME NOT NULL
    );
  INSERT INTO Purchase
    SELECT TOP 10 SubTotal, TaxAmt, Freight,
      SubTotal + TaxAmt + Freight AS total, OrderDate
      FROM AdventureWorks2016.Sales.SalesOrderHeader;
  GO
  SELECT * FROM purchase

有一段時間沒有檢查表源（要加載的派生表）中的列數是否與目標表中的列數兼容。 如今，源中的列數必須與表或column_list中的列兼容。 但是，如果有人更改了列的目的或更改了列的順序，則有很大的混亂空間。

更改列的目的

為了證明這一點，我們必須想象團隊負責人突然意識到該total專欄是多余的，而他需要該ShipDate專欄。

IF Object_Id('dbo.purchase') IS NOT NULL DROP TABLE dbo.Purchase;
  CREATE TABLE Purchase
    (
    SubTotal NUMERIC(19, 4) NOT NULL,
    TaxAmt NUMERIC(19, 4) NOT NULL,
    Freight NUMERIC(19, 4) NOT NULL,
    ShipDate DATETIME NOT NULL,
    OrderDate DATETIME not NULL
    );

不幸的是，他忘記了存儲過程中隱藏的INSERT例程。當它運行時，它不會觸發任何錯誤，但是在Purchase表中您會發現問題。

INSERT INTO Purchase
    SELECT TOP 10 SubTotal, TaxAmt, Freight,
      Total + TaxAmt + Freight AS total, OrderDate
      FROM AdventureWorks2016.Sales.SalesOrderHeader;
  --result set must be compatible with the columns in the table or in column_list.
  SELECT * FROM Purchase;

其中一些發貨日期應該引起人們的注意。 發生了什么？ total的值（貨幣數據類型）的合計值已成為日期。 怎么樣？ 這里有兩點。 一些開發人員認為SQL Server將對照目標表的列名檢查表源中結果集的列名。 不，不是。 更糟糕的是，如果源和目標中的列的數據類型不匹配，SQL代碼將在關于何時允許隱式轉換的嚴格規則內，盡力將前者轉換成后者。

這就是這里發生的情況：一個隱式轉換將要作為一筆錢的數據轉換為日期。我們可以更簡單地顯示它：

DECLARE @FirstTable TABLE (TheDate DateTime, TheCredit NUMERIC(19,4),TheDebit NUMERIC(19,4))
  INSERT INTO @FirstTable 
   SELECT $43183.8419, $42856.56, $43245.78
  SELECT * FROM @FirstTable

如果您要嘗試另一種方法（將日期放入“金錢”列中），則會出現錯誤，因為存在禁止該隱式轉換的規則。

消息257，第16級，狀態3，第28行

不允許從數據類型datetime到數值類型的隱式轉換。使用CONVERT函數運行此查詢。

由于錯誤通知我們，我們需要使用顯式轉換將日期轉換為金額

SELECT Convert(NUMERIC(19,4),Convert(DATETIME,'26 Mar 2018 20:12:23')) 
      AS TheDateAsMoney

更改列順序

我們已經表明，INSERT沒有列列表的語句“盲插入”，很容易受到表列變化的影響，但是即使弄錯了列順序也可能導致災難。更糟糕的是，只有在數據不協調的情況下，您才能發現這一點，因為它可以在不觸發錯誤的情況下發生。

如果您不小心將數據插入到的列與預期的列具有相同的數據類型，則只有結構合理的CHECK約束條件才能使您免于災難。為了說明這一點，我們將創建一個日記表。

IF Object_Id('dbo.JournalEntries') IS NOT NULL DROP TABLE dbo.JournalEntries;
  CREATE TABLE dbo.JournalEntries
    (
    DateOfEntry DATETIME NOT NULL DEFAULT GetDate() unique,
    description NVARCHAR(400) NOT NULL DEFAULT 'unexplained',
    account NVARCHAR(20) NOT NULL DEFAULT 'Ac44M',
    Dr NUMERIC(19, 4) NOT NULL DEFAULT 0,
    Cr NUMERIC(19, 4) NOT NULL DEFAULT 0
    );

現在我們使用盲插入添加一些日記帳分錄：

INSERT INTO dbo.JournalEntries
   VALUES 
   ('23 Mar 2018','sale of Vans','ac30', 00,40345),
   ('24 Mar 2018','pay creditors','ac30', 30000,00),
   ('25 Mar 2018','payment from debtor','ac30',00,60517.45),
   ('26 Mar 2018','purchase of transport','ac30',45462.45,00),
   ('27 Mar 2018','fixtures','ac30',65.45,00),
   ('28 Mar 2018','Stock','ac30',42.60,00),
   ('29 Mar 2018','tax refund','ac30',00,45008.60)

現在，我們可以看到余額。

SELECT Convert(CHAR(11),DateOfEntry,113) AS "Date", description, account, Cr, Dr, 
    Sum(Cr - Dr) OVER (ORDER BY DateOfEntry) AS CalculatedRunningTotal
    FROM journalEntries;

在日記帳表中，使用FOREIGN KEY將條目分配給特定帳戶，并且日記帳會跟蹤許多帳戶。 在大多數國家或地區，日記帳分錄按時間順序輸入，而借方則在貸方之前輸入。 因此，有人認為應將貸方（Cr）列置于借方（Dr）列之前：

IF Object_Id('dbo.JournalEntries') IS NOT NULL DROP TABLE dbo.JournalEntries;
  CREATE TABLE dbo.JournalEntries
    (
    DateOfEntry DATETIME NOT NULL DEFAULT GetDate() unique,
    description NVARCHAR(400) NOT NULL DEFAULT 'unexplained',
    account NVARCHAR(20) NOT NULL DEFAULT 'Ac44M',
    Cr NUMERIC(19, 4) NOT NULL DEFAULT 0, --we switched this with Dr
    Dr NUMERIC(19, 4) NOT NULL DEFAULT 0  --we switched this with Cr
    );
  INSERT INTO dbo.JournalEntries
   VALUES 
   ('23 Mar 2018','sale of Vans','ac30', 00,40345),
   ('24 Mar 2018','pay creditors','ac30', 30000,00),
   ('25 Mar 2018','payment from debtor','ac30',00,60517.45),
   ('26 Mar 2018','purchase of transport','ac30',45462.45,00),
   ('27 Mar 2018','fixtures','ac30',65.45,00),
   ('28 Mar 2018','Stock','ac30',42.60,00),
   ('29 Mar 2018','tax refund','ac30',00,45008.60)
   SELECT Convert(CHAR(11),DateOfEntry,113) AS "Date", description, account, Cr, Dr, 
    Sum(Cr - Dr) OVER (ORDER BY DateOfEntry) AS CalculatedRunningTotal
    FROM journalEntries;

在沒有列列表的情況下，該INSERT語句僅假定VALUES子句中列的順序與表中列的順序匹配。如果某些開發人員切換了列的順序，在這種情況下，例程仍然可以正常工作，但是賬簿無法平衡，并且辦公室里到處都是穿著黑鞋和木炭灰西裝的冷酷面孔。

這里甚至沒有任何內容可以指示VALUES語句中值的順序，因此需要一段時間才能發現問題。最糟糕的是，您將受到指責而不是更改列順序的開發人員。不指定列名對于交互式工作是很好的，但是如果您編寫的代碼依賴于希望什么都不會改變的話，那么重構將被證明是不可能的。

添加列列表可以清晰的說明結果集的哪一列進入目標表的哪一列，但是如何確定multi-row VALUES子句或您正在使用的任何其他表源中的順序與列列表匹配呢？作為建議，這里不僅是增加列列表，而且還記錄了VALUES子句的預期順序，是一種更具防御性和可維護性的方式。

INSERT INTO dbo.journalEntries (DateOfEntry, description, account, dr, cr)
    SELECT DateOfEntry, description, account, dr, cr
      FROM
        (
        VALUES ('23 Mar 2018', 'sale of Vans', 'ac30', 00, 40345),
          ('24 Mar 2018', 'pay creditors', 'ac30', 30000, 00),
          ('25 Mar 2018', 'payment from debtor', 'ac30', 00, 60517.45),
          ('26 Mar 2018', 'purchase of transport', 'ac30', 45462.45, 00),
          ('27 Mar 2018', 'fixtures', 'ac30', 65.45, 00),
          ('28 Mar 2018', 'Stock', 'ac30', 42.60, 00),
          ('29 Mar 2018', 'tax refund', 'ac30', 00, 45008.60)
        ) AS f (DateOfEntry, description, account, dr, cr);

列列表的額外規范只不過是強調表源希望每列成為什么樣子，而且很容易檢查它們實際上是這樣做的。它更像是文檔。

縱深防御：約束

除了針對這種情況的明顯防御（即按順序指定列列表）之外，您還需要約束。忽略它們是因為您確定它們永遠不會拋出錯誤，就像推理這樣，不需要煙霧探測器是因為煙霧探測器很少觸發它們。

讓我們來看第一個例子，purchase表格。缺少了什么？當然是約束條件。這個問題很奇怪而且很明顯，應該在purchase表中大量的添加約束來防止出現問題。

IF Object_Id('dbo.purchase') IS NOT NULL DROP TABLE dbo.Purchase;
  CREATE TABLE Purchase
    (
    SubTotal NUMERIC(19, 4) NOT NULL CHECK (Subtotal>0), 
    TaxAmt NUMERIC(19, 4) NOT NULL ,
    Freight NUMERIC(19, 4) NOT NULL ,
    ShipDate DATETIME NOT NULL,
    OrderDate DATETIME not NULL,
    CONSTRAINT Shipdate_Before_Orderdate CHECK (Shipdate>OrderDate),
    CONSTRAINT Tax_Charge_Too_High CHECK (TaxAmt>(SubTotal*30/100)),
    CONSTRAINT OrderDate_Is_Impossible CHECK (Year(OrderDate)<2000),
    CONSTRAINT Freight_Charge_Too_High CHECK (Freight>(SubTotal/2))
    );

現在我們測試一下，看看會發生什么

INSERT INTO Purchase
    SELECT TOP 10 SubTotal, TaxAmt, Freight,
      SubTotal + TaxAmt + Freight AS total, OrderDate
      FROM AdventureWorks2016.Sales.SalesOrderHeader;
  --result set must be compatible with the columns in the table or in column_list.
  SELECT * FROM Purchase;

當然，警報鈴會立即響起：

消息547，級別16，狀態0，第31行

INSERT語句與CHECK約束“Shipdate_Before_Orderdate”沖突。沖突發生在數據庫“業務”、表“ dbo.Purchase”中。

該語句已終止。

當某人犯這樣的錯誤時，允許它觸發測試錯誤要比讓其處于生產投入使用的機會小得多，這要好得多。

該journalEntries表中的約束將更多地取決于為業務制定的業務規則，并且觸發器通常會從余額的基線中獲得不尋常的偏差。

摘要

僅僅因為您可以省略INSERT語句中的列列表，但這并不意味著您應該這樣做。它將在您的代碼中引入脆弱性，在某些時候它會趕上您，或者更重要的是，隨后必須與您對付代碼的任何人，因為它很可能會以難以預測或無法追溯的方式失敗。

經過多年的經驗，我看到了最不可能的事情出了問題。最荒唐和最奇妙的CHECK約束條件被觸發，似乎不可能發生的異常總會得到榮耀的一天，用紅色字母寫在屏幕上。

本教程內容到這里就完結了，希望文章內容對您有所幫助！感興趣的朋友可以繼續關注我們哦~您還可以下載SQL Prompt免費版進行評估~

相關內容推薦：

SQL Prompt系列教程>>>

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊

1024，慧都致敬程序員們，zend現金優惠券限時放送，了解詳情請點擊下方圖片