How can we auto-generate an id with an alphabet value?

How can we 开发者_StackOverflow中文版create a table where we use autoid with alphabet value, like Ref00000001?

Writing if someone else needs this kind of function in future. I used php to generate this value. Very simple:

$q="select reference_number from accounts order by reference_number desc LIMIT 1";
$result = mysql_query($q);
$row = mysql_fetch_assoc($result);

$new_eps=explode('REF',$row['reference_number']); //get existing highest value from DB
$new_eps=$new_eps[1]+1; // increment by one 
$eps_string="REF";
$length = 8 - strlen($new_eps); //I need 8 digits for my REF number
    for($i=0;$i<$length; $i++){
     $eps_string .='0';
    }
    $eps_string =$eps_string.$new_eps;

That's it!

MySQL does not support autogeneration of such ids. Only integer auto_increment ids are supported.

You could do this using a trigger, but it wouldn't be reliable in case multiple inserts are performed at the same time.

Last but not least, string columns for primary keys should be avoided (especially in InnoDB tables), due to performance reasons.

It would probably be a good idea to simply generate the numerical part of the ID in MySQL using a standard AUTO_INCREMENT field and then simply add the "Ref" component on output within PHP.

As a bit of advice, you might want to create a single canonical function ("makeRef" or similar) so that this activity is only ever carried out via a single function, hence making it easy to amend in the future if so required.

In terms of MySQL, you might also want to use the ZEROFILL option to pad the numerical values to a certain length, although I personally almost be temped to handle this within the above "makeRef" function using PHP's str_pad function with the STR_PAD_LEFT option for the pad_type argument.