mysql8 uuid v7

.gitignore

docker/

compose.yml

version: '3'

services:
  # MySQL
  db:
    container_name: mysql8
    image: mysql:8.0
    command: mysqld --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_bin
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: sandbox
      MYSQL_USER: sandbox
      MYSQL_PASSWORD: passpass
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    ports:
      - '3306:3306'
    volumes:
      - './docker/db/data:/var/lib/mysql'
      - './docker/db/my.cnf:/etc/mysql/conf.d/my.cnf'
      - './docker/db/sql:/docker-entrypoint-initdb.d'

test.sql

create table
    `users` (
        `user_id` binary(16) not null,
        `user_id_string` char(36) as (bin_to_uuid (`user_id`, true)) virtual,
        `name` varchar(255),
        primary key (`user_id`)
    ) engine = innodb default charset = utf8mb4 collate = utf8mb4_bin;

insert into
    users (`user_id`, `name`)
values
    (uuid_to_bin (uuid ()), 'Kevin'),
    (uuid_to_bin (uuid ()), 'John');

test.txt

$ mysql -h localhost -P 3306 --protocol=tcp -u sandbox -p

mysql> select * from users;
+------------------------------------+-------+
| user_id                            | name  |
+------------------------------------+-------+
| 0x2ECB19F214E611EF80E10242C0A85002 | Kevin |
| 0x2ECB1DE214E611EF80E10242C0A85002 | John  |
+------------------------------------+-------+
2 rows in set (0.00 sec)

mysql> select bin_to_uuid(user_id, true) as uuid, name from users;
+--------------------------------------+-------+
| uuid                                 | name  |
+--------------------------------------+-------+
| 14e611ef-19f2-2ecb-80e1-0242c0a85002 | Kevin |
| 14e611ef-1de2-2ecb-80e1-0242c0a85002 | John  |
+--------------------------------------+-------+
2 rows in set (0.00 sec)

mysql> select * from users;
+------------------------------------+--------------------------------------+-------+
| user_id                            | user_id_string                       | name  |
+------------------------------------+--------------------------------------+-------+
| 0x09FF4B0514F511EFA8AD0242C0A89002 | 14f511ef-4b05-09ff-a8ad-0242c0a89002 | Kevin |
| 0x09FF4E9014F511EFA8AD0242C0A89002 | 14f511ef-4e90-09ff-a8ad-0242c0a89002 | John  |
+------------------------------------+--------------------------------------+-------+
2 rows in set (0.00 sec)