SQL Server - 布尔文字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7170688/
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
SQL Server - boolean literal?
提问by dpp
How to write literal boolean value in SQL Server? See sample use:
如何在 SQL Server 中编写文字布尔值？请参阅示例使用：
select * from SomeTable where PSEUDO_TRUE
another sample:
另一个示例：
if PSEUDO_TRUE
begin
select 'Hello, SQL!'
end
Note: The query above has nothing to do with how I'm going to use it. It is just to test the literal boolean.
注意：上面的查询与我将如何使用它无关。这只是为了测试文字布尔值。
回答by Damien_The_Unbeliever
SQL Server doesn't have a boolean data type. As @Mikael has indicated, the closest approximation is the bit. But that is a numeric type, not a boolean type. In addition, it only supports 2 values - 0or 1(and one non-value, NULL).
SQL Server 没有布尔数据类型。正如@Mikael 所指出的，最接近的近似值是位。但那是数字类型，而不是布尔类型。此外，它只支持 2 个值 -0或1（和一个非值，NULL）。
SQL (standard SQL, as well as T-SQL dialect) describes a Three valued logic. The boolean type for SQL should support 3 values - TRUE, FALSEand UNKNOWN(and also, the non-value NULL). So bitisn't actually a good match here.
SQL（标准 SQL，以及 T-SQL 方言）描述了一个三值逻辑。SQL 的布尔类型应支持 3 个值 - TRUE,FALSE和UNKNOWN（以及非值NULL）。所以bit这里实际上不是一个很好的匹配。
Given that SQL Server has no support for the data type, we should not expect to be able to write literals of that "type".
鉴于 SQL Server 不支持数据类型，我们不应该期望能够编写该“类型”的文字。
回答by nocache
select * from SomeTable where 1=1
回答by Sam
This isn't mentioned in any of the other answers. If you want a value that orms (should) hydrate as boolean you can use
这在任何其他答案中都没有提到。如果你想要一个 orms（应该）作为布尔值的值，你可以使用
CONVERT(bit, 0) -- false CONVERT(bit, 1) -- true
CONVERT(bit, 0) -- 假 CONVERT(bit, 1) -- 真
This gives you a bit which is not a boolean. You cannot use that value in an if statement for example:
这给了你一些不是布尔值的东西。您不能在 if 语句中使用该值，例如：
IF CONVERT(bit, 0)
BEGIN
print 'Yay'
END
woudl not parse. You would still need to write
不会解析。你仍然需要写
IF CONVERT(bit, 0) = 0
So its not terribly useful.
所以它不是非常有用。
回答by Bohemian
Most databases will accept this:
大多数数据库会接受这个：
select * from SomeTable where true
However some databases (eg SQL Server, Oracle) do not have a boolean type. In these cases you may use:
然而，一些数据库（例如 SQL Server、Oracle）没有布尔类型。在这些情况下，您可以使用：
select * from SomeTable where 1=1
BTW, if building up an sql where clause by hand, this is the basis for simplifying your code because you can avoid having to know if the condition you're about to add to a where clause is the firstone (which should be preceded by "WHERE"), or a subsequentone (which should be preceded by "AND"). By always starting with "WHERE 1=1", all conditions (if any) added to the where clause are preceded by "AND".
顺便说一句，如果手动构建一个 sql where 子句，这是简化代码的基础，因为您可以避免知道要添加到 where 子句中的条件是否是第一个（应该在前面"WHERE"）或随后的一个（其前应先"AND"）。通过始终以 开头"WHERE 1=1"，添加到 where 子句的所有条件（如果有）都以 开头"AND"。
回答by Dalex
According to Microsoft: syntax for searching is
根据微软的说法：搜索的语法是
[ WHERE <search_condition> ]*
And search condition is:
搜索条件是：
<search_condition> ::=
{ [ NOT ] <predicate> | ( <search_condition> ) }
[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]
[ ,...n ]
And predicate is:
谓词是：
<predicate> ::=
{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
As you can see, you alwayshave to write two expressions to compare. Here search condition is boolean expressionlike 1=1, a!=b
如您所见，您总是必须编写两个表达式进行比较。这里的搜索条件是 布尔表达式，如 1=1, a!=b
Do not confuse search expressions with boolean constantslike 'True'or 'False'. You can assign boolean constants to BIT variables
不要将搜索表达式与布尔常量（如'True'或'False' ）混淆。您可以将布尔常量分配给 BIT 变量
DECLARE @B BIT
SET @B='True'
but in TSQL you can not use boolean constants instead of boolean expressions like this:
但在 TSQL 中，您不能使用布尔常量代替布尔表达式，如下所示：
SELECT * FROM Somewhere WHERE 'True'
It will not work.
不起作用。
But you can use boolean constants to build two-sided search expression like this:
但是您可以使用布尔常量来构建这样的双面搜索表达式：
SEARCH * FROM Somewhere WHERE 'True'='True'
回答by Daniel Renshaw
SQL Server does not have literal true or false values. You'll need to use the 1=1method (or similar) in the rare cases this is needed.
SQL Server 没有文字 true 或 false 值。1=1在极少数情况下，您需要使用该方法（或类似方法）。
One option is to create your own named variables for true and false
一种选择是为真假创建自己的命名变量
DECLARE @TRUE bit
DECLARE @FALSE bit
SET @TRUE = 1
SET @FALSE = 0
select * from SomeTable where @TRUE = @TRUE
But these will only exist within the scope of the batch (you'll have to redeclare them in every batch in which you want to use them)
但是这些只会存在于批处理范围内（您必须在要使用它们的每个批处理中重新声明它们）
回答by Matt H
You can use the values 'TRUE'and 'FALSE'.
From https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql:
您可以使用值'TRUE'和'FALSE'。从https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql：
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
字符串值 TRUE 和 FALSE 可以转换为位值：TRUE 转换为 1，FALSE 转换为 0。
回答by Mikael Eriksson
How to write literal boolean value in SQL Server?
select * from SomeTable where PSEUDO_TRUE
如何在 SQL Server 中编写文字布尔值？
select * from SomeTable where PSEUDO_TRUE
There is no such thing.
哪有这回事。
You have to compare the value with something using = < > like .... The closest you get a boolean value in SQL Server is the bit. And that is an integer that can have the values null, 0and 1.
您必须将值与使用= < > like .... 在 SQL Server 中最接近布尔值的是bit。这是一个整数，可以具有值null,0和1。
回答by Marco Guignard
You should consider that a "true value" is everything except 0 and not only 1. So instead of 1=1 you should write 1<>0.
您应该考虑到“真值”是除了 0 而不仅仅是 1 之外的所有内容。因此，您应该写 1<>0 而不是 1=1。
Because when you will use parameter (@param <> 0) you could have some conversion issue.
因为当您使用参数 (@param <> 0) 时，您可能会遇到一些转换问题。
The most know is Access which translate True value on control as -1 instead of 1.
最了解的是 Access，它将控件上的 True 值转换为 -1 而不是 1。
回答by David Lean
I question the value of using a Boolean in TSQL. Every time I've started wishing for Booleans & For loops I realised I was approaching the problem like a C programmer & not a SQL programmer. The problem became trivial when I switched gears.
我质疑在 TSQL 中使用布尔值的价值。每次我开始希望使用 Booleans 和 For 循环时，我都意识到我正在像 C 程序员而不是 SQL 程序员一样处理这个问题。当我换档时，问题变得微不足道。
In SQL you are manipulating SETs of data. "WHERE BOOLEAN" is ineffective, as does not change the set you are working with. You need to compare each row with something for the filter clause to be effective. The Table/Resultset isan iEnumerable, the SELECT statement isa FOREACH loop.
在 SQL 中，您正在操作数据集。“WHERE BOOLEAN”无效，因为它不会改变您正在使用的集合。您需要将每一行与过滤子句进行比较才能有效。Table/Resultset是一个 iEnumerable，SELECT 语句是一个 FOREACH 循环。
Yes, "WHERE IsAdmin = True" is nicer to read than "WHERE IsAdmin = 1"
是的，“WHERE IsAdmin = True”比“WHERE IsAdmin = 1”更好读
Yes, "WHERE True" would be nicer than "WHERE 1=1, ..." when dynamically generating TSQL.
是的，在动态生成 TSQL 时，“WHERE True”会比“WHERE 1=1, ...”更好。
and maybe, passing a Boolean to a stored proc may make an if statement more readable.
也许，将布尔值传递给存储过程可能会使 if 语句更具可读性。
But mostly, the more IF's, WHILE's & Temp Tables you have in your TSQL, the more likely you should refactor it.
但大多数情况下，TSQL 中的 IF、WHILE 和临时表越多，就越有可能重构它。
