Bug #58492 Allow TRUNCATE TABLE to remember the original AUTO_INCREMENT value
Description:
At http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html the Manual says:
"The AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE"
and:
"The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values."
In some case a user can find useful to not reset the AUTO_INCREMENT value.
How to repeat:
mysql> CREATE TABLE tbl1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE tbl1\G
*************************** 1. row ***************************
Table: tbl1
Create Table: CREATE TABLE `tbl1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> TRUNCATE TABLE tbl1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE tbl1\G
*************************** 1. row ***************************
Table: tbl1
Create Table: CREATE TABLE `tbl1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Suggested fix:
Please introduce some way to keep the current auto_increment value in the table definition.
For example an extension of the TRUNCATE TABLE statement or some system variable.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.