Learn how to reset auto increment in MariaDB. Our Server Management Support team is here to help you with your questions and concerns.

How to reset auto increment MariaDB

The auto-increment feature in MariaDB automatically generates unique values for a specific column each time a new row is inserted into a table.

However, in some cases, we may have to reset the auto-increment value for a table in MariaDB. This is where the ALTER TABLE statement comes in handy.

Let’s take a look at how to reset the auto-increment value for a table in MariaDB:

1. First, we have to connect to our MariaDB server with a command-line client or a graphical user interface.
2. Then, choose the database that contains the table for which we want to reset the auto-increment value.

   USE databasename;
3. Then, choose the table for which we want to reset the auto-increment value.

   SHOW TABLES;
4. After that, we have to access the current auto-increment value for the table.

   SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename' AND TABLE_NAME = 'tablename';

   In case we want to reset the auto-increment value to a particular number we have to use this statement:

   ALTER TABLE tablename AUTO_INCREMENT = newvalue;

   Remember to replace tablename with the name of the table and newvalue with the desired starting value for the auto-increment column.

   Furthermore, we can reset the auto-increment value to the next available number with this statement:

   ALTER TABLE tablename AUTO_INCREMENT = 1;

   Similarly, we can reset the auto-increment value for a table named users to start at 1 with this statement:

   ALTER TABLE users AUTO_INCREMENT = 1;
5. Finally, we can check the auto-increment value has been reset with this query:

   SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename' AND TABLE_NAME = 'tablename';

If you run into any trouble resetting the auto-increment value for a table, let us know in the comments.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To sum up, our Support Techs demonstrated how to reset auto increment in MariaDB.