MYSQL: How to make NULL or empty data default to 0 during insert

Question

So this seems like it would be pretty straight forward and I swear I've done this before, but for some reason it's just not working for me.

I am using MAMP and have a table with about 200 columns and I want about 20 of them to default to 0 if NULL or empty data is inserted into it.

Here's a small example of what my table looks like as well as what I have done for columns that I want to default to 0.

CREATE TABLE `listings` (
  `ListingID` int(11) NOT NULL,
  `BathsFull` int(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ListingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So notice on BathsFull I have it set to NOT NULL DEFAULT '0' the problem is that when empty data is passed to it I get a SQL error of SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'BathsFull' cannot be null.

I've also tried so that BathsFull acceptsNULLandDEFAULT '0', however when empty data is passed, the table shows NULL instead of 0.

Am I missing something here? Do I need to write some sort of trigger? I don't want to scrub the data in my script before putting it into the DB if I don't have to.

Answer 1

If you are explicitly setting the value to NULL in your insert, but want MySQL to replace the NULL with 0, one way to do that is to define the column to allow NULL in the CREATE TABLE statement, and then replace the NULL with a TRIGGER.

Something like this:

CREATE TABLE `listings` (
  `ListingID` int(11) NOT NULL,
  `BathsFull` int(6) NULL DEFAULT 0,
  PRIMARY KEY (`ListingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

delimiter $$

create trigger tr_b_ins_listings before insert on listings for each row
begin
  set new.BathsFull = coalesce(new.BathsFull,0);
end $$

delimiter ;

Try it for yourself in this SQL Fiddle

Answer 2

You can definitely use a trigger for that

Assuming that you make the field nullable

CREATE TABLE `listings` (
  `ListingID` int(11) NOT NULL,
  `BathsFull` int(6),              <-----
  PRIMARY KEY (`ListingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Trigger

DELIMITER $$
CREATE TRIGGER tg_lst_insert BEFORE INSERT ON listings
FOR EACH ROW
BEGIN
    SET NEW.BathsFull = IFNULL(NEW.BathsFull, 0);
END $$
DELIMITER ; 

Inserting some rows

INSERT INTO `listings` VALUES(1, '');
INSERT INTO `listings` VALUES(3, 'a');
INSERT INTO `listings` VALUES(4, NULL);
INSERT INTO `listings` (ListingID) VALUES(2);
INSERT INTO `listings` VALUES(5, 3);

Result

+-----------+-----------+
| ListingID | BathsFull |
+-----------+-----------+
|         1 |         0 |
|         2 |         0 |
|         3 |         0 |
|         4 |         0 |
|         5 |         3 |
+-----------+-----------+