SYS_GUID Function - Oracle to MariaDB Migration

In Oracle, the SYS_GUID function generates an Universal Unique Identifier (UUID) of RAW(16) data type.

In MariaDB, you can use the SYS_GUID (MariaDB 10.6.1 and later) and UUID functions that return a UUID as a string.

Note that Oracle's SYS_GUID() returns a value without hyphens (-) and in uppercase, while MariaDB's SYS_GUID also returns a value without hyphens but in lowercase, and UUID() returns a value with hyphens and in lowercase.

You can use the REPLACE and UPPER functions to achieve the same string format as in Oracle. Additionally, you can use the UNHEX function to obtain a binary UUID value in MariaDB.

Oracle:

  -- Generate UUID
  SELECT SYS_GUID() FROM dual;
  /* 2869A88B7BC846FCB08812E2B28B2919 */

MariaDB - Oracle Compatibility:

  -- Generate UUID
  SELECT UUID();
  /* ad990181-f432-11ef-a3c4-fc5ceefdf1cd */
 
  -- Using SYS_GUID to get a value without hyphens (-)
  SELECT SYS_GUID();
  /* db782a63f44f11efa3c4fc5ceefdf1cd */
 
  -- Use UPPER and REPLACE to get the same string format as Oracle's SYS_GUID()
  SELECT UPPER(REPLACE(UUID(), '-', ''));
  /* 3316CBE6F43311EFA3C4FC5CEEFDF1CD */
 
  -- Use HEX to get a binary value - 16 bytes 
  SELECT UNHEX(REPLACE(UUID(), '-', ''));
  /* 0xF8B2990FF44711EFB0E1FC5CEEFDF1CD */
 
  -- Do not use CAST AS BINARY - INCORRECT RESULT (!) - 32 bytes
  SELECT CAST(REPLACE(UUID(), '-', '') AS BINARY); 
  /* 0x38636332613061332D663433662D313165662D623065312D666335636565666466316364 */

When converting a UUID value to binary form, use the UNHEX function, which converts each pair of hexadecimal digits into one byte, resulting in 16 bytes.

In contrast, CAST AS BINARY converts every hexadecimal digit into its own byte, producing 32 bytes. This is an incorrect conversion of a UUID to binary form.

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.