Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to auto-increment value of tables to lower value in MySQL?
If you’re using InnoDB engine, then you cannot set auto_increment value of tables to lower value. You need to change your engine from InnoDB to MyISAM.
Note: The engine MyISAM allows you to set lower value. Here, we are using the same.
According to the official documents:
You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.
As shown above, in MyISAM, let’s say some of the Ids are deleted. After that, if you will add auto_increment again, then the IDs will begin from the lower value i.e. starting from the final ids remaining (after we deleted some ids).
Let us first create a table with engine MyISAM:
mysql> create table DemoTable (Id int NOT NULL AUTO_INCREMENT PRIMARY KEY)ENGINE='MyISAM'; Query OK, 0 rows affected (0.23 sec)
Following is the query to insert records in the table using insert command:
mysql> insert into DemoTable values(); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.02 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.05 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.08 sec)
Following is the query to display records from the table using select command:
mysql> select *from DemoTable;
This will produce the following output
+----+ | Id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec)
Now, delete Ids 4,5 and 6:
mysql> delete from DemoTable where Id=4 or Id=5 or Id=6; Query OK, 3 rows affected (0.06 sec)
Let us display all records once again. Following is the query:
mysql> select *from DemoTable;
This will produce the following output after deleting some Ids:
+----+ | Id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
Now, let us set new auto_increment ids.
Following is the query to set auto_increment value to lower in MyISAM engine. However, the current auto_increment value should now begin from 7 but since we are using MyISAM engine, therefore the value is reset to the current maximum, which is 3 plus one i.e. 3+ 1 = 4 would be the new id.
Following is the query:
mysql> alter table DemoTable auto_increment=4; Query OK, 3 rows affected (0.38 sec) Records: 3 Duplicates: 0 Warnings: 0
Now insert some records once again and then display all records from table to check the auto_increment value starts from 4:
mysql> insert into DemoTable values(); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.02 sec)
Following is the query to display all records from table:
mysql> select *from DemoTable;
This will produce the following output. The new ids begin from 4:
+----+ | Id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec)
660 Views
