MySQL：NULL 与“”

MySQL: NULL vs ""

提问by Ion Br.

Is it better to use default nullor default ""for text fields in MySQL?

在 MySQL 中使用default null或default ""文本字段更好吗？

Why?

为什么？

Update: I know what means each of them. I am interested what is better to use considering disk space and performance.

更新：我知道每个人的意思。考虑到磁盘空间和性能，我对什么更好用很感兴趣。

Update 2: Hey ppl! The question was "what is better to use" not "what each means" or "how to check them"...

更新 2：嘿，人们！问题是“什么更好用”而不是“每个是什么意思”或“如何检查它们”......

采纳答案by Paul Sonier

Use default null. In SQL, nullis very different from the empty string (""). The empty string specifically means that the value was set to be empty; nullmeans that the value was not set, or was set to null. Different meanings, you see.

使用默认null. 在 SQL 中，null与空字符串 ("") 有很大不同。空字符串具体表示该值被设置为空；null表示该值未设置，或设置为空。不同的意思，你看。

The different meanings and their different usages are why it's important to use each of them as appropriate; the amount of space potentially saved by using default nullas opposed to default ""is so small that it approaches negligibility; however, the potential value of using the proper defaults as convention dictates is quite high.

不同的含义和不同的用法是为什么适当使用它们很重要的原因；使用default null而不是可能节省的空间量default ""非常小，几乎可以忽略不计；然而，按照惯例使用适当的默认值的潜在价值是相当高的。

回答by James

For MyISAM tables, NULL creates an extra bit for each NULLABLE column (the null bit) for each row. If the column is not NULLABLE, the extra bit of information is never needed. However, that is padded out to 8 bit bytes so you always gain 1 + mod 8 bytes for the count of NULLABLE columns. 1

对于 MyISAM 表，NULL 为每一行的每个 NULLABLE 列（空位）创建一个额外的位。如果该列不是 NULLABLE，则永远不需要额外的信息位。但是，它被填充到 8 位字节，因此对于 NULLABLE 列的计数，您总是获得 1 + mod 8 字节。1

Text columns are a little different from other datatypes. First, for "" the table entry holds the two byte length of the string followed by the bytes of the string and is a variant length structure. In the case of NULL, there's no need for the length information but it's included anyways as part of the column structure.

文本列与其他数据类型略有不同。首先，对于“”，表条目保存字符串的两个字节长度，后跟字符串的字节，并且是一个变体长度结构。在 NULL 的情况下，不需要长度信息，但它无论如何都作为列结构的一部分包含在内。

In InnoDB, NULLS take no space: They simply don't exist in the data set. The same is true for the empty string as the data offsets don't exist either. The only difference is that the NULLs will have the NULL bit set while the empty strings won't. 2

在 InnoDB 中，NULL 不占用空间：它们根本不存在于数据集中。空字符串也是如此，因为数据偏移也不存在。唯一的区别是 NULL 将设置 NULL 位，而空字符串则不会。2

When the data is actually laid out on disk, NULL and '' take up EXACTLY THE SAME SPACE in both data types. However, when the value is searched, checking for NULL is slightly faster then checking for '' as you don't have to consider the data length in your calculations: you only check the null bit.

当数据实际放置在磁盘上时，NULL 和 '' 在两种数据类型中占用完全相同的空间。但是，在搜索值时，检查 NULL 比检查 '' 稍快，因为您不必在计算中考虑数据长度：您只检查空位。

As a result of the NULL and '' space differences, NULLand ''have NO SIZE IMPACT unless the column is specified to be NULLable or not. If the column is NOT NULL, only in MyISAM tables will you see any peformance difference (and then, obviously, default NULL can't be used so it's a moot question).

由于 NULL 和 '' 空间差异，NULL和''没有大小影响，除非该列被指定为 NULLable 或不。如果该列不是 NULL，则只有在 MyISAM 表中您才会看到任何性能差异（然后，显然，不能使用默认 NULL，因此这是一个没有实际意义的问题）。

The real question then boils down to the application interpretation of "no value set here" columns. If the "" is a valid value meaning "the user entered nothing here" or somesuch, then default NULL is preferable as you want to distinguish between NULL and "" when a record is entered that has no data in it.

然后，真正的问题归结为“此处未设置值”列的应用程序解释。如果“”是一个有效值，表示“用户在此处未输入任何内容”或类似内容，则默认 NULL 更可取，因为您希望在输入没有数据的记录时区分 NULL 和“”。

Generally though, default is really only useful for refactoring a database, when new values need to come into effect on old data. In that case, again, the choice depends upon how the application data is interpreted. For some old data, NULL is perfectly appropriate and the best fit (the column didn't exist before so it has NULL value now!). For others, "" is more appropriate (often when the queries use SELECT * and NULL causes crash problems).

但是，一般来说，默认值实际上仅对重构数据库有用，当新值需要对旧数据生效时。在这种情况下，选择取决于应用程序数据的解释方式。对于一些旧数据，NULL 是完全合适的并且是最合适的（该列以前不存在，所以它现在有 NULL 值！）。对于其他人，"" 更合适（通常当查询使用 SELECT * 和 NULL 导致崩溃问题时）。

In ULTRA-GENERAL TERMS (and from a philosophical standpoint) default NULL for NULLABLE columns is preferred as it gives the best semantic interpretation of "No Value Specified".

在 ULTRA-GENERAL TERMS 中（从哲学的角度来看），NULLABLE 列的默认 NULL 是首选，因为它给出了“未指定值”的最佳语义解释。

1[http://forge.mysql.com/wiki/MySQL_Internals_MyISAM]

1[ http://forge.mysql.com/wiki/MySQL_Internals_MyISAM]

2[http://forge.mysql.com/wiki/MySQL_Internals_InnoDB]

2[ http://forge.mysql.com/wiki/MySQL_Internals_InnoDB]

回答by Grygoriy Gonchar

From High Performance MySQL, 3rd Edition

来自高性能 MySQL，第 3 版

Avoid NULL if possible.A lot of tables include nullable columns even when the application does not need to store NULL (the absence of a value), merely because it's the default. It's usually best to specify columns as NOT NULL unless you intend to store NULL in them. It's harder for MySQL to optimize queries that refer to nullable columns, 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 index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM. The performance improvement from changing NULL columns to NOT NULL is usually small, so don't make it a priority to find and change them on an existing schema unless you know they are causing problems. However, if you're planning to index columns, avoid making them nullable if possible. There are exceptions, of course. For example, it's worth mentioning that InnoDB stores NULL with a single bit, so it can be pretty space-efficient for sparsely populated data. This doesn't apply to MyISAM, though.

如果可能，请避免 NULL。许多表包含可为空的列，即使应用程序不需要存储 NULL（没有值），仅仅因为它是默认值。通常最好将列指定为 NOT NULL，除非您打算在其中存储 NULL。MySQL 更难优化引用可空列的查询，因为它们使索引、索引统计和值比较变得更加复杂。可为空的列使用更多的存储空间并且需要在 MySQL 内部进行特殊处理。当一个可空列被索引时，它需要每个条目一个额外的字节，甚至可能导致固定大小的索引（例如单个整数列上的索引）在 MyISAM 中转换为可变大小的索引。将 NULL 列更改为 NOT NULL 的性能改进通常很小，所以不要 不要优先在现有模式上查找和更改它们，除非您知道它们会导致问题。但是，如果您计划为列建立索引，请尽可能避免将它们设为可空。当然也有例外。例如，值得一提的是，InnoDB 使用单个位存储 NULL，因此对于稀疏填充的数据，它可以非常节省空间。但是，这不适用于 MyISAM。

回答by degenerate

I found out that NULL vs "" is insignificant in terms of disk-space and performance.

我发现 NULL 与 "" 在磁盘空间和性能方面无关紧要。

The only true reason I can personally see in using NULL over '' is when you have a field marked as UNIQUEbut need the ability to allow multiple "empty" columns.

我个人认为在 '' 上使用 NULL 的唯一真正原因是当您有一个字段标记为UNIQUE但需要能够允许多个“空”列时。

For example, the emailcolumn in my user table is only filled in if someone actually has an email address. Anyone without an email address gets NULL. I can still make this field unique because NULL isn't counted as a value, whereas the empty string '' is.

例如，我的用户表中的电子邮件列仅在某人实际拥有电子邮件地址时才填写。没有电子邮件地址的任何人都会得到 NULL。我仍然可以使这个字段唯一，因为 NULL 不被视为一个值，而空字符串 '' 是。

回答by Eric

A lot of folks are answering the what is the difference between nulland '', but the OP has requested what takes up less space/is faster, so here's my stab at it:

很多人都在回答null和之间的区别''，但是 OP 要求占用更少的空间/更快，所以这是我的尝试：

The answer is that it depends. If your field is a char(10), it will always take 10 bytes if not set to null, and therefore, nullwill take up less space. Minute on a row-by-row basis, but over millions and millions of rows, this could add up. I believe even a varchar(10)will store one byte (\0) as an empty string, so again this could add up over huge tables.

答案是视情况而定。如果您的字段是 a char(10)，如果未设置为null，它将始终占用 10 个字节，因此null将占用更少的空间。逐行按分钟计算，但超过数百万行，这可能会加起来。我相信即使 avarchar(10)也会将一个字节 ( \0)存储为一个空字符串，因此这可能会加起来超过巨大的表。

In terms of performance in queries, nullis in theory quicker to test, but I haven't seen able to come up with any appreciable difference on a well indexed table. Keep in mind though, that you may have to convert nullto ''on the application side if this is the desired return. Again, row-by-row, the difference is minute, but it could potentially add up.

在查询性能方面，null理论上测试速度更快，但我没有看到能够在索引良好的表上提出任何明显的差异。但请记住，如果这是所需的回报，您可能必须在应用程序端转换null为''。同样，逐行，差异很小，但它可能会加起来。

All in all it's a micro-optimization, so it boils down to preference. My preference is to use nullbecause I like to know that there's no value there, and not guess if it's a blank string ('') or a bunch of spaces (' '). nullis explicit in its nature. ''is not. Therefore, I go with nullbecause I'm an explicit kind of guy.

总而言之，这是一个微观优化，因此归结为偏好。我的偏好是使用，null因为我想知道那里没有值，而不是猜测它是空白字符串 ( '') 还是一堆空格 ( ' ')。null其性质是明确的。''不是。因此，我选择了，null因为我是一个明确的人。

回答by Blixt

Use whatever makes sense. NULLmeans "no value available/specified", ""means "empty string."

使用任何有意义的东西。NULL表示“没有可用/指定的值”，""表示“空字符串”。

If you don't allow empty strings, but the user does not have to enter a value, then NULLmakes sense. If you require a value, but it can be empty, NOT NULLand a value of ""makes sense.

如果您不允许空字符串，但用户不必输入值，则NULL有意义。如果你需要一个值，但它可以是空的，NOT NULL一个值是""有意义的。

And, of course, if you don't require a value, but an empty value can be specified, then NULLmakes sense.

而且，当然，如果您不需要一个值，但可以指定一个空值，那么NULL就有意义了。

Looking at an efficiency point of view, an extra bit is used to determine whether the field is NULLor not, but don't bother about such micro-optimization until you have millions of rows.

从效率的角度来看，额外的位用于确定该字段是否NULL存在，但在您拥有数百万行之前不要担心这种微优化。

回答by Sampson

""is like an empty box... nullis like no box at all.

""就像一个空盒子……null就像根本没有盒子。

It's a difficult concept to grasp initially, but as the answers here plainly state - there is a big difference.

这是一个最初难以掌握的概念，但正如这里的答案明确指出的那样 - 有很大的不同。

回答by KahWee Teng

I prefer null when it is semantically correct. If there is an address field available and the user did not fill in, I give it a "". However if there in an address attribute to in the users table yet I did not offer the user a chance to fill it in, I give it a NULL.

当语义正确时，我更喜欢 null。如果有可用的地址字段而用户没有填写，我给它一个“”。但是，如果在用户表中有一个地址属性，但我没有为用户提供填写它的机会，我给它一个 NULL。

I doubt (but I can't verify) that NULL and "" makes much of a difference.

我怀疑（但我无法验证）NULL 和 "" 有很大不同。

回答by clemahieu

Use "". It requires less programming effort if you can assert that columns are non-null. Space difference between these is trivial.

用 ””。如果您可以断言列不为空，则需要较少的编程工作。这些之间的空间差异是微不足道的。

回答by davidcl

In general, NULL should indicate data that is not present or has not been supplied, and therefore is a better default value than the empty string.

通常，NULL 应指示不存在或尚未提供的数据，因此是比空字符串更好的默认值。

Sometimes the empty string is what you need as a data value, but it should almost never be a default value.

有时空字符串是您需要的数据值，但它几乎不应该是默认值。