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
2009-08-06 12:16:35
I don't think there's a way to reset `auto_increment` unless you delete rows, truncate the table, or recreate the column.
Eric
2009-08-06 12:23:07
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
2009-08-06 12:19:55
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
2009-08-07 04:21:44
@derobert: ....that's what the OP asked for. 0 to 99999, then repeat it all over again.
Eric
2009-08-07 09:48:10
+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
2009-08-07 04:23:52