CODEWITHSUNDEEP
mysqlprimary-key
mvbl fst
mvbl fst

Reputation: 5263

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

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?

Upvotes: 4

Views: 11036

Answers (4)

Ross
Ross

Reputation: 1659

From my point of view, using UUID as primary key in MySQL is bad idea, if we speak about large databases (and large amount of inserts).

MySQL always creates Primary Keys as clustered, and there is no option to switch it off.

Taking this in to consideration, when you insert large amounts of records with non-sequential identifiers (UUIDs), database gets fragmented, and each new insert would take more time.

Advice: Use PostgreSQL / MS-SQL / Oracle with GUIDs. For MySQL use ints (bigints).

Upvotes: 9

Marc B
Marc B

Reputation: 360572

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, ....);

Upvotes: 6

Bill Karwin
Bill Karwin

Reputation: 562250

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.

Upvotes: 4

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

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).

Upvotes: 5

Related Questions