Pete Freitag Pete Freitag

Creating a ColdFusion UUID in MySQL

Published on August 16, 2022
By Pete Freitag
coldfusiondatabases

The uuid() function in MySQL returns a 36 character hex string, formatted as:

aa479ea9-1d9d-11ed-ba03-564760fe47b7

ColdFusion's createUUID() function returns a 35 character hex string formatted as:

AA479EA9-1D9D-11ED-BA03564760FE47B7

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


mysql uuid createuuid

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:

Fixinator

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.


Try Fixinator

CFBreak
The weekly newsletter for the CFML Community


Comments

Oh groovy. I was just starting to learn about the possibility of using a UUID as the primary key on a table. I wasn't sure if the UUID algorithm used in MySQL and ColdFusion were equivalent; but it sounds like I can probably just insert the missing dash to get the two platforms on the same page. Still feels crazy to use anything but an AUTO_INCREMENT primary key :D But, I need to break out of my comfort zone.
by Ben Nadel on 09/30/2022 at 9:52:44 AM UTC
Shouldn't it be the app that conforms to the database? Lucee and cflib.org both have CreateGUID() which includes the missing dash.
by Lionel Holt on 11/28/2023 at 5:32:52 AM UTC

Post a Comment