composite (alphanumeric) primary key and auto increment

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.

possible duplicate of [MySql auto-incrementing Alpha-numeric primary key ??](http://stackoverflow.com/questions/6444355/mysql-auto-incrementing-alpha-numeric-primary-key) — Tom H, Jun 29 '11 at 19:29
@fbas: If you don't mind using MyISAM, you can use a composite auto_incrementing key. See my answer in this question: http://stackoverflow.com/questions/5455436/creating-primary-key-based-on-date/5455513#5455513 — ypercubeᵀᴹ, Jul 09 '11 at 00:46

score 0 · Answer 1 · answered Jun 29 '11 at 19:24

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.

score 0 · Accepted Answer · answered Jun 29 '11 at 19:27

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;
    }

composite (alphanumeric) primary key and auto increment

2 Answers2

Linked