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.
UUID stands for Universally Unique IDentifier. UUID is defined based on RFC 4122, “a Universally Unique Identifier (UUID) URN Namespace).
Dec 24, 2016 AUTOINCREMENT has to be used on a primary key or a unique index. It makes sense to make the primary key column the AUTOINCREMENT column, but you can create any other column as the AUTOINCREMENT. Mysql Auto generated Key Value Executes the below program, Which insert the record in the trnemployee table. After record insertion it return the auto generated primary key value getGeneratedKeys method returns the ResultSet object and from that we retrive the primary key number. To get the generated key from a MySQL database table, just use the MySQL LASTINSERTID function, calling it as shown below, immediately after performing your INSERT command (and very importantly, also using the same connection): SELECT LASTINSERTID; Here's a blurb from this MySQL page. LASTINSERTID (with no argument) returns the first automatically generated value that was set for an AUTOINCREMENT column by the most recently executed INSERT statement to affect such a column.
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:
Using UUID for a primary key brings the following advantages:
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.Besides the advantages, UUID values also come with some disadvantages:
WHERE id = 'df3b7cb7-6a95-11e7-8846-b05adad3f0ae'
instead of WHERE id = 10
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: Watch dogs 2 license key generator browser.
UUID_TO_BIN
BIN_TO_UUID
IS_UUID
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:
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 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.