Solved

auto_increment product numbers

Posted on 2003-12-09
3
1,227 Views
Last Modified: 2008-02-01
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
Comment
Question by:illucid
3 Comments
 
LVL 7

Expert Comment

by:jconde
Comment Utility
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
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
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
 
LVL 17

Accepted Solution

by:
Squeebee earned 125 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now