site80443
site80443

Reputation: 301

mysql auto_increment PK id after truncate and select-insert goes to 65536

My table t1 has an int auto_increment PK (id) with values from 1 to about 40k.

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `col1` int NOT NULL,
  `col2` int NOT NULL,
  ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40124 DEFAULT CHARSET=latin1

Some code that clears and resets the data in the table is as follows:

TRUNCATE TABLE t1;
INSERT INTO t1 (col1, col2, ...)
SELECT ... FROM t2 WHERE  ... ORDER BY ...;

The number of rows inserted in this way is typically between 40k and 41k.

t1.id is not inserted in the select-insert.

Upon running

show create table t1;

it shows a new auto_increment value for the id of exactly 65536:

...
) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=latin1

However, the values of id are still from 1 to 40123 or whatever the number of rows resulted from the select-insert.

But a new insert sets the id to 65536.

> select * from t1 order by id desc limit 10;

+-------+---------------+---------------+---
| id    |          col1 |          col2 |... 
+-------+---------------+---------------+---
| 65536 |         12345 |        123456 |...
| 40123 |         73627 |        736273 |...
| 40122 |         45678 |        456789 |...

Does mysql anticipate that since 40k is the approximate resultset size for the Select-Insert, it should use the nearest upper milestone size of 65536 ?

Or is it necessary to put an explicit statement after TRUNCATE ?

TRUNCATE TABLE t1;
ALTER TABLE t1 SET AUTO_INCREMENT=1;
INSERT INTO t1 (col1, col2, ...)
SELECT ... FROM t2 WHERE  ... ORDER BY ...;

Upvotes: 1

Views: 208

Answers (0)

Related Questions