为什么有人会在 SQL 子句中使用 WHERE 1=1 AND <conditions>？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/242822/
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
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?
提问by Bogdan Maxim
Why would someone use WHERE 1=1 AND <conditions>in a SQL clause (Either SQL obtained through concatenated strings, either view definition)
为什么有人会WHERE 1=1 AND <conditions>在 SQL 子句中使用（要么是通过连接字符串获得的 SQL，要么是视图定义）
I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.
我在某处看到这将用于防止 SQL 注入，但这似乎很奇怪。
If there is injection WHERE 1 = 1 AND injected OR 1=1would have the same result as injected OR 1=1.
如果有注入WHERE 1 = 1 AND injected OR 1=1将有相同的结果injected OR 1=1。
Later edit: What about the usage in a view definition?
稍后编辑：视图定义中的用法怎么样？
Thank you for your answers.
谢谢您的回答。
Still, I don't understand why would someone use this construction for defining a view, or use it inside a stored procedure.
尽管如此，我还是不明白为什么有人会使用这种结构来定义视图，或者在存储过程中使用它。
Take this for example:
以这个为例：
CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1 AND table.Field=Value
回答by Greg Hewgill
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 <condition>
and concatenate them all together. With the 1=1at the start, the initial andhas something to associate with.
并将它们连接在一起。随着1=1在开始时，最初and有一些与之交往。
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 haveseen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1so it should have no performance impact.
我从未见过这用于任何类型的注射保护，正如您所说，它似乎没有多大帮助。我已经看到它被用作实现的便利。SQL 查询引擎最终会忽略 ，1=1因此它应该不会影响性能。
回答by Eduardo Molteni
Just adding a example code to Greg's answer:
只需在 Greg 的答案中添加示例代码：
dim sqlstmt as new StringBuilder
sqlstmt.add("SELECT * FROM Products")
sqlstmt.add(" WHERE 1=1")
''// From now on you don't have to worry if you must
''// append AND or WHERE because you know the WHERE is there
If ProductCategoryID <> 0 then
sqlstmt.AppendFormat(" AND ProductCategoryID = {0}", trim(ProductCategoryID))
end if
If MinimunPrice > 0 then
sqlstmt.AppendFormat(" AND Price >= {0}", trim(MinimunPrice))
end if
回答by Carl
I've seen it used when the number of conditions can be variable.
我已经看到它在条件数量可变时使用。
You can concatenate conditions using an " AND " string. Then, instead of counting the number of conditions you're passing in, you place a "WHERE 1=1" at the end of your stock SQL statement and throw on the concatenated conditions.
您可以使用“AND”字符串连接条件。然后，不是计算您传入的条件数，而是在您的库存 SQL 语句的末尾放置一个“WHERE 1=1”并抛出连接的条件。
Basically, it saves you having to do a test for conditions and then add a "WHERE" string before them.
基本上，它使您不必对条件进行测试，然后在它们之前添加“WHERE”字符串。
回答by John Lemp
Seems like a lazy way to always know that your WHERE clause is already defined and allow you to keep adding conditions without having to check if it is the first one.
似乎是一种总是知道您的 WHERE 子句已经定义并允许您继续添加条件而无需检查它是否是第一个条件的懒惰方式。
回答by milso
Indirectly Relevant: when 1=2 is used:
间接相关：当使用 1=2 时：
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)
这将创建一个与旧表具有相同架构的新表。（如果你想加载一些数据进行比较，非常方便）
回答by aku
1 = 1 expression is commonly used in generated sql code. This expression can simplify sql generating code reducing number of conditional statements.
1 = 1 表达式通常用于生成的 sql 代码。该表达式可以简化 sql 生成代码，减少条件语句的数量。
回答by paxdiablo
Actually, I've seen this sort of thing used in BIRT reports. The query passed to the BIRT runtime is of the form:
实际上，我已经在 BIRT 报告中看到过这种东西。传递给 BIRT 运行时的查询采用以下形式：
select a,b,c from t where a = ?
and the '?' is replaced at runtime by an actual parameter value selected from a drop-down box. The choices in the drop-down are given by:
和“？” 在运行时由从下拉框中选择的实际参数值替换。下拉列表中的选项由以下各项给出：
select distinct a from t
union all
select '*' from sysibm.sysdummy1
so that you get all possible values plus "*". If the user selects "*" from the drop down box (meaning all values of a should be selected), the query has to be modified (by Javascript) before being run.
以便您获得所有可能的值加上“ *”。如果用户*从下拉框中选择“ ”（意味着应选择 a 的所有值），则在运行之前必须修改查询（通过 Javascript）。
Since the "?" is a positional parameter and MUST remain there for other things to work, the Javascript modifies the query to be:
由于“？” 是一个位置参数，必须保留在那里才能让其他事情正常工作，Javascript 将查询修改为：
select a,b,c from t where ((a = ?) or (1==1))
That basically removes the effect of the where clause while still leaving the positional parameter in place.
这基本上消除了 where 子句的影响，同时仍然保留位置参数。
I've also seen the AND case used by lazy coders whilst dynamically creating an SQL query.
我还看到了懒惰的编码人员在动态创建 SQL 查询时使用的 AND 情况。
Say you have to dynamically create a query that starts with select * from tand checks:
假设您必须动态创建一个以以下内容开头的查询select * from t：
- the name is Bob; and
- the salary is > $20,000
- 名字是鲍勃；和
- 薪水 > 20,000 美元
some people would add the first with a WHERE and subsequent ones with an AND thus:
有些人会用 WHERE 添加第一个，然后用 AND 添加后续的，因此：
select * from t where name = 'Bob' and salary > 20000
Lazy programmers (and that's not necessarily a badtrait) wouldn't distinguish between the added conditions, they'd start with select * from t where 1=1and just add AND clauses after that.
懒惰的程序员（这不一定是一个坏特征）不会区分添加的条件，他们会从开始select * from t where 1=1并在之后添加 AND 子句。
select * from t where 1=1 and name = 'Bob' and salary > 20000
回答by Carlos Toledo
I found usefull this pattern when I'm testing or doublechecking things on the database, so I can comment very quickly other conditions:
当我在数据库上进行测试或复查时，我发现这种模式很有用，因此我可以非常快速地评论其他条件：
CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1
AND Table.Field=Value
AND Table.IsValid=true
turns into:
变成：
CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1
--AND Table.Field=Value
--AND Table.IsValid=true
回答by Carlos Toledo
where 1=0, This is done to check if the table exists. Don't know why 1=1 is used.
where 1=0, 这样做是为了检查表是否存在。不知道为什么使用 1=1。
回答by sanbikinoraion
While I can see that 1=1 would be useful for generated SQL, a technique I use in PHP is to create an array of clauses and then do
虽然我可以看到 1=1 对生成的 SQL 很有用，但我在 PHP 中使用的一种技术是创建一个子句数组，然后执行
implode (" AND ", $clauses);
thus avoiding the problem of having a leading or trailing AND. Obviously this is only useful if you know that you are going to have at least one clause!
从而避免了前导或尾随 AND 的问题。显然，这只有在您知道将有至少一个子句时才有用！
