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

«
»

11 Responses to 'MySQL Boolean Data Type'

Subscribe to comments with RSS or TrackBack to 'MySQL Boolean Data Type'.

  1. TINYINT(1) isn’t a synonym for bit(1).

    TINYINT(1) can store -9 to 9.
    TINYINT(1) UNSIGNED: 0-9
    BIT(1): 0, 1. (Bit, literally).

    Justin Rovang

    25 Aug 11 at 4:24 pm

  2. Excuse me, seems I’ve fallen victim to substr-ism:

    TINYINT(1): -128-+127
    TINYINT(1) UNSIGNED: 0-255

    Justin Rovang

    25 Aug 11 at 4:32 pm

  3. True enough, but the post was about what PHPMyAdmin listed as a Boolean, and there it only uses 0 or 1 from the entire wide range of 256 possibilities. ;-)

    maclochlainn

    25 Aug 11 at 11:35 pm

  4. What status of BOOL will return TINYINT value 5 for example?
    If we presume that TRUE is 1 and FALSE is 0, value 5 will be not in range.

    I know that BOOL value cannot be stored in 1 bit. Actually I don’t even know for any system which operates with lower than 8-bit words.

    interesting

    26 Aug 11 at 8:17 am

  5. Actually, although using 1 is prefered in bool, it’s actually 0 for false and non-zero for true – so 5 would be same as 1, beeing both non-zero

    Luís Rodrigues

    22 Feb 12 at 9:26 am

  6. of course as stated, the variables true and false will only be evaluated against the prefered values of 0 for false and 1 for true. If for some reason you store different values, you won’t be able to use those variables to evaluate the result – unless you only compare false – so if it’s 0 it’s false else it’s true

    Luís Rodrigues

    22 Feb 12 at 9:32 am

  7. Luis, Excellent points about programming in general but in MySQL TRUE is a constant with a value of 1 and FALSE is a constant with a value of 0. Try the test case provided in the blog post.

    maclochlainn

    22 Feb 12 at 9:18 pm

  8. [...] check this older post on how MySQL manages logical constants and the realities of TRUE and FALSE constants [...]

    How to stripe views

    28 May 12 at 9:38 am

  9. thanks for the explaination

    gigi

    26 Sep 12 at 9:37 pm

  10. Hi i want to add the true false button to mysql table , i have 10 quiz and i add this code to my sql as a column , how can i add the radio button pn php?
    thanks

    Celine

    15 Apr 13 at 7:56 pm

  11. Typically, you define a column with a bool or tinyint data type, and you send the following from your PHP form:

    Selected radio button should send 1
    Unselected radio button should send 0

    Hope that helps.

    maclochlainn

    15 Apr 13 at 9:35 pm

Leave a Reply