MySQL NULL 或 NOT NULL 这是问题吗？

MySQL NULL or NOT NULL That is The Question?

提问by mii

What is the difference between NULL and NOT NULL? And when should they be used?

NULL 和 NOT NULL 有什么区别？什么时候应该使用它们？

回答by Sparky

NULL means you do not have to provide a value for the field...

NULL 意味着您不必为该字段提供值...

NOT NULL means you must provide a value for the fields.

NOT NULL 意味着您必须为字段提供一个值。

For example, if you are building a table of registered users for a system, you might want to make sure the user-id is always populated with a value (i.e. NOT NULL), but the optional spouses name field, can be left empty (NULL)

例如，如果您正在为系统构建一个注册用户表，您可能希望确保用户 ID 始终填充有一个值（即非空），但可选的配偶姓名字段可以留空（空值）

回答by MarkR

I would suggest

我会建议

• Use NOT NULL on every field if you can
• Use NULL if there is a sensible reason it can be null

• 如果可以，请在每个字段上使用 NOT NULL
• 如果有合理的理由它可以为空，则使用 NULL

Having fields which don't have a meaningful meaning for NULL nullable is likely to introduce bugs, when nulls enter them by accident. Using NOT NULL prevents this.

具有对 NULL 可空没有意义的字段可能会引入错误，当空值意外输入它们时。使用 NOT NULL 可以防止这种情况。

The commonest reason for NULL fields is that you have a foreign key field which is options, i.e. not always linked, for a "zero or one" relationship.

NULL 字段的最常见原因是您有一个外键字段，它是“零或一”关系的选项，即并不总是链接。

If you find you have a table with lots of columns many of which can be NULL, that starts sounding like an antipattern, consider whether vertical partitioning makes more sense in your application context :)

如果您发现您有一个包含许多列的表，其中许多列可能是 NULL，这听起来像是一种反模式，请考虑垂直分区在您的应用程序上下文中是否更有意义:)

There is another useful use for NULL - making all the columns in an index NULL will stop an index record being created for that row, which optimises indexes; you may want to index only a very small subset of rows (e.g. for an "active" flag set on only 1% or something) - making an index which starts with a column which is usually NULL saves space and optimises that index.

NULL 还有一个有用的用途 - 使索引中的所有列都为 NULL 将停止为该行创建索引记录，从而优化索引；您可能只想索引行的一个非常小的子集（例如，对于仅设置为 1% 或其他内容的“活动”标志） - 制作以通常为 NULL 的列开头的索引可以节省空间并优化该索引。

回答by OMG Ponies

What is the difference between NULL and NOT NULL?

NULL 和 NOT NULL 有什么区别？

When creating a table or adding a column to a table, you need to specify the column value optionality using either NULLor NOT NULL. NOT NULLmeans that the column can not have a NULLvalue for any record; NULLmeans NULLis an allowable value (even when the column has a foreign key constraint). Because NULLisn't a value, you can see why some call it optionality - because database table requires that in order to have a column, there must be an instance of the column for every record within the table.

创建表或向表中添加列时，您需要使用NULL或指定列值可选性NOT NULL。NOT NULL意味着该列不能有NULL任何记录的值；NULLmeanNULL是一个允许值（即使该列具有外键约束）。因为NULL不是一个值，所以你可以看到为什么有人称它为可选性——因为数据库表要求为了有一个列，表中的每条记录都必须有一个列的实例。

And when should they be used?

什么时候应该使用它们？

That is determined by your business rules.
Generally you want as many columns as possible to be NOT NULLbecause you want to be sure data is always there.

这是由您的业务规则决定的。
通常，您需要尽可能多的列，NOT NULL因为您希望确保数据始终存在。

回答by Amber

NOT NULLmeans that a column cannot have the NULLvalue in it - instead, if nothing is specified when inserting a row for that column, it will use whatever default is specified (or if no default is specified, whatever MySQL's default is for that type).

NOT NULL意味着列中不能包含NULL值 - 相反，如果在为该列插入行时未指定任何内容，它将使用指定的任何默认值（或者如果未指定默认值，则无论 MySQL 对该类型的默认值是什么）。

Fields that aren't NOT NULLcan potentially have their value as NULL(which essentially means a missing/unknown/unspecified value). NULLbehaves differently than normal values, see here for more info.

不是的字段NOT NULL可能具有它们的值NULL（这基本上意味着缺少/未知/未指定的值）。NULL行为与正常值不同，请参阅此处了解更多信息。

回答by Alex Martelli

NOT NULLis a column constraint and should be used when you have a column that's not in a primary key (primary keys are intrinsically not-null so it's silly to say NOT NULLexplicitly about them) of which you know the values will always be known (not unknown or missing) so there's no need for nulls in that column.

NOT NULL是一个列约束，当你有一个不在主键中的列时应该使用它（主键本质上不是空的，所以NOT NULL明确说明它们是愚蠢的）你知道它的值总是已知的（不是未知的或丢失），因此该列中不需要空值。

NULLis a keyword occurring in many contexts -- including as a column constraint, where it means the same as the default (i.e., nulls are allowed) -- but also in many other contexts, e.g. to insert a null in a column as part of an INSERT...VALUESstatement.

NULL是出现在许多上下文中的关键字——包括作为列约束，它的含义与默认值相同（即允许空值）——但也出现在许多其他上下文中，例如在列中插入空值作为一个INSERT...VALUES声明。

回答by Bryan Field

As others have answered, NOT NULLsimply means that NULLis not a permitted value. However, you will always have the option of empty string ''(for varchar) or 0(for int), etc.

正如其他人所回答的那样，NOT NULL仅仅意味着这NULL不是一个允许的值。但是，您始终可以选择空字符串''(for varchar) 或0(for int) 等。

One nice feature when using NOT NULLis that you can get an error or warning should you forget set the column's value during INSERT. (assuming the NOT NULLcolumn has no DEFAULT)

使用时的一个很好的功能NOT NULL是，如果您忘记在INSERT. （假设该NOT NULL列没有DEFAULT）

---

---

The main hiccup with allowing NULLcolumns is that they will never be found with the <>(not equal) operator. For example, with the following categorys

允许NULL列的主要问题是它们永远不会被<>（不等于）运算符找到。例如，使用以下categorys

Desktops
Mobiles
NULL -- probably embedded devices

The =operator works as expected

该=操作按预期工作

select * from myTable where category="Desktops";

However, the <>operator will exclude any NULLentries.

但是，<>运营商将排除任何NULL条目。

select * from myTable where category<>"Mobiles";
-- returns only desktops, embedded devices were not returned

This can be the cause of subtle bugs, especially if the column has no NULLdata during testing initial, but later some NULLvalues are added due to subsequent development. For this reason I set all the columns to NOT NULL.

这可能是导致细微错误的原因，特别是如果该列NULL在测试初始期间没有数据，但后来NULL由于后续开发而添加了一些值。出于这个原因，我将所有列设置为NOT NULL.

---

---

However, it can be helpful to allow NULLvalues when using a UNIQUEKEY/INDEX. Normally a unique key requires the column (or combination of columns) to be unique for the whole table. Unique keys are a great safeguard that the database will enforce for you.

但是，NULL在使用UNIQUEKEY/时允许值会很有帮助INDEX。通常，唯一键要求列（或列组合）在整个表中是唯一的。唯一键是数据库将为您强制执行的一个很好的保护措施。

In some cases, you may want the safeguard for most of the rows, but there are exceptions.

在某些情况下，您可能需要对大多数行进行保护，但也有例外。

If any column referenced by that particular UNIQUE KEYis NULL, then the uniqueness will no longer be enforced for that row. Obviously this would only work if you permit NULLs on that column, understanding the hiccup I explained above.

如果该特定引用的任何列UNIQUE KEY是NULL，则该行将不再强制执行唯一性。显然，这只有在您允许NULL该列上的 s时才有效，理解我上面解释的打嗝。

---

---

If you decide to allow NULLvalues, consider writing your <>statements with an additional condition to detect NULLs.

如果您决定允许NULL值，请考虑<>使用附加条件编写语句以检测NULLs。

select * from myTable where category<>"Desktops" or category is null;

回答by diemacht

Also note that NULL is not equal to anything else, even not to NULL itself.

还要注意 NULL 不等于其他任何东西，甚至不等于 NULL 本身。

For example:

例如：

mysql> select if(NULL=NULL, "null=null", "null!=null");
+------------------------------------------+
| if(NULL=NULL, "null=null", "null!=null") |
+------------------------------------------+
| null!=null                               |
+------------------------------------------+
1 row in set (0.00 sec)

This definition of NULL is very useful when you need a unique key on a column that is partially filled. In such case you can just leave all the empty values as NULL, and it will not cause any violation of the uniqueness key, since NULL != NULL.

当您需要部分填充的列上的唯一键时，NULL 的此定义非常有用。在这种情况下，您可以将所有空值保留为 NULL，并且不会导致对唯一性键的任何违反，因为 NULL != NULL。

Here is an example of how you can see if something is NULL:

以下是如何查看某些内容是否为 NULL 的示例：

mysql> select if(null is null, "null is null", "null is not null");
+------------------------------------------------------+
| if(null is null, "null is null", "null is not null") |
+------------------------------------------------------+
| null is null                                         |
+------------------------------------------------------+
1 row in set (0.01 sec)

回答by dkellner

If you're not sure, use NOT NULL.

如果您不确定，请使用 NOT NULL。

Despite the common belief, NOT NULL doesn't require you to fill all fields; it just means whatever you omit will have the default value. So no, it doesn't mean pain. Also, NULL is less efficient in terms of indexing, and causes many edge case situations when processing what you receive from a query.

尽管普遍认为，NOT NULL 并不要求您填写所有字段；它只是意味着您省略的任何内容都将具有默认值。所以不，这并不意味着痛苦。此外，NULL 在索引方面的效率较低，并且在处理您从查询中收到的内容时会导致许多边缘情况。

So, while of course NULL values have a theoretical meaning (and in rare cases you can benefit from this), most of the time NOT NULL is the way to go. NOT NULL makes your fields work like any variable: they always have a value, and you decide if that value means something or not. And yes, if you need all the possible valuesand one extra value that tells you there's simply nothing there, you can still use NULL.

因此，虽然 NULL 值当然具有理论意义（在极少数情况下您可以从中受益），但大多数情况下 NOT NULL 是可行的方法。NOT NULL 使您的字段像任何变量一样工作：它们总是有一个值，您可以决定该值是否有意义。是的，如果您需要所有可能的值和一个额外的值，告诉您那里什么都没有，您仍然可以使用 NULL。

So why do they love NULL so much?

那么他们为什么那么喜欢NULL呢？

Because it's descriptive. It has a semantic meaning, like "Nnah, wait, this is not just an empty string, this is a lot more exotic - it's the lack of information!" They will explain how it's different to say "time is 00:00" and "i don't knot what time it is". And this is valid; it just takes some extra effort to handle this. Because the system will allocate extra space for the information "is there a value at all" and it will constantly struggle checking it out. So for the tiny piece of semantic beauty, you sacrifice time and storage. Not much, but still.

因为它是描述性的。它具有语义意义，例如“Nnah，等等，这不仅仅是一个空字符串，这更奇特 - 这是缺乏信息！”他们将解释说“时间是 00:00”和“我不知道现在几点了”。这是有效的；处理这个问题需要一些额外的努力。因为系统会为“是否有任何价值”的信息分配额外的空间，并且它会不断地努力检查它。因此，对于微小的语义美，您牺牲了时间和存储空间。不多，但还是。

The whole phenomenon reminds me of the good old issetdebate where people are somehow obsessed with the beauty of looking at a nonexistent array index and getting an error message. This is completely pointless. Practical defaults are a blessing, they simplify the work in a "you know what I mean" style and you can write more readable, more concise, more expressive code that will make sense even after you spend 4 years with other projects.

整个现象让我想起了古老的isset辩论，人们不知何故沉迷于查看不存在的数组索引并收到错误消息的美妙之处。这是完全没有意义的。实用的默认值是一种祝福，它们以“你知道我的意思”的风格简化了工作，并且您可以编写更具可读性、更简洁、更具表现力的代码，即使在您花了 4 年的时间从​​事其他项目后，这些代码也会有意义。

Inevitable NULLs

不可避免的 NULL

If you have JOINs, you will encounter NULLs sooner or later, when you want to join another row but it's not there. This is the only valid case where you can't really live without NULLs; you must know it's not just an empty record you got, it's no record at all.

如果您有 JOIN，当您想加入另一行但它不存在时，迟早会遇到 NULL。这是唯一一个没有 NULL 就不能真正生活的有效情况；你必须知道这不仅仅是你得到的一个空记录，它根本就没有记录。

Otherwise? NOT NULL is convenient, efficient, and gives you fewer surprises. In return, it will be called ignorant and outrageous by some people with semantical-compulsive disorder. (Which is not a thing but should be.)

除此以外？NOT NULL 方便、高效，并且给您的惊喜更少。作为回报，它会被一些有语义强迫症的人称为无知和无耻。（这不是一件事，但应该是。）

You decide :)

你决定 ：）