MySQL存储布尔值的数据类型选择

技术背景

在MySQL中，并没有专门的布尔数据类型。因此，在存储布尔值（true/false）时，需要选择合适的数据类型来存储这些信息。尤其是在与PHP脚本进行数据交互时，选择合适的数据类型能简化数据处理过程。

实现步骤

1. 使用BIT类型（MySQL 5.0.3及以上版本）

从MySQL 5.0.3开始，BIT数据类型可用于存储位字段值。BIT(M)类型允许存储M位的值，M的范围是1到64。若只存储布尔值，可使用BIT(1)。

-- 创建包含BIT(1)类型字段的表
CREATE TABLE example_table (
    is_active BIT(1)
);

-- 插入数据
INSERT INTO example_table (is_active) VALUES (b'1');

2. 使用BOOL或BOOLEAN类型

BOOL和BOOLEAN是TINYINT(1)的同义词。在这种情况下，值0被视为false，非零值被视为true。

-- 创建包含BOOL类型字段的表
CREATE TABLE example_table (
    is_active BOOL
);

-- 插入数据
INSERT INTO example_table (is_active) VALUES (1);

3. 使用TINYINT(1)类型

直接使用TINYINT(1)来存储布尔值，与BOOL和BOOLEAN本质相同，但更直观地表示是一个小整数类型。

-- 创建包含TINYINT(1)类型字段的表
CREATE TABLE example_table (
    is_active TINYINT(1)
);

-- 插入数据
INSERT INTO example_table (is_active) VALUES (0);

4. 使用CHAR(0)类型

这是一种节省空间的方法，CHAR(0)类型的字段可以只占用一位。将''视为true，NULL视为false。

-- 创建包含CHAR(0)类型字段的表
CREATE TABLE example_table (
    is_active CHAR(0) DEFAULT NULL
);

-- 设置为true
UPDATE example_table SET is_active = '';
-- 设置为false
UPDATE example_table SET is_active = NULL;

5. 使用ENUM类型

ENUM类型可以定义一组可能的值，如ENUM('False', 'True')，MySQL会将其内部存储为整数，其中'False'对应0，'True'对应1。

-- 创建包含ENUM类型字段的表
CREATE TABLE example_table (
    is_active ENUM('False', 'True')
);

-- 插入数据
INSERT INTO example_table (is_active) VALUES ('True');

核心代码

1. 使用BIT(1)插入和查询数据

-- 创建表
CREATE TABLE user_status (
    user_id INT,
    is_online BIT(1)
);

-- 插入数据
INSERT INTO user_status (user_id, is_online) VALUES (1, b'1');

-- 查询数据
SELECT user_id, is_online FROM user_status;

2. 使用TINYINT(1)插入和查询数据

-- 创建表
CREATE TABLE user_status (
    user_id INT,
    is_online TINYINT(1)
);

-- 插入数据
INSERT INTO user_status (user_id, is_online) VALUES (1, 1);

-- 查询数据
SELECT user_id, is_online FROM user_status;

最佳实践

• 选择TINYINT(1)：如果追求代码的可读性和跨数据库的兼容性，TINYINT(1)是一个不错的选择。它在各种客户端库和编程语言中都能很好地处理，并且与BOOL和BOOLEAN本质相同。
• 选择BIT(1)：当需要存储大量布尔值，并且对存储空间有严格要求时，BIT(1)可以节省空间。但要注意，不同的客户端库对BIT类型的处理可能不同。
• 避免使用ENUM：虽然ENUM类型可以明确表示布尔值，但它存在一些问题，如插入空字符串的风险，以及内部存储和处理的复杂性。

常见问题

1. BIT类型在某些客户端库中处理麻烦

在node.js + mysql中，BIT类型返回的是Buffer对象，始终为真值，需要进行额外的处理。

2. CHAR(0)类型不易理解和维护

使用CHAR(0)类型来表示布尔值可能会让新开发者难以理解，并且在一些ORM框架中难以映射。

3. ENUM类型的插入问题

ENUM类型容易插入空字符串，并且在类型转换和比较时可能会出现意外结果。