Auto Increment with letters?

Databases
1

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?

2

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.

3

“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

rudy

4

heh. :slight_smile:

from www.mysql.com/doc/en/example-AUTO_INCREMENT.html

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.

5

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.

6