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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
NULL you can distinguish between “put no data” and “put empty data”.
Some more differences:
LENGTHof an empty string is
NULLs are sorted before the empty strings.
COUNT(message)will count empty strings but not
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
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
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 = '' .
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
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.
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.
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.
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.