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
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.
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 |
+------------------------+-------------------------+-------------------------+
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.
Source: Stackoverflow.com