可以选择*用法是否合理吗? [英] 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.

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