Solved

auto_increment product numbers

Posted on 2003-12-09
3
1,233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Expert Comment

by:jconde
ID: 9906483
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
ID: 9906676
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
ID: 9909661
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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Creating and Managing Databases with phpMyAdmin in cPanel.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

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