How to generate a short 64-bit UUID (Universal Unique IDentifier) integer using the UUID_SHORT() function?

UUID_SHORT() is a MySQL built-in function that returns a short 64-bit UUID (Universal Unique IDentifier) integer. For example:

SELECT UUID_SHORT(), UUID_SHORT();
  -- +--------------------+--------------------+
  -- | UUID_SHORT()       | UUID_SHORT()       |
  -- +--------------------+--------------------+
  -- | 100608899635216387 | 100608899635216388 |
  -- +--------------------+--------------------+

SELECT UUID_SHORT(), SLEEP(3), UUID_SHORT();
  -- +--------------------+----------+--------------------+
  -- | UUID_SHORT()       | SLEEP(3) | UUID_SHORT()       |
  -- +--------------------+----------+--------------------+
  -- | 100608899635216389 |        0 | 100608899635216390 |
  -- +--------------------+----------+--------------------+

Reference information of the UUID_SHORT() function:

UUID_SHORT(): uuid
  Returns a 64-bit UUID integer based on system id and server startup time.

Arguments, return value and availability:
  uuid: Return value. The generated UUID.
  Available since MySQL 5.7.

UUID_SHORT() function uses the following expression to generate the 64-bit UUID integer:

  (server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;

So the 64-bit UUID integer consists of 3 parts:

• Part 1: server_id in 8 bits.
• Part 2: server_startup_time_in_seconds in 32 bits.
• Part 3: sequence_number in 24 bits.

We can see these parts, if we convert the UUID integer into a Hexadecimal string:

SELECT HEX(UUID_SHORT()), HEX(UUID_SHORT());
  -- +-------------------+-------------------+
  -- | HEX(UUID_SHORT()) | HEX(UUID_SHORT()) |
  -- +-------------------+-------------------+
  -- | 1656F42DC000008   | 1656F42DC000009   |
  -- +-------------------+-------------------+

⇒ UUID_TO_BIN() - Converting UUID to Binary

⇐ UUID() - Generating UUID String

⇑ MySQL Functions on UUID Values

⇑⇑ MySQL Function References