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. — user21926
– user21926, Commented Jul 30, 2018 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
– Mickaël Leger, Commented Jul 30, 2018 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
– IncredibleHat, Commented Jul 30, 2018 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
– Álvaro González, Commented Jul 30, 2018 at 15:12

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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 :)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 30, 2018 at 14:47

Kevin Bott

7333 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brainfeeder · Accepted Answer · 2019-03-29 08:34:52Z

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)

Danial · Accepted Answer · 2018-07-30 15:03:11Z

0

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.

answered Jul 30, 2018 at 15:03

Danial

1,62616 silver badges16 bronze badges

Collectives™ on Stack Overflow

Avoiding updating MySQL with blank values VS NULL

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related