8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Home » Articles » Mysql » Here
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...