Renew Auto Increment in MySql

Question

Suppose i have an auto incremented column named 'ID' and it is going on like 1,2,3,4 but after 4 i want it to start from 1 again. So it will be like 1,2,3,4,1,2,3,4,1,2...and so on. So how to reinitialize the auto increment to 0? I have tried with this also but no solution .

ALTER TABLE 'TABLENAME' AUTO_INCREMENT=0

Possible duplicate of [How to set initial value and auto increment in MySQL?](https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql) — Progman, Sep 28 '18 at 21:47
@Progman That's not correct. He wants it to go from 0 to 4, and then back to 0 again, reusing values for his ID. — Blue, Sep 28 '18 at 21:48
Because i need to separate those slots which will be needed for my work — , Sep 28 '18 at 21:48
My recommendations: Use a custom trigger on insert. SELECT a value from another table (Something like auto_increment), increment that, and if it's over 4, set it to 0. Update the updated increment index, and then update your column on insert. — Blue, Sep 28 '18 at 21:49
Actually @FrankerZ that 4 is not constant for my work actually it will happen upon clicking on a button — , Sep 28 '18 at 21:51
It's something that you're going to have to do manually regardless, either through a trigger or when you insert the object. — Blue, Sep 28 '18 at 22:07
In practice, nobody ever needs to store data like this. You can just take the modulus of a regular ai — Strawberry, Sep 28 '18 at 22:10
Echoing @Strawberry. Tell us what you are actually after, because doing what you want to do is definitely not the solution. — Matt Runion, Sep 29 '18 at 00:00
Possible duplicate of [mysql: how do i start auto increment from a specific point?](https://stackoverflow.com/questions/3470246/mysql-how-do-i-start-auto-increment-from-a-specific-point) — Sam M, Sep 29 '18 at 00:16

score 1 · Answer 1 · answered Sep 29 '18 at 06:20

Don't confuse data storage with data retrieval and display

create table my_table (id serial primary key);

insert into my_table values 
(1),(2),(3),(4),(5),(6),(7),(8),(9);

select id, mod(id-1,3) x from my_table;
id x
 1 0
 2 1
 3 2
 4 0
 5 1
 6 2
 7 0
 8 1
 9 2

http://sqlfiddle.com/#!9/7f460/3

Note also that the construction of 'x' could be handled just as easily (and perhaps more effectively) in application code

Renew Auto Increment in MySql

1 Answers1