Solved

auto_increment product numbers

Posted on 2003-12-09
3
1,232 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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