Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
Summary: this tutorial introduces you to MySQL UUID, shows you to use it as the primary key (PK) for a table, and discusses the pros and cons of using it as the primary key.
Introduction to MySQL UUID
UUID stands for Universally Unique IDentifier. UUID is defined based on RFC 4122, “a Universally Unique Identifier (UUID) URN Namespace).
UUID is designed as a number that is unique globally in space and time. Two UUID values are expected to be distinct, even they are generated on two independent servers.
In MySQL, a UUID value is a 128-bit number represented as a utf8 string of five hexadecimal numbers in the following format:
To generate UUID values, you use the
UUID()
function as follows: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 UUID vs. Auto-Increment INT as primary key
Pros
Using UUID for a primary key brings the following advantages:
Service Primary Key Avoid Auto Generate Money
- UUID values are unique across tables, databases, and even servers that allow you to merge rows from different databases or distribute databases across servers.
- UUID values do not expose the information about your data so they are safer to use in a URL. For example, if a customer with id 10 accesses 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 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 child tables, you have to insert into the parent table first, get generated id and then insert data into the child tables. By using UUID, you can generate the primary key value of the parent table up front and insert rows into both parent and child tables at the same time within a transaction.
Cons
Besides the advantages, UUID values also come with some disadvantages:
- Storing UUID values (16-bytes) takes more storage than integers (4-bytes) or even big integers(8-bytes).
- Debugging seems to be more difficult, imagine the expression
WHERE id = 'df3b7cb7-6a95-11e7-8846-b05adad3f0ae'
instead ofWHERE id = 10
- Using UUID values may cause performance 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 following 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:Tee generate a public key android. To query data from a UUID column, you use
BIN_TO_UUID()
function to convert binary format to human-readable format:In this tutorial, you have learned about MySQL UUID and how to use it for the primary key column.
What is auto increment?
Auto Increment is a function that operates on numeric data types. It automatically generates sequential numeric values every time that a record is inserted into a table for a field defined as auto increment.
When use auto increment?
In the lesson on database normalization, we looked at how data can be stored with minimal redundancy, by storing data into many small tables ,related to each other using primary and foreign keys.
A primary key must be unique as it uniquely identifies a row in a database. But, how can we ensure that the primary key is always unique? One of the possible solutions would be, to use a formula to generate the primary key, which checks for existence of the key, in the table, before adding data. This may work well but as you can see the approach is complex and not foolproof. In order to avoid such complexity and to ensure that the primary key is always unique, we can use MySQL's Auto increment feature to generate primary keys. Auto increment is used with the INT data type. The INT data type supports both signed and unsigned values. Unsigned data types can only contain positive numbers. As a best practice, it is recommended to define the unsigned constraint on the auto increment primary key.
Initial ssh key generation still running. Deploy Server Using Playbook.
Auto increment syntax
Let's now look at the script used to create the movie categories table.
Notice the 'AUTO_INCREMENT' on the category_id field. This causes the category Id to be automatically generated every time a new row is inserted into the table. It is not supplied when inserting data into the table, MySQL generates it.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record
Ec2 generate new key pair windows 10. Let's examine the current contents of the categories table.
Executing the above script in MySQL workbench against the myflixdb gives us the following results.
Let's now insert a new category into the categories table .
Executing the above script against the myflixdb in MySQL workbench gives us the following results shown below.
Note we didn't supply the category id. MySQL automatically generated it for us because the category id is defined as auto increment.
If you want to get the last insert id that was generated by MySQL, you can use the LAST_INSERT_ID function to do that. The script shown below gets the last id that was generated.
Executing the above script gives the last Auto increment number generated by the INSERT query. The results are shown below.
Summary
- Auto increment attribute when specified on a column with a numeric data types, generates numbers sequentially whenever a new row is added into the database.
- The Auto increment is commonly used to generate primary keys.
- The defined data type on the Auto increment should be large enough to accommodate many records. Defining TINYINT as the data type for an auto increment field limits the number of records that can be added to the table to 255 only since any values beyond that would not be accepted by the TINYINT data type.
- It is considered a good practice to specify the unsigned constraint on auto increment primary keys to avoid having negative numbers.
- When a row is deleted from a table, its auto incremented id is not re-used. MySQL continues generating new numbers sequentially.
- By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record
- To let AUTO_INCREMENT sequence start with another value , use AUTO_INCREMENT = 10