MySQL Integer 0 vs NULL

Question

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

For example, if a table had a parent_id field and a particular entry had no parent, would you use 0 or 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.

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

Softfinned

If you're not worried which is "more correct" then just output a hard-coded zero from the report or app that calls the database; that will be much faster than using any database. – Malapropos

Yes, this is a slightly absurd question - "I do not care if my data is wrong, just that it is fast at being either right or wrong". The number zero is a different concept to an absence of a value which is why both null and zero are 2 distinct values possible for an integer, use the correct one. – Niblick

Answer 1

Using NULL is preferable, for two reasons:

1. NULL is 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.

Recount

Answer 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.

MySQL - optimizing data size

Married

+1. I'd still argue that "'no value' is possible for this column" is reason enough to use NULL. I'd want to benchmark with and without before going against common sense. This quote is just talking about columns where you really, actually are never going to use it in such a way, AFAICT. – Providential

Answer 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.

Tubulure

Answer 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.

Spectra

Answer 5

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

Valdovinos

bit by bit. save from everything. it piles up! – Lastex

Answer 6

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

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

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

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

Shingle

Answer 7

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

Also if you are using integer column for only positive numbers, then you can use -1 for no value.

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

Snaffle

Answer 8

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

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.

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.

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.

Hope that makes sense.

Laveta

Answer 9

You cant use Foreign keys if you prefer 0, because you cant store in Mysql items having 0 id (if primary), so the only possible way is null

Cautery