SELECT *在SQL中有什么缺点吗? [英] Any disadvantages of SELECT * in SQL?

问题描述

比方说，我有一个执行SQL的程序，我想查询一个表以获取A，B和C列中的值(假设有些列我并不需要). SELECT *和SELECT A，B，C有什么区别?

Let's say I have a program that executes SQL, and I want to query a table for the values in columns A, B and C (and let's suppose there are some columns I don't strictly need). What are the differences between SELECT * and SELECT A, B, C?

到目前为止我的想法.仅选择所需的列:

My thoughts so far below. Selecting only the columns you need:

• ...将需要较少的内存来存储结果.
• ...可能使解析程序中的结果更加容易.
• ...速度不会更快，除非可以使用覆盖索引来检索所选的所有列.

我觉得可能还有其他问题.

I feel there are probably further issues at play though.

推荐答案

您说的是正确的，但还有更多.

What you say is correct, but there is more.

与选择*"有关的另一个非常重要的问题是，如果表架构发生更改，将会发生什么. 如果客户端使用选择*"，它将获得一组可能意外的列(更多列，更少列，不同顺序...).这很糟糕.

Another very important issue with "select *" is what happens if the table schema changes. If the client uses "select *" it will get a possibly unexpected set of columns (more columns, less columns, different order...). This is very bad.

如果客户端选择了所需的确切列名(A，B，C)，则它将获得具有预期格式(如果可能)或特定的明显错误的结果集.

If the client selects the exact column names (A, B, C) it wants, then it will get a result set with the expected format (if possible) or a specific, obvious error.

另外，对于大型"应用程序来说，内存消耗问题以及用于选择非必需列的网络流量问题也可能至关重要.

Also the issue of memory consumption and possibly network traffic for selecting unneded columns may be critical for "big" applications.

这篇关于SELECT *在SQL中有什么缺点吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！