Auto Increment with letters?

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

	letter CHAR(1) NOT NULL DEFAULT '',
	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


heh. :slight_smile:


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.

<example stuff>

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.