数据库，使用 null 作为三元选项的不好做法？
我以前从未真正使用过 null，我倾向于避免使用它并将其存在于我的数据中。不过，我最近设计了这个表（简化的）：
tblPeopleWhoNeedToCastVotes
User | hasVotedYes
在这种情况下，hasVotedYes 可以为 null、true 或 false。 Null 表示他们尚未投票（有用信息）。
这是不好的做法还是好的做法？
I've never used null before really, I tend to avoid using it and having it existing in my data. However I recently designed this table (simplified):
tblPeopleWhoNeedToCastVotes
User | hasVotedYes
In this scenario, hasVotedYes can either be null, true or false. Null indicates they have not yet cast their vote (useful information).
Is this bad practise or fine?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这确实是 NULL 的用途——数据不可用。来自维基百科：
我不建议将它用作一般的“第三个选项”，我会使用tinyint并将其映射到代码中的枚举。然而，对于“是/否”和“未知”，我认为 NULL 可能是最好的方法，因为在数据库中读取 0/1/2 会不太清楚。
This is really what NULL is intended for - where the data is not available. From Wikipedia:
I wouldn't recommend using it as a general "third option", I'd go with a tinyint and map it to an Enum in code instead. However, for Yes/No and an Unknown, I'd say a NULL is probably the best way, as reading 0/1/2 in the database would be less clear.
我宁愿使用 DEFAULT 值而不是 NULL。因此，在像 MySQL 这样的数据库中，我将创建一个列
，当用户投票“反对”时，我将更改为 1，如果用户投票“赞成”，我会将其标记为 2。但是，默认为 NULL NOT 这是一个不好的做法，除非您在应用程序代码中处理 NULL 对象。
再想一想，我想默认值是更好的主意。例如，您想要过滤投票赞成、投票反对或未投票的用户 - 您将创建一个准备好的语句，类似于
While 在 NULL 默认值的情况下，您将编写两种类型的查询。
这适用于投票赞成或反对的情况。
这是未投票的情况。
I would rather prefer a DEFAULT value instead of NULL. So, In database like MySQL I will create a column
when user votes "against" I will change to 1, if user votes "in favor", I will mark it as 2. However, NULL as default is NOT a bad practice, till you handle NULL object in your application code.
Thinking a bit more, I guess default values are even better idea. For example you want to filter users who voted in favor, voted against, or not voted -- you will create a prepared statement something like
While in case of NULL default, you will be writing two types of queries.
This works for voted in favor or against case.
This for not voted case.
我会选择
“投票可以为空”位：1 = 是，0 = 否，Null = 未投票。
否则（不使用 null 时）您需要知道：
A：此人投票了吗
B：他投票了什么。
如果不使用 NULL，B 将默认为 0。当想要创建所有投票赞成 (1) 或反对 (0) 的人的查询时，这可能会很烦人，然后您必须检查该人是否投票。
使用 NULL 时可以简单查询 1 或 0。
I would go for
Where votes is nullable bit: 1 = Yes, 0 = No, Null = not voted.
Otherwise (when not using null) you need to know:
A: Has the person voted
B: What did he vote.
By not using NULL B would be defaulted to 0. This could be annoying when wanting to create a query of all people who voted Yes (1) or No (0), then you must check if the person has voted.
When using NULL you can simple query for 1 or 0.
Null 是为此用途而构建的。它（应该）的意思是：不放心，不知道。
唯一的缺点是，当您想知道没有投票 Yes 的人数时：您可能必须将 null 转换为某些内容，否则结果将不正确。
Null has been built for this usage. It (should) means : not shure, don't know.
The only drawback is when you want to know, say, the number of people who didn't vote Yes : you may have to convert null to something or the result will not be correct.
我使用 NULL 表示 UNKNOWN，但如果您要对数据执行大量搜索子句以确保不会犯错误，您还应该考虑三个值逻辑，请参见此图：
I use NULL for UNKNOWN but you should also take three value logic into consideration if your going to be doing a lot of search clauses on the data to make sure you don't make a mistake, see this image:
Null 可用于各种用途，但通常会导致矛盾和不正确的结果。
在这种情况下，您的困境似乎源于试图将太多信息过载到单个二进制属性中 - 您试图记录一个人是否投票以及他们如何投票。这本身可能会给您带来问题。
我真的不明白你希望通过使用 null 来代表一个人没有投票的情况获得什么。为什么不在他们投票之前存储信息呢？这样，没有行就清楚地表明一个人还没有投票。
Null gets used for all sorts of things but usually it leads to contradictions and incorrect results.
In this case it seems that your dilemma stems from trying to overload too much information into a single binary attribute - you are trying to record both whether a person voted and how they voted. This in itself is probably going to create problems for you.
I don't really see what you hope to gain by using a null to represent the case where a person hasn't voted. Why don't you just not store the information until they have voted. That way the absence of the row clearly indicates that a person hasn't voted yet.