Due to a weird request, I can't put null in a database if there is no value. I'm wondering what can I put in the store procedure for nothing instead of null.
由于一个奇怪的请求，如果没有值，我就不能在数据库中输入null。我在想，我可以在存储过程中什么都不做，而不是空。
For example:
例如:
insert into blah (blah1) values (null)
Is there something like nothing or empty for "blah1" instead using null?
对于blah1，是否有什么东西是空的而不是空的?
6 个解决方案
#1
5
I would push back on this bizarre request. That's exactly what NULL is for in SQL, to denote a missing or inapplicable value in a column.
我会拒绝这个奇怪的要求。这正是SQL中的NULL，用于表示列中缺失或不适用的值。
Is the requester experiencing grief over SQL logic with NULL?
请求者是否因SQL逻辑为空而感到痛苦?
edit: Okay, I've read your reply with the extra detail about this job assignment (btw, generally you should edit your original question instead of posting more information in an answer).
编辑:好的，我已经阅读了你关于这份工作的额外细节的回复。
You'll have to declare all columns as NOT NULL and designate a special value in the domain of that column's data type to signify "no value." The appropriate value to choose might be different on a case by case basis, i.e. zero may signify nothing in a person_age column, but it might have significance in an items_in_stock column.
您必须声明所有列为NOT NULL，并在该列的数据类型的域中指定一个特殊值，以表示“没有值”。根据具体情况选择适当的值可能会有所不同，例如，在person_age列中，0可能没有任何意义，但在items_in_stock列中，它可能具有重要意义。
You should document the no-value value for each column. But I suppose they don't believe in documentation either. :-(
您应该为每个列记录无值的值。但我想他们也不相信文档。:-(
#2
1
Depends on the data type of the column. For numbers (integers, etc) it could be zero (0) but if varchar then it can be an empty string ("").
取决于列的数据类型。对于数字(整数，等等)，它可以是0(0)，但是如果varchar，它可以是一个空字符串(“”)。
I agree with other responses that NULL is best suited for this because it transcends all data types denoting the absence of a value. Therefore, zero and empty string might serve as a workaround/hack but they are fundamentally still actual values themselves that might have business domain meaning other than "not a value".
我同意其他响应，NULL是最适合的，因为它超越了所有数据类型，表示没有值。因此，0和空字符串可能作为一种解决方案/hack，但它们本质上仍然是具有业务域含义的实际值，而不是“值”。
(If only the SQL language supported a "Not Applicable" (N/A) value type that would serve as an alternative to NULL...)
(如果SQL语言支持“不适用的”(N/ a)值类型，可以作为空的替代…)
#3
0
Is null is a valid value for whatever you're storing?
null对于存储的内容是有效的吗?
- Use a sentry value like INT32.MaxValue, empty string, or "XXXXXXXXXX" and assume it will never be a legitimate value
- 使用像INT32这样的哨兵值。MaxValue、空字符串或“XXXXXXXXXX”，并假定它永远不会是一个合法的值。
- Add a bit column 'Exists' that you populate with true at the same time you insert.
- 添加一个位列“Exists”，并在插入时填充true。
Edit: But yeah, I'll agree with the other answers that trying to change the requirements might be better than trying to solve the problem.
编辑:但是，我同意其他的答案，尝试改变需求可能比尝试解决问题要好。
#4
0
If you're using a varchar or equivalent field, then use the empty string.
If you're using a numeric field such as int then you'll have to force the user to enter data, else come up with a value that means NULL.
I don't envy you your situation.
如果使用varchar或等效字段，则使用空字符串。如果您使用数字字段(比如int)，那么您必须强制用户输入数据，否则将产生一个值，该值表示NULL。我并不羡慕你的处境。
#5
0
There's a difference between NULLs as assigned values (e.g. inserted into a column), and NULLs as a SQL artifact (as for a field in a missing record for an OUTER JOIN. Which might be a foreign concept to these users. Lots of people use Access, or any database, just to maintain single-table lists.) I wouldn't be surprised if naive users would prefer to use an alternative for assignments; and though repugnant, it should work ok. Just let them use whatever they want.
空值作为分配值(例如插入到列中)和空值作为SQL工件(如外部连接丢失记录中的字段)之间存在差异。对于这些用户来说，这可能是一个陌生的概念。许多人使用Access或任何数据库来维护单表列表。如果天真的用户更喜欢在作业中使用其他选项，我不会感到惊讶;尽管令人反感，但它应该可以正常工作。让他们想用什么就用什么。
#6
0
There is some validity to the requirement to not use NULL values. NULL values can cause a lot of headache when they are in a field that will be included in a JOIN or a WHERE clause or in a field that will be aggregated.
要求不使用空值是有一定有效性的。当NULL值位于将包含在JOIN或WHERE子句中的字段或将被聚合的字段中时，会引起很多麻烦。
Some SQL implementations (such as MSSQL) disallow NULLable fields to be included in indexes.
一些SQL实现(如MSSQL)不允许将可空字段包含在索引中。
MSSQL especially behaves in unexpected ways when NULL is evaluated for equality. Does a NULL value in a PaymentDue field mean the same as zero when we search for records that are up to date? What if we have names in a table and somebody has no middle name. It is conceivable that either an empty string or a NULL could be stored, but how do we then get a comprehensive list of people that have no middle name?
MSSQL在为相等值计算NULL时表现得尤其出人意料。PaymentDue字段中的空值是否与我们搜索最新记录时的零值相同?如果我们在表中有名字，而某人没有中间名呢?可以想象一个空字符串或一个空字符串都可以被存储，但是我们如何才能得到一个没有中间名的人的全面列表呢?
In general I prefer to avoid NULL values. If you cannot represent what you want to store using either a number (including zero) or a string (including the empty string as mentioned before) then you should probably look closer into what you are trying to store. Perhaps you are trying to communicate more than one piece of data in a single field.
一般来说，我希望避免空值。如果不能使用数字(包括0)或字符串(包括前面提到的空字符串)表示要存储的内容，那么您应该更仔细地研究要存储的内容。也许您正在尝试在单个字段中传递多个数据。
#1
5
I would push back on this bizarre request. That's exactly what NULL is for in SQL, to denote a missing or inapplicable value in a column.
我会拒绝这个奇怪的要求。这正是SQL中的NULL，用于表示列中缺失或不适用的值。
Is the requester experiencing grief over SQL logic with NULL?
请求者是否因SQL逻辑为空而感到痛苦?
edit: Okay, I've read your reply with the extra detail about this job assignment (btw, generally you should edit your original question instead of posting more information in an answer).
编辑:好的，我已经阅读了你关于这份工作的额外细节的回复。
You'll have to declare all columns as NOT NULL and designate a special value in the domain of that column's data type to signify "no value." The appropriate value to choose might be different on a case by case basis, i.e. zero may signify nothing in a person_age column, but it might have significance in an items_in_stock column.
您必须声明所有列为NOT NULL，并在该列的数据类型的域中指定一个特殊值，以表示“没有值”。根据具体情况选择适当的值可能会有所不同，例如，在person_age列中，0可能没有任何意义，但在items_in_stock列中，它可能具有重要意义。
You should document the no-value value for each column. But I suppose they don't believe in documentation either. :-(
您应该为每个列记录无值的值。但我想他们也不相信文档。:-(
#2
1
Depends on the data type of the column. For numbers (integers, etc) it could be zero (0) but if varchar then it can be an empty string ("").
取决于列的数据类型。对于数字(整数，等等)，它可以是0(0)，但是如果varchar，它可以是一个空字符串(“”)。
I agree with other responses that NULL is best suited for this because it transcends all data types denoting the absence of a value. Therefore, zero and empty string might serve as a workaround/hack but they are fundamentally still actual values themselves that might have business domain meaning other than "not a value".
我同意其他响应，NULL是最适合的，因为它超越了所有数据类型，表示没有值。因此，0和空字符串可能作为一种解决方案/hack，但它们本质上仍然是具有业务域含义的实际值，而不是“值”。
(If only the SQL language supported a "Not Applicable" (N/A) value type that would serve as an alternative to NULL...)
(如果SQL语言支持“不适用的”(N/ a)值类型，可以作为空的替代…)
#3
0
Is null is a valid value for whatever you're storing?
null对于存储的内容是有效的吗?
- Use a sentry value like INT32.MaxValue, empty string, or "XXXXXXXXXX" and assume it will never be a legitimate value
- 使用像INT32这样的哨兵值。MaxValue、空字符串或“XXXXXXXXXX”，并假定它永远不会是一个合法的值。
- Add a bit column 'Exists' that you populate with true at the same time you insert.
- 添加一个位列“Exists”，并在插入时填充true。
Edit: But yeah, I'll agree with the other answers that trying to change the requirements might be better than trying to solve the problem.
编辑:但是，我同意其他的答案，尝试改变需求可能比尝试解决问题要好。
#4
0
If you're using a varchar or equivalent field, then use the empty string.
If you're using a numeric field such as int then you'll have to force the user to enter data, else come up with a value that means NULL.
I don't envy you your situation.
如果使用varchar或等效字段，则使用空字符串。如果您使用数字字段(比如int)，那么您必须强制用户输入数据，否则将产生一个值，该值表示NULL。我并不羡慕你的处境。
#5
0
There's a difference between NULLs as assigned values (e.g. inserted into a column), and NULLs as a SQL artifact (as for a field in a missing record for an OUTER JOIN. Which might be a foreign concept to these users. Lots of people use Access, or any database, just to maintain single-table lists.) I wouldn't be surprised if naive users would prefer to use an alternative for assignments; and though repugnant, it should work ok. Just let them use whatever they want.
空值作为分配值(例如插入到列中)和空值作为SQL工件(如外部连接丢失记录中的字段)之间存在差异。对于这些用户来说，这可能是一个陌生的概念。许多人使用Access或任何数据库来维护单表列表。如果天真的用户更喜欢在作业中使用其他选项，我不会感到惊讶;尽管令人反感，但它应该可以正常工作。让他们想用什么就用什么。
#6
0
There is some validity to the requirement to not use NULL values. NULL values can cause a lot of headache when they are in a field that will be included in a JOIN or a WHERE clause or in a field that will be aggregated.
要求不使用空值是有一定有效性的。当NULL值位于将包含在JOIN或WHERE子句中的字段或将被聚合的字段中时，会引起很多麻烦。
Some SQL implementations (such as MSSQL) disallow NULLable fields to be included in indexes.
一些SQL实现(如MSSQL)不允许将可空字段包含在索引中。
MSSQL especially behaves in unexpected ways when NULL is evaluated for equality. Does a NULL value in a PaymentDue field mean the same as zero when we search for records that are up to date? What if we have names in a table and somebody has no middle name. It is conceivable that either an empty string or a NULL could be stored, but how do we then get a comprehensive list of people that have no middle name?
MSSQL在为相等值计算NULL时表现得尤其出人意料。PaymentDue字段中的空值是否与我们搜索最新记录时的零值相同?如果我们在表中有名字，而某人没有中间名呢?可以想象一个空字符串或一个空字符串都可以被存储，但是我们如何才能得到一个没有中间名的人的全面列表呢?
In general I prefer to avoid NULL values. If you cannot represent what you want to store using either a number (including zero) or a string (including the empty string as mentioned before) then you should probably look closer into what you are trying to store. Perhaps you are trying to communicate more than one piece of data in a single field.
一般来说，我希望避免空值。如果不能使用数字(包括0)或字符串(包括前面提到的空字符串)表示要存储的内容，那么您应该更仔细地研究要存储的内容。也许您正在尝试在单个字段中传递多个数据。