MySQL 整数 0 与 NULL

MySQL Integer 0 vs NULL

提问by monkHyman

When using integer columns is it better to have 0or NULLto indicate no value.

当使用整数列时，最好有0或NULL没有值。

For example, if a table had a parent_idfield and a particular entry had no parent, would you use 0or NULL?

例如，如果一个表有一个parent_id字段，而一个特定的条目没有父项，你会使用0或NULL吗？

I have in the past always used 0, because I come from a Java world were (prior to 1.5) integers always had to have a value.

我过去总是使用0，因为我来自 Java 世界（在 1.5 之前）整数总是必须有一个值。

I am asking mainly in relation to performance, I am not too worried about which is the "more correct" option.

我问的主要是关于性能，我不太担心哪个是“更正确”的选项。

回答by Hosam Aly

Using NULLis preferable, for two reasons:

使用NULL更可取，原因有二：

1. NULLis used to mean that the field has no value, which is exactly what you're trying to model.
2. If you decide to add some referential integrity constraints in the future, you will have to use NULL.

1. NULL用于表示该字段没有值，这正是您要建模的内容。
2. 如果您决定将来添加一些参照完整性约束，则必须使用NULL.

回答by uldis.zarins

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

如果可能，将列声明为 NOT NULL。通过更好地使用索引并消除测试每个值是否为 NULL 的开销，它使 SQL 操作更快。您还可以节省一些存储空间，每列一位。如果您的表中确实需要 NULL 值，请使用它们。只需避免在每列中允许 NULL 值的默认设置。

MySQL - optimizing data size

MySQL - 优化数据大小

回答by jwenting

using NULL for "no value" is literally correct. 0 is a value for an integer, therefore it has meaning. NULL otoh literally means there is nothing, so there is no value.

将 NULL 用于“无值”是字面上正确的。0 是一个整数值，因此它是有意义的。NULL otoh 字面意思是什么都没有，所以没有价值。

Performance would likely be irrelevant, but using NULL may well be faster somewhat if you learn to code using NULL correctly.

性能可能无关紧要，但如果您学会正确使用 NULL 进行编码，则使用 NULL 可能会更快一些。

回答by Brian

You shouldn't expect to see any real life performance difference from this

您不应该期望从中看到任何现实生活中的性能差异

回答by kapa

In your parent_id example 0 is perfectly valid, because it stands for 'root'. In most cases, NULL is a better choice for 'no value' logically.

在您的 parent_id 示例中，0 是完全有效的，因为它代表“root”。在大多数情况下，NULL 是逻辑上“无值”的更好选择。

It has no performance impact that I know of, though.

不过，据我所知，它没有性能影响。

回答by Sachin Shanbhag

0is still a valid value for an integer column. Hence you have to use NULLand allow null on that column.

0仍然是整数列的有效值。因此，您必须NULL在该列上使用并允许 null。

Also if you are using integercolumn for only positive numbers, then you can use -1for novalue.

此外，如果您仅将integer列用于正数，那么您可以将其-1用于no值。

In your example of parent_idreference to use 0, it is fine until you make sure that there are no reference ids starting with id 0.

在您parent_id使用的参考示例中，在您0确保没有以 id 开头的参考 ID 之前，一切都很好0。

回答by sveva

UNIQUE( id1, id2 )won't work with null values because it would allow, for example 1, nulltwice

UNIQUE( id1, id2 )不会使用空值，因为它会允许，例如1, null两次

on the other hand if you use 0, JOIN atable ON this.extID = atable.IDthe join will be executed (resulting in no rows joined) whereas NULL will just be ignored

另一方面，如果您使用 0，JOIN atable ON this.extID = atable.ID连接将被执行（导致没有行连接）而 NULL 将被忽略

Anyway I suggest to always use "empty values" (like 0 or empty string) instead of NULL unless the empty value has a different meaning from NULL

无论如何，我建议始终使用“空值”（如 0 或空字符串）而不是 NULL，除非空值与 NULL 的含义不同

and I also modify queries like so: JOIN atable ON this.extID = atable.id AND extID > 0which prevents to execute the useless join

我也像这样修改查询：JOIN atable ON this.extID = atable.id AND extID > 0这可以防止执行无用的连接

回答by chrony

I think 0 can be used instead of NULL if you don't actually expect 0 to be used as a value.

如果您实际上不希望将 0 用作值，我认为可以使用 0 代替 NULL。

That is for example your column is a foreign key. Since foreign keys don't normally start with 0 but instead start with 1, it means you wouldn't expect the 0 to be used as a value.

例如，您的列是外键。由于外键通常不以 0 开头，而是以 1 开头，这意味着您不会期望将 0 用作值。

You can then use the 0 to denote the 'No' value state. Using it in joins would not match any columns on the other table. Thus, having the same effect as NULL.

然后，您可以使用 0 来表示“无”值状态。在连接中使用它不会匹配另一个表上的任何列。因此，具有与 NULL 相同的效果。

But if you have a column where the 0 actually has a meaning. Like for example a quantity field. And apart from that, you also need to express and empty value. For example, to denote that the quantity hasn't been inputted yet. Then you need a NULL for that.

但是，如果您有一列 0 实际上具有含义。例如数量字段。除此之外，您还需要表达和清空价值。例如，表示尚未输入数量。那么你需要一个NULL。

Hope that makes sense.

希望这是有道理的。