5

I am trying to add a BOOLEAN column using EDIT TABLE in mysql workbench, but it automatically changes to TINYINT on applying changes.

How can I get the column to be BOOLEAN?

0

3 Answers 3

10

BOOL and BOOLEAN are synonyms of TINYINT in MySQL. See MySQL docs: Numeric Type Overview

You probably want to use the BIT datatype.

3
  • if i want to enter values "true" or "false" as we have in boolean, then how do we need to add boolean?
    – yukti
    May 15, 2012 at 8:59
  • 2
    @yukti: Not sure that I understand what your comment is asking. May 24, 2012 at 22:28
  • If I understood his comment, which is by no means a sure thing, he was asking if there was a data type to which he could assign true and false, instead of 0 and 1.
    – Xynariz
    Feb 24, 2014 at 18:24
5

MySQL doesn't support true booleans as per standard SQL. It uses integer values 1 and 0 respectively for true and false. It does support the keywords true and false, but these are mapped to the integer values.

mysql> SELECT true, false;
+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+

They really are mapped to integers:

mysql> SELECT true + 10;
+-----------+
| true + 10 |
+-----------+
|        11 |
+-----------+

This does mean that certain expressions are simpler in MySQL, such as counting the rows where a certain value is true is as easy as SUM() of a boolean expression, which will be 1 where the condition is true and 0 otherwise.

SELECT SUM(name = 'bill') FROM table;

Standard SQL doesn't support this use of booleans as integers, and would require the more verbose:

SELECT COUNT(CASE name WHEN 'bill' THEN 1 END) FROM table;

As other answers have already noted, you can use a data type alias BOOL but it is immediately replaced by TINYINT(1) (MySQL does something similar with other data types, like REAL and SERIAL).

2

In Mysql you can't have bool, because in Mysql bool is Tinyint, just that it's converted when you create the table.So you can "create" the bool and you can use it as a boolean, but it's a tinyint.

jcho360> create table bool_test (bool_field bool);
Query OK, 0 rows affected (0.04 sec)

jcho360> show create table bool_test \G
*************************** 1. row ***************************
       Table: bool_test
Create Table: CREATE TABLE `bool_test` (
  `bool_field` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

the bool_field I created as bool and mysql put it as tinyint(1).

if you want to set it as true or false, in the programming world 1 is true and 0 is false.

You can even make conditionals with 1 and 0, like

if (bool_field){

code here //and if bool_field is 1, it will be the same as bool_field=true

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.