sql 当为空值时置0_MYSQL：如何在插入过程中使NULL或空数据默认为0

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.

解决方案

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 |

+-----------+-----------+