select * 的用法是否合理? [英] Can select * usage ever be justified?

问题描述

我一直向我的开发人员宣扬 SELECT * 是邪恶的，应该像瘟疫一样避免.

I've always preached to my developers that SELECT * is evil and should be avoided like the plague.

是否有任何可以证明其合理性的案例?

Are there any cases where it can be justified?

我不是在谈论 COUNT(*) - 大多数优化器都能弄清楚.

I'm not talking about COUNT(*) - which most optimizers can figure out.

编辑

我说的是生产代码.

我看到的关于这种不良做法的一个很好的例子是一个遗留的 asp 应用程序，它在存储过程中使用 select *，并使用 ADO 循环遍历返回的记录，但按索引获取列.您可以想象在字段列表末尾以外的其他位置添加新字段时会发生什么.

And one great example I saw of this bad practice was a legacy asp application that used select * in a stored procedure, and used ADO to loop through the returned records, but got the columns by index. You can imagine what happened when a new field was added somewhere other than the end of the field list.

推荐答案

我很高兴在审计触发器中使用 *.

I'm quite happy using * in audit triggers.

在这种情况下，它实际上可以证明是一个好处，因为它将确保如果将额外的列添加到基表中，它将引发错误，因此不能忘记在审计触发器和/或审计表结构中处理此问题.

In that case it can actually prove a benefit because it will ensure that if additional columns are added to the base table it will raise an error so it cannot be forgotten to deal with this in the audit trigger and/or audit table structure.

(比如 dotjoe)我也很高兴在派生表和列表表达式.虽然我习惯性地反其道而行之.

(Like dotjoe) I am also happy using it in derived tables and column table expressions. Though I habitually do it the other way round.

WITH t
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY a) AS RN
         FROM   foo)
SELECT a,
       b,
       c,
       RN
FROM   t; 

我最熟悉 SQL Server，至少优化器没有问题，认识到只有 a,b,c 列是必需的，并且使用了 * 在内表表达式中不会导致任何不必要的开销，检索和丢弃不需要的列.

I'm mostly familiar with SQL Server and there at least the optimiser has no problem recognising that only columns a,b,c will be required and the use of * in the inner table expression does not cause any unnecessary overhead retrieving and discarding unneeded columns.

原则上 SELECT * 在视图中应该没问题，并且它是视图中应该避免的最终 SELECT 但是在 SQL Server 中这是可能会导致问题，因为它存储视图的列元数据，当基础表更改时不会自动更新，除非运​​行 sp_refreshview，否则使用 * 会导致混淆和不正确的结果更新此元数据.

In principle SELECT * ought to be fine in a view as well as it is the final SELECT from the view where it ought to be avoided however in SQL Server this can cause problems as it stores column metadata for views which is not automatically updated when the underlying tables change and the use of * can lead to confusing and incorrect results unless sp_refreshview is run to update this metadata.

这篇关于select * 的用法是否合理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！