tags:

views:

61

answers:

3

How can I get Mysql to incrementally generate a number between 0 to 99999 and add it to the record? Also once 99999 is reached its starts back at 0.

A: 

You want AUTO_INCREMENT.

Although getting it to reset at 99999 might be a problem. I would also question your requirement that it work that way.

Mitch Wheat
I don't think there's a way to reset `auto_increment` unless you delete rows, truncate the table, or recreate the column.
Eric
A: 

If you need to reset it, you can create a trigger:

create trigger auto_number before insert on `sometable`
begin
    new.autoid = coalesce((select max(autoid)+1 from sometable),0) % 1000000
end

Essentially, when an insert occurs, this just sets the autoid column as the next largest ID. However, it uses modulo to ensure that the number repeats itself at 1,000,000.

Eric
This won't work. You'll hit the max autoid, and then max(autoid) will become a constant. So you'll repeatedly generate the same ID. Not to mention that there had better be a nice index, or this will be /slow/.
derobert
@derobert: ....that's what the OP asked for. 0 to 99999, then repeat it all over again.
Eric
+1  A: 

Generate an ever-incrementing number (e.g., a standard auto_increment) and then when selecting the data (or via a view), run it through mod 100000:

⋮
99998  % 100000 = 99998
99999  % 100000 = 99999
100000 % 100000 = 0
100001 % 100000 = 1
⋮

You can make this transparent by creating a view for select:

CREATE SQL SECURITY INVOKER VIEW whatever AS
  SELECT
    foo, bar, baz, autoid % 100000 AS autoid, taz
  FROM base_table;

Unfortunately, this view will probably not be updatable and MySQL doesn't support INSTEAD OF triggers, so you'll have to send writes to the underlying table.

derobert
do you think it would just be better if I let php do this instead?
EddyR
@EddyR: No idea if it'd be better to let PHP do this; you haven't described at all *why* you want to do this.
derobert