Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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

You probably want to use the BIT datatype.

share|improve this answer
    
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 '12 at 8:59
    
@yukti: Not sure that I understand what your comment is asking. –  ypercube May 24 '12 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 at 18:24

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

}
share|improve this answer

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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