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;
There's a good conversation about it here: MySQL: NULL vs ""
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 :)
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)
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.