composite (alphanumeric) primary key and auto incr

Question

Is it possible to make an auto-increment function in MySQL that combines alpha elements and numeric?

I have an existing system with keys like QAb99, QAb101, QAd9003, etc. The max numeric portion ranges from 1 to 9999, while the letters begin with QA and range from QAa to QAd, etc. and will eventually pass QAd9999 to QAe1, etc.

Is it better to manage generating new keys in SQL or outside of SQL (i.e. php script)?

thanks.

Answer 1

MySQL auto-increment are always numeric. There are a couple approaches you could take:

• Implement your own incrementing in your app
• Implement auto-increment with your alphanumeric key using a trigger
• Use MySQL auto-increment, and then have your app convert the number to alphanumeric for display purposes.
• As above, but use a view to have MySQL convert. (This would be one-way, as I don't believe MySQL supports instead-of triggers; see http://bugs.mysql.com/bug.php?id=16525)

As you probably realize, you can convert your sequence to a number and back; the 'a' through 'z' are just 0 through 250,000; if you go to double-a, that's 260,000. You've got base-26 system, multiplied by 10,000.

Answer 2

I asked this a while ago. Mysql doesn't do this unfortunately. I'd love it to, but it just doesn't. In php you could do it. Example:

public function random_id_gen($length)
    {
        //the characters you want in your id
        $characters = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
        $max = strlen($characters) - 1;
        $string = '';

        for ($i = 0; $i < $length; $i++) {
            $string .= $characters[mt_rand(0, $max)];
        }

        return $string;
    }