[mysql] How to reset AUTO_INCREMENT in MySQL?

I googled and found this question, but the answer I am really looking for fulfils two criteria:

  1. using purely MySQL queries
  2. reset an existing table auto-increment to max(id) + 1

Since I couldn't find exactly what I want here, I have cobbled the answer from various answers and sharing it here.

Few things to note:

  1. the table in question is InnoDB
  2. the table uses the field id with type as int as primary key
  3. the only way to do this purely in MySQL is to use stored procedure
  4. my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
  5. I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu

Step 1: Create Stored Procedure

create a stored procedure like this:

DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN

      SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
      PREPARE stmt FROM @get_next_inc; 
      EXECUTE stmt; 
      SELECT @next_inc AS result;
      DEALLOCATE PREPARE stmt; 

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Then run it.

Before run, it looks like this when you look under Stored Procedures in your database.

enter image description here

When I run, I simply select the stored procedure and press Run Selection

enter image description here

Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.

After I run, I should see the stored procedure

enter image description here

If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.

Step 2: Call the stored procedure

This time you can simply use normal MySQL queries.

call reset_autoincrement('products');

Originally from my own SQL queries notes in https://simkimsia.com/library/sql-queries/#mysql-reset-autoinc and adapted for StackOverflow

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to auto-increment

How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME? How to make MySQL table primary key auto increment with some prefix Get the new record primary key ID from MySQL insert query? How to generate auto increment field in select query Get current AUTO_INCREMENT value for any table Add Auto-Increment ID to existing table? How to AUTO_INCREMENT in db2? How to create id with AUTO_INCREMENT on Oracle? auto increment ID in H2 database ERROR: permission denied for sequence cities_id_seq using Postgres