How Mysql stores a unique identifier UUID

Typically, a UUID is used as a unique identifier and needs to be stored in the database.

Format of UUID

[Java]

String string = Uuid.randomuuid (). toString ();
System.out.println ("UUID:" + string);

Uuid:05ba463f-1dab-471f-81c7-58e0b06f35f0

Disadvantages of directly storing UUID in a database:

A completely ' random ' string, for example, generated by MD5 (), SHA1 (), UUID (). Each new value they produce is arbitrarily kept in a large space, which slows inserts and some select queries. 1 They slow down the insert query because the inserted values are randomly placed in the index. This results in paging, random disk access, and clustered index fragmentation on the storage engine. 2 They slow down the select query because logically adjacent rows are distributed around the disk and in memory. 3 random values cause caching to be poor for all types of query performance because they invalidate the access locality that the cache relies on for work. If the entire dataset becomes equally "hot", there is no advantage in caching a particular part of the data into memory. And if the working set cannot be loaded into memory, the cache does a lot of brush-writing work and can cause many cache misses.

If you save the UUID value, you should remove the dash, preferably by using Uhex () to convert the UUID value to a 16-byte number and save it in the binary (16) column.

[SQL]

DELIMITER $$
CREATE FUNCTION ' Guidtobinary ' (
$Data VARCHAR (36)
) RETURNS Binary (16)
BEGIN
DECLARE $Result BINARY DEFAULT NULL;
IF $Data is not NULL THEN
SET $Data = REPLACE ($Data, '-', ");
SET $Result = CONCAT (Unhex (SUBSTRING ($Data, 7,2)), Unhex (SUBSTRING ($Data, 5,2)), Unhex (SUBSTRING ($Data, 3,2)), Unhex ( SUBSTRING ($Data, 1,2)),
Unhex (SUBSTRING ($Data, 11,2)), Unhex (SUBSTRING ($Data, 9,2)), Unhex (SUBSTRING ($Data, 15,2)), Unhex (SUBSTRING ($Data, 13,2)),
Unhex (SUBSTRING ($Data, 17,16));
End IF;
return $Result;
End
$$
CREATE FUNCTION ' Toguid ' (
$Data BINARY (16)
) RETURNS char () CHARSET UTF8
BEGIN
DECLARE $Result CHAR () DEFAULT NULL;
IF $Data is not NULL THEN
SET $Result = CONCAT (HEX (SUBSTRING ($Data, 4,1)), HEX (SUBSTRING ($Data, 3,1)), HEX (SUBSTRING ($Data, 2,1)), HEX (SUBSTRING ( $Data, 1, 1)), '-',
HEX (SUBSTRING ($Data, 6,1)), HEX (SUBSTRING ($Data, 5, 1)), '-',
HEX (SUBSTRING ($Data, 8,1)), HEX (SUBSTRING ($Data, 7, 1)), '-',
HEX (SUBSTRING ($Data, 9,2)), '-', HEX (SUBSTRING ($Data, 11,6)));
End IF;
return $Result;
End

[SQL]

CREATE FUNCTION ' Uuidtobin ' () RETURNS binary (16)
BEGIN
DECLARE My_uuid Char (36);
SET my_uuid = uuid ();
Return CONCAT (Unhex (my_uuid,8), Unhex (Mid (my_uuid,10,4)), Unhex (Mid (my_uuid,15,4)), Unhex (Mid (my_uuid,20,4)) , Unhex (Right (my_uuid,12));
End
CREATE FUNCTION ' Bintouuid ' (UUID BINARY ()) RETURNS char (36)
BEGIN
Return CONCAT (HEX (uuid,4), '-', HEX (Mid (uuid,5,2)), '-', HEX (Mid (uuid,7,2)), '-', HEX (Mid (uuid,9,2)), '-', HEX ( Right (uuid,6));
End