CODEWITHSUNDEEP
mysqlguiduuid
Ross McFarlane
Ross McFarlane

Reputation: 4254

Unguessable integer primary keys for MySQL

I'm working on a database that uses integers as primary keys for a number of tables. I'd like to make the primary keys relatively difficult to guess — they needn't be super-tight, just not incrementing integers in the low hundreds. Since I'm retrofitting this into existing schemata, with existing data, changing the datatype of the primary key (integer) is not feasible. What I'm wondering is how best to generate the IDs. So far, I can think of these options:

  1. Generate UUIDs using UUID() and convert them to integer.
  2. Keep a separate table full of random integers and use a procedure to select and delete one from the table inside a transaction.
  3. Use the UNIX timestamp, plus a random, n-digit number, e.g. CONCAT(UNIX_TIMESTAMP(),SUBSTRING(RAND() FROM 3 FOR 6))

I'm open to other suggestions, too.

I'd appreciate any thoughts you can offer.

Thanks, Ross

Upvotes: 2

Views: 586

Answers (1)

N.B.
N.B.

Reputation: 14091

Why are you doing that? You'll mess up the way the data is physically stored. Use secondary index and make it a GUID if you need to pass it trough URLs for lookups.

Upvotes: 4

Related Questions