MySQL 8 - Primary Keys - Sequential UUID or INT with AI?

Question

My DB has ~100 tables and 3 for which I need UUIDs.

For these 3 tables, the main goal is simply to hide sensitive IDs to visitors.

MySQL 8 has introduced UUID support and I was planning for these 3 tables to use an UUID as a sequential PK doing:

UUID_TO_BIN(UUID(), true) -- Stored as BINARY(16)

My concerns are:

• No Last Inserted ID available anymore, I would have to select the UUID first
• No Auto-increment anymore (you can use a trigger instead, adding some complexity)
• Slight performance loss

For these reasons, I'm thinking about a hybrid solution:

• Using an INT with AI as the PK
• Adding a separate BINARY(16) column for the UUID, keeping it sequential and indexed (but not unique as unnecessary and resource consuming on inserts)

Am I missing something here? Hopefully MySQL will follow PostgreSQL and introduce a UUID() data type in the future to avoid these issues.

Answer 1

• Are you sure about the No last inserted id? Let's see the manual page or the error message.

• Why have id if you have UUID? (Remove the column and all references to it.)

• Make the UUID the PK -- replacing id.

  uuid BINARY(16), PRIMARY KEY(uuid)

• Don't worry about the overhead on INSERT; you need the uniqueness test (which will ever happen), but you do need a unique PK.

• UUIDs are "unique by design" meaning that there will (in all likelihood) never be any dups.