How the SYS_GUID() function works in Mariadb?

The SYS_GUID() function in MariaDB is used to generate a globally unique identifier (GUID) based on a combination of various system values, such as the server’s startup time, the server ID, and a sequence value. This function ensures that the generated GUID is unique across all servers and platforms.

Syntax

The syntax for the MariaDB SYS_GUID() function is as follows:

SYS_GUID()

The SYS_GUID() function does not take any arguments. It returns a binary string of 16 bytes, which is typically represented as a hexadecimal string of 32 characters.

Examples

Example 1: Generate a GUID

This example demonstrates how to generate a GUID using the SYS_GUID() function.

SELECT SYS_GUID() AS guid;

The output of this statement is:

+----------------------------------+
| guid                             |
+----------------------------------+
| 5a6a06b8dde011eebfa8c4b301d17c47 |
+----------------------------------+

Each time you execute the SYS_GUID() function, it will generate a new unique GUID.

Example 2: Store GUIDs in a table

This example shows how to store GUIDs generated by SYS_GUID() in a table.

DROP TABLE IF EXISTS example;
CREATE TABLE example (id INT AUTO_INCREMENT PRIMARY KEY, guid VARCHAR(32));
INSERT INTO example (guid) VALUES (SYS_GUID()), (SYS_GUID()), (SYS_GUID());

SELECT id, guid AS guid
FROM example;

The output of this statement is:

+----+----------------------------------+
| id | guid                             |
+----+----------------------------------+
|  1 | c56d3e80dde011eebfa8c4b301d17c47 |
|  2 | c56d42d6dde011eebfa8c4b301d17c47 |
|  3 | c56d44d4dde011eebfa8c4b301d17c47 |
+----+----------------------------------+

In this example, three GUIDs generated by SYS_GUID() are inserted into the example table. The [HEX()] function is used to display the binary GUID values in a hexadecimal format.

Example 3: Use SYS_GUID() for a primary key

This example demonstrates how to use SYS_GUID() to generate unique primary keys for a table.

DROP TABLE IF EXISTS example;
CREATE TABLE example (
    id BINARY(32) PRIMARY KEY,
    data VARCHAR(255)
);

INSERT INTO example (id, data) VALUES (SYS_GUID(), 'Record 1');
INSERT INTO example (id, data) VALUES (SYS_GUID(), 'Record 2');
INSERT INTO example (id, data) VALUES (SYS_GUID(), 'Record 3');

SELECT id AS id, data
FROM example;

The output of this statement is:

+----------------------------------+----------+
| id                               | data     |
+----------------------------------+----------+
| e2122d48dde011eebfa8c4b301d17c47 | Record 1 |
| e2125354dde011eebfa8c4b301d17c47 | Record 2 |
| e21283cedde011eebfa8c4b301d17c47 | Record 3 |
+----------------------------------+----------+

In this example, the id column is defined as a binary column with a length of 16 bytes, which can store the GUID generated by SYS_GUID(). The HEX() function is used to display the binary GUID values in a hexadecimal format.

Example 4: Generate multiple GUIDs in a single query

This example shows how to generate multiple GUIDs in a single query using SYS_GUID().

SELECT SYS_GUID() AS guid1, SYS_GUID() AS guid2, SYS_GUID() AS guid3;

The output of this statement is:

+----------------------------------+----------------------------------+----------------------------------+
| guid1                            | guid2                            | guid3                            |
+----------------------------------+----------------------------------+----------------------------------+
| f64d6d68dde011eebfa8c4b301d17c47 | f64d6d72dde011eebfa8c4b301d17c47 | f64d6d73dde011eebfa8c4b301d17c47 |
+----------------------------------+----------------------------------+----------------------------------+

In this example, three unique GUIDs are generated in a single query using the SYS_GUID() function.

Related Functions

The following are some functions related to the MariaDB SYS_GUID() function:

• The MariaDB UUID() function is used to generate a universally unique identifier (UUID) based on the UUID specification.
• The MariaDB UUID_SHORT() function generates a 64-bit UUID value based on the UUID specification.

Conclusion

The SYS_GUID() function in MariaDB provides a convenient way to generate globally unique identifiers that can be used for various purposes, such as primary keys, unique identifiers, or tracking objects. By understanding the function’s behavior and usage, you can effectively incorporate unique identifiers into your database design and ensure data integrity and consistency across different servers and platforms.