何时在 MySQL 表中使用 NULL

Question

我很欣赏数据库表中 NULL 值的语义，它不同于 false 和空字符串 ''。 然而，我经常读到有关字段可为空时的性能问题的信息，并建议在 NULL 实际上语义正确的情况下使用空字符串。

什么情况适合使用可为空字段和 NULL 值？ 有哪些权衡？ 完全避免使用 NULL 并简单地使用空字符串、 false 或 0 来指示值不存在是否明智？

更新

好的 - 我理解 '' 和 NULL 之间的语义差异以及 NULL 是适当字段值的（与性能无关的）情况。 不过，让我详细阐述一下暗示的性能问题。 这是来自 Schwartz、Zeitsev 等人的优秀《高性能 MySQL》 http://www. borders.co.uk/book/high-performance-mysql-optimization-backups-replication-and-more/857673/：

MySQL 更难优化 引用可为空的列的查询， 因为他们制作索引，索引 统计和价值比较更多 复杂的。 可空列使用 更多的存储空间和要求 MySQL内部的特殊处理。 什么时候 可为空列建立索引，它 每个条目需要一个额外的字节并且 甚至会导致固定大小的索引 （例如单个整数的索引 列）转换为 MyISAM 中的可变大小。

更多这里： Google 图书预览

这很可能是最终的答案 - 我只是在寻找来自一线的第二意见和经验。

Answer 1

但是，我经常读到
当字段是时的性能问题
可为 null 并被建议使用
如果 NULL 为空字符串
实际上在语义上是正确的。

我将对单词的选择挑剔一会儿：

• 即使它是一个重要的性能因素，这并不意味着使用值而不是 NULL 在语义上是正确的。 在 SQL 中，NULL 具有语义作用，表示缺失或不适用的值。 给定 RDBMS 实现中 NULL 的性能特征与此无关。 性能可能因品牌或版本不同而有所不同，但 NULL 在语言中的用途是一致的。

无论如何，我还没有听说过任何证据表明 NULL 表现不佳。 我对任何显示可空列的性能比不可空列差的性能测量的参考感兴趣。

我并不是说我没有错，或者在某些情况下这不可能是真的——只是说做出无聊的假设是没有意义的。 科学不是由猜想组成的，而是由猜想组成的。 必须通过可重复的测量来提供证据。

指标还可以告诉您性能差异有多大，以便您可以判断是否值得担心。 也就是说，影响可能是可衡量的且非零，但与更大的性能因素（例如正确索引表或调整数据库缓存大小）相比仍然微不足道。

在 MySQL 中，搜索 NULL 可以从索引中受益：

mysql> CREATE TABLE foo (
  i INT NOT NULL,
  j INT DEFAULT NULL,
  PRIMARY KEY (i),
  UNIQUE KEY j_index (j)
);

mysql> INSERT INTO foo (i, j) VALUES 
  (1, 1), (2, 2), (3, NULL), (4, NULL), (5, 5);

mysql> EXPLAIN SELECT * FROM foo WHERE i = 3;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

mysql> EXPLAIN SELECT * FROM foo WHERE j IS NULL;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | foo   | ref  | j_index       | j_index | 5       | const |    2 | Using where | 
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

请注意，这仍然不是性能的衡量标准。 我仅表明您可以在搜索 NULL 时使用索引。 我要断言（诚然没有测量，但这只是 StackOverflow）索引的好处掩盖了搜索 NULL 与空白字符串时任何可能的惩罚。

选择零或空白或任何其他值来代替 NULL 不是正确的设计决策。 您可能需要在列中使用这些重要值。 这就是 NULL 存在的原因，根据定义，它作为任何数据类型值域之外的值，因此您可以使用整数或字符串或其他任何值的完整范围，并且仍然有一些东西可以表示“以上值都不是”。 ”

Answer 2

MySQL手册实际上有一篇关于问题的好文章NULL。

希望有帮助。

还发现了另一篇关于 NULL 和性能的帖子

Answer 3

我们不允许数据库中存在 NULL 值，除非它用于数值或日期。 我们这样做的原因是因为数值有时不应该默认为零，因为这是非常非常糟糕的。 我是一名股票经纪人的开发人员，NULL 和 0 之间有很大的区别。 如果我们确实想要将默认值恢复为零，即使我们不这样存储它们，那么 COALESCE 的使用会很方便。

MyVal = COALESCE(TheData, 0)

当我们从平面文件中批量插入数据时，我们使用格式文件来确定数据的条目，无论如何，数据都会自动将空值转换为空白字符串。

日期默认为任何可能出现的值，具体取决于我认为的排序规则，但我们的默认值为 1900 之类的值，同样，日期非常重要。 其他纯文本值并不那么重要，如果留空通常也可以。

Answer 4

通常，如果某个属性是必需的，则将其定义为 Not NULL，如果可以省略，则将其定义为可为空。

Answer 5

正如@ForYourOwnGood 所说 - Null 应该用于“未知”信息。 例如：如果您有很多字段需要客户在注册时填写，其中一些字段是可选的。 由于某种原因，您可能想为该特定客户保留一个 ID，并且由于您不知道可选字段是否是客户真正选择留空的，因此您应该在第一次保存时将它们设置为 NULL，即“未知”行。 如果客户提交表单，通过所有验证，然后保存信息，那么您实际上就知道可选字段有意留空。

这只是使用 NULL 的一个很好的例子。

Answer 6

不应使用空字符串来代替 NULL。 NULL 代表什么都没有，因为空字符串代表什么，里面什么也没有。 与另一个值（甚至 NULL）相比，NULL 始终为 false，并且 NULL 不会在 COUNT 功能。

如果您需要表示未知信息，则 NULL 是无可替代的。

Answer 7

NULL 列的含义或多或少是“不适用于此上下文”。 我通常在两种情况下使用 NULL 列：

• 如果该字段不适用（假设您有一个布尔列 is_thirsty 并添加两个数据集。一个人和一块石头。对于人类，您将 is_thirsty 设置为 true 或false，而对于石头，您可能会将其设置为 NULL，
• 如果我需要标记某些内容并使用该值存储一些数据，例如库存截止日期，您可以使用它来 a) 指定该值。库存不能再更改，并且 b) 指定库存关闭时间。 我只是创建了 Closed_at 列，如果库存集仍可以更改，则将其设置为 NULL，但设置一次日期，而不是使用两列（lated_at 和 is_close）它已经关闭了。

基本上，它可以归结为这样一个事实：当字段的空性与空字段具有不同的唯一语义时，我使用 NULL。 中间名首字母的缺失就是这样。 没有截止日期意味着库存集仍可能发生变化。

NULL 值可能会产生令人讨厌的副作用，它们会使您向表中添加数据变得更加困难，并且通常情况下，您最终可能会得到 NULL 值和空字符串的混合体。

另外，NULL 不等于任何东西，如果你不小心的话，查询就会乱七八糟。

就我个人而言，我仅在上述两种情况之一适用时才使用 NULL 列。 当空除了没有值之外没有任何意义时，我从不使用它来表示空字段。

Answer 8

我知道有时 MySQL 的 NULL 语义是完全合适的。

也就是说，它们确实造成了严重的阻碍，尤其是在文本字段方面。

这是一个现实世界的例子。

我们希望将数据从 FileMaker 数据库复制到 mysql 表中。

如果我们执行“SELECT * from table where textfield <> 'test'”，则不会返回文本字段为 NULL 的行。 这很可能不是您所期望或想要的。

如果在 where 查询中单独或作为 AND 的一部分使用可为空的字段，则永远不会返回 NULL 条目，除非使用 IS NULL 测试。 我们必须做类似“where ((textfield<>"test") OR (textfield IS NOT NULL))”的事情，这充其量是丑陋的。

因此在这种情况下，我们可能不希望该字段可为空。

这里的问题是您无法使用 Filemaker 将空字符串插入 MySQL。 它会转换为 NULL，如果您将列设置为非空，则会出错！ 如果您确实允许 NULL，那么到 mysql 的传输可以工作，但是您的非查询无法按照您想要的方式运行！

解决方法是更改​​表两次，将导入后现有的空值转换为空字符串，然后将表更改回再次允许空值。 你呀！

该死的文件制作者。

Answer 9

当然，主要的好处是您提到的 NULL 的语义。

除此之外，它可能取决于您的存储引擎，一如既往，请检查文档，但至少在某些数据库中，NULL 占用的空间比常规值少得多。 例如，如果您有一个声明为 20 个字符的“varchar”列，并且很少填充它，则可以通过将其设置为 NULL 而不是空字符串来节省大量磁盘空间。

我从未听说过使用 NULL 会带来任何性能问题，恰恰相反。 我听说有人因为对 NULL 的计数错误而搞砸了计数，但从来没有听说过性能。 如果这是真的，我很想听听！

Answer 10

如今，任何有自尊心的数据库引擎都不应该因正确使用 NULL 而受到任何惩罚，除非您的查询设计不正确（这通常不是您在 NULL 方面经常遇到的问题）。

您首先应该注意按预期使用数据库（包括 NULL）； 然后担心优化后果何时发生以及如果发生的话。

不正确的 NULL 列值对 SQL 复杂性和准确性的累积影响几乎肯定会超过欺骗母 DBMS 的好处。 此外，它会扰乱你的头脑，以及后来试图弄清楚你想要做什么的人的头脑。

Answer 11

在某些数据库（例如 Oracle）上，MySQL 上的某些想法可能是正确的：

• 空值没有索引，那么如果查找空值可能会成为瓶颈。
• 行上的尾随空值可以节省空间。