MYSQL IFNull proper usage

Question

This is just a quick question, but when making update procedures for a table say Personinfo for example is this acceptable

Update Person
 Set 
   FirstName = IFNULL(_FirstName, FirstName),
   LastName = IFNULL(_LastName, LastName)
  Where PID = _PID

Is this at all advisable for doing partial updates to a record or should all the information just be sent back regardless?

Your answer looks fine to me -- except remove the last comma before the WHERE clause. This assume you pass in _FirstName and it can be passed in as a NULL value. — sgeddes, Mar 02 '13 at 19:00
Was wondering if it's advisable to allow it or if IFNULL() might not be worth allowing nulls to be sent back due to it's performance which I can't find much about. — SCFi, Mar 02 '13 at 19:03

score 0 · Accepted Answer · answered Mar 02 '13 at 20:01

IFNULL will return exp2 if exp1 is not null.

I assume you want to return a fixed string of ‘First Name’ and ‘Last Name’ if the parameter you’ve passed to the stored procedure isn’t set. I asume by performance you're also talking in relation to speed of updating / selecting and data fragmantation.

You need to be asking yourself, whether for application reasons you want NULL or a fixed string rather than worrying about performance (unless you're already got a performance issue related to this).

It is true that this can affect performance but it should only start to cause issues if your row size exceeds the limit - the internal representation of a table has a maximum row size of 65,535 bytes. If you’re not going to exceed that then the performance difference between having a fixed string and NULL isn't worth thinking about.

You could also opt for a CHAR type as that will always be the same length and never cause any fragmentation. But then you should store an empty value and not a NULL.

MYSQL IFNull proper usage

1 Answers1