0

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.

1 Answer 1

0
  • 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.

4
  • Thank you for your answer! - Yes, AI works only with INT, not UUID (i.e. dev.mysql.com/doc/refman/8.0/en/…) - Yes, the last inserted ID won't be retrievable unless you selected it before the insert (2 queries instead of 1) - Thought I read that has UUID is already UNIQUE by design, adding a UNIQUE index in addition to the PK was unnecessary and costly. Aug 5, 2021 at 23:33
  • @BrunoLeveque - I added some more. I want to get rid of id, leaving the UUID as the PK and the only unique key. (The PK is "unique" and is an "index".)
    – Rick James
    Aug 6, 2021 at 0:02
  • Thank you :) My goal is to use the UUID whenever info is displayed to users (URLs, forms, etc.) and ID in the backend to speed things up and not having to deal with AI while keeping the benefits of both systems. Aug 6, 2021 at 0:41
  • Your concern about Inserts -- How many new users per second do you expect to add?
    – Rick James
    Aug 6, 2021 at 2:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.