3

What happens when a UUID() generated by MySQL is not unique? If this is for a column that is a primary key, does MySQL error out, or does it try generating another UUID until a truly unique one is found?

ina
  • 19,167
  • 39
  • 122
  • 201

2 Answers2

5

Well, if you call UUID() twice and get the same results, the most problematic thing would be that "stuff is broken" (tm). It's supposed to be unique and it should be always, as far as I know.

There would be no "regenerate" code available: the function is designed to create unique keys even across computers, so how could it even know its result was not unique?

from http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

Maybe you mean something else? For instance, if you use UUID() to generate somethingthat should be unique (like a primary key, or a Unique field etc), and you've previously added the same number (like for instance you called UUID() once, but inserted something twice), then you'll just get the default error you get when adding non-unique content to a place that should be unique. You will not get a new one.

some reading material about the uniqueness:

  1. the manual. Read it, and how it uses various parts to generate the uuid.: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

  2. Check the link on that manual for the actual definition (but that's quite a read, so you might skip this one): https://www2.opengroup.org/ogsys/jsp/publications/PublicationDetails.jsp?catalogno=c706

  3. Some easier to understand metrics on probability of dubplicates here: http://en.wikipedia.org/wiki/Universally_unique_identifier

  4. there is some talk on the mysql site (forums etc) too, about using it as primary key, but as i'm at work and short on time, you need to search for that yourself :)

Nanne
  • 64,065
  • 16
  • 119
  • 163
  • Hmm, but aren't UUID's "mostly unique with a low chance of collision" rather than guaranteed unique? I guess that's the part i'm not certain about - is there even a chance that UUID() might not generate a unique ID? – ina Mar 14 '12 at 01:10
  • Change is a tricky word, but it's not unheard of to use them as a primary key, if that's what you're asking. I will post some reading material, but you can find some quite easily. – Nanne Mar 14 '12 at 08:12
  • Would you recommend using UUID as primary key? – ina Mar 14 '12 at 11:07
  • Not really. I don't see the need, as for most cases an auto-increment is fine. If you have special needs, you can consider it, and there is some talk that it is efficient and all. If you have a special case you might want to explain that instead of thinking about your sollution (use uuid) and go from there :) – Nanne Mar 14 '12 at 12:56
4

MySQL UUID() is deterministically based on MAC Address, Version and timestamp. If 2 threads on a single server call with the same timestamp (exact same microseconds), equivalent results are expected. This will not be possible with a single thread, as the calls will be separated by a few microseconds on 4Ghz processors.

dusc2don
  • 41
  • 1