MySQL using 'random' number as primary key

Question

I have a table with an auto-incrementing primary key, but I now need to change this so that the primary keys aren't as guessable by someone typing in consecutive numbers. To create the random primary key I think I'm going to create a table which contains all 6 digit numbers shuffled into a random order and keyed against 1 to n.

My question is, without changing all of my existing queries, how can I use make the link between the auto-incremented key and the 6 digit number at the corresponding index in the second table?

As there is only one query to insert to this table I'd like to change this to keep things simple. I'd like to query the next number in the second table and insert this into the primary key column in the first table. The problem I forsee is that two queries may hit the server at the same time and may return the same 6 digit number from the second table. What are my options for getting around this issue in MySQL?

Use GUID, as it is described here stackoverflow.com/questions/412341/… — Iłya Bursov
– Iłya Bursov, Commented Oct 11, 2013 at 18:40

Schleis · Accepted Answer · 2013-10-11 15:51:19Z

1

Rather than doing all that, why not use a UUID? You can modify your tables adding a column of this type to your tables and you do not need to worry about generating anything or keeping track of it.

https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

answered Oct 11, 2013 at 15:51

Schleis

44.2k8 gold badges73 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Josh Over a year ago

The problem with UUID is that it's not user friendly. The primary key is being used as a way for real humans to navigate through the app. Having a 40-odd character key isn't going to win me any favours with the users.

Schleis Over a year ago

If you are using the id for navigation, why are you concerned with people guessing it?

Josh Over a year ago

The brief was to have users create games which their friends can join using a pin, which is currently the primary key of the existing table. Now the brief has changed to make the pin less guessable by making it non-sequential. Nothing like working with moving goal posts.

Josh · Accepted Answer · 2013-10-14 15:16:20Z

1

Thanks for the suggestions, but no one actually answered my question as it stands.

My table of pins looks like:

id |  pin   | used
 1   123456    1
 2   258464    0
 3   842364    0

And I use the query to get the next pin:

SELECT id, pin FROM game_pins WHERE used = '0' ORDER BY id LIMIT 1;

And then I update that row to SET used = '1'.

To prevent two people getting the same pin I use InnoDB transactions, which in CodeIgniter I've done by wrapping everything in:

$this->db->trans_start();

...

$this->db->trans_complete();

answered Oct 14, 2013 at 15:16

Josh

3,4656 gold badges39 silver badges57 bronze badges

2 Comments

user3338098 Over a year ago

this answer is better than tometzky's answer if you require that pins for deleted games never get reused. If however this is not a requirement, then you are just wasting disk space. In generating this table you will have to fill it with random pins and to avoid duplicates you would have to potentially generate multiple random pins in order to find a unique one, in exactly the same way as tometzky.

user3338098 Over a year ago

you say tometzky's answer is to slow, but the run time is expected run time O(1), however your answer has worst case time O(1), which means your algorithm has "real-time" guarantees, which is another positive aspect to your algorithm, however it is unlikely you need such performance for a game.

Tometzky · Accepted Answer · 2013-10-11 16:35:56Z

0

Ditch this additional table and just use rand() to generate random number between 100000 and 999999. Pick this random number until you find unused. It doesn't look like you need to do it very often, so it doesn't have to be very fast. But queries will be faster, as there's not need for additional join.

But remember that it's not any more secure than simple autoincrement - user can just try all 900k possibilities in several hours.

answered Oct 11, 2013 at 16:35

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

2 Comments

Josh Over a year ago

Unfortunately this will need to be done often, see stackoverflow.com/questions/19322234/… for an explanation. Using rand() until a number is found that hasn't been used will be too slow.

Tometzky Over a year ago

That should not be a problem. I'd just use rand() until about about 50% percent of numbers will be used up and then I'd just add one "9" digit to the upper limit. This way a probability of drawing used number X times will be always less than 1/2^X, like getting tails X times in a row while tossing a coin. In worst case you'd only try twice on average.

Collectives™ on Stack Overflow

MySQL using 'random' number as primary key

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related