如何在 SQLite 中切换布尔值

2025-02-28

首先，SQLite 中没有单独的布尔数据类型，通常使用整数来存储布尔值。那么，我们可不可以在不先检索存储值的前提下，只使用一条 SQL 语句来切换这个值呢？

例如，我们有一个 people 表，内容如下：

id	name	is_friend
1	Peter	0
2	Mary	1
3	John	0

要切换 Mary 的 is_friend 属性，我们可以执行这条 SQL 语句：

1	UPDATE `people` SET is_friend = ((is_friend \| 1) - (is_friend & 1)) WHERE id = 2

当然，为了使这条 SQL 起作用，is_friend 列必须限制为只存储 0 或 1。在创建表时，请确保通过引入默认值（0）和验证 CHECK 表达式来正确初始化布尔列：

CREATE TABLE `people` (
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`name` TEXT NOT NULL,
	`is_friend` INTEGER NOT NULL DEFAULT 0 CHECK(is_friend IN (0,1))
);

不幸的是，SQLite 不允许向现有表添加 DEFAULT 或 CHECK 属性。但是，我们可以创建一个临时表，并将现有表内容复制到临时表中，删除现有表，然后重命名临时表的表名：

-- begin transaction
BEGIN;

-- create a new table with proper structure
CREATE TABLE `people_proper` (
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`name` TEXT NOT NULL,
	`is_friend` INTEGER NOT NULL DEFAULT 0 CHECK(is_friend IN (0,1))
);

-- copy data from old table
INSERT INTO `people_proper`
    SELECT  
        `id`, 
        `name`, 
        -- make all invalid data to default to 0 (false)
        (CASE WHEN is_friend NOT IN(0,1) THEN 0 ELSE is_friend END) AS `is_friend`
    FROM `people`;

-- drop old table
DROP TABLE `people`;

-- rename new table
ALTER TABLE `people_proper` RENAME TO `people`;

-- commit transaction
COMMIT;