Pete Freitag Pete Freitag

Creating a ColdFusion UUID in MySQL

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

Like this? Follow me ↯

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:

FuseGuard Web App Firewall for ColdFusion

The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.

Post a Comment