MYSQL：如何在插入期间使 NULL 或空数据默认为 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15814466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL: How to make NULL or empty data default to 0 during insert
提问by bigmike7801
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 MAMPand 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.
我正在使用MAMP并且有一个包含大约 200 列的表，如果将 NULL 或空数据插入其中，我希望其中大约 20 列默认为 0。
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.
这是一个小示例，说明我的表的外观以及我为要默认为 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 BathsFullI 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.
所以请注意，BathsFull我将它设置为NOT NULL DEFAULT '0'问题是，当空数据传递给它时，我收到 .sql 错误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 NULLinstead of 0.
我也试过BathsFull acceptsNULL andDEFAULT '0'，但是当传递空数据时，表显示NULL而不是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.
我在这里错过了什么吗？我需要编写某种触发器吗？如果不需要，我不想在将脚本中的数据放入数据库之前清理它。
采纳答案by peterm
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 |
+-----------+-----------+
回答by Ike Walker
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 TABLEstatement, and then replace the NULL with a TRIGGER.
如果您在插入中显式地将值设置为 NULL，但希望 MySQL 将 NULL 替换为 0，那么一种方法是将列定义为在CREATE TABLE语句中允许 NULL ，然后将 NULL 替换为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
在这个SQL Fiddle 中亲自尝试一下
回答by DessDess
You can use the defaultkeyword to get the default value of the column during insertion.
default插入时可以使用关键字获取列的默认值。
Example :
例子 ：
INSERT INTO listings(ListingID, BathsFull) VALUES (2,DEFAULT);
回答by Doan Cuong
Maybe you should try this:
也许你应该试试这个：
insert into listings (ListingID, ListingID, BathsFull)
values (@listing_id, @ListingID, isnull(BathsFull, 0)
回答by álvaro González
You forgot to post the INSERT queries but I'm sure the problem is there. This works as expected:
您忘记发布 INSERT 查询，但我确定问题出在那里。这按预期工作：
insert into listings (ListingID) values(1)
... because you omit BathsFullso MySQL uses the defalt. This doesn't:
...因为你省略了BathsFull所以 MySQL 使用默认值。这不会：
insert into listings (ListingID, BathsFull) values(1, null);
... because your are telling MySQL that you wantNULL.
...因为你告诉 MySQL 你想要NULL.
