How the UUID() function works in Mariadb?

The UUID() function in MariaDB is used to generate a universally unique identifier (UUID), which is a 128-bit number that is designed to be unique across space and time. UUIDs are widely used in various applications, such as distributed systems, databases, and web services, to uniquely identify records, objects, or entities.

Syntax

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

UUID()

The function does not take any parameters. It generates and returns a 36-character string representation of a UUID in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, where each group of hexadecimal digits is separated by a hyphen.

Examples

Example 1: Generate a UUID value

This example demonstrates how to use the UUID() function to generate a UUID.

SELECT UUID();

The following is the output:

+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| f5c6405c-debe-11ee-b48c-c4b301d17c47 |
+--------------------------------------+

This output represents a randomly generated UUID in the standard 36-character format.

Example 2: Store UUID values in a table

This example shows how to store UUID() values in a table column.

DROP TABLE IF EXISTS uuid_table;
CREATE TABLE uuid_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid_value CHAR(36)
);

INSERT INTO uuid_table (uuid_value) VALUES (UUID()), (UUID()), (UUID());

SELECT * FROM uuid_table;

The following is the output:

+----+--------------------------------------+
| id | uuid_value                           |
+----+--------------------------------------+
|  1 | fd30f95e-debe-11ee-b48c-c4b301d17c47 |
|  2 | fd30fc06-debe-11ee-b48c-c4b301d17c47 |
|  3 | fd30fd32-debe-11ee-b48c-c4b301d17c47 |
+----+--------------------------------------+

This example creates a table uuid_table with an id column and a uuid_value column of type CHAR(36). It then inserts three randomly generated UUID() values into the uuid_value column and retrieves all rows to demonstrate that the UUIDs were correctly stored.

Example 3: Use UUID values as unique identifiers

This example illustrates how UUID() values can be used as unique identifiers in a table.

DROP TABLE IF EXISTS products;
CREATE TABLE products (
  product_uuid CHAR(36) PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  product_price DECIMAL(10, 2)
);

INSERT INTO products (product_uuid, product_name, product_price) VALUES
  (UUID(), 'Product A', 19.99),
  (UUID(), 'Product B', 29.99),
  (UUID(), 'Product C', 39.99);

SELECT * FROM products;

The following is the output:

+--------------------------------------+--------------+---------------+
| product_uuid                         | product_name | product_price |
+--------------------------------------+--------------+---------------+
| 057c2a34-debf-11ee-b48c-c4b301d17c47 | Product A    |         19.99 |
| 057c2c32-debf-11ee-b48c-c4b301d17c47 | Product B    |         29.99 |
| 057c2cc8-debf-11ee-b48c-c4b301d17c47 | Product C    |         39.99 |
+--------------------------------------+--------------+---------------+

This example creates a table products with a product_uuid column of type CHAR(36) as the primary key. It then inserts three products, each with a unique UUID() value as the primary key. The SELECT statement retrieves all rows, demonstrating that the UUID() values serve as unique identifiers for the products.

Example 4: Compare UUID values

This example shows how to compare UUID() values and demonstrates their uniqueness.

SELECT
  UUID() AS uuid1,
  UUID() AS uuid2,
  UUID() = UUID() AS same_uuid;

The following is the output:

+--------------------------------------+--------------------------------------+-----------+
| uuid1                                | uuid2                                | same_uuid |
+--------------------------------------+--------------------------------------+-----------+
| 0cfa1d20-debf-11ee-b48c-c4b301d17c47 | 0cfa1d2a-debf-11ee-b48c-c4b301d17c47 |         0 |
+--------------------------------------+--------------------------------------+-----------+

This example generates three UUID() values: uuid1, uuid2, and compares the third and fourth values using the equality operator. The result (same_uuid) is 0, indicating that the third and fourth values are different, even though they were generated in the same statement.

Example 5: Generate unique keys with UUID

This example demonstrates how UUID() can be used to generate unique keys for a table.

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  order_uuid CHAR(36) PRIMARY KEY,
  customer_name VARCHAR(255) NOT NULL,
  order_date DATE
);

INSERT INTO orders (order_uuid, customer_name, order_date)
VALUES
  (UUID(), 'John Doe', '2023-05-01'),
  (UUID(), 'Jane Smith', '2023-05-02'),
  (UUID(), 'Bob Johnson', '2023-05-03');

SELECT * FROM orders;

The following is the output:

+--------------------------------------+---------------+------------+
| order_uuid                           | customer_name | order_date |
+--------------------------------------+---------------+------------+
| 19f89236-debf-11ee-b48c-c4b301d17c47 | John Doe      | 2023-05-01 |
| 19f894e8-debf-11ee-b48c-c4b301d17c47 | Jane Smith    | 2023-05-02 |
| 19f89560-debf-11ee-b48c-c4b301d17c47 | Bob Johnson   | 2023-05-03 |
+--------------------------------------+---------------+------------+

This example creates a table orders with an order_uuid column of type CHAR(36) as the primary key. It then inserts three orders, each with a unique UUID() value as the order_uuid. The SELECT statement retrieves all rows, demonstrating that the UUID() values serve as unique identifiers for the orders.

Related Functions

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

• MariaDB UUID_SHORT() function is used to generate a 64-bit universally unique identifier.

Conclusion

The UUID() function in MariaDB is a powerful tool for generating globally unique identifiers that can be used in various scenarios, such as primary keys, unique constraints, or other situations where a unique identifier is required. By understanding its usage and characteristics, as demonstrated through the examples provided, you can effectively incorporate this function into your database design and operations to ensure data integrity and uniqueness.