Calculate hash of multiple columns in MySQL

What do I calculate the hash of multiple columns for? I’m working with API that is limiting the number of calls per hour. I don’t want to send the same data over and over again, so I’m saving the hash of the data I already sent. If the newly calculated hash is different from the previous one, I update the data through API.

This can be also handled by triggers. I can create the trigger that is setting the “updated” flag on a given record. Later I can simply use this flag to select records that should be sent to the API. Unfortunately, this gets complicated when I’m using multiple columns from different tables and the data is changed by various engines – sometimes even other triggers.

The calculation of the hash itself looks like this:

SELECT fName, lName, email, lastVisitDate,
    md5(
        concat(
            coalesce(fName, 'no-fname'), 
            coalesce(lName, 'no-lname'),
            coalesce(email, 'no-email'),
            cast(coalesce(lastVisitDate, 'no-date') as char)
            )
        ) AS newHash
FROM myTable

Have you noticed what I did here? Each value has to be converted to char since md5 is calculated from the string value. There are no null values allowed (md5 will return null) so I have to replace all nulls with a different value. I’m not using empty strings, to make sure that I can differentiate between the “empty fName, lName set to ‘test'” and “fName set to ‘test’ and empty lName” which will give the same hash otherwise. All column values are then concatenated and such a string is placed as the parameter of the md5 function.

After each update to the API, I’m saving the hash value to the utility table. Now the only thing I need is to check if the current hash is different from the one I saved. If they differ, I should update the API and save the new hash value.