In this article, we will study the MySQL UUID_SHORT() function with its syntax, examples, and advantages over the UUID() function. We will also be learning how the short UUID is created, the logic behind it, and the alternative methods to the UUID_SHORT().
What is UUID_SHORT() Function?
As the name suggests, UUID_SHORT() is a MySQL function to generate a unique short version of a UUID. The produced short UUID is a 64-bit unsigned integer and does not contain any hexadecimal characters like a string format 128-bit identifier produced by UUID() function.
The UUID_SHORT() generates a pure integer string without hex characters and dashes. Like UUID() function, the UUID_SHORT() also generates a unique identifier, but it’s an auto-incrementing identifier, unlike the 128-bit UUID.
The short UUID is composed of the three components-
- server_id – server id of your server, it’s unique by default for all your server replicas and source.
- server startup time in seconds – The time in seconds when your server starts/boots
- incremented variable – It’s a 24 bit sequentially increasing integer number.
Note that the UUID_SHORT() generates a unique identifier as long as you don’t-
- Produce more than 16 million IDs on same server per second.
- Boot the servers with the same server_id at the same time and share data across them.
- Update the system clock and reboot the server.
The second issue is unexpected in most cases, but the first one should be taken into consideration. If you are in a situation where you need to generate more than 16 million unique IDs per second, we would recommend you to go with 128-bit UUID.
Again, the UUID_SHORT() does not provide uniqueness in space if you have more than 256 servers. The server_id in the UUID_SHORT() is only a 1 byte/8bit. So, if you have more than 256 servers, chances are few of them share the same node id, which will eventually fail in the space uniqueness. On the other hand, the server_id in the version 1 UUID (generated by UUID() function) is 6 bytes long so, no need to worry about server_id duplication even for large corporations.
Syntax of MySQL UUID_SHORT() Function
The syntax of UUID_SHORT() is identical to the UUID() function. Both can be used in the same way.
UUID_SHORT();Code language: SQL (Structured Query Language) (sql)Note-The UUID_SHORT() function accepts no parameter.
Examples of MySQL UUID_SHORT() Function
The short UUIDs are different from the 128-bit UUIDs. The generated short UUID is a pure integer, is 17-digit long, and consists neither of any hex values nor dashes.
You can use the short UUID as a primary key in your databases. A short UUID is the best option to use as a primary key if you are using distributed databases. It is auto-incremented, will be unique across all the databases, and will save storage space.
Now, let’s generate some short UUIDs using the UUID_SHORT() function.
SELECT UUID_SHORT();Code language: SQL (Structured Query Language) (sql)
Now, again generate the short UUID. It should be auto-incremented by +1 than the previous UUID.
SELECT UUID_SHORT();Code language: SQL (Structured Query Language) (sql)
Here, you can see the short UUID is incremented by +1.
Now, let’s restart the server and generate a short UUID to check if we get a different short UUID than the previous one.
SELECT UUID_SHORT();Code language: SQL (Structured Query Language) (sql)
Here, you can observe the difference between the previous UUID and the new UUID. Because we have rebooted the server, the resulting UUID is different. However, the auto-incrementing value is still in the same route.
Alternative Approach
UUID is not the only option to set a primary key in large projects. Apart from UUID or SHORT_UUID, you can manually get non-repeating and unique space and time values.
The approach is simple. You can create a compound primary key as primary_key(id,server_id) where the id is auto-incremented, and the server_id is your server id which should be unique if you have multiple servers. This will result in unique values across the databases and help you achieve uniqueness without using UUID functions.
Conclusion
We have learned what the UUID_SHORT() function is, how it is different from the 128-bit long string UUID generated by UUID() function, how to create it, in what conditions it fails, and the alternative approach of it. It is a straightforward function and easy to implement in your project. However, you are free to use your own approach as well! See you in the next tutorial!
References
MySQL official documentation for UUID_SHORT() function.
Stackoverflow thread for UUID and UUID_SHORT comparison.