[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1238
  • Last Modified:

auto_increment product numbers

hi

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?

Cheers
0
illucid
Asked:
illucid
1 Solution
 
jcondeCommented:
Hi!

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.

or

when you create the table, issue the CREATE statement as follows:
CREATE TABLE `tx` (`t` int NOT NULL AUTO_INCREMENT , PRIMARY KEY (`t`)) AUTO_INCREMENT=10000

0
 
snoyes_jwCommented:
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.
0
 
SqueebeeCommented:
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 http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html ------------------

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,
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (grp,id)
             );
INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
                  ("bird","penguin"),("fish","lax"),("mammal","whale"),
                  ("bird","ostrich");
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.)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now