php - Best way to handle large (UUID) as a MySQL table primary key

Question

I have a UUID string that I want to use as my MySQL tables primary key, the UUID is a 32 character hexadecimal string (after '-' characters are stripped). Due to the fact that it is better to use a numeric column (int) as your primary key in a database, I would like to convert this to an integer but not sure of the best way to handle it.

1. Due to the size of the string (ie. uuid='a822ff2bff02461db45ddcd10a2de0c2'), do I need to break this into multiple 'substrings'.
2. I am running PHP on a 32 bit architecture at the moment so converting it within PHP will not work due to PHP_INT_MAX size (max 0xFFFFFFFF). And I suspect that would be the same restriction for MySQL.
3. I do not like the idea of multiple primary keys as a fix for this, I would rather use a string representation even though that's not the preferred method.

I might be thinking about this all wrong, and am not against reading documentation, so either examples or suggested reading as a response would be acceptable.

Answer 1

For most cases it is best to store UUIDs/GUIDs as BINARY(16). See these related StackOverflow questions:

• UUID Performance in MySQL
• How to Store UUID as Number

The conversion can (and probably should) be done in MySQL instead of PHP so whether you're using a 32bit PHP client or 64bits doesn't matter a bit (pun intended :P)