MySQL 从表中选择1是什么意思？

What does it mean by select 1 from table?

提问by Microsoft Developer

I have seen many queries with something as follows.

我已经看到许多查询如下。

Select 1  
From table

What does this 1mean, how will it be executed and, what will it return?

这是什么1意思，它将如何执行，它将返回什么？

Also, in what type of scenarios, can this be used?

另外，在什么类型的场景中，可以使用它吗？

采纳答案by cwallenpoole

SELECT 1 FROM TABLE_NAMEmeans, "Return 1 from the table". It is pretty unremarkable on its own, so normally it will be used with WHEREand often EXISTS(as @gbn notes, this is not necessarily best practice, it is, however, common enough to be noted, even if it isn't really meaningful (that said, I will use it because others use it and it is "more obvious" immediately. Of course, that might be a viscous chicken vs. egg issue, but I don't generally dwell)).

SELECT 1 FROM TABLE_NAME意思是“从表中返回 1”。它本身非常不起眼，所以通常会WHERE经常使用它EXISTS（正如@gbn 指出的，这不一定是最佳实践，但是，它很常见，需要注意，即使它没有真正意义（也就是说，我会使用它，因为其他人使用它并且它立即“更明显”。当然，这可能是一个粘性鸡与蛋的问题，但我通常不会停留））。

 SELECT * FROM TABLE1 T1 WHERE EXISTS (
     SELECT 1 FROM TABLE2 T2 WHERE T1.ID= T2.ID
 );

Basically, the above will return everything from table 1 which has a corresponding ID from table 2. (This is a contrived example, obviously, but I believe it conveys the idea. Personally, I would probably do the above as SELECT * FROM TABLE1 T1 WHERE ID IN (SELECT ID FROM TABLE2);as I view that as FARmore explicit to the reader unless there were a circumstantially compelling reason not to).

基本上，上面将返回表 1 中的所有内容，它具有表 2 中的相应 ID。（显然，这是一个人为的示例，但我相信它传达了这个想法。就我个人而言，我可能会SELECT * FROM TABLE1 T1 WHERE ID IN (SELECT ID FROM TABLE2);按照我的看法执行上述操作FAR更明确的读者，除非有一个根据情形令人信服的理由不）。

EDIT

编辑

There actually is one case which I forgot about until just now. In the case where you are trying to determine existence of a value in the database from an outside language, sometimes SELECT 1 FROM TABLE_NAMEwill be used. This does not offer significant benefit over selecting an individual column, but, depending on implementation, it may offer substantial gains over doing a SELECT *, simply because it is often the case that the more columns that the DB returns to a language, the larger the data structure, which in turn mean that more time will be taken.

实际上有一个案例我直到现在才忘记。在您尝试从外部语言确定数据库中值是否存在的情况下，有时SELECT 1 FROM TABLE_NAME会使用。与选择单个列相比，这并没有提供显着的好处，但是，根据实现，它可能比执行 a 提供显着的收益SELECT *，因为通常情况下，DB 返回给语言的列越多，数据越大结构，这反过来意味着将花费更多时间。

回答by Sahil Muthoo

select 1 from tablewill return the constant 1 for every row of the table. It's useful when you want to cheaply determine if record matches your whereclause and/or join.

select 1 from table将为表的每一行返回常量 1。当您想廉价地确定记录是否与您的where子句和/或join.

回答by gbn

If you mean something like

如果你的意思是这样的

SELECT * FROM AnotherTable
  WHERE EXISTS (SELECT 1 FROM table WHERE...) 

then it's a myththat the 1is better than

那么这是一个神话，1比

SELECT * FROM AnotherTable
  WHERE EXISTS (SELECT * FROM table WHERE...) 

The 1or *in the EXISTS is ignored and you can write this as per Page 191 of the ANSI SQL 1992 Standard:

EXISTS 中的1or*被忽略，您可以按照 ANSI SQL 1992 标准的第 191 页编写：

SELECT * FROM AnotherTable
  WHERE EXISTS (SELECT 1/0 FROM table WHERE...) 

回答by Neville Kuyt

it does what it says - it will always return the integer 1. It's used to check whether a record matching your where clause exists.

它做它所说的 - 它总是返回整数 1。它用于检查是否存在与您的 where 子句匹配的记录。

回答by Tom

select 1 from tableis used by some databases as a query to test a connection to see if it's alive, often used when retrieving or returning a connection to / from a connection pool.

select 1 from table某些数据库将其用作查询来测试连接以查看它是否处于活动状态，通常在从连接池检索或返回连接时使用。

回答by TiyebM

The result is 1for every record in the table.

结果是1针对表中的每条记录。

回答by onedaywhen

Although it is not widely known, a query can have a HAVINGclause without a GROUP BYclause.

虽然它并不广为人知，但查询可以有一个HAVING没有GROUP BY子句的子句。

In such circumstances, the HAVINGclause is applied to the entire set. Clearly, the SELECTclause cannot refer to any column, otherwise you would (correct) get the error, "Column is invalid in select because it is not contained in the GROUP BY" etc.

在这种情况下，该HAVING条款适用于整个集合。显然，该SELECT子句不能引用任何列，否则您将（更正）得到错误，“列在选择中无效，因为它不包含在 GROUP BY 中”等。

Therefore, a literal value mustbe used (because SQL doesn't allow a resultset with zero columns -- why?!) and the literal value 1 (INTEGER) is commonly used: if the HAVINGclause evaluates TRUEthen the resultset will be one row with one column showing the value 1, otherwise you get the empty set.

因此，必须使用文字值（因为 SQL 不允许具有零列的结果集——为什么？！）并且INTEGER通常使用文字值 1 ( )：如果该HAVING子句求值，TRUE则结果集将是一行列显示值 1，否则您将获得空集。

Example: to find whether a column has more than one distinct value:

示例：查找一列是否有多个不同的值：

SELECT 1
  FROM tableA
HAVING MIN(colA) < MAX(colA);

回答by Robert Martin

To be slightly more specific, you would use this to do

更具体地说，您将使用它来执行

SELECT 1 FROM MyUserTable WHERE user_id = 33487

instead of doing

而不是做

SELECT * FROM MyUserTable WHERE user_id = 33487

because you don't care about looking at the results. Asking for the number 1 is very easy for the database (since it doesn't have to do any look-ups).

因为你不在乎看结果。对于数据库来说，查询数字 1 非常容易（因为它不需要进行任何查找）。

回答by mahbub_siddique

If you don't know there exist any data in your table or not, you can use following query:

如果您不知道您的表中是否存在任何数据，您可以使用以下查询：

SELECT cons_value FROM table_name;

For an Example:

例如：

SELECT 1 FROM employee;

1. It will return a column which contains the total number of rows & all rows have the same constant value 1 (for this time it returns 1 for all rows);
2. If there is no row in your table it will return nothing.

1. 它将返回一个包含总行数的列，所有行都具有相同的常量值 1（这次它为所有行返回 1）；
2. 如果您的表中没有行，它将不返回任何内容。

So, we use this SQL query to know if there is any data in the table & the number of rows indicates how many rows exist in this table.

所以，我们使用这个 SQL 查询来知道表中是否有任何数据 & 行数表示该表中存在多少行。

回答by mahbub_siddique

If you just want to check a true or false based on the WHERE clause, select 1 from table where condition is the cheapest way.

如果您只想根据 WHERE 子句检查真假，请从表中选择 1，其中条件是最便宜的方式。