I want to create a table in MySQL with a boolean column whose default value is false. But it's accepting NULL as default...
Add a comment
|
4 Answers
You have to specify 0 (meaning false) or 1 (meaning true) as the default. Here is an example:
create table mytable (
mybool boolean not null default 0
);
FYI: boolean is an alias for tinyint(1).
Here is the proof:
mysql> create table mytable (
-> mybool boolean not null default 0
-> );
Query OK, 0 rows affected (0.35 sec)
mysql> insert into mytable () values ();
Query OK, 1 row affected (0.00 sec)
mysql> select * from mytable;
+--------+
| mybool |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
FYI: My test was done on the following version of MySQL:
mysql> select version();
+----------------+
| version() |
+----------------+
| 5.0.18-max-log |
+----------------+
1 row in set (0.00 sec)
5 Comments
tudor -Reinstate Monica-
Or does it? In shell scripting 0 can means successful or 'true'. It would be nice if MySQL actually returned 'true' and 'false' so that we didn't have to rely on the code deciding what the value means.
tudor -Reinstate Monica-
Incidentally, since boolean is an alias for tinyint(1), that means you can set booleans to numbers other than 0 and 1 and it won't complain! Which means if you accidentally increment or decrement the field then you can mess up your data! :-O I'd recommend using an ENUM field instead.
Matthew Read
"Successful" and "true" are completely orthogonal things. Error codes are not booleans.
Asaf M
I think a better proof that it is tinyint is running show create table mytable; I get this: CREATE TABLE
mytable ( mybool tinyint(1) NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Jivan Pal
@MatthewRead That may be, but Bash really does use 0 to mean "true". For those who are used to Bash-style boolean logic (which operates on exit statuses, and where 0 exit status is true and others are false) vs. C-style (where 0 is false and other values are true), this can be very confusing.
You can set a default value at creation time like:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
Married boolean DEFAULT false);
Comments
Use ENUM in MySQL for true / false it gives and accepts the true / false values without any extra code.
ALTER TABLE `itemcategory` ADD `aaa` ENUM('false', 'true') NOT NULL DEFAULT 'false'
Comments
If you are making the boolean column as not null then the default 'default' value is false; you don't have to explicitly specify it.
