8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

MySQL : How TRUNCATE TABLE affects AUTO_INCREMENT

In MySQL, the TRUNCATE TABLE command will reset AUTO_INCREMENT values, as shown in the following example.

CREATE DATABASE test;
USE test;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
  id          INT(11) NOT NULL AUTO_INCREMENT,
  description VARCHAR(50),
  PRIMARY KEY(id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT INTO t1 (description) VALUES ('ONE');
INSERT INTO t1 (description) VALUES ('TWO');
INSERT INTO t1 (description) VALUES ('THREE');

SELECT * FROM t1;
+----+-------------+
| id | description |
+----+-------------+
|  1 | ONE         |
|  2 | TWO         |
|  3 | THREE       |
+----+-------------+
3 rows in set (0.00 sec)

mysql>


TRUNCATE TABLE t1;
INSERT INTO t1 (description) VALUES ('FOUR');

SELECT * FROM t1;
+----+-------------+
| id | description |
+----+-------------+
|  1 | FOUR        |
+----+-------------+
1 row in set (0.00 sec)

mysql>

If this is a problem, you can reset the AUTO_INCREMENT value using the ALTER TABLE command.

TRUNCATE TABLE t1;
ALTER TABLE t1 AUTO_INCREMENT = 4;
INSERT INTO t1 (description) VALUES ('FOUR');

SELECT * FROM t1;
mysql> SELECT * FROM t1;
+----+-------------+
| id | description |
+----+-------------+
|  4 | FOUR        |
+----+-------------+
1 row in set (0.01 sec)

mysql>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.