MySQL BOOLEAN 数据类型

---

简介：本教程向您展示如何使用MySQL BOOLEAN数据类型来存储布尔值true和false。

---

MySQL BOOLEAN数据类型简介

MySQL没有内置的布尔类型。但是，我们可以使用TINYINT(1)。为了使它更方便，MySQL提供BOOLEAN或BOOL作为同义词TINYINT(1)。 在MySQL中，零被视为假，非零值被视为真。要使用布尔文字，请使用常量TRUE，FALSE并分别计算为1和0。请参阅以下示例：

SELECT true, false, TRUE, FALSE, True, False;
-- 1 0 1 0 1 0

mysql> SELECT true, false, TRUE, FALSE, True, False;
+------+-------+------+-------+------+-------+
| TRUE | FALSE | TRUE | FALSE | TRUE | FALSE |
+------+-------+------+-------+------+-------+
|    1 |     0 |    1 |     0 |    1 |     0 |
+------+-------+------+-------+------+-------+
1 row in set (0.00 sec)

---

MySQL BOOLEAN的例子

MySQL将布尔值存储在表中作为整数。为了证明这一点，让我们看看下tasks表：

CREATE TABLE tasks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    completed BOOLEAN
);

即使我们将completed列指定为BOOLEAN，当我们显示表定义时，它如下所示：

DESCRIBE tasks;

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| title     | varchar(255) | NO   |     | NULL    |                |
| completed | tinyint(1)   | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

向tasks表中插入2行：

INSERT INTO tasks(title,completed)
VALUES('Master MySQL Boolean type',true),
      ('Design database table',false);

在将数据保存到布尔列之前，MySQL将其转换为1或0.以下查询从tasks表中检索数据：

SELECT 
    id, title, completed
FROM
    tasks;

+----+---------------------------+-----------+
| id | title                     | completed |
+----+---------------------------+-----------+
|  1 | Master MySQL Boolean type |         1 |
|  2 | Design database table     |         0 |
+----+---------------------------+-----------+
2 rows in set (0.00 sec)

如您所见，true和false分别转换为1和0。 因为布尔值是TINYINT(1)，您可以将除1和0之外的值插入布尔列。请考虑以下示例：

INSERT INTO tasks(title,completed)
VALUES('Test Boolean with a number',2);

查看结果：

+----+----------------------------+-----------+
| id | title                      | completed |
+----+----------------------------+-----------+
|  1 | Master MySQL Boolean type  |         1 |
|  2 | Design database table      |         0 |
|  3 | Test Boolean with a number |         2 |
+----+----------------------------+-----------+
3 rows in set (0.00 sec)

如果要将结果输出为true和false，则可以使用以下IF函数：

SELECT 
    id, 
    title, 
    IF(completed, 'true', 'false') completed
FROM
    tasks;

+----+----------------------------+-----------+
| id | title                      | completed |
+----+----------------------------+-----------+
|  1 | Master MySQL Boolean type  | true      |
|  2 | Design database table      | false     |
|  3 | Test Boolean with a number | true      |
+----+----------------------------+-----------+
3 rows in set (0.00 sec)

MySQL BOOLEAN 运算符

要获取tasks表中的所有已完成任务，您可能会提出以下查询：

SELECT 
    id, title, completed
FROM
    tasks
WHERE
    completed = TRUE;

+----+---------------------------+-----------+
| id | title                     | completed |
+----+---------------------------+-----------+
|  1 | Master MySQL Boolean type |         1 |
+----+---------------------------+-----------+
1 row in set (0.00 sec)

如您所见，它只返回completed值为1 的任务。要修复它，您必须使用IS运算符：

SELECT 
    id, title, completed
FROM
    tasks
WHERE
    completed IS TRUE;

+----+----------------------------+-----------+
| id | title                      | completed |
+----+----------------------------+-----------+
|  1 | Master MySQL Boolean type  |         1 |
|  3 | Test Boolean with a number |         2 |
+----+----------------------------+-----------+
2 rows in set (0.00 sec)

在此示例中，我们使用IS运算符针对布尔值测试值。 要获取待处理的任务，请使用IS FALSE或IS NOT TRUE如下：

SELECT 
    id, title, completed
FROM
    tasks
WHERE
    completed IS NOT TRUE;

+----+-----------------------+-----------+
| id | title                 | completed |
+----+-----------------------+-----------+
|  2 | Design database table |         0 |
+----+-----------------------+-----------+
1 row in set (0.00 sec)

在本教程中，您学习了如何使用MySQL BOOLEAN数据类型，这是同义词TINYINT(1)，以及如何操作布尔值。

---