MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Boolean Data Type

with 11 comments

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’t 0 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 |
+-------+----------+-------+---------+

Based on the comment, the 256 value range of TINYINT is found here.

Written by maclochlainn

February 26th, 2010 at 2:32 am

Posted in MAMP,MySQL,PHPMyAdmin,sql

«
»