MySQL Boolean Data Type

最新推荐文章于 2025-04-15 15:18:53 发布

转载最新推荐文章于 2025-04-15 15:18:53 发布 · 772 阅读

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文探讨了在PHPMyAdmin中定义BOOL数据类型的细节及其在MySQL中的实际表现形式为TINYINT(1)。文章通过示例展示了如何正确使用TRUE和FALSE值，并解释了这些值与其他整数在验证过程中的区别。

Written by maclochlainn

February 26th, 2010 at 2:32 am

Somebody posted a quick question about the outcome of defining a table with a bool data type in PHPMyAdmin. They were subsequently surprised when they checked the MySQL database and found that it was actually a tinyint(1). The natural question they had was: “What do you enter – true/false or 1/0?”

I promised to post an answer tonight, and morning counts too. You can enter a true or false because they’re synonyms for a 1 or 0 respectively. TINYINT is the supported data type, and BIT, BOOL, and BOOLEAN are synonyms for the base data type.

Here’s an example in MySQL:

mysql> CREATE TABLE data_type (TYPE bool);
 
mysql> DESCRIBE data_type;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | Extra |
+-------+------------+------+-----+---------+-------+
| TYPE  | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
 
mysql> INSERT INTO data_type VALUES (TRUE),(FALSE);
 
mysql> SELECT * FROM data_type;
+------+
| TYPE |
+------+
|    1 |
|    0 |
+------+

The comment below raises the question of what happens with values in the valid range of TINYINT that aren’t0 or 1, like 5. The simple answer is they’re not valid when compared against the true and false constants, as you can see by creating the following example.

-- Create a test table.
CREATE TABLE verify
( verify_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, range_1    tinyint UNSIGNED
, range_2    tinyint );
 
-- Insert test values.
INSERT INTO verify
VALUES
 (NULL, 0, 0)
,(NULL, 1, 1)
,(NULL, 1,-1)
,(NULL, 2, 2);
 
-- Query results.
SELECT range_1 AS "Value"
,      CASE
         WHEN range_1 = TRUE THEN 'True'
         WHEN range_1 = FALSE THEN 'False'
         ELSE 'Invalid'
       END AS "Unsigned"
,      range_2 AS "Value"
,      CASE
         WHEN range_2 = TRUE  THEN 'True'
         WHEN range_2 = FALSE THEN 'False'
         ELSE 'Invalid'
       END AS "Signed"
FROM   verify;

The results of the test demonstrate that only a 0 or 1 value validates against the false or true constants, as shown:

+-------+----------+-------+---------+
| Value | Unsigned | Value | Signed  |
+-------+----------+-------+---------+
|     0 | False    |     0 | False   |
|     1 | True     |     1 | True    |
|     1 | True     |    -1 | Invalid |
|     2 | Invalid  |     2 | Invalid |
+-------+----------+-------+---------+