SYS_GUID and UUID

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SQL

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:

SQL

CREATE TABLE id_t(a INT, b BINARY(16));
INSERT id_t VALUES(1, SYS_GUID());

SQL

SELECT a, HEX(b) FROM id_t;

+------+----------------------------------+
| a    | HEX(b)                           |
+------+----------------------------------+
|    1 | 7CD5B7769DF95CEFE034080020825436 |
+------+----------------------------------+

UUID Example

SQL

CREATE TABLE id_t2(a INT, b VARCHAR(36));
INSERT INTO id_t2 VALUES(1, UUID());

SQL

SELECT * FROM id_t2;

+------+--------------------------------------+
| a    | b                                    |
+------+--------------------------------------+
|    1 | 15c67225-38f9-483b-8394-02a9e35a9156 |
+------+--------------------------------------+