Identifier Generation Functions

SYS_GUID and UUID

Generates and returns a new globally unique identifier (GUID) each time it is called.

Syntax

SYS_GUID()
UUID()

Return Type

SYS_GUID: BINARY(16)

UUID: VARCHAR(36)

Remarks

  • Returns a v4 UUID

  • The value returned will be globally unique

  • A new value will be produced for each row processed

  • A new value will be produced for each occurrence of the function in the query syntax

  • UUID works exactly like SYS_GUID except for the return type

  • SYG_GUID/UUID cannot be used as a default column value for a sharded table

  • SYS_GUID/UUID cannot be used in a computed column expression in a table definition

Note

Use INT or BIGINT fields for joining tables if performance is the most important consideration. Joining on BINARY(16) columns works, but is noticeably slower. Rather than UUID or SYS_GUID, use AUTO_INCREMENT, or implement application code to provide integer key values on which to join tables, for the best performance.

Examples

SYS_GUID Example

Create a simple table and insert a row. In order to display the GUID, convert it to HEX:

CREATE TABLE id_t(a INT, b BINARY(16));
INSERT id_t VALUES(1, SYS_GUID());
SELECT a, HEX(b) FROM id_t;
+------+----------------------------------+
| a    | HEX(b)                           |
+------+----------------------------------+
|    1 | 7CD5B7769DF95CEFE034080020825436 |
+------+----------------------------------+

UUID Example

CREATE TABLE id_t2(a INT, b VARCHAR(36));
INSERT INTO id_t2 VALUES(1, UUID());
SELECT * FROM id_t2;
+------+--------------------------------------+
| a    | b                                    |
+------+--------------------------------------+
|    1 | 15c67225-38f9-483b-8394-02a9e35a9156 |
+------+--------------------------------------+

Last modified: July 29, 2024

Was this article helpful?