Generating Unique Primary Key In MySQL To Avoid Collision

Since I'm having a bit of trouble publishing this at stackoverflow.com, I'm posting it here instead.

One may want to consider using BIGINT data type in MySQL for table primary key index. Thanks to the following link,

http://forums.mysql.com/read.php?24,423422,423697#msg-423697

I was able to finalize my solution.

A bit of modification from its original suggestion, I made use of:

- PHP microtime: 14 digit number, after removing the decimal point
- zerofill autoincrement column from "Accounts" table

I have a utility class in PHP with the following static method:

    public static function generateIdx($isFloat, $random=NULL) {
        $idx = (String)microtime($isFloat);
        $idx = preg_replace("/\./", "", $idx);
        $maxMicrotimeLength = 14;
        $max = 18;
 
        if (strlen($idx) < $max)
        {
            if (strlen($idx) < maxMicrotimeLength)
            {
                $diff = (maxMicrotimeLength - strlen($idx));
                while($diff > 0)
                {
                    $idx .= 0;
                    $diff--;
                }
            }
            if (!is_null($random))
            {
                $idx += $random;
            }
        }
 
        return $idx;
    }

This generates 18 digit unique code that combines 14-digit from microtime and 4-digit from account code. Hence I have up to 9999 accounts to fit in the 4 digit allotment. But in actuality, BIGINT can still be extended up to a total of 20 digit, so I still have 999999 accounts that can be created. Instead of account code, you may rather use the server id, to further avoid key collision.


In my apps case, accounts are created per client who accesses my app offline in their premises. Each offline app has a sync capability to a central app that contains universal data from the offline clients, hence collision is apparent, especially for the logs table which has to be sequential as well for the synchronization to work correctly.


With this solution, I'm at peace, knowing that numeric index keys are a lot faster than character keys.


Hope this helps.