UUID() function in MySQL

In MySQL, the UUID() function is used to generate a Universal Unique Identifier (UUID), which is a 128-bit value that is globally unique. This function adheres to the RFC 4122 specification for creating universally unique identifiers. The generated UUID is especially useful for distributed systems, where the need for unique values across multiple servers or instances is crucial.

In this article, we will explain the UUID() function in MySQL, how it generates globally unique identifiers, and its common use cases in database design. We will also provide detailed examples of how to implement UUIDs as primary keys and discuss best practices for using them efficiently in our projects.

UUID() function in MySQL

A UUID (Universal Unique Identifier) is a standardized 128-bit identifier that is unique across both space and time. It is formatted as a 32-character hexadecimal string divided into five sections by hyphens. Here’s the format of a UUID

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

Where the characters represent hexadecimal digits, and the UUID is designed to be globally unique. It is used in various systems to uniquely identify objects, transactions, records, and more, regardless of the context or server generating the UUID. The basic syntax of the UUID() function is:

Syntax

UUID()

Key Terms

• Parameter: The UUID() function does not take any parameters.
• Returns: It returns a string of 36 characters, which represents the generated UUID in the standard format.

How UUID Works in MySQL

In MySQL, the UUID() function generates a universally unique identifier (UUID) based on a combination of factors that ensure the value is distinct across systems and time. Here’s how it works:

1. Timestamp:

• The UUID is partially based on the current time (in UTC) when the UUID is generated. The timestamp ensures temporal uniqueness, meaning that the UUID will be unique to the exact moment it is created. This helps avoid collisions when UUIDs are generated on different servers at the same time.

2. Hardware Information:

• To ensure spatial uniqueness, the UUID also incorporates hardware information, such as the MAC address of the machine generating the UUID. This guarantees that even if two systems generate UUIDs simultaneously, they will be distinct due to their unique hardware identifiers.

This combination of timestamp and hardware information helps ensure that the UUID generated is globally unique, even across different systems, and over time. In MySQL, this 128-bit value is typically represented in hexadecimal format as a string like xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.

Example:

e762634c-3e41-11eb-b897-0862660ccbd4

This structure of the UUID is designed to prevent clashes both across different instances and over time, making it suitable for use as a primary key in distributed databases or when you need to guarantee uniqueness across different servers or locations.

Example 1: Generating UUID in MySQL

Generating a Universal Unique Identifier value with the help of UUID Function. Every time you call the UUID() function, it will return a new, unique UUID. To generate a UUID in MySQL, you can run the following query:

Query:

SELECT UUID() 
AS UUID_Value ;

Output :

Example 2: Generating Multiple UUIDs

Whenever we will use UUID function we will get different Universal Unique Identifier value. Notice that each UUID is different, even if the queries are executed in quick succession. So, you can generate multiple UUIDs in a single query. Here’s an example:

Query:

SELECT UUID() AS UUID_Value1,
       UUID() AS UUID_Value2,
       UUID() AS UUID_Value3;

Output :

Example 3: Using UUID as a Primary Key in MySQL

A common use case for UUIDs is as a primary key in database tables. Using UUIDs as primary keys allows for distributed systems to generate unique identifiers without conflict. However, because UUIDs are larger than typical integers, they may have some performance overhead.

Creating a Table with UUID as Primary Key

Here’s how you can use UUID as a primary key in MySQL. To demonstrate this create a table named OrderDetails.

CREATE TABLE OrderDetails(
   OrderId BINARY(16) PRIMARY KEY,
   ProductName VARCHAR(100) NOT NULL,
   Price DECIMAL(10, 2) NOT NULL,
   ExpectedDelivery DATE NOT NULL
);

Here, OrderId is a primary key column, and the BINARY(16) type stores the UUID in its binary format. This format is more efficient for storage and indexing compared to the string format.

Inserting Data with UUID

You can insert data into the OrderDetails table using the UUID() function along with the UUID_TO_BIN() function, which converts the UUID string to its binary format:

INSERT INTO OrderDetails(OrderId, ProductName, Price, ExpectedDelivery)
VALUES(UUID_TO_BIN(UUID()), 'Asus Rog', 90000.00, '2020-12-20'),
      (UUID_TO_BIN(UUID()), 'Acer Predator', 100000.00, '2020-12-18'),
      (UUID_TO_BIN(UUID()), 'Lenovo Legion', 85000.00, '2020-12-19'),
      (UUID_TO_BIN(UUID()), 'Hp Omen', 70000.00, '2020-12-18'),
      (UUID_TO_BIN(UUID()), 'Dell Inspiron', 65000.00, '2020-12-23'),
      (UUID_TO_BIN(UUID()), 'Acer Nitro', 60000.00, '2020-12-22'),
      (UUID_TO_BIN(UUID()), 'Asus Tuf', 80000.00, '2020-12-19');

Checking the Data

Next we will use the following command to check the table.

SELECT  * from OrderDetails;

Output :

Why Use UUIDs in MySQL?

• Global Uniqueness: UUIDs are globally unique, which means they can be used across different systems and databases without the risk of duplication.
• Distributed Systems: Ideal for distributed applications where different nodes may be generating unique identifiers.
• No Central Authority: UUIDs do not require a central authority to assign unique IDs, unlike auto-increment values.
• Scalability: With UUIDs, you can scale your application horizontally (adding more servers) without worrying about ID conflicts.

Best Practices for Using UUID in MySQL

1. Use BINARY(16) for Storage: Storing UUIDs in their binary format (BINARY(16)) is more efficient than storing them as strings (CHAR(36) or VARCHAR).
2. Indexing: UUIDs, being random, can lead to inefficient indexing if used as primary keys in large tables. If performance becomes an issue, consider using a UUID generation strategy that introduces some degree of sequentiality.
3. Avoid Overusing UUIDs as Primary Keys: While UUIDs offer unique identifiers, they are larger than integers and may impact performance, especially in large databases. Consider using UUIDs for unique records where global uniqueness is necessary, but for local, sequential data, use integer-based auto-increment keys.

Conclusion

The UUID() function in MySQL provides an easy way to generate globally unique identifiers that can be used for various purposes in distributed systems, databases, and applications. Whether you’re generating identifiers for new records, using them as primary keys, or ensuring uniqueness across systems, UUID() is a valuable tool in MySQL. By understanding the syntax, application, and best practices of UUIDs, you can use this powerful feature to enhance our MySQL-based projects.

jana_sayantan

Follow

Improve

Article Tags :

• Databases
• SQL
• DBMS-SQL
• mysql