Avoiding updating MySQL with blank values VS NULL

Question

I have a form, that has some optional inputs, that the user can skip from updating, those inputs can be empty, the issue is when I update a column that is set to NULL, the query will update that column to a blank "" instead of keeping it as null.

My concern is how efficient is this on MySQL specially large tables, is there a difference in performance when values are set to Blank VS NULL. If yes then I will make sure all empty inputs are updated as NULL.

This is the code am using to avoid blank, is there a better way to it?

#Avoid blank with NULL
$value1 = !empty($_POST['input1']) ? $_POST['input1'] : NULL;
$value2 = !empty($_POST['input2']) ? $_POST['input2'] : NULL;

I think it is more about correctness than efficiency. The empty string is a value, `NULL` is the absence of a value. They are different semantically. — Sean Bright, Jul 30 '18 at 14:39
One difference between empty string and `NULL` is for `UNIQUE INDEX` : if you create one on multiple value and one of those value are `NULL`, the index won't work — Mickaël Leger, Jul 30 '18 at 14:46
Well, a note about your code... if this is in a proper `prepare`, then it still ends up going in as a blank, and not a null. You would have to do a condition on the placement of `?` vs `NULL` in the actual `prepare` sql string. And then dynamically include the proper number of bound variables off that initial setup. A messy thing to do. — IncredibleHat, Jul 30 '18 at 14:54
Please don't even consider storing empty strings for nulls. I maintain legacy software in a daily basis and having to deal everywhere with endless non-standard variations of "no data" is one of the most absurd and time consuming tasks: `if (is_null($start) || $start=='' || $start=='0000-00-00 00:00:00') {...}`. I bet the server harly cares but it really hurts *my* efficiency ;-) — Álvaro González, Jul 30 '18 at 15:12

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

There's a good conversation about it here: MySQL: NULL vs ""

Answer from /u/degenerate

I found out that NULL vs "" is insignificant in terms of disk-space and performance.

There's some others discussing that it's faster to search for a "null bit" than checking that a string is ''. That question is 9 years old, so we have come a long way hardware wise since then, and the difference is probably negligible. As a DBA, I'd recommend using nulls appropriately :)

Brainfeeder · Answer 2 · 2019-03-29T08:34:52.100

As in terms of disk-space and performance, there might be almost no difference, I would certainly take the time to write a function which catches requests and replaces all 'empty's with NULL. Certainly when NULL is the default value for that field.

For example, I use NULL as default for datetime fields. When an invoice gets payed, I'll set the field to the date and time.

if($record->payed != NULL) { echo 'invoice payed'; } // This will say it is payed when the field is ""
if(!empty($record->payed)) { echo 'invoice payed'; } // This will not say it is payed when field is "" or NULL

Or when you would like to list all open invoices:

SELECT * FROM invoices WHERE payed IS NULL // will not return any records where payed is ""

So to avoid troubles while programming, you should use only one method and stick with that :) (Been there)

score 0 · Answer 3 · answered Jul 30 '18 at 15:03

I always avoid nulls because it's just another thing to test for. If a value can be null or '', you have to:

WHERE foo == '' OR foo is null.

whether it's more efficient or less efficient will depend on the field and what your queries are. If you're testing for specific values then it shouldn't make any difference at all, unless you're testing for empty/null.

Avoiding updating MySQL with blank values VS NULL

3 Answers3