MySQL插入的时候将 NULL 或空数据默认为 0

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

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

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);

原文