在 SQL 语句中使用 WHERE 1=1 的目的是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1264681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the purpose of using WHERE 1=1 in SQL statements?
提问by RaYell
Possible Duplicates:
Why would a sql query have “where 1 = 1”
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?
可能的重复：
为什么 sql 查询会有“where 1 = 1”
为什么有人会在 SQL 子句中使用 WHERE 1=1 AND <conditions>？
I've seen that a lot in different query examples and it goes to probably all SQL engines.
我在不同的查询示例中看到了很多，它可能适用于所有 SQL 引擎。
If there is a query that has no conditions defined people (and specially ORM frameworks) often add always-true condition WHERE 1 = 1or something like that.
如果有一个没有定义条件的查询，人们（特别是 ORM 框架）通常会添加始终为真的条件WHERE 1 = 1或类似的东西。
So instead of
所以代替
SELECT id, name FROM users;
they use
他们使用
SELECT id, name FROM users WHERE 1 = 1;
The only possible reason I could think of if you are adding conditions dynamically you don't have to worry about stripping the initial ANDbut still quite often this 1 = 1condition is stripped if there is an actual condition in the query.
我能想到的唯一可能原因是，如果您动态添加条件，您不必担心剥离初始条件，AND但1 = 1如果查询中存在实际条件，则通常会剥离此条件。
Actual example from CakePHP (generated by framework):
CakePHP 的实际示例（由框架生成）：
(no conditions)
（无条件）
SELECT `User`.`id`, `User`.`login`
FROM `users` AS `User` WHERE 1 = 1
ORDER BY `User`.`id` ASC;
(with condition)
（有条件）
SELECT `User`.`id`, `User`.`login`
FROM `users` AS `User`
WHERE `User`.`login` = '[email protected]'
LIMIT 1;
Is there any reason for adding that extra condition?
是否有任何理由添加该额外条件？
采纳答案by Noon Silk
Yeah, it's typically because it starts out as 'where 1 = 0', to force the statement to fail.
是的，这通常是因为它以“where 1 = 0”开始，以强制语句失败。
It's a more naive way of wrapping it up in a transaction and not committing it at the end, to test your query. (This is the preferred method).
这是一种将其包装在事务中而不是在最后提交以测试您的查询的更幼稚的方式。（这是首选方法）。
回答by HeDinges
It's also a common practice when people are building the sql query programmatically, it's just easier to start with 'where 1=1 ' and then appending ' and customer.id=:custId' depending if a customer id is provided. So you can always append the next part of the query starting with 'and ...'.
当人们以编程方式构建 sql 查询时，这也是一种常见的做法，从“where 1=1”开始，然后附加“和 customer.id=:custId”（取决于是否提供了客户 ID）更容易。因此，您始终可以附加以“和...”开头的查询的下一部分。
回答by paxdiablo
People use it because they're inherently lazy when building dynamic SQL queries. If you start with a "where 1 = 1"then all your extra clauses just start with "and"and you don't have to figure out.
人们使用它是因为他们在构建动态 SQL 查询时天生就很懒惰。如果你以 a 开头，"where 1 = 1"那么你所有的额外子句"and"都以开头，你不必弄清楚。
Not that there's anything wrong with being inherently lazy. I've seen doubly-linked lists where an "empty" list consists of two sentinel nodes and you start processing at the first->nextup until last->previnclusive.
并不是说天生懒惰有什么问题。我见过双向链表，其中一个“空”列表由两个哨兵节点组成，你开始处理first->next直到last->prev包含。
This actually removed all the special handling code for deleting firstand lastnodes. In this set-up, everynode was a middle node since you weren't able to delete firstor last. Two nodes were wasted but the code was simpler and (ever so slightly) faster.
这实际上删除了所有用于删除first和last节点的特殊处理代码。在此设置中，每个节点都是中间节点，因为您无法删除first或last. 浪费了两个节点，但代码更简单并且（稍微）更快。
The only other place I've ever seen the "1 = 1" construct is in BIRT. Reports often use positional parameters and are modified with Javascript to allow all values. So the query:
我见过的“1 = 1”构造的唯一其他地方是在 BIRT 中。报告通常使用位置参数并使用 Javascript 进行修改以允许所有值。所以查询：
select * from tbl where col = ?
when the user selects "*"for the parameter being used for colis modified to read:
当用户选择"*"正在使用的参数被col修改为：
select * from tbl where ((col = ?) or (1 = 1))
This allows the new query to be used without fiddling around with the positional parameter details. There's still exactly one such parameter. Any decent DBMS (e.g., DB2/z) will optimize that query to basically remove the clause entirely before trying to construct an execution plan, so there's no trade-off.
这允许使用新查询而无需摆弄位置参数详细信息。仍然只有一个这样的参数。任何体面的 DBMS（例如 DB2/z）都会优化该查询以在尝试构建执行计划之前基本上完全删除该子句，因此没有折衷。
回答by Christian13467
The 1=1is ignored by always all rdbms. There is no tradeoff executing a query with WHERE 1=1.
在1 = 1是始终都RDBMS忽略。使用WHERE 1=1执行查询没有任何折衷。
Building dynamic WHERE conditions, like ORM frameworks or other do very often, it is easier to append the realwhere conditions because you avoid checking for prepending an ANDto the current condition.
构建动态 WHERE 条件，如 ORM 框架或其他经常做的事情，更容易附加真实的where 条件，因为您避免检查是否在当前条件前添加AND。
stmt += "WHERE 1=1";
if (v != null) {
stmt += (" AND col = " + v.ToString());
}
This is how it looks like without 1=1.
这就是没有 1=1 时的样子。
var firstCondition = true;
...
if (v != null) {
if (!firstCondition) {
stmt += " AND ";
}
else {
stmt += " WHERE ";
firstCondition = false;
}
stmt += "col = " + v.ToString());
}
回答by Wael Dalloul
As you said:
如你所说：
if you are adding conditions dynamically you don't have to worry about stripping the initial AND that's the only reason could be, you are right.
如果您要动态添加条件，则不必担心剥离初始条件，这可能是唯一的原因，您是对的。
回答by Bhaskar
Using 1=1 is actually not a very good idea as this can cause full table scans by itself.
使用 1=1 实际上不是一个好主意，因为这本身会导致全表扫描。
See this--> T-SQL 1=1 Performance Hit
看到这个--> T-SQL 1=1 性能命中
