Mysql repeating incremental numbers?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.