MySql better NULL or NOT NULL?

Databases
1

Hi,
The other thay I came a cross the following article: https://www.bennadel.com/blog/85-why-null-values-should-not-be-used-in-a-database-unless-required.htm

The author suggest never use NULL to store empty values in the database, I know I need to set some column type to NULL such as date and datetime but what about the rest?

The problem with using empty is: if I need to execute a insert query from PHP I need to create some dummy empty variables to be added to the query even if I don’t need to insert them.

Just wanted to ask what your opinion is.

2

Hey Vincekaribusana,

It is Depending upon the variation of the Field’s width. If it is a fixed then it will take the same space as the width of the field.
Otherwise no space is taken up by NULL. However one bit is used to check the value of column is NULL or not.
:blush:

3

I’m not entirely sure what the article’s point is.

They seem to be railing against using NULL’s because… you can’t do a LEN() against them? I’m not sure what use case he’s trying to point at in which NULL requires especial effort.

Yes, you should be aware that NULL and “” are not the same thing. But that’s not a reason to never use NULL’s, and its the same thing in multiple languages - undefined is not the same as “” in javascript, PHP, etc… In fact, most if not all database engines can easily dismiss this problem by simply specifying your WHERE clause properly in your select query. WHERE field IS NOT NULL.

4

i didn’t even have to click on the link, the url said it all – why-null-values-should-not-be-used-in-a-database-unless-required.htm

use NULL when you have to, otherwise go with NOT NULL and hopefully have a DEFAULT value

note: DEFAULT '' is not acceptable

1 Like
5

Hi and thanks for your replies, I think will set for each column in the database the default to null so I will not have problems with storing empty fields in the database and mysql will take care of them

6

Why is an empty DEFAULT string not allowed?

7

oh, syntactically it’s allowed

it’s just not acceptable if you care about data quality

1 Like
8

no, that’s not how it works

if you say DEFAULT NULL and then insert an empty string, it will insert that empty string

9

this isn’t even necessary when you’re doing aggregates like SUM() or MAX() because they simply ignore NULLs right out of the box

1 Like
10

I’ve noticed if for example my form date field is left empty then in mysql is stored as null if the default is set to null for that column

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.