Is mysql auto increment safe to use as userID?

Question

I am working on website that allows people to create profiles online. I was wondering if it is the right choice to use MySQL AUTO_INCREMENTed IDs as my user ids. Also bearing in mind that I might have to duplicate the database across multiple servers one day?

e.g. would you use this method for userIds on a website like Twitter or Facebook?

I have tried generating userIds with PHP before. I used something like this:

function generateID() {
      $possible = "1234567890";
      $code = "";
      $characters = mt_rand(7,14);
      $i = 0;
      while ($i < $characters) { 
            $code .= substr($possible, mt_rand(0, strlen($possible)-1), 1);
            $i++;
      }
      return $code;
}

This gave the type of values I needed, but I always had to check from the DB if that ID does not exist yet.

Isn't there a better approach?

score 26 · Answer 1 · answered Nov 10 '11 at 11:41

26

Is mysql auto increment safe to use as userID?

If your security system is reliable, basically, yes. But generally, web developers feel uncomfortable exposing IDs that allow guessing other IDs by just incrementing or decrementing a number. Many resort to random, multi-digit IDs instead.

On a more obscure note, numeric IDs may also allow competitors to estimate your growth by keeping track of how the incremental value increases.

answered Nov 10 '11 at 11:41

Pekka

442,112
142
972
1,088

And would the best practice for generating the "random, multi-digit IDs" be as the OP suggested? ie: generating the ID on server and then making sure it doesn't exist in the database before assigning it. – theyuv Apr 01 '18 at 17:44

score 5 · Answer 2 · answered Nov 10 '11 at 11:43

5

No, it is not a good idea because auto_increment ids are not easily portable. For your user ids you want ids that can be backed up, restored, moved between database instances, etc. without worrying about collision of ids.

It would be better to generate a unique number or alpha-numeric string using something like UUID.

answered Nov 10 '11 at 11:43

davidethell

11,708
6
43
63

Nothing prevents you to create compound key.. I won't downvote, but you dismissed using auto_increment too quickly. – N.B. Nov 10 '11 at 11:49

score 2 · Answer 3 · answered Nov 10 '11 at 11:42

2

Given you are planning ahead and potentially would be distributing the data across databases, you would be better considering using the UUID() function to get unique identifiers. This makes the merging of data far easier in the future.

answered Nov 10 '11 at 11:42

Andrew

26,629
5
63
86

Compound primary key is much better solution than UUID() for merging data. – N.B. Nov 10 '11 at 11:48
There are two schools of thought on the matter - you either use artifically generated keys to uniquely identify someone, or a compound primary key - Joe Celko is an advocate of the latter approach for example. – Andrew Nov 10 '11 at 12:00
Thank you. I have never used UUID() before. According to what I saw on most example on the internet, it creates long IDs e.g. 1E8EF774581C102CBCFEF1AB81872213 am I right? If so, is there any way I can make these shorter? Or can anyone refer me to a website that explains this function better (I don't think I understand it very well)? Thanks – Sthe Nov 10 '11 at 12:15
1

@Sthe - compound primary key might be the solution for you. It's an auto_increment indexed with additional column(s) that are also integer type. If you have your `id` field as auto_increment, you can add another field called `server_id` and then you define your primary key as `PRIMARY KEY(id, server_id)`. Each of your servers gets one number to identify it (say 1 to 10) and then your auto_increments are working as usual and are differentiated by the server_id. The other way is using longer values generated by UUID. – N.B. Nov 10 '11 at 13:22

score 0 · Answer 4 · answered Nov 10 '11 at 11:43

personally, yes I think so, up until a point. When duplicating a database table with an auto increment field, I think the field retains its max value (depending on how you duplicate it, the scenario I have in mind is mysqldump with data) so when you add a new row, it will be assigned the next ID.

However if you were to have this running on several databases at once, and they weren't replicated, you would need some other way of generating IDs to avoid getting rows with the same ID (although I can't think of a scenario where you would do this).

I couldn't say if it's best practise or not, but it certainly works.

score 0 · Answer 5 · answered Nov 10 '11 at 11:46

Yes If you delete a row say with id 7 the next value of auto increment will be 8 and not 7 again. So all the values will definitely be unique. and you will need to truncate the table to start the auto increment again. SO you can always use auto increment fields for user IDS.

Is mysql auto increment safe to use as userID?

5 Answers5

Linked