top of page
Search

MySQL UUID vs INT for Primary Key

Writer's picture: Team GoplarDBTeam GoplarDB

This blog familiarizes you to MySQL UUID, demonstrations you to use it as the primary key for a table and discusses the pros and cons of with it as the primary key.


Overview to MySQL UUID


UUID stands for Universally Unique IDentifier. UUID is defined built on RFC 4122, “a Universally Unique Identifier (UUID) URN Namespace).


UUID is considered as a number that is unique globally in space and time. Two UUID values are likely to be distinct, even they are created on two independent servers.

In MySQL, a UUID value is a 128-bit number characterized as a utf8 string of five hexadecimal numbers in the subsequent format:


To create UUID values, you use the UUID() function as tracks:


The UUID() function returns a UUID value in agreement with UUID version 1 described in the RFC 4122.


For example, the subsequent statement uses the UUID() function to produce a UUID value:


MySQL UUID vs. Auto-Increment INT as primary key


Pros


Using UUID for a primary key brings the subsequent rewards:


  • UUID values are unique crosswise tables, databases, and even servers that let you to merge rows from changed databases or allocate databases across servers.

  • UUID values do not expose the info about your data so they are harmless to use in a URL. For example, if a customer with id 10 entrees his account via http://www.example.com/customers/10/ URL, it is easy to guess that there is a customer 11, 12, etc., and this could be a target for an attack.

  • UUID values can be created anywhere that evade a round trip to the database server. It also make things easier logic in the application. For example, to insert data into a parent table and child tables, you must insert into the parent table first, get created id and then insert data into the child tables. By using UUID, you can create the primary key value of the parent table up front and insert rows into mutually parent and child tables at the same time within a transaction.


Cons


As well the advantages, UUID values also come with some drawbacks:

  • Storing UUID values (16-bytes) takes more storage than integers (4-bytes) or even big integers(8-bytes).

  • Debugging appears to be more tough, visualize the expression WHERE id = 'df3b7cb7-6a95-11e7-8846-b05adad3f0ae' instead of WHERE id = 10

  • Using UUID values may source routine issues due to their size and not being ordered.


MySQL UUID solution


In MySQL, you can store UUID values in a compact format (BINARY) and display them in human-readable format (VARCHAR) with help of the subsequent functions:


  • UUID_TO_BIN

  • BIN_TO_UUID

  • IS_UUID


Notice that UUID_TO_BIN(), BIN_TO_UUID(), and IS_UUID() functions are only available in MySQL 8.0 or later.


The UUID_TO_BIN() function converts a UUID from a human-readable format (VARCHAR) into a compact format (BINARY) format for storing and the BIN_TO_UUID() function converts UUID from the compact format (BINARY)to human-readable format (VARCHAR) for displaying.


The IS_UUID() function returns 1 if the argument is a valid string-format UUID. If the argument is not valid string format UUID, the IS_UUID function returns 0. In case the argument is NULL, the IS_UUID()function returns NULL.


The following are the valid string-format UUID in MySQL:


MySQL UUID example


Let’s take a look at an example of using UUID as the primary key.


The following statement creates a new table named customers:


To insert UUID values into the id column, you use UUID() and UUID_TO_BIN() functions as follows:


To query data from a UUID column, you use BIN_TO_UUID() function to change binary format to human-readable format:


In this blog, you have learned about MySQL UUID and how to use it for the primary key column.

391 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with...

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values...

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who...

Comments


Call

T: +44 131 538 5542

Follow me

 

© 2020 by GoplarDB 

All Rights Reserved

Powered by: Goplar LTD

  • LinkedIn Social Icon
  • Twitter Clean
bottom of page