Creating a ColdFusion UUID in MySQL
uuid() function in MySQL returns a 36 character hex string, formatted as:
createUUID() function returns a 35 character hex string formatted as:
Both store the same amount of data (16 bytes), the only difference is that there is an extra dash in the MySQL uuid() function result.
Here's some SQL I came up with to create a UUID using ColdFusion's formatting in raw SQL:
SELECT upper(concat(left(uuid(), 23), right(uuid(), 12)))
It is not an ideal solution because I am actually calling
uuid() twice, but it is sufficient for my use case. You could probably use a regex to remove the extra dash and avoid calling uuid twice if you wanted to try and optimize it. Feel free to post a comment if you can come up with a better way to do it.
Now suppose you want to convert the CFML uuid back to a MySQL uuid, you can do so like this:
SELECT lower(concat(left(some_id, 23), '-', right(some_id, 12))) FROM table
Like this? Follow me ↯Tweet Follow @pfreitag
Creating a ColdFusion UUID in MySQL was first published on August 16, 2022.
If you like reading about mysql, uuid, or createuuid then you might also like:
The Fixinator Code Security Scanner for ColdFusion & CFML is an easy to use security tool that every CF developer can use. It can also easily integrate into CI for automatic scanning on every commit.