auto_increment product numbers


I would like to auto-increment numbers starting at P10000 for products and Q10000 for quotes. Does MySQL handle this sort of thing? If so which data type would I need to use?

Who is Participating?
SqueebeeConnect With a Mentor Commented:
Or... You can use a little-know aspect of auto_increment: auto_increments as a secondard column in a multipart key.

Let me quote some documentation below:

Read below and what you will note is that you can create two columns, one will be an ENUM that takes P or Q, and one that is an auto_increment INT. When you INSERT records only specify the P or Q and both will auto_increment independantly. You will still need to use a method given by jconde to preset the auto_increment at 10000 but then you should get what you need.

BEGIN QUOTE FROM ------------------

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT 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.

CREATE TABLE animals (
             grp ENUM('fish','mammal','bird') NOT NULL,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (grp,id)
INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
SELECT * FROM animals ORDER BY grp,id;

Which returns:

| grp    | id | name    |
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |

Note that in this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values will be reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.)

First, you can only have one auto_increment field per table in mysql.

Now, with that in mind, you can't have auto_increment numbers that start with letters ... that will have to be handled by your application.

And regarding the last part, about making the auto_increment fields start up at 10000, you can do that by doing one of the following:

insert a bogus record with the auto_increment set to 9999 and then delete the record.


when you create the table, issue the CREATE statement as follows:

jconde is right.  Just as a thought, you might keep the product number split into 2 fields, one (auto_increment) for the number part, the other for the letter part.  Then you can do something like "SELECT CONCAT(letter_field, number_field) AS id".  Your application will have to determine whether to set the letter part to a P or a Q when inserting records.  Also note that you won't be able to have P10000 and Q10000 at the same time in the same table, since the number part will be unique.  You can get around that by having two separate tables, or by just accepting that P/Q numbers might not be consecutive.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.