MySQL Auto Increment Values ​​after DELETE without WHERE

Question

I noticed after launch,

DELETE FROM tablename

Identifier values ​​(auto increment) have become weird

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

In that order, when I do a,

SELECT * FROM tablename

I know that the certification guide says that identifiers may or may not be reset when DELETE without WHERE is used to delete the table, but what led to the identification sequence being so weird? I am pretty sure that the row order has been inserted. Initially, before deleting, I had 6 rows in the table, so 7, 8, 9 seems understandable.

Answer 1

: http://img19.imageshack.us/img19/7336/82051321.png http://img27.imageshack.us/img27/2935/24285862.png

" ". LOAD DATA INFILE (\ r\n), mysql, , - unix (\n).

, , :

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)

, , - clobbering . , "" ? , - , hex (val), "" .

, :

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)

Answer 2

order by . , - -. , , .

, , order by.

, , . - .

Answer 3

DELETE FROM

TRUNCATE tablename

, reset ID.

Answer 4

something happens there, except for just DELETE, then 10 simple INSERTs, then a simple SELECT. you have 0 in your list of identifiers, which means something is somewhere indicating the values ​​for your auto_increment column.

I would check your application code.

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