Menu
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.
675
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. Because the result of any arithmetic comparison with NULL is also NULL , you cannot obtain any meaningful results from such comparisons. In MySQL, 0 or NULL means false and anything else means true.
A null should not be confused with a value of 0. A null value indicates a lack of a value, which is not the same thing as a value of zero. For example, consider the question "How many books does Adam own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns).
Introduction to MySQL INT typeAn integer can be zero, positive, and negative. MySQL supports all standard SQL integer types INTEGER or INT and SMALLINT . In addition, MySQL provides TINYINT MEDIUMINT , and BIGINT as extensions to the SQL standard.
Use 0. from msdn, A value of NULL indicates that the value is unknown.
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.NULL.
66
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
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
