CODEWITHSUNDEEP
sqlmysql
JPro
JPro

Reputation: 6546

Using NULL in MySQL

The data in my dabatase comes from an external source and where ever there is no data passed, I keep NULL in those places. Can anyone tell me if there are any implications in using NULL to represent empty value? Should I follow some other convention like 'data_not_available'? or something like that?

Can anyone suggest?

Upvotes: 3

Views: 145

Answers (3)

Daniel Vassallo
Daniel Vassallo

Reputation: 344281

Conceptually, NULL means "a missing unknown value" and it is treated somewhat differently from other values. For example, to test for NULL in MySQL, you cannot use the arithmetic comparison operators such as =, <, or <>.

Since you will be having columns that may have "missing or unkown" values, you have to set them to accept NULL. On the other hand, a table with many NULL columns may be indicating that this table needs to be refactored into smaller tables that better describe the entities they represent.

Note that in general using a convention like 'data_not_available' is not recommended. Using NULLs is the convention, and your DBMS already knows about it.

Upvotes: 1

Aiden Bell
Aiden Bell

Reputation: 28386

Wikipedia's entry on NULL in SQL is actually very informative. NULL is acceptable, but can often indicate database design problems, where data should reside in a separate table with a FK.

Upvotes: 1

Andy Shellam
Andy Shellam

Reputation: 15535

I've always used NULL. I've seen arguments saying NULL was a hack and should never have been put into mainstream use and it's now out of control, but I can't think of a better way to treat something as having "no value."

After all how you can represent a number as having no value? 0 is a value. -1 is a value. -9999999 is a value.

Also foreign keys depend on a NULL value to signify there is no related record.

Upvotes: 2

Related Questions