Re: truncate table-auto increment values

[Barry Galbraith]

Create a table.

CREATE TABLE `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert some data. Auto Increment values generated by MySQL.

INSERT into mytable(data)
VALUES('John')
, ('Paul')
,('George')
,('Ringo')
;

Check the data, with Auto Increment values.

SELECT id, data from mytable;
+----+--------+
| id | data   |
+----+--------+
|  1 | John   |
|  2 | Paul   |
|  3 | George |
|  4 | Ringo  |
+----+--------+
4 rows in set (0.11 sec)

SHOW CREATE TABLE now shows the next Auto Increment value.

mysql> show create table mytable \G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Now truncate the table.

mysql> truncate table mytable;
Query OK, 0 rows affected (0.34 sec)

SHOW CREATE TABLE doesn't show Auto Increment value.

mysql> show create table mytable \G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Insert data to empty table. Auto Increment starts at 1

mysql> INSERT into mytable(data)
    -> VALUES('John')
    -> , ('Paul')
    -> ,('George')
    -> ,('Ringo')
    -> ;
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT id, data from mytable;
+----+--------+
| id | data   |
+----+--------+
|  1 | John   |
|  2 | Paul   |
|  3 | George |
|  4 | Ringo  |
+----+--------+
4 rows in set (0.00 sec)

If you show us your SHOW CREATE TABLE for your table, and some sample insert data that demonstrates your problem,
like PB asked you many times, we might be able to see why
TRUNCATE TABLE doesn't reset your AUTO_INCREMENT.

That said, like Rick James has said, you should NOT read ANYTHING into AUTO INCREMENT values
other than they are guaranteed to be unique, and not necessarily a complete sequence.
If you want a sequence, you should generate and store it your self.

Good luck,
Barry.