SYS_GUID Function - Oracle to MySQL Migration

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

In MySQL, you can use the UUID function that returns a UUID as a string.

Note that Oracle's SYS_GUID() returns a value without hyphens (-) and in uppercase, while MariaDB's 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 UUID_TO_BIN and UNHEX functions to obtain a binary UUID value in MySQL.

Oracle:

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

MySQL:

  -- Generate UUID
  SELECT UUID();
  /* ad990181-f432-11ef-a3c4-fc5ceefdf1cd */
 
  -- Use UPPER and REPLACE to get the same string format as Oracle's SYS_GUID()
  SELECT UPPER(REPLACE(UUID(), '-', ''));
  /* 3316CBE6F43311EFA3C4FC5CEEFDF1CD */
 
  -- Use UUID_TO_BIN to get a binary value - 16 bytes 
  SELECT UUID_TO_BIN(UUID());
  /* 0x0BFE36E5F45211EFB0E1FC5CEEFDF1CD */
 
  -- 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 UUID_TO_BIN and UNHEX (requires removing hyphens) functions, which convert 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 MySQL Migration.