is it possible to set a starting point and maximum value for an autoincrement in mysql?

i am creating a web application that contains 4 important id's

and to speed up identification of which auto incremented id is for开发者_StackOverflow what

i was hoping to set limits on the autp incremented number.

example:

orderid = 10000000 so would generate a number between 10m - 19999999

palletid = 20000000 20m - 29999999

productid = 30000000 30m - 39999999

and when these id's reach a limit will they start reusing old numbers that are now available?

That's not how autoincrement columns work.

Sure you can probably set the starting point, but a maximum and the part about reusing old numbers, sorry, not built into the system.

Why do you need that?

The general consensus is that if you use autoincrement columns you don't really care about the value itself. Sure, you care that it is unique, but you don't care what the actual value is.

So since you care, why do you care? Why do you need it to reuse old numbers?

You say you want to use it to identify the various items in the system. You mean, for users? Why not just use a prefix? O123 for orders, C123 for customers, P123 for products, etc.

One way to do it is to define the data type. . . for example the maximum value of tiny int would be 127. . . if you were trying to restrict yourself to 100 incrementations, set your start value to 27.

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Nope, but try something like this:

INSERT INTO table VALUES(id)
(
 Case when
 (
  SELECT id FROM table
  LIMIT COUNT(SELECT * FROM table)-1, 1) >= maximumhere
 )
then minimumhere
else MAX(SELECT id FROM table)+1
)

This is just theoretical of course, play with it...