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?

Answer 1

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.