MySQL boolean型別

2019-10-16 22:58:30

本教學將向您展示如何使用MySQL BOOLEAN資料型別來儲存布林值:truefalse

MySQL BOOLEAN資料型別簡介

MySQL沒有內建的布林型別。 但是它使用TINYINT(1)。 為了更方便,MySQL提供BOOLEANBOOL作為TINYINT(1)的同義詞。

在MySQL中,0被認為是false,非零值被認為是true。 要使用布林文字,可以使用常數TRUEFALSE來分別計算為10。 請參閱以下範例:

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

MySQL BOOLEAN範例

MySQL將布林值作為整數儲存在表中。為了演示,讓我們來看下面的tasts表:

USE testdb;

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

上面建立表語句中,即使將completed列指定為BOOLEAN型別,當顯示表定義時,它是卻是TINYINT(1)型別,如下所示:

DESCRIBE tasks;

以下語句向tasts表中插入2行資料:

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

在將資料儲存到布林列之前,MySQL將其轉換為10,以下查詢從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

如上所見, truefalse 分別被轉換為10

因為Boolean型別是TINYINT(1)的同義詞,所以可以在布林列中插入10以外的值。如下範例:

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

上面語句,工作正常~,查詢tasts表中的資料,如下所示 -

mysql> SELECT 
    id, title, completed
FROM
    tasks; 
+----+----------------------------+-----------+
| 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

如果要將結果輸出為truefalse,可以使用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

MySQL BOOLEAN運算子

要在tasts表中獲取所有完成的任務,可以執行以下查詢:

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

執行上面查詢語句,得到結果如下所示 -

+----+---------------------------+-----------+
| id | title                     | completed |
+----+---------------------------+-----------+
|  1 | Master MySQL Boolean type |         1 |
+----+---------------------------+-----------+
1 row in set

如您所見,它只返回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

在這個例子中,我們使用IS運算子來測試一個與布林值的值。

要獲得待處理(未完成)的任務,請使用IS FALSEIS 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

在本教學中,您已經學習了如何使用MySQL BOOLEAN資料型別(它是TINYINT(1)的同義詞),以及如何操作布林值。