将空字符串插入 SQL Server 的 INT 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13189688/
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
Insert empty string into INT column for SQL Server
提问by user1793297
A SAMPLEtable has only one column IDof type int, default null.
一个SAMPLE表只有一个ID类型的列int，默认为空。
In Oracle when I do:
在 Oracle 中，当我这样做时：
insert into SAMPLE (ID) values ('');
the new record is added with blank value. But in SQL Server 2008, when I run the same insertstatement, the new record has the value of 0.
新记录添加空白值。但是在 SQL Server 2008 中，当我运行相同的insert语句时，新记录的值为 0。
Is there a way to force SQL Server 2008 to default blank string to NULL instead of 0 (for numerical type of columns)?
有没有办法强制 SQL Server 2008 将空白字符串默认为 NULL 而不是 0（对于数字类型的列）？
回答by Mikael Eriksson
Use NULL instead.
请改用 NULL。
insert into SAMPLE (ID) values (NULL);
回答by Andriy M
Assuming that your INSERT statement is part of a stored procedure re-used in many places of your application (or, perhaps, is a batch always constructed by the same part of the client code) and that the inserted value is a number passed as a string argument, you could modify the INSERT like this:
假设您的 INSERT 语句是在您的应用程序的许多地方重复使用的存储过程的一部分（或者，可能是始终由客户端代码的同一部分构造的批处理）并且插入的值是作为传递的数字字符串参数，您可以像这样修改 INSERT：
INSERT INTO SAMPLE (ID) VALUES (NULLIF(@argument, ''));
回答by Bridge
How about another idea - define an INSTEAD OF INSERT Trigger.
另一个想法如何 - 定义一个INSTEAD OF INSERT 触发器。
Despite the fact that you're trying to insert a string, with this the operation is "intercepted", empty string is replaced by NULL, and the insert succeeds.
尽管您试图插入一个字符串，但操作被“拦截”，空字符串被 NULL 替换，插入成功。
If you define this trigger on your table, then you can continue to insert empty string as before, with no other changes.
如果你在你的表上定义了这个触发器，那么你可以像以前一样继续插入空字符串，没有其他变化。
Edit:As Martin Smith points out, this effectively is a comparison to 0 (the equivalent of empty string as an int) meaning you won't be able to store 0 in this table. I leave this answer here in case that's acceptable to your situation - either that or re-do all your queries!
编辑：正如 Martin Smith 所指出的，这实际上是与 0（相当于一个 int 的空字符串）的比较，这意味着您将无法在此表中存储 0。如果您的情况可以接受，我将这个答案留在这里 - 或者重新做您的所有查询！
CREATE TRIGGER EmptyStringTrigger
ON [SAMPLE]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [SAMPLE](ID)
SELECT CASE
WHEN ID = '' THEN NULL
ELSE ID
END
FROM inserted
END
回答by Jarod Elliott
You can't insert a 'string' into a int column. Oracle must be just handling that for you.
您不能在 int 列中插入“字符串”。Oracle 必须只是为您处理。
Just try inserting NULL if that's what you need.
如果您需要，请尝试插入 NULL。
insert into SAMPLE (ID) values (NULL);
回答by Aleksandr Fedorenko
One more option
多一个选择
insert into SAMPLE (ID) values (DEFAULT)
