MySQL Integer 0 vs NULL
Solution 1
Using NULL
is preferable, for two reasons:
-
NULL
is used to mean that the field has no value, which is exactly what you're trying to model. - If you decide to add some referential integrity constraints in the future, you will have to use
NULL
.
Solution 2
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.
Solution 3
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.
Performance would likely be irrelevant, but using NULL may well be faster somewhat if you learn to code using NULL correctly.
Solution 4
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.
It has no performance impact that I know of, though.
Solution 5
You shouldn't expect to see any real life performance difference from this
sksamuel
Building systems with lots of love, drippings of chocolate, lashings of Java and sprinkles of Scala.
Updated on February 20, 2020Comments
-
sksamuel about 4 years
When using integer columns is it better to have
0
orNULL
to indicate no value.For example, if a table had a
parent_id
field and a particular entry had no parent, would you use0
orNULL
?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.I am asking mainly in relation to performance, I am not too worried about which is the "more correct" option.