何时在MySQL表中使用NULL [英] When to use NULL in MySQL tables

问题描述

我欣赏数据库表中NULL值的语义含义，该值不同于false和空字符串".但是，我经常读到字段为可空值时的性能问题，并建议在语义上实际上为NULL的情况下使用空字符串.

I appreciate the semantic meaning of a NULL value in a database table, different from both false and the empty string ''. However, I have often read about performance problems when fields are nullable and been advised to use an empty string in cases where NULL is actually semantically correct.

什么情况下适合使用可为空的字段和NULL值?权衡是什么?仅避免完全不使用NULL并仅使用空字符串，false或0表示缺少值是否明智?

What circumstances are appropriate to use nullable fields and NULL values? What are the trade-offs? Is it sensible to simply avoid using NULLs altogether and simply use empty strings, false or 0 to indicate the absence of a value?

更新

好-我了解''和NULL之间的语义差异，以及(NULL)是适当字段值的(性能未知)情况.但是，让我扩展暗示的性能问题.这来自Schwartz，Zeitsev等人的出色的高性能MySQL" http://www. borders.co.uk/book/high-performance-mysql-optimization-backups-replication-and-more/857673/:

OK - I understand the semantic difference between '' and NULL as well as the (performance-agnostic) circumstances in which NULL is the appropriate field value. However, let me expand on the hinted performance issue. This is from the excellent "High Performance MySQL" by Schwartz, Zeitsev et al http://www.borders.co.uk/book/high-performance-mysql-optimization-backups-replication-and-more/857673/:

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

It's harder for MySQL to optimize queries that refer to nullable coumns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size inded (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.

更多信息: 这很可能是肯定的答案-我只是从前线寻求第二意见和经验.

This is quite possibly the definitive answer - I was just looking for second opinions and experience from the front-line.

推荐答案

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

However, I have often read about performance problems when fields are nullable and been advised to use an empty string in cases where NULL is actually semantically correct.

我暂时会选择单词:

• 即使这是一个重要的性能指标，也不能正确地使用值代替NULL.在SQL中，NULL具有语义作用，表示缺少或不适用的值.在给定的RDBMS实现中，NULL的性能特征与此无关.性能可能因品牌而异，也可能因版本而异，但是NULL在语言中的用途是一致的.

在任何情况下，我都没有听说过NULL表现不佳的任何证据.我对性能测量的任何参考都会感兴趣，这些参考显示可空列的性能比不可空列差.

In any case, I have not heard of any evidence that NULL performs poorly. I'd be interested in any references to performance measurements that show nullable columns perform worse than non-nullable columns.

我并不是说我没错，或者在某些情况下这不是真的-只是说些无聊的假设没有意义.科学不是由猜想构成的；必须通过重复测量来证明证据.

I'm not saying I'm not wrong or that it can't be true in some cases -- just that it's not meaningful to make idle suppositions. Science is not made up of conjecture; one has to show evidence with repeatable measurements.

指标还会通过多少告诉您性能，因此您可以判断是否值得担心.也就是说，影响可能是可衡量的且非零，但与更高的性能因素(例如正确索引表或调整数据库缓存大小)相比仍然微不足道.

Metrics also tell you by how much the performance differs, so you can make a judgment about whether it's something to worth worrying about. That is, the impact could be measurable and nonzero, but still insignificant compared to greater performance factors, such as properly indexing tables or sizing your database cache.

在MySQL中，搜索NULL可以受益于索引:

In MySQL, searches for NULL can benefit from an index:

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而不是空白字符串时的任何代价都蒙上阴影.

Note that's still not a measurement of performance. I've only shown that you can use an index while searching for NULL. I'm going to assert (admittedly without having measured, but hey this is just StackOverflow) that the benefit of an index overshadows any possible penalty when searching for NULL versus a blank string.

选择零或空白或任何其他值来代替NULL并不是正确的设计决定.您可能需要在列中使用那些重要的值.这就是为什么存在NULL的原因，因为它的定义超出了任何数据类型的值的范围，因此您可以使用整数或字符串之类的所有值的范围，并且仍然可以表示上述值中没有一个". "

It's not a correct design decision to choose zero or blank or any other value to substitute for NULL. You may need to use those values as significant in the column. That's why NULL exists, as a value that is by definition outside the domain of values of any data type, so you can use the full range of values of integers or strings or whatever and still have something to signify "none of the above values."

这篇关于何时在MySQL表中使用NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！