-1

My Current Table: (id is auto increament)

id | name 1 | spain 2 | canada 4 | england 6 | russia 7 | Brazil 9 | Italy

ID: 3,5,8 Miss

i want when inserting new row , new row insert to row id 3 after row id 5 and then row id 8.

but when i insert new row, cause to 10 | germany.!!!

Dharman
  • 30,962
  • 25
  • 85
  • 135
Farzad
  • 1,975
  • 1
  • 25
  • 47
  • 1
    See [Auto Increment skipping numbers](http://stackoverflow.com/questions/17798835/auto-increment-skipping-numbers). This will usually happen when you use statements like `insert ignore`, `insert ... on duplicate key ...` or similar stuff. You can manually use id 3, 5, and 8 as an id for your next inserts, mysql will not autofill it for you. If you deleted it yourself, then never reuse this id. – Solarflare May 19 '16 at 21:58
  • its not http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html – Hosseini May 19 '16 at 22:35

2 Answers2

4

Auto-increment columns in RDBMS systems don't re-use numbers from rows that have been deleted. If you want functionality as you have described, then you have to turn off auto-increment in the column, and your program will have to figure out how to insert in the way you're describing. It's a lot more work.

Xavier J
  • 4,326
  • 1
  • 14
  • 25
0

The MySQL documentation specifies the following:

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.

Can you provide the text of your insert statement?

Ben
  • 4,798
  • 3
  • 21
  • 35