Can we force a MySQL auto-increment field skip insertion of some incremented values whenever we want it to skip? If yes, then how?

As Muhammad Raheel said we can do it as we alter table, but the question is what if you want to change the value before insertion? Not to alter the whole table. Well here we can use triggers to do it in a more better way.

In the trigger you can check if value of autoincrement (infomation_schema.tables) is100 then skip it.  trigger has another benifit here, and that is when InnoDb forgets its highest auto_increment after server restart.

 CREATE TRIGGER trigger_autoinc_tbl BEFORE INSERT ON tbl

Now you should select auto_increment from schema table and see if its equal to for example100 like below:  

IF (auto_incr1=100) THEN SET NEW.id = auto_incr1+1;

I couldn't put the whole code here! Text editor didnt accept these codes. Maybe it thought I'm trying to hack or something.

Best solution already given by Mr. Raheel & Mr. Alireza. I need to know why you need to skip auto increment ? In my development, most of the time i have created a temporary table which keep the last number of row. During insert every time i am taking id from there. Another the worst solution to skip auto increment number is to insert a dummy record and delete the record immediately after insert.

Hi Daljeet,

Yes you can force MySQL to skip auto-incremental sequence and tell MySQL database to set your desired value for next incremental by running following command before your insertion query.

ALTER TABLE users AUTO_INCREMENT = xxx;

xxx = any positive number but it should be greater than last existing ID.

Raheel

While inserting a record only if we specify the value for the auto increment field as null or we do not refer the field, the field will be populated with incremented value. If you want to give a specific value to the auto increment field, you can do it by specifying the value in your insert query.

Hi Muhammad Raheel and Alireza Hoseini !I appreciate you efforts. Thanks for your participation and time.