How the UUID_SHORT() function works in Mariadb?

The UUID_SHORT() function in MariaDB is used to generate a 64-bit universally unique identifier (UUID). Unlike the standard UUID functions that generate 128-bit UUIDs, the UUID_SHORT() function generates a shorter, 64-bit UUID, which can be more efficient in terms of storage space and performance in certain scenarios.

Syntax

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

UUID_SHORT()

The function does not take any parameters. It generates and returns a 64-bit UUID as a hexadecimal string.

Examples

Example 1: Generate a UUID_SHORT value

This example demonstrates how to use the UUID_SHORT() function to generate a 64-bit UUID.

SELECT UUID_SHORT();

The following is the output:

+--------------------+
| UUID_SHORT()       |
+--------------------+
| 100747239978172416 |
+--------------------+

This output represents a randomly generated 64-bit UUID in hexadecimal format.

Example 2: Store UUID_SHORT values in a table

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

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

INSERT INTO uuid_table (short_uuid) VALUES (UUID_SHORT()), (UUID_SHORT()), (UUID_SHORT());

SELECT * FROM uuid_table;

The following is the output:

+----+--------------------+
| id | short_uuid         |
+----+--------------------+
|  1 | 100747239978172418 |
|  2 | 100747239978172419 |
|  3 | 100747239978172420 |
+----+--------------------+

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

Example 3: Use UUID_SHORT values as unique identifiers

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

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

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

SELECT * FROM products;

The following is the output:

+--------------------+--------------+---------------+
| product_uuid       | product_name | product_price |
+--------------------+--------------+---------------+
| 100747239978172421 | Product A    |         19.99 |
| 100747239978172422 | Product B    |         29.99 |
| 100747239978172423 | Product C    |         39.99 |
+--------------------+--------------+---------------+

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

Example 4: Compare UUID_SHORT values

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

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

The following is the output:

+--------------------+--------------------+-----------+
| uuid1              | uuid2              | same_uuid |
+--------------------+--------------------+-----------+
| 100747239978172424 | 100747239978172425 |         0 |
+--------------------+--------------------+-----------+

This example generates three UUID_SHORT() 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_SHORT

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

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

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

SELECT * FROM orders;

The following is the output:

+--------------------+---------------+------------+
| order_id           | customer_name | order_date |
+--------------------+---------------+------------+
| 100747239978172429 | John Doe      | 2023-05-01 |
| 100747239978172430 | Jane Smith    | 2023-05-02 |
| 100747239978172431 | Bob Johnson   | 2023-05-03 |
+--------------------+---------------+------------+

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

Related Functions

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

• MariaDB UUID() function is used to generate a standard 128-bit universally unique identifier.

Conclusion

The UUID_SHORT() function in MariaDB is a useful tool for generating unique 64-bit identifiers that can be used in various scenarios, such as primary keys, unique constraints, or other situations where a compact yet 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.