Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

In MySQL/MariaDB the most efficient way to store uuid is in a BINARY(16) column. However, sometimes you want to obtain it as a formatted uuid string.

Given the following table structure, how would I obtain all uuids in a default formatted way?

CREATE TABLE foo (uuid BINARY(16));
    SUBSTR(HEX(uuid), 9, 4), '-',
    SUBSTR(HEX(uuid), 13, 4), '-',
    SUBSTR(HEX(uuid), 17, 4), '-',
    SUBSTR(HEX(uuid), 21)
FROM foo;
                See Oleg Mikheev's answer, it's now much easier to do it in mysql 8.0 using BIN_TO_UUID()
– Vlad Dinulescu
                Jan 18, 2019 at 0:05
                Optimization: instead of repeating the full HEX(uuid) operation 5 times, wouldn't it be faster to SET @hex = (SELECT LOWER(HEX(bytes))); first, then reference @hex 5 times, and also skip the enclosing LOWER()?
– Liviu Chircu
                Sep 28, 2020 at 16:23
                I was wondering if you could just BIN_TO_UUID(<fieldName>), this confirms it. Although, If you are having to do this, you probably passed in the true flag to UUID_TO_BIN, and so you will also need it get the correct UUID back. I.E. BIN_TO_UUID( <fieldName>, true )
– Native Coder
                Sep 30, 2020 at 15:00
                If you use uuids without dashes, you can also use the shorter hexadecimal literal x'uuid' instead of UUID_TO_BIN('uuid').
– Synthesis
                Jan 26 at 20:34
In earlier (prior to 8) versions you can create a function in MySQL like the following:
CREATE
  FUNCTION uuid_of(uuid BINARY(16))
  RETURNS VARCHAR(36)
  RETURN LOWER(CONCAT(
  SUBSTR(HEX(uuid), 1, 8), '-',
  SUBSTR(HEX(uuid), 9, 4), '-',
  SUBSTR(HEX(uuid), 13, 4), '-',
  SUBSTR(HEX(uuid), 17, 4), '-',
  SUBSTR(HEX(uuid), 21)
And then simply use it in your queries:
SELECT
  uuid_of(id)
  name,
FROM users
And it produces:
(c6f5703b-fec2-43fd-8f45-45f06583d450, Some name, 20)
                I'm sorry but this calculation is wrong, it does not reflect the original UUID. The UUID 2e9660c2-1e51-4b9e-9a86-6db1a2770422 is shown as c260962e-511e-9e4b-9a86-6db1a2770422 when querying in MySQL Workbench
– Alexz S.
                Feb 2, 2019 at 16:59
                @Alex.H any reproduces? SELECT hex(uuid_column) FROM ${table} and then SELECT uuid_of(uuid_column) FROM ${table}?
– Andrii Abramov
                Feb 3, 2019 at 6:32
                The hex(myId) and uuid_of(uuid_column) are actually the same C260962E511E9E4B9A866DB1A2770422 and c260962e-511e-9e4b-9a86-6db1a2770422, however it's not the ID (I believe) my MVC application is generating and saving to the database. It generates 2E9660C2-1E51-4B9E-9A86-6DB1A2770422 which is saved as C260962E511E9E4B9A866DB1A2770422. It may be an issue with the MySQL EF Adapter I'm using, because if I query anything using the UUID from uuid_of I just don't get anything, but if I use the one from the function I posted above, I do get my records.
– Alexz S.
                Feb 5, 2019 at 11:14
If you are looking for the opposite, i.e., how to convert from string to binary, perhaps to do a join or something, this is covered here : Convert UUID to/from binary in Node
This piece of SQL run on Mysql 5.7 helped lock in the concept for me:
SELECT
  LOWER(CONCAT(
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 1, 8), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 9, 4), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 13, 4), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 17, 4), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 21)
The output is 43d597d7-2323-325a-90fc-21fa5947b9f3.
string -> binary
So UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', '')) to convert a UUID to binary during an INSERT / UPDATE / JOIN / SELECT whatever, and 
binary -> string
LOWER(CONCAT(
  SUBSTR(HEX(uuid), 1, 8), '-',
  SUBSTR(HEX(uuid), 9, 4), '-',
  SUBSTR(HEX(uuid), 13, 4), '-',
  SUBSTR(HEX(uuid), 17, 4), '-',
  SUBSTR(HEX(uuid), 21)
The correct result is generated by the script below, the other scrips generated a UUID however not the right one.
CONCAT(
    substr(hex(Id), 7, 2), substr(hex(Id), 5, 2), substr(hex(Id), 3, 2), substr(hex(Id), 1, 2), '-'
    , substr(hex(Id), 11, 2) , substr(hex(Id), 9, 2) , '-'
    , substr(hex(Id), 15, 2) , substr(hex(Id), 13, 2) , '-'
    , substr(hex(Id), 17, 4) , '-'
    , substr(hex(Id), 21, 12) 
Results running the other scripts generated wrong UUID as per below:
Expected UUID - 2e9660c2-1e51-4b9e-9a86-6db1a2770422
What was generated - c260962e-511e-9e4b-9a86-6db1a2770422
As you can see they are different.
                This worked for me too. I tried with MySQL 8 method BIN_TO_UUID or the other methods but it wasn't the correct UUID. I have a C# solution and GUIDs are transformed to HEX and encrypted in base64.
– Eduard
                Mar 11, 2022 at 11:25
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.