How to Insert BOOL Value to MySQL Database

Question

I am trying to insert values into a BOOL data type in MySQL (v 5.5.20) using the following script:

CREATE DATABASE DBTest;
USE DBTest;
DROP TABLE IF EXISTS first;
CREATE TABLE first (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY , name VARCHAR(30) ,sale  BOOL,); 
INSERT INTO first VALUES ("", "G22","TRUE");
INSERT INTO first VALUES ("", "G23","FALSE");

But the INSERT statement just inserts 0 (Zero) to the boolean column for both TRUE and FALSE options! Can you please let me know why this is happening?

Note that BOOL, BOOLEAN field types in MySQL are really just aliases for TINYINT(1). Many find it better to work with 0 and 1 as values for insert such that it is explicitly clear what you are trying to do in the code (i.e. not depending on casting behavior) — Mike Brant
– Mike Brant, Commented Nov 6, 2012 at 21:31

Michael Berkowski · Accepted Answer · 2012-11-06 21:44:32Z

73

TRUE and FALSE are keywords, and should not be quoted as strings:

INSERT INTO first VALUES (NULL, 'G22', TRUE);
INSERT INTO first VALUES (NULL, 'G23', FALSE);

By quoting them as strings, MySQL will then cast them to their integer equivalent (since booleans are really just a one-byte INT in MySQL), which translates into zero for any non-numeric string. Thus, you get 0 for both values in your table.

Non-numeric strings cast to zero:

mysql> SELECT CAST('TRUE' AS SIGNED), CAST('FALSE' AS SIGNED), CAST('12345' AS SIGNED);
+------------------------+-------------------------+-------------------------+
| CAST('TRUE' AS SIGNED) | CAST('FALSE' AS SIGNED) | CAST('12345' AS SIGNED) |
+------------------------+-------------------------+-------------------------+
|                      0 |                       0 |                   12345 |
+------------------------+-------------------------+-------------------------+

But the keywords return their corresponding `INT` representation:

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

Note also, that I have replaced your double-quotes with single quotes as are more standard SQL string enclosures. Finally, I have replaced your empty strings for id with NULL. The empty string may issue a warning.

edited Nov 6, 2012 at 21:44

answered Nov 6, 2012 at 21:25

Michael Berkowski

271k47 gold badges451 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1760110 Over a year ago

Hi Michael, Thanks for your hints but the table still displaying 0 even when I just did likecodeINSERT INTO first VALUES (NULL, 'G22', TRUE);code

Michael Berkowski Over a year ago

@user1760110 That seems unlikely. Here's a demonstration with 5.5.28 sqlfiddle.com/#!2/1fc2a/1

Collectives™ on Stack Overflow

How to Insert BOOL Value to MySQL Database

1 Answer 1

Non-numeric strings cast to zero:

But the keywords return their corresponding `INT` representation:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Non-numeric strings cast to zero:

But the keywords return their corresponding INT representation:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

But the keywords return their corresponding `INT` representation: