Should MySQL columns always have Default Values?

Question

Is it best practice to always have default values on MySQL columns?
Does this improve performance?
I have set my columns to be NOT NULL but have not specified a default value.
Is this bad practice?

Answer 1

Is it best practice to always have default values on mysql columns?

NO

It really depends.
If you want to ensure that sensible data is input, then you should not set a default value for a not null column.
Only set default values for columns that have a sensible default value.
If you can get away with null just allow null.

Does this improve performance?

It slows down your inserts ever so slightly.
But may it speed up your selects, because you don't have to test for null.
Of course the DB spends a small amount of time to setting the default values.
Unless you're inserting in bulk it's very unlikely you'll notice any delay.

Answer 2

Performance

1. Inserts Theoretically, it should be faster to use default values instead of sending every all values. In fact the performance difference is so small, you can not measure it.

2. In general: Using NOT NULL is faster than working with NULL values. Specially index structures work faster if they do not have to handle the Null values.

3. If you use NOT NULL use Default value

Best Practice

In real world you should do, what you desire to have as an outcome.

• Use NULL values if you want to keep the information "I don't know the real value"

• Use DEFAULT value, if you don't care if you know the real value, but you want to make sure that at least a default value is present.

• If you use NOT NULL always use DEFAULT.

Examples

• Birthday: Do use NULL values for those users, you don't know the birthday.

• isDisabled: Do not use NULL, instead set default to 0; so if there is nothing set, the User is not disabled. You decide, so you don't need NULL.

Answer 3

If your specifying it as NOT NULL then yes you should have a default value unless your controlling what data is inputted. NOT NULL implies there will always be a value so having a default value is good practice. Do all of your columns need to be NOT NULL?