How To Save Uuid In Mysql Using Rest Services

Summary: this tutorial introduces you to MySQL UUID, shows y'all to use information technology as the primary key (PK) for a table, and discusses the pros and cons of using it as the master key.

Introduction to MySQL UUID

UUID stands for Universally Unique IDentifier. UUID is divers based on RFC 4122, "a Universally Unique Identifier (UUID) URN Namespace).

UUID is designed as a number that is unique globally in space and fourth dimension. Ii UUID values are expected to be distinct, even they are generated on 2 independent servers.

In MySQL, a UUID value is a 128-bit number represented equally a utf8 string of five hexadecimal numbers in the post-obit format:

            
              aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee                          
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

To generate UUID values, yous use the UUID() function equally follows:

            
              UUID()                          
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )                      

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

For example, the following statement uses the UUID() function to generate a UUID value:

            
              mysql>                SELECT                UUID(); +--------------------------------------+                | uuid()                               | +--------------------------------------+                | 865234ad-6a92-11e7-8846-b05adad3f0ae | +--------------------------------------+                1 row in                set                (0.05                sec)                          
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )                      

MySQL UUID vs. Auto-Increment INT as primary key

Pros

Using UUID for a primary key  brings the following advantages:

• UUID values are unique across tables, databases, and even servers that let you to merge rows from different databases or distribute databases across servers.
• UUID values do not betrayal the information about your data and then they are safer to use in a URL. For example, if a customer with id 10 accesses his account via http://world wide web.example.com/customers/10/ URL, information technology is easy to guess that in that location is a customer eleven, 12, etc., and this could be a target for an attack.
• UUID values tin can be generated anywhere that avoid a round trip to the database server. It also simplifies logic in the application. For example, to insert data into a parent table and kid tables, yous have to insert into the parent table commencement, go generated id and then insert data into the child tables. By using UUID, you tin can generate the master key value of the parent tabular array upward front and insert rows into both parent and child tables at the same time within a transaction.

Cons

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

• Storing UUID values (16-bytes) takes more than storage than integers (four-bytes) or even big integers(8-bytes).
• Debugging seems to be  more difficult, imagine the expression WHERE id = 'df3b7cb7-6a95-11e7-8846-b05adad3f0ae' instead of WHERE id = 10
• Using UUID values may cause operation issues due to their size and non being ordered.

MySQL UUID solution

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

• UUID_TO_BIN
• BIN_TO_UUID
• IS_UUID

Notice that UUID_TO_BIN(), BIN_TO_UUID(), and IS_UUID() functions are only bachelor in MySQL viii.0 or later.

The UUID_TO_BIN() function converts a UUID from a homo-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() office returns one if the argument is a valid string-format UUID. If the statement is not valid string format UUID, the IS_UUID function returns 0. In case the statement is Zippo, theIS_UUID() function returns Goose egg.

The post-obit are the valid string-format UUID in MySQL:

            
              aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee aaaaaaaabbbbccccddddeeeeeeeeeeee {aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}                          
                          Lawmaking linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )                      

MySQL UUID example

Allow'southward take a look at an example of using UUID as the primary key.

The following statement creates a new table named customers:

            
                              CREATE                TABLE                customers (                id                BINARY(16) PRIMARY                Primal,                name                VARCHAR(255) );                          
                          Code linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )                      

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

            
                              INSERT                INTO                customers(id,                proper noun)                VALUES(UUID_TO_BIN(UUID()),'John Doe'),       (UUID_TO_BIN(UUID()),'Will Smith'),       (UUID_TO_BIN(UUID()),'Mary Jane');                          
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

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

            
                              SELECT                BIN_TO_UUID(id)                id,                proper noun                FROM                customers;                          
                          Lawmaking language:              SQL (Structured Query Language)              (              sql              )                      

In this tutorial, you lot have learned almost MySQL UUID and how to utilise information technology for the principal primal column.

Was this tutorial helpful?

Source: https://www.mysqltutorial.org/mysql-uuid/

Posted by: boydreste1954.blogspot.com

Share this post