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:
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 |
+-------+----------+-------+---------+