SQL語(yǔ)法提示工具SQL Prompt教程：為什么SELECT *（BP005）在生產(chǎn)代碼中不好？（下）

SQL Prompt根據(jù)數(shù)據(jù)庫(kù)的對(duì)象名稱(chēng)、語(yǔ)法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開(kāi)發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt試用版

如果“提示”警告您在SELECT語(yǔ)句中使用星號(hào)或“star”（*），請(qǐng)考慮將其替換為顯式列列表。它將防止不必要的網(wǎng)絡(luò)負(fù)載和查詢性能問(wèn)題，并避免在插入表時(shí)如果列順序更改而造成問(wèn)題。這篇文章主要描述該教程的后半部分內(nèi)容，“為什么SELECT *在生產(chǎn)代碼中不好？”的一些內(nèi)容（緊接上文），還有“在應(yīng)用程序中選擇*”的內(nèi)容。

誤解

使用SELECT *，您不能確保代碼始終以相同的順序返回相同的列，這意味著它對(duì)數(shù)據(jù)庫(kù)重構(gòu)沒(méi)有彈性。對(duì)表源的上游修改可以更改列的順序或數(shù)量。如果使用來(lái)傳輸數(shù)據(jù)，INSERT INTO…SELECT *，那么最佳結(jié)果將是一個(gè)錯(cuò)誤，因?yàn)榉峙鋽?shù)據(jù)的后果是錯(cuò)誤的目標(biāo)列可能會(huì)令人恐懼

我將演示如果在生產(chǎn)代碼中使用它，然后需要進(jìn)行一些數(shù)據(jù)庫(kù)重構(gòu)，那么這將是多么危險(xiǎn)。在這里，我們?cè)趶?fù)制敏感信息時(shí)會(huì)犯一個(gè)錯(cuò)誤。這是非常容易做到的，并且可能導(dǎo)致財(cái)務(wù)違規(guī)，而不會(huì)觸發(fā)任何錯(cuò)誤。如果您情緒緊張，請(qǐng)立即移開(kāi)視線。

/* we create a table just for our testing */
  CREATE TABLE dbo.ExchangeRates --lets pretend we have this data
    (
    CurrencyRateDate DATETIME NOT NULL,
    AverageRate MONEY NOT NULL,
    EndOfDayRate MONEY NOT NULL,
    FromCurrency NVARCHAR(50) NOT NULL,
    FromRegion NVARCHAR(50) NOT NULL,
    ToCurrency NVARCHAR(50) NOT NULL,
    ToRegion NVARCHAR(50) NOT NULL
    );
  /* we now steal data for it from AdventureWorks next-door */
  INSERT INTO dbo.ExchangeRates
  SELECT CurrencyRate.CurrencyRateDate, CurrencyRate.AverageRate,
      CurrencyRate.EndOfDayRate, Currency.Name AS FromCurrency,
      CountryRegion.Name AS FromRegion, CurrencyTo.Name AS ToCurrency,
      CountryRegionTo.Name AS ToRegion
      FROM Adventureworks2016.Sales.CurrencyRate
        INNER JOIN Adventureworks2016.Sales.Currency
          ON CurrencyRate.FromCurrencyCode = Currency.CurrencyCode
        INNER JOIN Adventureworks2016.Sales.CountryRegionCurrency
          ON Currency.CurrencyCode = CountryRegionCurrency.CurrencyCode
        INNER JOIN Adventureworks2016.Person.CountryRegion
          ON CountryRegionCurrency.CountryRegionCode = CountryRegion.CountryRegionCode
        INNER JOIN Adventureworks2016.Sales.Currency AS CurrencyTo
          ON CurrencyRate.ToCurrencyCode = CurrencyTo.CurrencyCode
        INNER JOIN Adventureworks2016.Sales.CountryRegionCurrency AS CountryRegionCurrencyTo
          ON CurrencyTo.CurrencyCode = CountryRegionCurrencyTo.CurrencyCode
        INNER JOIN Adventureworks2016.Person.CountryRegion AS CountryRegionTo
          ON CountryRegionCurrencyTo.CountryRegionCode = CountryRegionTo.CountryRegionCode;
  GO
  /* so we start our test by creating a view to show exchange rates from equador  */
  CREATE VIEW dbo.EquadorExhangeRates
  AS
  SELECT ExchangeRates.CurrencyRateDate, ExchangeRates.AverageRate,
         ExchangeRates.EndOfDayRate, ExchangeRates.FromCurrency,
         ExchangeRates.FromRegion, ExchangeRates.ToCurrency, ExchangeRates.ToRegion
    FROM dbo.ExchangeRates
    WHERE ExchangeRates.FromRegion = 'Ecuador';
  go
  /* now we just fill a table variable with the first ten rows from the view and display them */
  DECLARE  @MyUsefulExchangeRates TABLE
    (
    CurrencyRateDate DATETIME NOT NULL,
    AverageRate MONEY NOT NULL,
    EndOfDayRate MONEY NOT NULL,
    FromCurrency NVARCHAR(50) NOT NULL,
    FromRegion NVARCHAR(50) NOT NULL,
    ToCurrency NVARCHAR(50) NOT NULL,
    ToRegion NVARCHAR(50) NOT NULL
    );
  INSERT INTO @MyUsefulExchangeRates (
    CurrencyRateDate, AverageRate, EndOfDayRate,
    FromCurrency, FromRegion,ToCurrency, ToRegion)
    SELECT * --this isn't good at all
      FROM dbo.EquadorExhangeRates;
  --disply the first ten rows from the table to see what we have
  SELECT TOP 10 UER.CurrencyRateDate, UER.AverageRate, UER.EndOfDayRate,
    UER.ToCurrency, UER.ToRegion, UER.FromCurrency, UER.FromRegion
    FROM @MyUsefulExchangeRates AS UER
    ORDER BY UER.CurrencyRateDate DESC;
  GO
  /* end of first part. Now someone decides to alter the view */
  alter VIEW dbo.EquadorExhangeRates
  AS
  SELECT ExchangeRates.CurrencyRateDate, ExchangeRates.AverageRate,
    ExchangeRates.EndOfDayRate, ExchangeRates.ToCurrency, ExchangeRates.ToRegion, ExchangeRates.FromCurrency,
    ExchangeRates.FromRegion
    FROM dbo.ExchangeRates
    WHERE ExchangeRates.FromRegion = 'Ecuador';
  GO
  /* we repeat the routine to extract the first ten rows exactly as before */
  DECLARE  @MyUsefulExchangeRates TABLE
    (
    CurrencyRateDate DATETIME NOT NULL,
    AverageRate MONEY NOT NULL,
    EndOfDayRate MONEY NOT NULL,
    FromCurrency NVARCHAR(50) NOT NULL,
    FromRegion NVARCHAR(50) NOT NULL,
    ToCurrency NVARCHAR(50) NOT NULL,
    ToRegion NVARCHAR(50) NOT NULL
    );
  INSERT INTO @MyUsefulExchangeRates(
    CurrencyRateDate, AverageRate, EndOfDayRate,
    FromCurrency, FromRegion,ToCurrency, ToRegion)
    SELECT * --bad, bad, bad
      FROM dbo.EquadorExhangeRates;
  --check that the data is the same. It isn't is it? No sir!
  SELECT TOP 10 UER.CurrencyRateDate, UER.AverageRate, UER.EndOfDayRate,
    UER.ToCurrency, UER.ToRegion, UER.FromCurrency, UER.FromRegion
    FROM @MyUsefulExchangeRates AS UER
    ORDER BY UER.CurrencyRateDate DESC;
  GO
  /* now just tidy up and tear down */
  DROP VIEW dbo.EquadorExhangeRates
  DROP TABLE  dbo.ExchangeRates

這是“之前”和“之后”結(jié)果…。

如您所見(jiàn)，通過(guò)切換“to”和“from”列，我們“無(wú)意”破壞了數(shù)據(jù)。引用列列表在您的代碼中是多余的。但是，它的執(zhí)行速度甚至比僅用星號(hào)指定所有列（假設(shè)它們按特定順序排列）時(shí)的速度甚至更快。

約束問(wèn)題

當(dāng)我們使用SELECT *與大量的聯(lián)接表時(shí)，我們可以并且可能會(huì)有重復(fù)的列名。這是來(lái)自AdventureWorks的簡(jiǎn)單查詢：

SELECT *
    FROM HumanResources.Employee AS e
      INNER JOIN Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
      INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.BusinessEntityID = edh.BusinessEntityID
      INNER JOIN HumanResources.Department AS d
        ON edh.DepartmentID = d.DepartmentID
    WHERE (edh.EndDate IS NULL);

此代碼將顯示重復(fù)的列名稱(chēng)：

DECLARE @SourceCode NVARCHAR(4000)=' 
  SELECT *
    FROM HumanResources.Employee AS e
      INNER JOIN Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
      INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.BusinessEntityID = edh.BusinessEntityID
      INNER JOIN HumanResources.Department AS d
        ON edh.DepartmentID = d.DepartmentID
    WHERE (edh.EndDate IS NULL);
  --'
  SELECT Count(*) AS Duplicates, name
    FROM sys.dm_exec_describe_first_result_set(@SourceCode, NULL, 1)
    GROUP BY name
    HAVING Count(*) > 1
    ORDER BY Count(*) DESC;

這將給試圖在選擇命名列時(shí)理解這種結(jié)果的應(yīng)用程序帶來(lái)問(wèn)題。如果您嘗試根據(jù)結(jié)果創(chuàng)建一個(gè)臨時(shí)表，使用SELECT…INTO會(huì)失敗。

SELECT * INTO MyTempTable
    FROM HumanResources.Employee AS e
      INNER JOIN Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
      INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.BusinessEntityID = edh.BusinessEntityID
      INNER JOIN HumanResources.Department AS d
        ON edh.DepartmentID = d.DepartmentID
    WHERE (edh.EndDate IS NULL);
  Msg 2705, Level 16, State 3, Line 19
  Column names in each table must be unique. Column name 'BusinessEntityID' in table 'MyTempTable' is specified more than once.

同樣，這意味著您的SELECT *代碼很脆弱。如果有人在一個(gè)表中更改了名稱(chēng)，則可能會(huì)在SELECT * INTO其他位置的上創(chuàng)建重復(fù)的列，而您只能撓頭，想知道為什么正常工作的例程突然崩潰了

有一個(gè)地方SELECT *具有特殊的意義，不能被替代。這是在將結(jié)果轉(zhuǎn)換為JSON時(shí)，并且您需要將聯(lián)接表作為對(duì)象嵌入的結(jié)果時(shí)發(fā)生的情況。

SELECT * 
    FROM HumanResources.Employee AS employee
      INNER JOIN Person.Person AS person
        ON person.BusinessEntityID = employee.BusinessEntityID
      INNER JOIN HumanResources.EmployeeDepartmentHistory AS history
        ON employee.BusinessEntityID = history.BusinessEntityID
      INNER JOIN HumanResources.Department AS d
        ON  history.DepartmentID = d.DepartmentID
    WHERE ( history.EndDate IS NULL) FOR JSON AUTO

這將為您提供…（我僅顯示數(shù)組中的第一個(gè)文檔）

[{"BusinessEntityID": 1,"NationalIDNumber": "295847284","LoginID": "adventure-works\\ken0","JobTitle": "Chief Executive Officer","BirthDate": "1969-01-29","MaritalStatus": "S","Gender": "M","HireDate": "2009-01-14","SalariedFlag": true, "VacationHours": 99, "SickLeaveHours": 69, "CurrentFlag": true, "rowguid": "F01251E5-96A3-448D-981E-0F99D789110D","ModifiedDate": "2014-06-30T00:00:00",
        "person": [{
            "BusinessEntityID": 1, "PersonType": "EM","NameStyle": false, "FirstName": "Ken","MiddleName": "J","LastName": "Sánchez","EmailPromotion": 0, "Demographics": "0<\/TotalPurchaseYTD><\/IndividualSurvey>","rowguid": "92C4279F-1207-48A3-8448-4636514EB7E2","ModifiedDate": "2009-01-07T00:00:00",
            "history": [{
                "BusinessEntityID": 1, "DepartmentID": 16, "ShiftID": 1, "StartDate": "2009-01-14","ModifiedDate": "2009-01-13T00:00:00",
                "d": [{
                    "DepartmentID": 16, "Name": "Executive","GroupName": "Executive General and Administration","ModifiedDate": "2008-04-30T00:00:00"
                }]
            }]
        }]
    }}

這里沒(méi)有沖突，因?yàn)镸odifiedDate列被封裝在表示源表的對(duì)象中

對(duì)應(yīng)的XML給出如下：

<employee BusinessEntityID="1" NationalIDNumber="295847284" LoginID="adventure-works\ken0" 
          JobTitle="Chief Executive Officer" BirthDate="1969-01-29" MaritalStatus="S" Gender="M" HireDate="2009-01-14" SalariedFlag="1" VacationHours="99"
          SickLeaveHours="69" CurrentFlag="1" rowguid="F01251E5-96A3-448D-981E-0F99D789110D" ModifiedDate="2014-06-30T00:00:00">
 <person BusinessEntityID="1" PersonType="EM" NameStyle="0" FirstName="Ken" MiddleName="J" LastName="Sánchez" EmailPromotion="0" 
         rowguid="92C4279F-1207-48A3-8448-4636514EB7E2" ModifiedDate="2009-01-07T00:00:00">
     <Demographics>
         <IndividualSurvey
             xmlns="//schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
             <TotalPurchaseYTD>0</TotalPurchaseYTD>
         </IndividualSurvey>
     </Demographics>
     <history BusinessEntityID="1" DepartmentID="16" ShiftID="1" StartDate="2009-01-14" ModifiedDate="2009-01-13T00:00:00">
         <d DepartmentID="16" Name="Executive" GroupName="Executive General and Administration" ModifiedDate="2008-04-30T00:00:00"/>
     </history>
  </person>
</employee>

可維護(hù)性

在布置代碼時(shí)，您指定的列不僅避免在將值分配給正確的列或變量時(shí)出錯(cuò)，而且還使代碼更具可讀性。盡您所能，僅出于將來(lái)的目的，或者有一天要負(fù)責(zé)維護(hù)代碼的可憐的靈魂，就應(yīng)詳細(xì)說(shuō)明所涉及的列的名稱(chēng)。當(dāng)然，代碼看起來(lái)有些笨拙，但是如果您的肩膀上出現(xiàn)了一位仙女，并說(shuō)如果您兩次鍵入代碼，您的代碼將更加清晰和可靠，您會(huì)這樣做嗎？

在應(yīng)用程序中選擇*

有時(shí)，您會(huì)看到長(zhǎng)時(shí)間運(yùn)行的查詢，這些查詢請(qǐng)求所有列并且源于一個(gè)應(yīng)用程序，通常是使用LINQ的應(yīng)用程序。通常，這不是故意的，但是開(kāi)發(fā)人員犯了一個(gè)錯(cuò)誤，沒(méi)有指定列的說(shuō)明，看起來(lái)無(wú)辜的LINQ查詢會(huì)轉(zhuǎn)換為SELECT *或包含每個(gè)列的列列表。如果該WHERE條款過(guò)于籠統(tǒng)，或者甚至被完全遺漏，那么后果就更加復(fù)雜了，因?yàn)榫W(wǎng)絡(luò)始終是最慢的組件，所有不必要的數(shù)據(jù)都在網(wǎng)絡(luò)上堆積。

例如，使用Adventureworks和LinqPad，可以在LINQ中執(zhí)行此操作：

Persons.OrderBy (p => p.BusinessEntityID).Take (100)

…LINQ將其轉(zhuǎn)換為實(shí)際執(zhí)行的查詢。您會(huì)看到它選擇了所有列…

SELECT TOP (100) [t0].[BusinessEntityID], [t0].[PersonType], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate]
  FROM [Person].[Person] AS [t0]
  ORDER BY [t0].[BusinessEntityID]

同樣，這個(gè)表達(dá)式

from row in Persons select row

…將提供整個(gè)表格中每一行的每一列。

SELECT [t0].[BusinessEntityID], [t0].[PersonType], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate]
  FROM [Person].[Person] AS [t0]

相比之下，這…

from row in Persons.Where(i => i.LastName == "Bradley") select row.FirstName+" "+row.LastName

…翻譯成更明智的：

-- Region Parameters
  DECLARE @p0 NVarChar(1000) = 'Bradley'
  DECLARE @p1 NVarChar(1000) = ' '
  -- EndRegion
  SELECT ([t0].[FirstName] + @p1) + [t0].[LastName] AS [value]
  FROM [Person].[Person] AS [t0]
  WHERE [t0].[LastName] = @p0

結(jié)論

一般的代碼味道是請(qǐng)求提供比您需要的更多的數(shù)據(jù)。允許數(shù)據(jù)源為您進(jìn)行過(guò)濾幾乎總是更好、更快的方法。使用SELECT *，在某些情況下是完全合法的，通常是這個(gè)更普遍問(wèn)題的標(biāo)志。對(duì)于那些精通C?；騐B但不精通SQL的開(kāi)發(fā)人員來(lái)說(shuō)，誘使他們下載整行甚至整個(gè)表，并在更熟悉的領(lǐng)域進(jìn)行過(guò)濾是很誘人的。額外的網(wǎng)絡(luò)負(fù)載和延遲本身應(yīng)該足以阻止這種做法，但這通常被誤認(rèn)為是“數(shù)據(jù)庫(kù)慢”。長(zhǎng)列列表（通常列出所有列）幾乎與SELECT *一樣有害，盡管SELECT *在進(jìn)行任何重構(gòu)時(shí)會(huì)帶來(lái)額外的風(fēng)險(xiǎn)。

本教程內(nèi)容到這里就結(jié)束了，感興趣的朋友可以繼續(xù)關(guān)注我們，后面會(huì)不管更新新的文章內(nèi)容！您也可以下載SQL Prompt免費(fèi)版評(píng)估一下~

相關(guān)內(nèi)容推薦：

SQL語(yǔ)法提示工具SQL Prompt教程：為什么SELECT *（BP005）在生產(chǎn)代碼中不好？（上）

SQL Prompt系列教程>>>

想要購(gòu)買(mǎi)SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊