Creating a ColdFusion UUID in MySQL

by Pete Freitag

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

Related Posts

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.

Comments

Ben Nadel

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.

Lionel Holt

Shouldn't it be the app that conforms to the database? Lucee and cflib.org both have CreateGUID() which includes the missing dash.