Doctrine Annotations type="guid" and strategy="UUID" Behavior?

Question

Ok, so I just want to know what the behavior of this annotation does:

/**
 *
 * @ORM\Id
 * @ORM\Column(name="id", type="guid")
 * @ORM\GeneratedValue(strategy="UUID")
 */
protected $id;

Are the keys in an incrementing order as I insert rows to my table?

I do know that the keys are not as simple as 00001, 000002 and it behaves like

• 007f13ff-ce26-11e4-8e3d-a0b3cce9bb7e
• 00805a63-ce26-11e4-8e3d-a0b3cce9bb7e
• 0b1b6ca9-d178-11e4-8e3d-a0b3cce9bb7e

but my question is, are the keys in a specific order like the first inserted row is a lower alphanumeric value than the last value.. Always?

Answer 1

Short answer: UUID are not in a specific order.

Some background

UUID are an alternative to autoincrement integers which are becoming more and more used because they have some advantages.

1. You will not break your application when you insert the 65537 record (or similar 2^x + 1, depending on the original size of the ID). Worst that can happen is that you have a duplicate identifier, but that's very unlikey: https://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates

2. You can generate them in your code before storing the record; you don't have to get "last_insert_id" from the database. That's good, for example when you also have to store some related records in a different table (of course Doctrine may handle this for you but this is another story).

3. You don't give away the number of records in your table to a smart guy who can read the ID of the current order :-)

About sorting

In the most common version currently used (v4), UUID are purely random so they are not in specific order.

Version 1 UUID are a combination of a 60 bit timestamp with a 48 bit MAC-address.

When you use Doctrine UUID strategy, you are basically using the database specific implementation (i.e. UUID() function). In the case of MySQL 5.7, it's a v1 compatible schema which produces the UUID as

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

where

• aaaaaaaa is the low part of the timestamp
• bbbb is the medium part of the timestamp
• cccc is the high part of the timestamp
• dddd adds temporal uniqueness in case time is adjusted
• eeeeeeeeeeee is the node id (MAC address)

Since the timestamp is reversed, if you produce several UUID in a short amount of time you WILL have them sorted, but on the long run they will not be sorted. Think of using the current seconds as the first part of the UUID.

Note: answer improved thanks to @Etki comment, which added a valuable contribution and forced me to learn some more about the subject.