How do I create an auto increment field will letters and numbers, but only the numbers are incremented. For example: “E 98732”, “E 98733”, “E 98734”, etc… I know that MS Access has this ability, they call it Masking. Does MySQL have this ability, or do I have to use smoke and mirrors to achieve this?
if it’s going to be 1_letter AUTO_INCREMENT_number, you could use this
CREATE TABLE test ( letter CHAR(1) NOT NULL DEFAULT '', id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (letter, id) );
when you have a sub-part of a key AUTO_INCREMENT it works basically the same way as a regular AUTO_INCREMENT. it’s just that it starts from 1 for each value in the key(s) to the left – so each letter in this case would have its ids start from 1. that, and each new AUTO_INCREMENT number is current max value + 1, instead of the traditional max value ever in the column + 1.
“it’s just that it starts from 1 for each value in the key(s) to the left”
whoa, i didn’t know it could do that
that’s so cool it’s scary
For MyISAM and BDB tables you can specify AUTO_INCREMENT on secondary column in a multi-column key. In this case the generated value for the autoincrement column is calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
Note that in this case, the AUTO_INCREMENT value will be reused if you delete the row with the biggest AUTO_INCREMENT value in any group.
Thanks everyone for your help. My boss just change the requirements of the DB so I no longer have to do this, but at least I learned about a nifty new trick.