I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnuĬreate a stored procedure like this: DELIMITER //ĬREATE PROCEDURE reset_autoincrement(IN tablename varchar(200)).

You should be able to adapt it based on your preferred MySQL editor

my images below are using SequelPro as the GUI.

the only way to do this purely in MySQL is to use stored procedure.

the table uses the field id with type as int as primary key.

Since I couldn't find exactly what I want here, I have cobbled the answer from various answers and sharing it here. reset an existing table auto-increment to max(id) + 1.I googled and found this question, but the answer I am really looking for fulfils two criteria: See How can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value. The documentation is not particular clear on that but this was observed with Mariadb 10.11.3 See also If set to a higher value it will continue to use and increment from that. The next insert however will use the next available value (max + 1) ignoring the set value. In a table with the Aria storage engine the auto_increment value can be set to any value, even lower than that of the current maximum. InnoDB, if the value is less than the current maximum value in theĬolumn, no error occurs and the current sequence value is not changed. Than or equal to the maximum value currently in the AUTO_INCREMENTĬolumn, the value is reset to the current maximum plus one.

Note that you cannot reset the counter to a value less than or equal You can reset the counter with: ALTER TABLE tablename AUTO_INCREMENT = 1įor InnoDB you cannot set the auto_increment value lower or equal to the highest current index.

Reset mysql quot increment