在 MySQL 中使用 TINYINT 类型进行比较查询时，需要注意的事项

boyanx1个月前 (05-15)技术教程10

最近在项目中在表中新增了两个字段，使用的TINYINT（1）定义布尔类型，并且未设置默认值，这个字段是后来新增，新增之后没有对以前的历史数据进行处理，于是在代码中比较的时候，使用了如下示例代码：

List<ImportSource> hisImportSourceList = this.importSourceRepository.lambdaQuery()
  .eq(ImportSource::getTemplateCode, template.getTemplateCode())
                .ne(ImportSource::getTerminateStatus,true)
                .in(ImportSource::getTerminalCode, terminalCodeSet)
                .ne(ImportSource::getStudentStatus,false)
                .list();

以上代码导致未查询到已经存在数据的数据，导致数据多次批量导入。

关键问题点：因为NULL 与任何值（包括 NULL ）比较都会返回 NULL ，而不是 true 或 false。因此对TINYINT的用法进行整理，希望能够给大家工作中提供一点帮助，具体内容如下：

1. TINYINT 的取值范围

- TINYINT 是有符号类型时，取值范围是 -128 到 127 。
- TINYINT 是无符号类型时，取值范围是 0 到 255 。

在比较时，确保比较的值在合法范围内，否则可能导致查询结果错误。

2. 布尔值的处理

MySQL 中没有专门的布尔类型，通常使用 TINYINT(1) 来表示布尔值：

- 0 表示 false 。
- 1 表示 true 。

在查询时，可以直接使用 0 或 1 进行比较：

SELECT * FROM users WHERE is_active = 1;

3. 隐式类型转换

MySQL 在比较时可能会进行隐式类型转换。例如，将字符串与 TINYINT 比较时，字符串会被转换为数字：

SELECT * FROM users WHERE is_active = '1'; -- 字符串 '1' 会被转换为数字 1

为了避免潜在的问题，建议使用相同类型进行比较：

SELECT * FROM users WHERE is_active + 1 = 2; -- 索引可能失效

4. NULL 值的处理

TINYINT 列可能包含 NULL 值。在比较时，需要注意 NULL 的特殊性：

- NULL 与任何值（包括 NULL ）比较都会返回 NULL ，而不是 true 或 false 。
- 使用 IS NULL 或 IS NOT NULL 来检查 NULL 值

SELECT * FROM users WHERE is_active IS NULL;

5. 索引的使用

如果 TINYINT 列上有索引，确保查询条件能够有效利用索引。例如：

SELECT * FROM users WHERE is_active = 1; -- 可以使用索引

避免在 TINYINT 列上使用函数或表达式，这可能导致索引失效：

SELECT * FROM users WHERE is_active + 1 = 2; -- 索引可能失效

6. 示例代码

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    is_active TINYINT(1) -- 表示布尔值
);

-- 插入数据
INSERT INTO users (id, name, is_active) VALUES (1, 'Alice', 1);
INSERT INTO users (id, name, is_active) VALUES (2, 'Bob', 0);
INSERT INTO users (id, name, is_active) VALUES (3, 'Charlie', NULL);

-- 查询活跃用户
SELECT * FROM users WHERE is_active = 1;

-- 查询非活跃用户
SELECT * FROM users WHERE is_active = 0;

-- 查询状态未知的用户
SELECT * FROM users WHERE is_active IS NULL;