5 ms·
The reason they are not uniform is that the first part is essentially a time stamp. From the docs: UUID() returns a value that conforms to UUID version 1 as de
by lialuna 8y ago
The reason they are not uniform is that the first part is essentially a time stamp. From the docs:
UUID() returns a value that conforms to UUID version 1 as described in RFC 4122. The value is a 128-bit number represented as a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:
The first three numbers are generated from the low, middle, and high parts of a timestamp. The high part also includes the UUID version number.
The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).
The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host device has no Ethernet card, or it is unknown how to find the hardware address of an interface on the host operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.
The MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MySQL uses a randomly generated 48-bit number.
- jrochkind1 8y agoAha, UUID v1! The author really wanted/expected a UUIDv4 there probably. It's confusing that we talk about "UUIDs", when there are a variety of different kinds -- called "versions" but they're not really "versions" in the sense of updated/replaced algorithms, just different approaches. I don't know if MySQL has built-in UUIDv4 though. pg does.
- jerf 8y agoUUIDs have the same problem RAID levels do; they use numbers to identify different types, but the types aren't related to each other in the way numbers are related to each other (some sort of sequential operation where "thing 3" has less of whatever it is than "thing 4" does, be it age, quality, benefits and costs like certification levels, whatever). I can never keep straight which level of RAID is which either, since I only brush them every couple of years, but if they were named for what they are instead of by number I wouldn't have this problem. I would know what "RAID Full Mirroring" or "RAID Striped" were much more easily. We really ought to use names for them, not version numbers. If someone wrote "Timestamp-based UUIDs Are Not Fully Random", well, by the time you've typed out the title of your blog post you'd have figured out what the problem was.
- jrochkind1 8y agoeven if using numbers, in retrospect they should have maybe both been called "types" UUID type 1 or 4. RAID type 1 or 3. To avoid the association that both "version" and "level" have, that the numbers are incrementing the functionality in some way.
- ken 8y agoWords always get truncated or omitted, though. It's technically UUID "version 1" (I think), but see how everybody just says "UUID 1". Teaching people to say "type" instead of "version" wouldn't do much good because most people aren't saying any word there anyway. I'd prefer to use letters in cases like this. Letters have a much weaker association with ordering, there's no implication that the set is of infinite size, and there's no affordance for doing anything arithmetic with them except enumerating them. For example, a plain old USB cable has an "A" and "B" ends, radiation can be alpha/beta/gamma, and power outlets can be type A, B, C, ... (it looks like they're up to "N" now). Nobody would think that a Danish "type K" plug is 9 versions improved on an American "type B" plug. It's just different.
- jerf 8y agoYeah, letters are much more used in situations where they do not denote some sort of order. They do, at least in English, but it's a much weaker association. It seems on some level we intuit that letter orders are arbitrary, whereas in some sense integers are literally nothing but their order.
- Dylan16807 8y ago>see how everybody just says "UUID 1" Are you sure? If they have the number I always see a "v" in front of it.
- giancarlostoro 8y agoAt least with RAID it's enumeration that'd solve that. With UUID, I'm not sure. I wonder where IPv4 vs IPv6 falls under in this mess too.
- dvlsg 8y agoIs it built in to pg now? Last time I set up pg I had to add an extension to get UUID support. It was relatively painless, sure, but I seem to remember having to add it.
- brlewis 8y agoThe pgcrypto module ships with pg but you have to enable it. https://www.postgresql.org/docs/10/pgcrypto.html#id-1.11.7.35.9 https://www.postgresql.org/docs/10/pgcrypto.html#id-1.11.7.3...
- Waterluvian 8y agoI think "version" is the right word if you take it in the traditional sense, not the common technical meaning.
- tracker1 8y agoThat was my first thought looking at them. And depending on how the binary values are indexed, probably much more efficient for inserting into a primary key than a UUIDv4.