创建数据库表的NULL最佳实践

Question

创建数据库表的NULL最佳实践

sqldatabasedatabase-designnull

11

当我有一个单一的表，其中两个字段仅在某些情况下才有值，从而在行中创建了许多NULL值时，不确定处理NULL值的最佳实践是什么。

是否应将这两个字段移动到单独的表中，从而创建两个没有NULL值的表？

跨越这两个表的连接将返回一个与具有NULL的原始表相同的结果，那么这有什么意义呢？

似乎将它们分开是毫无意义的，但我已经阅读了一些关于完全避免使用空值的数据库的文章。

- baileyswalk

你会对这两个字段运行查询吗？ - Eric K Yung

可能的答案也在这里：http://dba.stackexchange.com/a/5227/14987 - Marco Demaio

4个回答

4

空值会导致查询结果不正确和不一致，并且由于需要特殊处理，通常会增加代码复杂度。出于这些原因，在数据库设计中避免或最小化空值通常是有意义的。您在查询中也不需要使用空值 - 尽管 SQL 不幸地使它们很难避免。但是，通过不在基本表中使用空值，您将确保数据模型更准确地反映现实，并为数据库用户提供更多对如何使用空值的控制。

- nvogel

1

如果您混合使用哨兵值和NULL，则NULL会导致查询结果不正确和不一致。我更喜欢干净的null而不是空字符串或NULL，尤其是在使用SQL Server null位图时。 - gbn

1

@DVK，Null不是一个值。与常规值不同，SQL处理null的方式通常在现实世界中没有太多意义。结果的有效性取决于null的预期含义。在实践中，它们有许多不同和矛盾的含义。例如，您建议使用null表示“未知值”，但是SQL实际上并不支持这种做法。在数学、现实和常识中，如果x是未知的，则x=x将评估为TRUE，但是如果x为null，则在SQL中不会如此。因此，SQL不能准确地将null视为表示“未知值”。 - nvogel

1

@gbn - 我有点假设没有理智的人会混合使用哨兵值和空值。 - DVK

2

@dportas - 好的，现在我明白你的意思了。NULL值不会导致查询结果不正确和不一致。NULL值会导致人们编写糟糕的查询语句，其结果非常一致，但与他们的期望结果不同，因为他们没有阅读基本文档。 - DVK

1

@DVK：“未知”意味着未知，而不是每次都相同的某个特殊值。因此，实际上 SQL 对使用 NULL 作为“未知”的支持非常完美。如果这是真的，那么在包含 null 的列上进行 GROUP BY 将导致每个 null 处于自己独特的组中。然而，在 SQL 中，nulls 组合在一起，即在这种情况下，null 确实被视为每次都相同的特殊值。 - onedaywhen

显示剩余5条评论

3

如dportas在评论中所示，了解特定字段中null值的含义很有帮助 - 不是理论上的含义，而是在您的数据中的含义。

我认为只要您清楚地知道表格中null的含义，并且如果您确信它只表示一种含义，那么您可以就是否允许它做出明智的决策。

意见：我的经验法则是可为空的字段是可以接受的，但不应多用。

- user533832

Keith Hare是SQL标准委员会的领导，他曾说过：“在SQL:1999 ANSI和ISO标准的开发早期，有一个用户定义的NULL类型的概念。这个想法是允许最多128种不同类型的NULL。然后需要一种机制来指定哪种NULL类型，并比较两种NULL类型以查看它们是否是相同类型的NULL。从数据库设计的角度来看，这个概念非常强大，但在标准中规定非常复杂。没有任何迹象表明任何供应商有可能实现这个概念，所以最终被淘汰了。” - onedaywhen

@onedaywhen 嘿嘿，我比你先发了（http://dba.stackexchange.com/questions/5222/why-shouldnt-we-allow-nulls/5223#5223） - user533832

@gbn 不要轻视“遗忘理论”。理论是关系型数据库管理系统的支柱。 - user3308043

2

Null值在数据库中非常重要。我从未遇到过不允许null值的数据库，最终这些数据库查询起来很困难，维护也很困难（你如何确定哪个值表示我不知道答案），通常会有更多的坏数据。是的，null值需要在查询中进行特殊处理，但是像将一个较晚的日期（1/1/9999）添加为结束日期以避免出现null值等操作也需要特殊处理。

事实上，有些数据在记录插入时就无法知道，没有任何替代品可以取代null值。

现在，在您的情况下，是否应该拆分为两个表取决于表的宽度和您需要查询可空列的频率。即使有很多null值，我也不太可能将middlename列移动到另一个表中，因为它总是与基本表中的其他信息一起查询。我也不太可能移动结束日期列。但是如果这些列是一些很好了解但通常不会在查询基本数据时查询的内容（例如生日、头发颜色等），那么仅包含包含数据的记录的单独表可能是可以接受的。但请记住，当您进行查询时，如果使用内连接，您将消除第二个表中没有值的所有记录。如果我通常想要所有记录（例如中间名，我很少查询只查找具有“Mary”中间名的人），那么我倾向于将它们保留在同一个表中，除非该表变得非常宽且我通常不想查询该信息。

- HLGEM

可以争论空值的有用性，但说它们是“关键”的或者说没有任何替代品是过分了。数据库只是关于世界事实的集合。科学、数学和逻辑在 SQL 和空值出现之前就已经准确地描述了世界数个世纪。即使在 SQL 中，很多人设计的数据库完全可以不使用空值而正常工作。 - nvogel

是的，他们可以在没有数据库设计的情况下进行开发，但我从未见过一个能够良好运作的例子。如果你需要一个数值类型的变量，在之后才会被赋值，而且0对该字段有特殊含义，你会使用什么呢？开发者如何知道使用哪个虚假值或者过去使用了什么值呢？ - HLGEM

@HLGEM - 请看我的回答中的第1点。你评论所提到的是关系逻辑中实际100％预期使用NULL的“未知值”；因此，它绝对很难摆脱 - 魔术般的“无效值”特殊值非常糟糕。随着时间的推移，NULL作为“无值”的使用是可选的。 - DVK

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- DVK · Accepted Answer

从理论上讲，NULL应该表示“未知值”。因此-再次强调，从纯理论角度出发-在规范化时应设计表格，以便您无需填写NULL值来表示“不适用于此行”。然而，这一点与任何实际考虑（设计、性能或查询可读性）几乎没有关系。
从实际角度出发，有一些性能方面的考虑。以下情况下，您应将非常稀疏的数据规范化：
- 缩短表格具有实质性的好处（无论是IO还是空间）。NULL会占据空间，并且列越宽，性能越差。当表格具有大量行且存在许多这种稀疏列时，特别是如此。对于只有2个这样的列的较小表格，实现的好处可能不值得增加额外的连接麻烦。
- 您的查询在WHERE子句中涉及到相应的列。我IRC，查询一个具有大量NULL值的列效率相当低。
- 另一方面，在某一点上，查询中具有额外的连接可能会损害优化器的性能（至少在Sybase上，当您的连接具有10个以上的表格时会从占用优化器运行时的CPU资源到实际上困惑优化器选择一个非常不好的计划）。解决方案是避免由于规范化而拥有太多表格（例如，不要费力将2个列拆分成单独的表格），或者强制查询计划。后者显然是不好的做法。