CODEWITHSUNDEEP
mysqlnulldefault-valueddl
Shafizadeh
Shafizadeh

Reputation: 10340

Is leaving a field empty better or filling it with null?

I have a table that contains a column named first_name. It is not mandatory for users to fill it, and sometimes users leave it empty. Now I want to know: is it better to define null value as its default?

Upvotes: 4

Views: 9248

Answers (5)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385098

If you want the default value for your column to be NULL, then write DEFAULT NULL.

It's all well and good saying that shorter code is better and we can rely on the "default default" to do this job for us, but the fact that every single existing answer on this question leaves you prone to a potentially catastrophic bug is proof of what a terrible approach that really is!

Take a look at this:

CREATE TABLE `test` (`ts` TIMESTAMP);
SHOW CREATE TABLE `test`;

-- Result:
CREATE TABLE `test` (
  `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Woah! What's that DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP? That's what you get with a TIMESTAMP field with NOT NULL set and no explicit default. Whenever you update a row in that table, the timestamp column will be reset to "now". This catches lots of people out, and is a great example of why making assumptions is a terrible practice.

So, if you're going to study the manual and you are absolutely confident that omitting DEFAULT NULL will result in the semantics you intended, then go right ahead; but I posit that it really costs you nothing to just say what you mean.

Upvotes: 3

zedfoxus
zedfoxus

Reputation: 37029

Consider the following table:

create table test1 (
  id int not null,
  first_name varchar(50), -- nullable
  last_name varchar(50)   -- also nullable
);

If first_name is not provided in your UI, you can choose to not insert data into that field by doing:

insert into test1 (id, last_name) values (123, 'Smith');

Or, you can choose to explicitly provide NULL for first_name like so:

insert into test1 (id, first_name, last_name) values (123, NULL, 'Smith');

-- you could also do like this below:
-- insert into test1 values (123, NULL, 'Smith');
-- I just like providing explicit fieldnames and values

Either way you choose, just stay consistent throughout your application. Your results will look the same:

+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+
| 123 | NULL       | Smith     |
| 123 | NULL       | Smith     |
+-----+------------+-----------+

So - to answer the real question: don't define an explicit null in your table creation.

When supplying '' or NULL, just make sure you are consistent. If some first_name are '' and some are NULL, your select statement would have to be:

select * from test1 where first_name is NULL or first_name is '';

That brings another point - what if user typed ' ' (4 spaces)? You would have to ensure that first_name meets certain criteria and trimmed version of first_name goes through validation before being entered in the database. If your database ends up with '', ' ', ' ' etc. you would have to constantly run:

select * from test1 where first_name is NULL or trim(first_name) = '';
--or--
--select * from test1 where first_name is NULL or length(trim(first_name)) = 0;

Consistency with NULL first_name will help querying with confidence.

Upvotes: 4

lumee
lumee

Reputation: 623

Using NULL is perfectly valid if it means that a field was not given or is unknown. It makes no difference really, it's a matter of taste, but sometimes is very useful to knoww when something was not given rather than querying empty or blank fields.

Upvotes: 1

Hassan Ali Shahzad
Hassan Ali Shahzad

Reputation: 2724

Question is Null better or not?

As per my experience in your mentioned scenario you have to put default empty value instead of null. In this way you can save you in scripting to check its null or not you can just check it empty or not checking is null is bit tricky.

Any how its my opinion.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311053

Defining default null is superfluous. If you do not have an explicit default defined, the column will default to null if no value is supplied. Some people and project standards like explicitly stating these null defaults, but it serves no real purpose, and is a matter of taste more than anything else.

Upvotes: 1

Related Questions