Last year a friend who runs a mini-program storefront asked me to look at his admin backend, where order numbers were auto-incrementing integers. A customer had complained in the support group: the previous order was 100231, this one 100237, and after checking a few orders from acquaintances, roughly how many orders the shop takes per day was laid out in plain sight. The owner brought a screenshot and asked whether the public-facing number could be replaced with characters that reveal nothing. This is not encryption but opacity: as long as the number rises monotonically, the difference is a public statistic.
The usual replacement is a random string, most often a UUID. In that project I did not migrate the whole table in the end; I touched only one public-facing column, for the three reasons below.
1. Structure: 6 of the 128 bits are fixed
A UUID is a 128-bit identifier written in 8-4-4-4-12 groups, 32 hexadecimal characters plus 4 hyphens:
f3c1a072-8ad4-4b6e-91cf-0d5a2e6b7c10With all 128 bits in play the space is 2 to the 128th, about 3.4 x 10 to the 38th. But v4 does not have 128 random bits; it has 122. The 13th character counting from the left, the first character of the third group, is hard-coded to 4, since the version field takes 4 binary bits. The 17th character, the first of the fourth group, can only be 8, 9, a or b, because the two high bits of the variant field are fixed at 10, taking 2 bits. 128 minus 4 minus 2 is what is actually random.
Calling it 128 fully random bits is the most common mistake: the space is overstated by 64 times, since 2 to the 128th divided by 2 to the 122nd is exactly 2 to the 6th, and every probability estimate after that drifts with it.
2. Collision probability can only be estimated with the birthday bound
The question is not whether the space is large, but how likely a pair of duplicates is after generating n values. The measure to use is the birthday bound.
N = 2^122 = 5316911983139663491615228241121378304
p = 1 - exp( -n*(n-1) / (2*N) )
when n is far smaller than N: p ≈ n*n / (2*N)For N, evaluate pow(2,122) with integer arithmetic; floating point will alter the mantissa, and the 37-digit number above is the exact value. Solving for p = 0.5 needs about 1.1774 times the square root of N, that is 1.1774 times 2 to the 61st (2305843009213693952), which gives 2714899559048203264 values, an order of 2.7 x 10 to the 18th. The version that circulates says a few billion, off by ten orders of magnitude.
Check it against an extreme case: 1 billion per second for a full 100 years (365 days a year, 31536000 seconds) is 3153600000000000000 values, 16% above the threshold, and the same formula puts the probability near 61%. So the idea that many years of generation almost never repeats is backwards: it reaches a coin flip, not zero.
The scale I actually meet is at the other end: loading 10 million rows at once means n is 10 to the 7th, p is about 9.4 x 10 to the -24, and treating that as something that will not happen is fine. But state the range clearly: 10 to the 7th is safe, 10 to the 18th is a different story, and the two should not share one sentence.
3. Three costs of switching to a random primary key
The first is index writes. An auto-increment key always lands at the far right of the B+ tree, while a random key lands wherever those 122 bits point, so page splits are more frequent and cache hit rates fall. Consensus stops about there: random keys are usually slower than auto-increment and cause more write amplification. Anyone who claims 3 times slower or an index 4 times larger should show the schema, the database version and the benchmark.
The second is bytes. A v4 value as a string is 36 bytes, and as BINARY(16) it is 16 bytes. On his 200-million-row order table: a string key is 7200000000 bytes and a binary key is 3200000000 bytes, a 4 GB difference in one column. InnoDB also copies the key value into the leaf of every secondary index, so 4 secondary indexes mean 5 copies of the key: about 36 GB at 36 bytes and about 16 GB at 16 bytes. If the column is declared CHAR(36) with utf8mb4, fixed length counted at up to 4 bytes per character, 200 million rows come to 28.8 GB.
The third is friction for people and systems. A 36-character value cannot be read out over the phone, and two or three of them fill a log screen; a random string carries no time meaning, so the last 10 orders have to be sorted by created_at, and the key order should not be expected to mean anything.
| Item | UUID v4 (random) | UUID v1 (timestamp) | Auto-increment ID |
|---|---|---|---|
| Where the bits come from | 122 random bits, 6 fixed | Timestamp plus node identifier | A counter in the table |
| Shape of the key on insert | Random placement, more page splits | Leading part advances with time | Appended in order |
| Bytes per value | 36 characters, or 16 binary | Same as above | 4 or 8 bytes |
| What a stranger can infer | Neither scale nor time | Possibly the generation time | Subtract and you know the order volume |
| Is the order usable | Not readable | Not guaranteed monotonic across machines | Strictly increasing |
| This site's tool | Generates directly | Simulated in the browser, no real MAC | Not applicable |
4. The tool is only one link in the chain
Once v4 is decided, the first job is loading test data. Day to day I use UUID Generator: it produces v4 (random) or v1 (timestamp), 1 to 100 values per batch, with format options for upper or lower case, hyphens and braces, and you can copy one value or all of them; 5000 rows means 50 runs. When the other side only accepts the braced uppercase GUID form, those three format options matter more than the version.
Paste 100 ID lines into Line Operations, add the same prefix and suffix to each line, and you have every line of an INSERT statement. The trouble is never the generation but the dozens of string operations after it.
I have also used it as an idempotency key: the client reuses one key for the same submission, the server records the keys it has seen, and a repeat submission is not executed twice. That guards against duplicate submits, not against concurrency bugs: if two requests really do update the same row at once, you still need a lock or a version column.
One thing has to be said about v1: this site's v1 is simulated entirely in the front end and never reads a real MAC address, so it has none of the traceability a real MAC-derived v1 offers. It is not the same thing as a library v1 that uses the network card address as the node identifier. If you need to trace a value back to the machine that produced it, use a library implementation.
5. The questions I get asked most
Q: Are there 122 random bits or 128?
122. The 4 version bits and 2 variant bits are fixed. Writing 128 overstates the space by about 64 times and shifts the 50% threshold by a whole level.
Q: If I want time ordering, is switching to v1 enough?
The leading part does advance with time, but this site's v1 is simulated in the browser, contains no real MAC and is not guaranteed monotonic across processes. If you truly need order, the usual approach is to prefix the ID with a time segment of your own; do not place that expectation on the order of a random string.
Q: Can a UUID be used directly as a password, invite code or key?
I would not. Those 122 random bits exist to make an identifier unique; the format is fixed, the source promises no cryptographic strength, and the whole value often ends up in logs. For a password use Random Password Generator, where you set the length and character set (upper and lower case, digits, symbols) and the entropy can be calculated exactly. The two sources serve different purposes: one authenticates a person, the other reconciles records for machines.
Q: Should an auto-increment key already in production be migrated to UUID?
First ask whether a stranger will ever see this ID. Tables used only for internal joins I leave alone. Where the ID appears in an API, a URL or an export, add an opaque public column and keep the auto-increment internally, so index rebuilds and foreign-key cascades press on one column only.
Q: Can a client-generated UUID be forged?
Yes, and it never proved anything: it only guarantees this one string is very likely unique, not who sent the request or whether the content was altered. Unguessable is not the same as trustworthy, and authentication rests on signatures.
Back to that order table: my friend left the primary key alone, the internal id still auto-increments, and a new order_no column holds v4, exposed only through the API and exports. Opacity only has to hold on the exposed surface, and the index, byte and friction costs on one column are far smaller than on a 200-million-row primary key. Whether to switch depends on whether strangers will see your ID; if you switch, accept the write cost of randomness first, then win it back through a binary column, fewer index copies and an explicit sort field.