在微软的SQL Server中是否存在一个布尔数据类型，比如MySQL?

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

If so, what is the alternative in MS SQL Server?

如果是的话，在MS SQL Server中还有什么可选的呢?

8 个解决方案

#1

333

You could use the BIT datatype to represent boolean data. A BIT field's value is either 1,0 or null.

您可以使用位数据类型来表示布尔数据。位域的值可以是1、0或null。

#2

You may want to use the BIT data type, probably setting is as NOT NULL:

您可能想要使用位数据类型，可能设置为NOT NULL:

Quoting the MSDN article:

引用MSDN文章:

bit (Transact-SQL)

位(transact - sql)

An integer data type that can take a value of 1, 0, or NULL.

可以取值为1、0或NULL的整数数据类型。

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

SQL Server数据库引擎优化位列的存储。如果一个表中有8个或更少的位列，那么列将存储为1个字节。如果有9到16位的列，这些列将被存储为2字节，依此类推。

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。

#3

You are looking for a `bit`. It stores 1 or 0 (or `NULL`).

Alternatively, you could use the strings 'true' and 'false' in place of 1 or 0, like so-

或者，您可以使用字符串“true”和“false”来代替1或0，比如so-

declare @b1 bit = 'false'
print @b1                    --prints 0

declare @b2 bit = 'true'
print @b2                    --prints 1

Also, any non 0 value (either positive or negative) evaluates to (or converts to in some cases) a 1.

同样，任何非0值(或正值或负值)的计算结果都是(或在某些情况下转换为)a 1。

declare @i int = -42
print cast(@i as bit)    --will print 1, because @i is not 0

Note that SQL Server uses three valued logic (true, false, and NULL), since NULL is a possible value of the bit data type. Here are the relevant truth tables-

注意，SQL Server使用三个值逻辑(true、false和NULL)，因为NULL是位数据类型的一个可能值。以下是相关的真相表格

More information on three valued logic-

SQL Server中三值逻辑的示例

http://www.firstsql.com/idefend3.htm

https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/

#4

There is boolean data type in SQL Server. Its values can be TRUE, FALSE or UNKNOWN. However, the boolean data type is only the result of a boolean expression containing some combination of comparison operators (e.g. =, <>, <, >=) or logical operators (e.g. AND, OR, IN, EXISTS). Boolean expressions are only allowed in a handful of places including the WHERE clause, HAVING clause, the WHEN clause of a CASE expression or the predicate of an IF or WHILE flow control statement.

SQL Server中有布尔数据类型。它的值可以是TRUE、FALSE或UNKNOWN。但是，布尔数据类型仅是包含比较运算符(例如=、<>、<、>=)或逻辑运算符(例如AND、IN)组合的布尔表达式的结果。布尔表达式只允许在少数地方使用，包括WHERE子句、have子句、CASE表达式的WHEN子句或IF或WHILE流控制语句的谓词。

For all other usages, including the data type of a column in a table, boolean is not allowed. For those other usages, the BIT data type is preferred. It behaves like a narrowed-down INTEGER which allows only the values 0, 1 and NULL, unless further restricted with a NOT NULL column constraint or a CHECK constraint.

对于所有其他用途，包括表中列的数据类型，布尔值是不允许的。对于那些其他用法，比特数据类型是首选。它的行为类似于一个narrove -down整数，它只允许值0,1和NULL，除非进一步限制为非空列约束或检查约束。

To use a BIT column in a boolean expression it needs to be compared using a comparison operator such as =, <> or IS NULL. e.g.

要在布尔表达式中使用位列，需要使用比较运算符(如=、<>或为NULL)进行比较。如。

SELECT
    a.answer_body
FROM answers AS a
WHERE a.is_accepted = 0;

From a formatting perspective, a bit value is typically displayed as 0 or 1 in client software. When a more user-friendly format is required, and it can't be handled at an application tier in front of the database, it can be converted "just-in-time" using a CASE expression e.g.

从格式的角度来看，在客户端软件中，位值通常显示为0或1。当需要一种更用户友好的格式时，并且不能在数据库前面的应用程序层中处理它时，可以使用CASE表达式(如:just-in-time)将其转换为“just-in-time”格式。

SELECT
    a.answer_body,
    CASE a.is_accepted WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS is_accepted
FROM answers AS a;

Storing boolean values as a character data type like char(1) or varchar(5) is also possible, but that is much less clear, has more storage/network overhead, and requires CHECK constraints on each column to restrict illegal values.

将布尔值存储为字符数据类型(如char(1)或varchar(5))也是可能的，但这要清楚得多，它有更多的存储/网络开销，并且需要检查每个列上的约束以限制非法值。

For reference, the schema of answers table would be similar to:

作为参考，答案表的模式类似于:

CREATE TABLE answers (
    ...,
    answer_body nvarchar(MAX) NOT NULL,
    is_accepted bit NOT NULL DEFAULT (0)
);

#5

Use the Bit datatype. It has values 1 and 0 when dealing with it in native T-SQL

使用一些数据类型。在本机T-SQL中处理时，它的值为1和0

#6

You can use Bit DataType in SQL Server to store boolean data.

您可以在SQL Server中使用位数据类型来存储布尔数据。

#7

SQL Server uses the Bit datatype

SQL Server使用位数据类型

#8

I use TINYINT(1)datatype in order to store boolean values in SQL Server though BIT is very effective

我使用TINYINT(1)数据类型来在SQL Server中存储布尔值，尽管BIT非常有效

#1

333

You could use the BIT datatype to represent boolean data. A BIT field's value is either 1,0 or null.

您可以使用位数据类型来表示布尔数据。位域的值可以是1、0或null。

#2

You may want to use the BIT data type, probably setting is as NOT NULL:

您可能想要使用位数据类型，可能设置为NOT NULL:

Quoting the MSDN article:

引用MSDN文章:

bit (Transact-SQL)

位(transact - sql)

An integer data type that can take a value of 1, 0, or NULL.

可以取值为1、0或NULL的整数数据类型。

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

SQL Server数据库引擎优化位列的存储。如果一个表中有8个或更少的位列，那么列将存储为1个字节。如果有9到16位的列，这些列将被存储为2字节，依此类推。

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。

#3

You are looking for a `bit`. It stores 1 or 0 (or `NULL`).

Alternatively, you could use the strings 'true' and 'false' in place of 1 or 0, like so-

或者，您可以使用字符串“true”和“false”来代替1或0，比如so-

declare @b1 bit = 'false'
print @b1                    --prints 0

declare @b2 bit = 'true'
print @b2                    --prints 1

Also, any non 0 value (either positive or negative) evaluates to (or converts to in some cases) a 1.

同样，任何非0值(或正值或负值)的计算结果都是(或在某些情况下转换为)a 1。

declare @i int = -42
print cast(@i as bit)    --will print 1, because @i is not 0

Note that SQL Server uses three valued logic (true, false, and NULL), since NULL is a possible value of the bit data type. Here are the relevant truth tables-

注意，SQL Server使用三个值逻辑(true、false和NULL)，因为NULL是位数据类型的一个可能值。以下是相关的真相表格

More information on three valued logic-

SQL Server中三值逻辑的示例

http://www.firstsql.com/idefend3.htm

https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/

#4

To use a BIT column in a boolean expression it needs to be compared using a comparison operator such as =, <> or IS NULL. e.g.

要在布尔表达式中使用位列，需要使用比较运算符(如=、<>或为NULL)进行比较。如。

SELECT
    a.answer_body
FROM answers AS a
WHERE a.is_accepted = 0;

SELECT
    a.answer_body,
    CASE a.is_accepted WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS is_accepted
FROM answers AS a;

For reference, the schema of answers table would be similar to:

作为参考，答案表的模式类似于:

CREATE TABLE answers (
    ...,
    answer_body nvarchar(MAX) NOT NULL,
    is_accepted bit NOT NULL DEFAULT (0)
);

#5

Use the Bit datatype. It has values 1 and 0 when dealing with it in native T-SQL

使用一些数据类型。在本机T-SQL中处理时，它的值为1和0

#6

You can use Bit DataType in SQL Server to store boolean data.

您可以在SQL Server中使用位数据类型来存储布尔数据。

#7

SQL Server uses the Bit datatype

SQL Server使用位数据类型

#8

I use TINYINT(1)datatype in order to store boolean values in SQL Server though BIT is very effective

我使用TINYINT(1)数据类型来在SQL Server中存储布尔值，尽管BIT非常有效

秒客网

在微软的SQL Server中是否存在一个布尔数据类型，比如MySQL?

8 个解决方案

#1

#2

#3

You are looking for a `bit`. It stores 1 or 0 (or `NULL`).

#4

#5

#6

#7

#8

#1

#2

#3

You are looking for a `bit`. It stores 1 or 0 (or `NULL`).

#4

#5

#6

#7

#8

相关文章

在微软的SQL Server中是否存在一个布尔数据类型，比如MySQL?

8 个解决方案

#1

#2

#3

You are looking for a bit. It stores 1 or 0 (or NULL).

#4

#5

#6

#7

#8

#1

#2

#3

You are looking for a bit. It stores 1 or 0 (or NULL).

#4

#5

#6

#7

#8

相关文章

You are looking for a `bit`. It stores 1 or 0 (or `NULL`).

You are looking for a `bit`. It stores 1 or 0 (or `NULL`).