这是在 SQL 中进行布尔测试的正确方法吗？

假设 active 是一个“ boolean 字段”(非常小的 int，带0或1)

-- Find all active users
select * from users where active


-- Find all inactive users
select * from users where NOT active

换句话说，“ NOT”操作符是否可以直接应用于布尔字段？

302212 次浏览

小开

In SQL Server you would generally use. I don't know about other database engines.

select * from users where active = 0

小开

A boolean in SQL is a bit field. This means either 1 or 0. The correct syntax is:

select * from users where active = 1 /* All Active Users */

select * from users where active = 0 /* All Inactive Users */

小开

MS SQL 2008 can also use the string version of true or false...

select * from users where active = 'true'
-- or --
select * from users where active = 'false'

小开

With Postgres, you may use

select * from users where active

select * from users where active = 't'

If you want to use integer value, you have to consider it as a string. You can't use integer value.

select * from users where active = 1   -- Does not work


select * from users where active = '1' -- Works

小开

I personally prefer using char(1) with values 'Y' and 'N' for databases that don't have a native type for boolean. Letters are more user frendly than numbers which assume that those reading it will now that 1 corresponds to true and 0 corresponds to false.

'Y' and 'N' also maps nicely when using (N)Hibernate.

小开

PostgreSQL supports boolean types, so your SQL query would work perfectly in PostgreSQL.

小开

If u r using SQLite3 beware:

It takes only 't' or 'f'. Not 1 or 0. Not TRUE OR FALSE.

Just learned the hard way.