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.

Solution 1

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 |
+-----------+-----------+

Solution 2

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

Solution 3

You forgot to post the INSERT queries but I'm sure the problem is there. This works as expected:

insert into listings (ListingID) values(1)

... because you omit BathsFull so MySQL uses the defalt. This doesn't:

insert into listings (ListingID, BathsFull) values(1, null);

... because your are telling MySQL that you want NULL.

Solution 4

You can use the default keyword to get the default value of the column during insertion.

Example :

INSERT INTO listings(ListingID, BathsFull) VALUES (2,DEFAULT);

Solution 5

Maybe you should try this:

insert into listings (ListingID, ListingID, BathsFull) 
values (@listing_id, @ListingID, isnull(BathsFull, 0)

View more solutions

Comments

[bigmike7801]

I'm using cakePHP for my insert scripts so it's not as easy as just changing what my insert statement looks like. That's why I want MySQL handle it.

[DessDess]

I'm not familiar with it so I can't really help maybe you can use Álvaro G. Vicario's solution

[bigmike7801]

I gave this a shot but am getting the following error Error: SQLSTATE[HY000]: General error: 1436 Thread stack overrun: 15168 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.

[bigmike7801]

Please see comment I put under Peterm's answer.

[peterm]

Increase the value of thread_stack parameter to 192(256)KB. See this SO question stackoverflow.com/a/5264965/1920232 and mysql doc dev.mysql.com/doc/refman/5.0/en/…