MySQL IS_UUID Function – A Complete Guide

In this tutorial, we will study the MySQL IS_UUID() function. In MySQL, UUID is a Universal Unique Identifier which is generated by the RFC 4122 standard, “A Universally Unique IDentifier (UUID) URN Namespace”.

The function IS_UUID() is used to check whether the parameter is valid UUID or not. The function will return 1 if the given parameter is valid string UUID, 0 if the parameter is invalid, and NULL if the parameter is NULL.

Before diving deep into the IS_UUID function, let’s see what actually UUID is. As stated before, it is a universally unique identifier that means the generated string is unique globally by time and space even if the UUID strings are generated on two different servers. Two UUID will never be identical anyhow.

Now, let’s see what is a “valid UUID” in MySQL.

Valid UUID Format in MySQL

A valid UUID in MySQL is a 128-bit long number that is represented as an utf8 string and consists of five hexadecimal numbers separated by dashes. The most common format of a UUID is as follows.

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

However, the following UUID formats are also permitted in MySQL.

aaaaaaaabbbbccccddddeeeeeeeeeeee
{aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}

MySQL IS_UUID() function is only available in MySQL version 8.0 and later.

Syntax for MySQL IS_UUID() Function

Now, let’s see the syntax to check whether or not the given string is a valid UUID.

IS_UUID(string_to_check);Code language: SQL (Structured Query Language) (sql)

Generally, we generate UUID using the function UUID() in MySQL to store it in the database. However, you are free to use create your own as well, but it must be in a valid format.

Examples of MySQL IS_UUID() Function

UUIDs are very useful for uniquely identifying the records in the database. You can use it as a primary key that will help you to achieve normalization in a better way.

You may also use auto-incremented values as a primary key. However, UUID helps you to secure your data.

Let me tell you how. If you are making a request like “/items/1” then the user can easily change the values in the URL and get the data if no extra layer of the session is added as a security practice.

But if you use UUID, it is nearly impossible to guess the item id, which will eventually help you to simplify logic as well.

But we will not be looking at that right now. We will see some examples to check if the given string is a valid UUID.

SELECT IS_UUID("aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee") as is_valid_uuid;Code language: SQL (Structured Query Language) (sql)

Here, we have passed a string that only consists of the characters ‘a’, ‘b’, ‘c’, ‘d’, and ‘e’ which are, after all, valid hexadecimal digits.

Note that, you must write the UUID string in a single quote (‘ ‘) or a double quote(” “), else you will get an error.

As discussed above, there can be other two formats also for a valid UUID. We will check those as well.

SELECT IS_UUID("aaaaaaaabbbbccccddddeeeeeeeeeeee") as is_valid_uuid;Code language: SQL (Structured Query Language) (sql)

As you can see, the UUID string without dashes is also permitted in MySQL. Let’s check the third format of UUID.

SELECT IS_UUID("{aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}") as is_valid_uuid;Code language: SQL (Structured Query Language) (sql)

As expected, it is also a valid UUID.

Now, let’s change some digits in the above UUID and then check if it returns true or false.

Note that, we are only changing the digits in the UUID and not the “number of digits” in the UUID. In the next example, we will check if the number is a valid UUID by altering the sequence of a set of hexadecimal digits.

SELECT IS_UUID("{aaaaaaaa-zzzz-cccc-dddd-eeeeeeeeeeee}") as is_valid_uuid;Code language: SQL (Structured Query Language) (sql)

As you can see, it returns 0 because ‘z’ is not a valid hexadecimal character even though the sequence of characters/digits is exactly correct.

Now, let’s change the sequence of a set of digits and check what will be the result.

SELECT IS_UUID("bbbb-aaaaaaaa-cccc-dddd-eeeeeeeeeeee") as is_valid_uuid;Code language: SQL (Structured Query Language) (sql)

It returns 0 which is obvious because it is not the correct format for UUID.

Conclusion

Even though you can specify your own UUID in the program, it is not recommended. MySQL offers you the UUID() function to generate a unique ID that you can use in your application. Whether you are using a single database or multiple databases, UUID simplifies your workflow.

You are encouraged to play around with UUID functions like UUID(), IS_UUID(), UUID_TO_BIN(), BIN_TO_UUID() to make your database operations simple. See you in the next interesting tutorial!