1

When I insert data into a brand new table, it will assign a new id via AUTO_INCREMENT. So the first time I perform an insert I get an id of 1. However, if I delete the row and insert new data, the table acts as if there is still a preceding row (the new row will have an id of 2). This behavior concerns me because I feel like the data is still persisting somewhere. Any ideas of what it could be?

tocoolforscool
  • 422
  • 1
  • 9
  • 24
  • You aren't alone in feeling that data is persisting. When I started working with databases, I felt the same way. Once you work with auto_increment fields enough you will get in the habit of doing `select id, name...from tablename order by id`. If you notice ID 1, 3, 4...you can conclude that id 2 was deleted. In some cases you will have to archive deleted data, which makes it easier to see what got deleted. – zedfoxus Sep 21 '16 at 03:48

2 Answers2

2

Your data is not persisting. MySql maintains a separate table about your table containing, among other things, the next auto-increment value for your table. You can reset this with:

ALTER TABLE tablename AUTO_INCREMENT = 1

However, be aware that if you are resetting to a value below another valid value in the table, you're asking for trouble.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • How can I access that information? I'd like to start from id=1 every time I empty the table (the only reason I'm emptying tables is for experimentation and learning purposes). – tocoolforscool Sep 21 '16 at 03:43
  • @j3ssi3ftw - See [this thread](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) for more info. But just use the `ALTER TABLE` statement like I suggested whenever you empty the table. – Ted Hopp Sep 21 '16 at 03:45
  • You can do `truncate table tablename;` and then `ALTER TABLE tablename AUTO_INCREMENT = 1;` to reset auto increment to 1 – zedfoxus Sep 21 '16 at 03:45
  • Okay, thank you very much for the swift assistance! I'll definitely avoid resetting the id to 1 if any data exists in the table. – tocoolforscool Sep 21 '16 at 03:46
  • @j3ssi3ftw - Also, it never hurts to consult [the documentation](http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html). :) – Ted Hopp Sep 21 '16 at 03:46
  • I'll accept your answer in 7 minutes. You were too fast! haha. – tocoolforscool Sep 21 '16 at 03:47
  • I'll definitely continue to read up on the documentation. "The pros always practice the basics." – tocoolforscool Sep 21 '16 at 03:48
  • @j3ssi3ftw it's a very common tendency to want your IDs tidy, but it will help you if you can get it into your mind that `AUTO_INCREMENT` values -- also known as surrogate keys -- have no intrinsic meaning, and good database practices include the fact that they should not be imbued with meaning. – Michael - sqlbot Sep 21 '16 at 20:15
  • @Michael-sqlbot - I agree in general. There may be exceptions, though, for things like running test cases. – Ted Hopp Sep 21 '16 at 21:01
  • True, @TedHopp. +1 – Michael - sqlbot Sep 21 '16 at 21:10
2

you should simply use.

truncate table tablename;
Manish Singh
  • 934
  • 1
  • 12
  • 27
  • Just for OP's consideration: truncate table will reset auto_increment field to its start value: http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html – zedfoxus Sep 21 '16 at 03:50
  • One problem with `truncate table` is that it won't work on InnoDB or NDB tables if any foreign keys in other tables reference the table. It also doesn't invoke any `ON DELETE` triggers. Otherwise it's pretty much as good as (and faster than) deleting all the rows and altering the table's `AUTO_INCREMENT` value explicitly. – Ted Hopp Sep 21 '16 at 03:50
  • 1
    I just looked up an example for truncate, it's extremely useful, but thanks for the heads up on the exceptions. – tocoolforscool Sep 21 '16 at 03:53
  • Yeah, even if the child FK's are empty, truncate won't work. – Drew Sep 21 '16 at 04:37