为什么要在 SQL 中使用 WHERE 1=0 语句？

why would you use WHERE 1=0 statement in SQL?

提问by MozenRath

I saw a query run in a log file on an application. and it contained a query like:

我看到一个查询在应用程序的日志文件中运行。它包含一个查询，如：

SELECT ID FROM CUST_ATTR49 WHERE 1=0

what is the use of such a query that is bound to return nothing?

这种必然不返回任何内容的查询有什么用？

回答by Andrea Colleoni

A query like this can be used to ping the database. The clause:

像这样的查询可用于 ping 数据库。条款：

WHERE 1=0

Ensures that non data is sent back, so no CPU charge, no Network traffic or other resource consumption.

确保发回非数据，因此没有 CPU 费用，没有网络流量或其他资源消耗。

A query like that can test for:

这样的查询可以测试：

• server availability
• CUST_ATTR49 table existence
• ID column existence
• Keeping a connection alive
• Cause a trigger to fire without changing any rows
• manage many OR conditions in dynamic queries (e.g WHERE 1=0 OR <condition>)

• 服务器可用性
• CUST_ATTR49 表存在
• ID列存在
• 保持连接活跃
• 在不更改任何行的情况下触发触发器
• 管理动态查询中的许多 OR 条件（例如WHERE 1=0 OR <condition>）

回答by duedl0r

A usecase I can think of: you have a filter form where you don't want to have any search results. If you specify some filter, they get added to the where clause.

我能想到的一个用例：您有一个过滤器表单，您不想在其中获得任何搜索结果。如果您指定某个过滤器，它们将被添加到 where 子句中。

Or it's usually used if you have to create a sql query by hand. E.g. you don't want to check whether the where clause is empty or not..and you can just add stuff like this:

或者，如果您必须手动创建 sql 查询，则通常使用它。例如，您不想检查 where 子句是否为空……您可以添加如下内容：

where := "WHERE 0=1"

if X then where := where + " OR ... "
if Y then where := where + " OR ... "

(if you connect the clauses with OR you need 0=1, if you have AND you have 1=1)

（如果你用 OR 连接子句，你需要 0=1，如果你有 AND 你有 1=1）

回答by Darshana

This may be also used to extract the table schema from a table without extracting any data inside that table. As Andrea Colleoni said those will be the other benefits of using this.

这也可用于从表中提取表模式，而不提取该表内的任何数据。正如 Andrea Colleoni 所说，这些将是使用它的其他好处。

回答by Guest

Some systems use scripts and can dynamically set selected records to be hidden from a full list; so a false condition needs to be passed to the SQL. For example, three records out of 500 may be marked as Privacy for medical reasons and should not be visible to everyone. A dynamic query will control the 500 records are visible to those in HR, while 497 are visible to managers. A value would be passed to the SQL clause that is conditionally set, i.e. ' WHERE 1=1 ' or ' WHERE 1=0 ', depending who is logged into the system.

一些系统使用脚本并且可以动态地将选定的记录设置为从完整列表中隐藏；所以需要向 SQL 传递一个 false 条件。例如，出于医疗原因，500 条记录中的 3 条可能被标记为“隐私”，并且不应该对所有人可见。动态查询将控制 500 条记录对 HR 人员可见，而 497 条记录对经理可见。一个值将传递给有条件设置的 SQL 子句，即“WHERE 1=1”或“WHERE 1=0”，具体取决于登录系统的人员。

回答by YoYo

As an answer - but also as further clarification to what @AndreaColleoni already mentioned:

作为答案 - 但也作为对@AndreaColleoni 已经提到的内容的进一步澄清：

manage many OR conditions in dynamic queries (e.g WHERE 1=0 OR <condition>)

管理动态查询中的许多 OR 条件（例如WHERE 1=0 OR <condition>）

Purpose as an on/off switch

用作开/关开关

I am using this as a switch (on/off) statement for portions of my Query.

我将它用作我的部分查询的开关（开/关）语句。

If I were to use

如果我要使用

WHERE 1=1
  AND (0=? OR first_name = ?) 
  AND (0=? OR last_name = ?)

Then I can use the first bind variable (?) to turn on or off the first_namesearch criterium. , and the third bind variable (?) to turn on or off the last_namecriterium.

然后我可以使用第一个绑定变量 ( ?) 打开或关闭first_name搜索条件。, 和第三个绑定变量 ( ?) 打开或关闭last_name标准。

I have also added a literal 1=1just for esthetics so the text of the query aligns nicely.

我还添加了一个1=1仅用于美学的文字，以便查询的文本很好地对齐。

For just those two criteria, it does not appear that helpful, as one might thing it is just easier to do the same by dynamically building your WHERE condition by either putting only first_nameor last_name, or both, or none. So your code will have to dynamically build 4 versions of the same query. Imagine what would happen if you have 10 different criteria to consider, then how many combinations of the same query will you have to manage then?

仅就这两个标准而言，它似乎并没有那么有用，因为通过仅放置first_name或放置last_name或两者都放置或不放置来动态构建您的 WHERE 条件，这样做可能会更容易。因此，您的代码必须动态构建同一查询的 4 个版本。想象一下，如果您要考虑 10 个不同的标准，那么您将需要管理多少个相同查询的组合？

Compile Time Optimization

编译时间优化

I also might add that adding in the 0=? as a bind variable switch will not work very well if all your criteria are indexed. The run time optimizer that will select appropriate indexes and execution plans, might just not see the cost benefit of using the index in those slightly more complex predicates. Hence I usally advice, to inject the 0 / 1 explicitly into your query (string concatenating it in in your sql, or doing some search/replace). Doing so will give the compiler the chance to optimize out redundant statements, and give the Runtime Executer a much simpler query to look at.

我也可以在 0=? 因为如果您的所有条件都已编入索引，则绑定变量开关将无法正常工作。将选择适当索引和执行计划的运行时优化器可能只是看不到在那些稍微复杂的谓词中使用索引的成本优势。因此，我通常建议将 0 / 1 显式注入到您的查询中（将它连接到您的 sql 中，或进行一些搜索/替换）。这样做将使编译器有机会优化冗余语句，并为运行时执行器提供一个更简单的查询来查看。

(0=1 OR cond = ?) --> (cond = ?)
(0=0 OR cond = ?) --> Always True (ignore predicate)

In the second statement above the compiler knows that it never has to even consider the second part of the condition (cond = ?), and it will simply remove the entire predicate. If it were a bind variable, the compiler could never have accomplished this.

在上面的第二条语句中，编译器知道它甚至不必考虑条件 ( cond = ?)的第二部分，它只会删除整个谓词。如果它是一个绑定变量，编译器永远不可能完成这个。

Because you are simply, and forcedly, injecting a 0/1, there is zero chance of SQL injections.

因为您只是强制地注入了 0/1，所以 SQL 注入的可能性为零。

In my SQL's, as one approach, I typically place my sql injection points as ${literal_name}, and I then simply search/replace using a regex any ${...} occurrence with the appropriate literal, before I even let the compiler have a stab at it. This basically leads to a query stored as follows:

在我的 SQL 中，作为一种方法，我通常将我的 sql 注入点放置为 ${literal_name}，然后我简单地使用正则表达式搜索/替换任何 ${...} 出现的适当文字，在我什至让编译器试一试。这基本上导致查询存储如下：

WHERE 1=1
  AND (0=${cond1_enabled} OR cond1 = ?)
  AND (0=${cond2_enabled} OR cond2 = ?)

Looks good, easily understood, the compiler handles it well, and the Runtime Cost Based Optimizer understands it better and will have a higher likelihood of selecting the right index.

看起来不错，容易理解，编译器处理得很好，基于运行时成本的优化器更好地理解它，并且选择正确索引的可能性更高。

I take special care in what I inject. Prime way for passing variables is and remains bind variables for all the obvious reasons.

我特别注意我注射的东西。出于所有显而易见的原因，传递变量的主要方式是并且仍然是绑定变量。

回答by Jaideep Karande

This is very good in metadata fetching and makes thing generic. Many DBs have optimizer so they will not actually execute it but its still a valid SQL statement and should execute on all DBs. This will not fetch any result, but you know column names are valid, data types etc. If it does not execute you know something is wrong with DB(not up etc.) So many generic programs execute this dummy statement for testing and fetching metadata.

这在元数据获取方面非常好，并使事情变得通用。许多数据库都有优化器，因此它们实际上不会执行它，但它仍然是一个有效的 SQL 语句，应该在所有数据库上执行。这不会获取任何结果，但您知道列名是有效的，数据类型等。如果它不执行，您知道 DB 有问题（未启动等）因此许多通用程序执行此虚拟语句以测试和获取元数据.

回答by John Woo

quoted from Greg

引用自格雷格

If the list of conditions is not known at compile time and is instead built at run time, you don't have to worry about whether you have one or more than one condition. You can generate them all like:

and

and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with.

I've never seen this used for any kind of injection protection, as you say it doesn't seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.

如果条件列表在编译时未知，而是在运行时构建，则不必担心是否有一个或多个条件。您可以像这样生成它们：

和

并将它们连接在一起。以 1=1 开头，初始 和 有一些关联。

我从未见过这用于任何类型的注射保护，正如您所说，它似乎没有多大帮助。我已经看到它被用作实现的便利。SQL 查询引擎最终会忽略 1=1，因此它应该不会影响性能。

Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?

为什么有人会在 SQL 子句中使用 WHERE 1=1 AND <conditions>？

回答by Bruce52

An example of using a where condition of 1=0 is found in the Northwind 2007 database. On the main page the New Customer Order and New Purchase Order command buttons use embedded macros with the Where Condition set to 1=0. This opens the form with a filter that forces the sub-form to display only records related to the parent form. This can be verified by opening either of those forms from the tree without using the macro. When opened this way all records are displayed by the sub-form.

在 Northwind 2007 数据库中可以找到使用 where 条件 1=0 的示例。在主页上，新客户订单和新采购订单命令按钮使用嵌入的宏，其中条件设置为 1=0。这将打开带有过滤器的表单，该过滤器强制子表单仅显示与父表单相关的记录。这可以通过在不使用宏的情况下从树中打开这些表单中的任何一个来验证。当以这种方式打开时，所有记录都由子表单显示。

回答by kriti

It can be useful when only table metadata is desired in an application. For example, if you are writing a JDBC application and want to get the column display size of columns in the table.

当应用程序中只需要表元数据时，它会很有用。例如，如果您正在编写 JDBC 应用程序，并且想要获取表中列的列显示大小。

Pasting a code snippet here

在此处粘贴代码片段

String query = "SELECT * from <Table_name> where 1=0";
PreparedStatement stmt = connection.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
ResultSetMetaData rsMD  = rs.getMetaData();
int columnCount = rsMD.getColumnCount();
for(int i=0;i<columnCount;i++) {
    System.out.println("Column display size is: " + rsMD.getColumnDisplaySize(i+1));
}

Here having a query like "select * from table" can cause performance issues if you are dealing with huge data because it will try to fetch all the records from the table. Instead if you provide a query like "select * from table where 1=0" then it will fetch only table metadata and not the records so it will be efficient.

如果您正在处理大量数据，那么像“select * from table”这样的查询可能会导致性能问题，因为它会尝试从表中获取所有记录。相反，如果您提供诸如“select * from table where 1=0”之类的查询，那么它将只获取表元数据而不是记录，因此它会很高效。

回答by Mark

Per user milso in another thread, another purpose for "WHERE 1=0":

每个用户 milso 在另一个线程中，“WHERE 1=0”的另一个目的：

CREATE TABLE New_table_name as select * FROM Old_table_name WHERE 1 = 2;

this will create a new table with same schema as old table. (Very handy if you want to load some data for compares)

CREATE TABLE New_table_name as select * FROM Old_table_name WHERE 1 = 2;

这将创建一个与旧表具有相同架构的新表。（如果你想加载一些数据进行比较，非常方便）