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 accepts`NULL`and`DEFAULT '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 so…

Accepted Answer

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

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

Solution

Problem

Related problems