MySQL Auto Increment Values After DELETE without WHERE

Question

i noticed after i ran

DELETE FROM tablename

my ID (auto increment) valus became weird

7, 8, 9, 0, 1, 12, 3, 4, 15

in this order when i do a

SELECT * FROM tablename

i know that the certification guide says that IDs may or may not be reset when DELETE without WHERE is used to empty a table, but what caused the ID sequence to be so weird? i am quite certain that's the order the rows were inserted. originally before delete i had 6 rows in the table so 7, 8, 9 seems understandable

Answer 1

Instead of DELETE FROM you should try

TRUNCATE tablename

I believe this will also reset the sequence for ID.

Answer 2

There is absolutely no semblence or guarantee of order in a database without an order by clause. This has to do with how the records are stored--they are not stored in sequence of anything. The database often optimizes how it stores the data based on clustered indices, but each database stores data a bit differently.

You should never, ever, ever think that you will have a repeatable order unless you use the order by clause.

So, it appears that your IDs got reclaimed. There's absolutely nothing weird about that order--you essentially selected a pseudo-random ordering in the first place.

Answer 3

there's something going on here besides just DELETE, then 10 simple INSERT, then a simple SELECT. you have a 0 in your ID list, which means something somewhere is specifying values for your auto_increment column.

i would check your application code.

if that's not it, the you need to come up with a specific set of steps (including a CREATE TABLE) that shows how this problem can be reproduced.

Answer 4

in reference to your screen shots:http://img19.imageshack.us/img19/7336/82051321.png and http://img27.imageshack.us/img27/2935/24285862.png

the problem is with your "application code". you're using LOAD DATA INFILE with a file that has windows style (\r\n) line endings, and the default in mysql unless you specify otherwise is unix style (\n).

to see what i mean, try this:

mysql> load data infile 'data.txt' into table testDel (val);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testDel;
+----+----------------+
| id | val            |
+----+----------------+
 | 7 | hello world 1
 | 8 | hello world 2
 | 9 | hello world 3
 | 0 | hello world 4
 | 1 | hello world 5
| 12 | hello world 6  |
+----+----------------+
6 rows in set (0.00 sec)

mysql> select id, hex(val) from testDel;
+----+------------------------------+
| id | hex(val)                     |
+----+------------------------------+
|  7 | 68656C6C6F20776F726C6420310D |
|  8 | 68656C6C6F20776F726C6420320D |
|  9 | 68656C6C6F20776F726C6420330D |
| 10 | 68656C6C6F20776F726C6420340D |
| 11 | 68656C6C6F20776F726C6420350D |
| 12 | 68656C6C6F20776F726C642036   |
+----+------------------------------+
6 rows in set (0.01 sec)

what's happening is the \r is clobbering the display of your values. did you notice how your tables "walls" don't line up? this should be a hint that something is wrong with the display, as evidenced by the query with hex(val) where the "walls" do line up.

to fix the import, you have to specify the line endings in the file:

mysql> load data infile 'data.txt' into table testDel lines terminated by '\r\n' (val);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testDel;
+----+---------------+
| id | val           |
+----+---------------+
| 13 | hello world 1 |
| 14 | hello world 2 |
| 15 | hello world 3 |
| 16 | hello world 4 |
| 17 | hello world 5 |
| 18 | hello world 6 |
+----+---------------+
6 rows in set (0.00 sec)