这看起来很简单,我发誓我以前做过,但由于某种原因,它不适合我。
我使用MAMP
,有一个大约200列的表,我希望其中大约20个默认为0,如果NULL或空数据插入其中。
下面是一个小例子,展示了我的表是什么样子的,以及我对我希望默认为0的列做了什么。
CREATE TABLE `listings` (
`ListingID` int(11) NOT NULL,
`BathsFull` int(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`ListingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
所以请注意,在BathsFull
上,我把它设置为NOT NULL DEFAULT '0'
,问题是,当空数据传递给它时,我得到SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'BathsFull' cannot be null
的SQL错误。
我也尝试过让BathsFull accepts
NULL and
DEFAULT '0',但是当传递空数据时,表显示NULL
而不是0
。
我错过了什么吗?我需要写点什么触发器吗?如果没有必要,我不想在将脚本中的数据放入DB之前对其进行清理。
如果您在插入中显式地将值设置为NULL,但希望MySQL将NULL替换为0,一种方法是在CREATE TABLE
语句中定义允许NULL的列,然后将NULL替换为TRIGGER
。
像这样:
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 ;
可以使用触发器
假设您使字段为空
CREATE TABLE `listings` (
`ListingID` int(11) NOT NULL,
`BathsFull` int(6), <-----
PRIMARY KEY (`ListingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
触发DELIMITER $$
CREATE TRIGGER tg_lst_insert BEFORE INSERT ON listings
FOR EACH ROW
BEGIN
SET NEW.BathsFull = IFNULL(NEW.BathsFull, 0);
END $$
DELIMITER ;
插入一些行
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);
结果+-----------+-----------+
| ListingID | BathsFull |
+-----------+-----------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 3 |
+-----------+-----------+
你忘了张贴INSERT查询,但我相信问题是存在的。如下图所示:
insert into listings (ListingID) values(1)
…因为您省略了BathsFull
,所以MySQL使用默认值。这并不:
insert into listings (ListingID, BathsFull) values(1, null);
…因为你在告诉MySQL你想要 NULL
.
您可以使用default
关键字在插入时获取列的默认值。
的例子:
INSERT INTO listings(ListingID, BathsFull) VALUES (2,DEFAULT);
也许你应该试试这个:
insert into listings (ListingID, ListingID, BathsFull)
values (@listing_id, @ListingID, isnull(BathsFull, 0)