CODEWITHSUNDEEP
mysql
EddyR
EddyR

Reputation: 6941

Mysql repeating incremental numbers?

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.

Upvotes: 1

Views: 188

Answers (3)

Eric
Eric

Reputation: 95133

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.

Upvotes: 0

derobert
derobert

Reputation: 51157

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.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

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.

Upvotes: 0

Related Questions