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现金优惠券限时放送，了解详情请点击下方图片