| Bug #119729 | Add native support for UUID v7 | ||
|---|---|---|---|
| Submitted: | 20 Jan 17:17 | Modified: | 21 Jan 9:27 |
| Reporter: | Vinicius Malvestio Grippa | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[20 Jan 17:17]
Vinicius Malvestio Grippa
[21 Jan 9:27]
Roy Lyseng
Thank you for this useful feature request.
[23 Jan 12:08]
Daniël van Eeden
> Using standard v4 UUIDs as Primary Keys in MySQL is terrible for performance because the randomness causes massive page splitting and fragmentation in the clustered index (B-Tree). The UUID() function in MySQL creates v1 UUIDs, not v4 UUID's. > Please implement a native function, likely UUID_V7(), that generates a UUID string compliant with RFC 9562. Current alternative for this: https://github.com/lefred/mysql-component-uuid_v7 Besides UUID_V7 and possibly other versions as well you may want to include functions to - Extract the timestamp from time based UUID formats - Extract the UUID version from a UUID > Ideally, this should pair well with UUID_TO_BIN so we can easily store them as BINARY(16) for maximum efficiency, preserving the sorting characteristics of the timestamp component. The UUID_TO_BIN() and BIN_TO_UUID() functions should already work with any UUID version. The `swap_flag` option should not be needed anymore. Note that UUID_TO_BIN(UUID(), 0) is UUIDv1: 32 time_low 16 time_mid 16 ver+time_high 64 rest Note that UUID_TO_BIN(UUID, 1) is not RFC compliant: 16 ver+time_high 16 time_mid 32 time_low 64 rest Note that there is a format that is similar to UUID_TO_BIN(UUID, 1), but that is RFC compliant: UUIDv6: 32 time_high 16 time_mid 16 ver+time_low 64 rest The difference is that the location of `ver` is kept the same. So instead of `UUID_TO_BIN(@uuid, 1)` there could be a `UUID_CONVERT(v1, v6, @uuid)` that works the same but where it is always RFC compliant. And ofcourse UUID_v6() as generator function. # UUIDv1 ``` 0 1 2 3 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | time_low | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | time_mid | ver | time_high | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ |var| clock_seq | node | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | node | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ ``` # UUIDv1 with swap_fields=1 (MySQL specific) ``` 0 1 2 3 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | ver | time_high | time_mid | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | time_low | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ |var| clock_seq | node | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | node | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ ``` # UUIDv6 ``` 0 1 2 3 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | time_high | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | time_mid | ver | time_low | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ |var| clock_seq | node | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | node | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ ```
