Generating a unique MySQL field based on the contents of other fields

Question

Hi folks,

In creating unique custom reference codes (i.e. JOB1293XYZ) for a while I have been generating custom reference/hashes in PHP then checking my database to see if it already exists and if so generating another.

I'm curious if in MySQL it is possible to to generate the contents of a field based on the other fields in that row, i would be a handy shortcut. For Example...

Table: Employees

• employeeId
• firstName
• lastName
• jobTitle
• DOB
• employeeCode

So for employeeCode can I perhaps use a MySQL Custom Function (which I know very little about) to generate the contents of the field. perhaps by taking the first letter of the firstname, first letter of the second name, the str length of the job title and the last two digits of the DOB? This is purely an example.

If this is possible and anyone can point me to any reference material that would be great, have been wading through the heavy MySQL documentation and will continue to do so for the time being.

Thanks in advance!

Answer 1

You could concatenate all of the fields together and do an MD5 on the resulting string.

UPDATE Employees SET md5str=MD5(CONCAT(field1, field2, field3))...

I wouldn't recommend this approach because then the question of what to do if you had a hash collision would be very difficult if not impossible to answer.

The above idea is not mine: I spotted this in the maatkit code.