0

Not duplicate!!! Reset primary key auto_increment

How to reset AUTO_INCREMENT in MySQL?

mysql> CREATE TABLE ids1(id int not null PRIMARY KEY AUTO_INCREMENT,num int) AUT
O_INCREMENT=199;
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO ids1(num) VALUES(1),(2),(3),(4);
Query OK, 4 rows affected (0.04 sec)

mysql> SELECT * FROM ids1;
+-----+------+
| id  | num  |
+-----+------+
| 199 |    1 |
| 200 |    2 |
| 201 |    3 |
| 202 |    4 |
+-----+------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE ids1 AUTO_INCREMENT=7;  
Query OK, 4 rows affected (0.34 sec)
Records: 4  Duplicates: 0  Warnings: 0

AND problem: 

mysql> INSERT INTO ids1(num) VALUES(77),(72);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ids1;
+-----+------+
| id  | num  |
+-----+------+
| 199 |    1 |
| 200 |    2 |
| 201 |    3 |
| 202 |    4 |
| 203 |   77 |
| 204 |   72 |
+-----+------+
6 rows in set (0.00 sec)

But why when auto_increment>last_inserd_id then 

mysql> ALTER TABLE ids1 AUTO_INCREMENT=1027;
Query OK, 8 rows affected (0.18 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO ids1(num) VALUES(77),(72);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ids1;
+------+------+
| id   | num  |
+------+------+
|  199 |    1 |
|  200 |    2 |
|  201 |    3 |
|  202 |    4 |
|  203 |   77 |
|  204 |   72 |
|  205 |   77 |
|  206 |   72 |
| 1027 |   77 |
| 1028 |   72 |

How Reset primary key ?

Community
  • 1
  • 1
Michael Phelps
  • 3,451
  • 7
  • 36
  • 64
  • 1
    Don't. It's a bad idea. Whatever problem you think it's presenting you has another solution. – Dan Bracuk May 07 '14 at 11:53
  • But your second link shows you that you can't do what you want. It's quoted there in the accepted answer. **"Note that 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."** – VMai May 07 '14 at 12:03

1 Answers1

2

Reading the official MySQL documentation and the comments (*), it seems that it is not possible. With the instruction ALTER TABLE ids1 AUTO_INCREMENT = n you can reset the value, but it cannot be less than the maximum value previously inserted for that column.

(*) http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

If you want to change the records so that the values into the column "ID" start from 1 (i.e., 1 instead of 199, 2 instead of 200, etc...), you can:

  1. Make a copy of the table
  2. Delete all records of the table: DELETE FROM ids1
  3. Reset the auto increment column: ALTER TABLE ids1 AUTO_INCREMENT = 1
  4. Insert again all records
winterlude
  • 131
  • 6