# sql – MySQL, better to insert NULL or empty string?

## The Question :

233 people think this question is useful

I have a form on a website which has a lot of different fields. Some of the fields are optional while some are mandatory. In my DB I have a table which holds all these values, is it better practice to insert a NULL value or an empty string into the DB columns where the user didn’t put any data?

221 people think this answer is useful

By using NULL you can distinguish between “put no data” and “put empty data”.

Some more differences:

• A LENGTH of NULL is NULL, a LENGTH of an empty string is 0.

• NULLs are sorted before the empty strings.

• COUNT(message) will count empty strings but not NULLs

• You can search for an empty string using a bound variable but not for a NULL. This query:

SELECT  *
FROM    mytable
WHERE   mytext = ?



will never match a NULL in mytext, whatever value you pass from the client. To match NULLs, you’ll have to use other query:

SELECT  *
FROM    mytable
WHERE   mytext IS NULL



44 people think this answer is useful

One thing to consider, if you ever plan on switching databases, is that Oracle does not support empty strings. They are converted to NULL automatically and you can’t query for them using clauses like WHERE somefield = '' .

10 people think this answer is useful

One thing to keep in mind is that NULL might make your codepaths much more difficult. In Python for example most database adapters / ORMs map NULL to None.

So things like:

print "Hello, %(title)s %(firstname) %(lastname)!" % databaserow



might result in “Hello, None Joe Doe!” To avoid it you need something like this code:

if databaserow.title:
print "Hello, %(title)s %(firstname) %(lastname)!" % databaserow
else:
print "Hello, %(firstname) %(lastname)!" % databaserow



Which can make things much more complex.

9 people think this answer is useful

Better to Insert NULL for consistency in your database in MySQL. Foreign keys can be stored as NULL but NOT as empty strings.

You will have issues with an empty string in the constraints. You may have to insert a fake record with a unique empty string to satisfy a Foreign Key constraint. Bad practice I guess.

5 people think this answer is useful

I don’t know what best practice would be here, but I would generally err in favor of the null unless you want null to mean something different from empty-string, and the user’s input matches your empty-string definition.

Note that I’m saying YOU need to define how you want them to be different. Sometimes it makes sense to have them different, sometimes it doesn’t. If not, just pick one and stick with it. Like I said, I tend to favor the NULL most of the time.

Oh, and bear in mind that if the column is null, the record is less likely to appear in practically any query that selects (has a where clause, in SQL terms) based off of that column, unless the selection is for a null column of course.

2 people think this answer is useful

If you are using multiple columns in a unique index and at least one of these columns are mandatory (i.e. a required form field), if you set the other columns in the index to NULL you may end up with duplicated rows. That’s because NULL values are ignored in unique columns. In this case, use empty strings in the other columns of the unique index to avoid duplicated rows.

COLUMNS IN A UNIQUE INDEX:
(event_type_id, event_title, date, location, url)

EXAMPLE 1:
(1, 'BBQ', '2018-07-27', null, null)
(1, 'BBQ', '2018-07-27', null, null) // allowed and duplicated.

EXAMPLE 2:
(1, 'BBQ', '2018-07-27', '', '')
(1, 'BBQ', '2018-07-27', '', '') // NOT allowed as it's duplicated.


Here are some codes:

CREATE TABLE test (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
event_id int(11) DEFAULT NULL,
event_title varchar(50) DEFAULT NULL,
date date DEFAULT NULL,
location varchar(50) DEFAULT NULL,
url varchar(200) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY event_id (event_id,event_title,date,location,url)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



Now insert this to see it will allow the duplicated rows:

INSERT INTO test (id, event_id, event_title, date, location,
url) VALUES (NULL, '1', 'BBQ', '2018-07-27', NULL, NULL);

INSERT INTO test (id, event_id, event_title, date, location,
url) VALUES (NULL, '1', 'BBQ', '2018-07-27', NULL, NULL);



Now insert this and check that it’s not allowed:

INSERT INTO test (id, event_id, event_title, date, location,
url) VALUES (NULL, '1', 'BBQ', '2018-07-28', '', '');

INSERT INTO test (id, event_id, event_title, date, location,
url) VALUES (NULL, '1', 'BBQ', '2018-07-28', '', '');



So, there is no right or wrong here. It’s up to you decide what works best with your business rules.