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?

Answer 1

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.

Answer 2

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.