Store UUID data in MySQL in 16 bytes

There are similar stories in various places, but I will write them for my own understanding and memorandum.

About UUID

https://ja.wikipedia.org/wiki/UUID

The original occurrence is seen in Wikipedia etc., but in actual operation it is often used when assigning a unique ID to data at the time of development.

There are multiple versions of UUIDs, the one that seems to be most used in development is ** UUID version 4 (randomly generated) **, and the other ** version 1 (generated using Mac address and Timestamp information). ** and so on.

The bit width is ** 128 bits (16 bytes) **, and it is expressed in hexadecimal notation in the following form in character string representation. (32 bytes + boundary 4 bytes = 36 bytes. "-" Is the boundary character string)

550e8400-e29b-41d4-a716-446655440000

Save to MySQL with 16 bytes using SQL (8.0 and earlier)

When saving in MySQL with 16 bytes, the UUID of the hexadecimal character string representation minus the boundary character string is converted to Binary and saved. Expressed in SQL, it looks like this:

CREATE TABLE `sada ` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `UUID VARBINARY(16) NOT NULL,
  `name` VARCHAR(1024) NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB,
  CHARACTER SET = `utf8mb4`, COLLATE = `utf8mb4_bin`;

INSERT INTO sada (
    uuid,
    name
)
VALUES(
    UNHEX(REPLACE('550e8400-e29b-41d4-a716-446655440000', '-', '')),
    'masashi'
);

Save to MySQL with 16 bytes using SQL (8.0 or later)

Starting with MySQL 8, functions for manipulating UUIDs have been added. If you are using 8 or later, you can use this to get rid of the original processing that makes you feel confused.

https://mysqlserverteam.com/mysql-8-0-uuid-support/

--UUID_TO_BIN (Convert UUID string to Binary) --BIN_TO_UUID (Convert Binary to UUID string)

The example quotes the official blog above.

CREATE TABLE t (id binary(16) PRIMARY KEY);

INSERT INTO t VALUES(UUID_TO_BIN(UUID()));
Query OK, 1 row affected (0,00 sec)

#a few inserts later..

SELECT BIN_TO_UUID(id) FROM t;
+--------------------------------------+
| BIN_TO_UUID(id); |
+--------------------------------------+
| 586bcc2d-9a96-11e6-852c-4439c456d444 |
| 5942e49a-9a96-11e6-852c-4439c456d444 |
| 841ae775-9a96-11e6-852c-4439c456d444 |
| 84393c8e-9a96-11e6-852c-4439c456d444 |
| af0f27e2-9aad-11e6-852c-4439c456d444 |
+--------------------------------------+
5 rows in set (0,00 sec)

Save to MySQL with 16 bytes using Java / Kotlin

From the program, the UUID information generated in each language can be stored in 16 bytes by binary.

Only the Kotlin example is shown here. Since the UUID class exists in Java / Kotlin, it is OK if you create the conversion logic of UUID type-> Byte array and Byte array-> UUID type as follows and incorporate it according to the environment (ORM etc.) to be used. is.

    fun uuidToBytes(uuid: UUID): ByteArray {
        val buffer = ByteBuffer.allocate(16)
        buffer.putLong(uuid.mostSignificantBits)
        buffer.putLong(uuid.leastSignificantBits)
        return buffer.array()
    }

    fun bytesToUuid(bytes: ByteArray?): UUID? {
        if (bytes == null) {
            return null
        }
        val buffer = ByteBuffer.wrap(bytes)
        return UUID(buffer.long, buffer.long)
    }

Appendix. Smaller ID representation

Since the UUID is a fairly large ID representation with 16 bytes, I think there is a way to adopt a larger ID instead of trying to save the size by saving the UUID. The following is for reference only.

[About the lightweight Time-based ID generator "shakeflake (tentative name)"](https://developer.smartnews.com/blog/2013/07/31/shakeflake-is-a-tool-for-generating-unique- id-numbers /)