How to format uuid string from binary column in MySQL/MariaDB

Question

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)); 

Answer 1

The following would create the result I was after:

SELECT   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)   )) FROM foo; 

Answer 2

MySQL 8 adds two new UUID functions:

• UUID_TO_BIN
• BIN_TO_UUID - this is the one you're looking for

So:

SELECT BIN_TO_UUID(uuid) FROM foo