Primary key in MySQL: INT(n) or UUID as varchar(36)

Question

Does it make sense to use UUID as primary key in MySQL?

What would be pros and cons of using UUID instead of regular INT, beside trouble of hand querying?

Answer 1

The PRO I can think of is that your ID will be unique, not only in your table but on every other table of your database. Furthermore, it should be unique among any table from any database in the world.

If your table semantic needs that feature, then use a UUID. Otherwise, just use a plain INT ID (faster, easier to handler, smaller).

Answer 2

The cons of UUID is that it's bulkier and hence a bit slower to search. It's hard to type in a long hex string for every ad hoc query. It solves a need that you might not have, i.e. multi-server uniqueness.

By the way, INT(n) is always a 32-bit integer in MySQL, the (n) argument has nothing to do with size or range of allowed values. It's only a display width hint.

If you need an integer with a range of values greater than what 32-bit provides, use BIGINT.

Answer 3

The major downside of UUIDs is that you have to create them beforehand if you want to refer back to the record for further usage afterwards (ie: adding child records in dependent foreign keyed tables):

INSERT INTO table (uuidfield, someotherfield) VALUES (uuid(), 'test'));

will not let you see what the new UUID value is, and since you're not using a regular auto_incremented primary key, you can't use last_insert_id() to retrieve it. You'd have to do it in a two-step process:

SELECT @newuid := uuid();
INSERT INTO table (uuidfield, someotherfield) VALUES (@newuid, 'test');
INSERT INTO childtable ..... VALUES (@newuid, ....);