Is Mysql UUID_SHORT () comparable to UUID ()

Question

Quick question or opinion if you want.

I need to generate some UUID for a database table.

Auto-increment keys will not be truncated since I need a key to be unique for databases and systems. The UUID works fine, but its output is too long for some systems to which strings will be exported. UUID_SHORT () does the job perfectly, and I read the MYSQL conditions, guaranteeing its uniqueness.

But I just want to double check that if I use UUID_SHORT () to generate UUIDs for strings from time to time, they will be really unique in time and space, as in UUID ().

Greetings.

Answer 1

uuid_short() creates a bitwise conglomeration of the server identifier, a rather static time component, and a sequential increase in the 24-bit integer. These bits are filled into an 8-byte integer. The time component is based on server load time.

uuid() creates a hexadecimal string that represents the UUID of version 16 bytes of version1. Version 1 UUID is a bitwise conglomeration of the server identifier, the current timestamp, several bytes that come into play if you generate identifiers in a hyper-stream, and several service bits.

To answer your question: uuid_short provide the uniqueness of time and space that competes with uuid ? The answer is no. The fact is that the server identifier in uuid_short is only one byte. Therefore, if you have 256 or more servers, at least some of them will have the same node identifier, which means that you are losing uniqueness in space. For comparison, the server identifier in UUID version 1 is 6 bytes long, which actually kills the likelihood of duplication for all but the largest of the corporate server farms :)

Best question: is uuid_short . You could see collisions with identifiers if you:

• Generate over 16 million IDS from one server in a short time. ***
• Download servers with the same server ID at the same time and exchange data between them.
• System clock script and server reboot.

The second problem seems unlikely for most people, but the first is worth exploring before you commit yourself to making uuid_short basis of your keys.

*** Based on the mysql uuid_short for uuid_short , it seems like you will see collisions if you created more than 16 million identifiers while running a single server. But that would be stupid. The mysql docs say you're fine if you don't generate 16 million identifiers per second. This means that they must beat some bits in the timestamp if you run out of 16 million consecutive identifiers. I have not tested this.

Answer 2

Your key question: UUID_SHORT() create values ​​that are unique in time and space, as in UUID() . The short answer is yes, if you fulfill the special conditions required by MySQL.

The long answer is yes, but why do you want to use it? The only obvious drawback of UUID() is that its representation is less efficient for storage (it generates a 36-character string rather than a 64-bit integer) and cannot be used with instruction-based replication. But UUID() has great potential to never think about the special conditions required by MySQL for UUID_SHORT() . If you are sure that the conditions for you will never be a problem, and you want to save all 224 bits for writing, UUID_SHORT() in order. But if you have any concerns about special conditions, then it is probably best to avoid this.

The degree of anxiety you are experiencing regarding special conditions is highly dependent on your operating environment. The requirement to never set the system clock back between mysqld reboots is a big problem for me. Servers are often configured so that their clock is automatically synchronized with another time source (for example, ntp on unix, Time Service on Windows), and if this behavior does not work according to your expectations, you may not be able to guarantee that the condition is met sequentially .

---