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

Question

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?

Answer 1

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.

Answer 2

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.