[mysql] How to Insert BOOL Value to MySQL Database

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?

This question is related to mysql

The answer is


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.