MySQL's UUID() function generates v1 UUIDs , which are time-based, but not time-ordered. We can define our own function to generate v7 UUIDs , which are more appropriate for primary keys.
Check out "Why You Should Use UUIDs for Your Primary Keys" for a reason to use UUIDs in databases, and "Generating v4 UUIDs in MySQL" for a fully random alternative.
Why You Should Use UUIDs for Your Primary Keys
Jan 30, 2020 · 4 min read
Universally unique identifiers (UUIDs) are 128-bit (16-byte) numbers that are designed to be globally unique, and as a result they make for great primary keys.
Generating v4 UUIDs in MySQL
Nov 13, 2020 · 4 min read
MySQL's UUID() function generates v1 UUIDs, which have a time component that make them unevenly distributed over short periods of time. We can define our own function to generate v4 UUIDs, which are random and therefore more evenly distributed.
A custom uuid_v7() function
Here's the function, with comments explaining each group:
1CREATE FUNCTION uuid_v7() RETURNS CHAR(36)
2 NOT DETERMINISTIC
3 NO SQL
4BEGIN
5 -- 10 random bytes = 20 random hex characters, generated all at once
6 DECLARE rnd CHAR(20) DEFAULT HEX(RANDOM_BYTES(10));
7
8 -- 48-bit Unix timestamp in milliseconds = 6 bytes
9 DECLARE ts CHAR(12) DEFAULT LPAD(HEX(FLOOR(UNIX_TIMESTAMP(SYSDATE(3)) * 1000)), 12, '0');
10
11 -- 1st group is 8 characters = 4 bytes, the first part of the timestamp
12 DECLARE g1 CHAR(8) DEFAULT SUBSTR(ts, 1, 8);
13
14 -- 2nd group is 4 characters = 2 bytes, the rest of the timestamp
15 DECLARE g2 CHAR(4) DEFAULT SUBSTR(ts, 9, 4);
16
17 -- 3rd group is 4 characters = 2 bytes, starting with a: '7'
18 DECLARE g3 CHAR(4) DEFAULT CONCAT('7', SUBSTR(rnd, 1, 3));
19
20 -- 4th group is 4 characters = 2 bytes, starting with a: '8', '9', 'A', or 'B'
21 DECLARE g4 CHAR(4) DEFAULT CONCAT(HEX(CONV(SUBSTR(rnd, 4, 1), 16, 10) & 3 | 8), SUBSTR(rnd, 5, 3));
22
23 -- 5th group is 12 characters = 6 bytes
24 DECLARE g5 CHAR(12) DEFAULT SUBSTR(rnd, 8, 12);
25
26 RETURN LOWER(CONCAT(g1, '-', g2, '-', g3, '-', g4, '-', g5));
27END;
This generates all the random bytes with a single call to RANDOM_BYTES(), rather than one call per group, and then splits them up. It uses local variables , which are cheaper than @ user-defined variables , and they don't leak into the caller's session.
Here's a version with only one variable, to remove any overhead the others might add. It uses INSERT() to add the first hyphen into the timestamp, so that the timestamp only has to be calculated once:
1CREATE FUNCTION uuid_v7() RETURNS CHAR(36)
2 NOT DETERMINISTIC
3 NO SQL
4BEGIN
5 DECLARE rnd CHAR(20) DEFAULT HEX(RANDOM_BYTES(10));
6 RETURN LOWER(CONCAT(
7 INSERT(LPAD(HEX(FLOOR(UNIX_TIMESTAMP(SYSDATE(3)) * 1000)), 12, '0'), 9, 0, '-'),
8 '-7', SUBSTR(rnd, 1, 3),
9 '-', HEX(CONV(SUBSTR(rnd, 4, 1), 16, 10) & 3 | 8), SUBSTR(rnd, 5, 3),
10 '-', SUBSTR(rnd, 8, 12)
11 ));
12END;
A few notes on the timestamp:
- This uses
SYSDATE()instead ofNOW()becauseNOW()returns the time the statement started, so every row in a multi-rowINSERTwould get the same timestamp.SYSDATE()returns the time the function actually executes. - The
3passed toSYSDATE(3)requests millisecond precision, whichUNIX_TIMESTAMP()preserves as a decimal. Multiplying by 1,000 and flooring gives an integer number of milliseconds.
MariaDB doesn't have RANDOM_BYTES(), but MariaDB v11.7.0 (2024) added a native UUID_v7() function, so you don't need any of this.
Putting it all together
Here's an example where a table users uses UUIDs for its primary key. Using CHAR(36) simplifies this example, but see "Making UUIDs More Performant in MySQL" for why you don't want to use strings for performance reasons. Note that because v7 UUIDs are already time-ordered, you don't want to use the swap_flag option of UUID_TO_BIN() with them, as that's specifically for rearranging v1 UUIDs.
1CREATE TABLE users
2(
3 id CHAR(36) NOT NULL,
4 name VARCHAR(255) NOT NULL,
5 PRIMARY KEY (id)
6);
7
8INSERT INTO users (id, name) VALUE (uuid_v7(), 'Tanya Reilly');
9INSERT INTO users (id, name) VALUE (uuid_v7(), 'Charity Majors');
10INSERT INTO users (id, name) VALUE (uuid_v7(), 'Camille Fournier');
11
12SELECT *
13FROM users
14ORDER BY id;
Making UUIDs More Performant in MySQL
Nov 14, 2020 · 3 min read
MySQL does not have a native UUID type, so strings are often used instead, which come with large storage and performance costs.
If everything went well, at the end you should have table contents similar to the following, sorted in the order the rows were inserted:
1+--------------------------------------+------------------+
2| id | name |
3+--------------------------------------+------------------+
4| 01a1c3c4-b3e4-7c2e-9d4f-3b8e1f6a2c07 | Tanya Reilly |
5| 01a1c3c4-b3e4-74b1-a6e2-c90d7f15e8b3 | Charity Majors |
6| 01a1c3c4-b3e4-7f08-8b71-52e4ad9c06f1 | Camille Fournier |
7+--------------------------------------+------------------+
Why choose v7?
InnoDB is the default storage engine for MySQL, so it's probably what your tables are using. InnoDB uses a clustered index , which means data is stored on disk in primary key order. Most tables use a monotonically increasing AUTO_INCREMENT primary key, causing INSERTs to always append to the end of the clustered index.
v4 UUIDs are completely random, which makes them a poor choice for clustered indexes because INSERTs will land on random pages, which:
- Has a high chance of causing page splits and therefore fragmentation because of the default
innodb_fill_factorsetting of100(%). - Has a high chance of buffer pool misses, requiring data to be read from disk.
Generating v4 UUIDs in MySQL
Nov 13, 2020 · 4 min read
MySQL's UUID() function generates v1 UUIDs, which have a time component that make them unevenly distributed over short periods of time. We can define our own function to generate v4 UUIDs, which are random and therefore more evenly distributed.
v7 UUIDs alleviate these issues by being time-ordered (though not strictly monotonically increasing). v7 UUIDs are only required to have millisecond precision, though, so not every INSERT is guaranteed to be an append if there are multiple at the same time.
