someone123 someone123 - 2 months ago 19
MySQL Question

MySQL table: BIT(1) not null default b'1', however, always default to '0'

This is how I created the table:

CREATE TABLE `item_spa_cust` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`spa_id` INT(10) NULL DEFAULT NULL,
`type` VARCHAR(20) NOT NULL COLLATE 'latin1_swedish_ci',
`is_valid` BIT(1) NOT NULL DEFAULT b'1',
`company_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`custno` VARCHAR(6) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
PRIMARY KEY (`id`),
INDEX `FK_item_spa_cust_item_spa` (`spa_id`),
CONSTRAINT `FK_item_spa_cust_item_spa` FOREIGN KEY (`spa_id`) REFERENCES `item_spa` (`id`))
;


I was expecting the
is_valid
field to default to '1' as I set it, however, it's always defaulting to '0'.

I'm very confused about this, please help.

Answer

You should probably use 1, rather than b'1'

That being said, in this sqlfiddle, it works as expected.

If you're inserting and looking to default the value, you should NOT SPECIFY it in your insert query.