执行alter语句后MYSQL自动增量重置(mysql 5.0.92)

Question

mysql> describe phppos_sales_suspended;
+--------------+--------------+------+-----+-------------------+----------------+
| Field        | Type         | Null | Key | Default           | Extra          |
+--------------+--------------+------+-----+-------------------+----------------+
| sale_time    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| customer_id  | int(10)      | YES  | MUL | NULL              |                |
| employee_id  | int(10)      | NO   | MUL | 0                 |                |
| comment      | text         | NO   |     | NULL              |                |
| sale_id      | int(10)      | NO   | PRI | NULL              | auto_increment |
| payment_type | varchar(255) | YES  |     | NULL              |                |
+--------------+--------------+------+-----+-------------------+----------------+

在空表上执行以下命令后，主键似乎重置回 1。这是为什么？我认为那是不可能的。这在 mysql 5.1.54 中不会发生

ALTER TABLE  `phppos_sales_suspended` ADD  `deleted` INT( 1 ) NOT NULL DEFAULT  '0',
ADD INDEX (  `deleted` );

mysql> describe phppos_sales_suspended;
+--------------+--------------+------+-----+-------------------+----------------+
| Field        | Type         | Null | Key | Default           | Extra          |
+--------------+--------------+------+-----+-------------------+----------------+
| sale_time    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| customer_id  | int(10)      | YES  | MUL | NULL              |                |
| employee_id  | int(10)      | NO   | MUL | 0                 |                |
| comment      | text         | NO   |     | NULL              |                |
| sale_id      | int(10)      | NO   | PRI | NULL              | auto_increment |
| payment_type | varchar(255) | YES  |     | NULL              |                |
| deleted      | int(1)       | NO   | MUL | 0                 |                |
+--------------+--------------+------+-----+-------------------+----------------+

Answer 1

向 InnoDB 表添加索引会重建表。不继承旧的自动增量值是一个错误，已在您的新版本中修复。

http://bugs.mysql.com/bug.php?id=34920

http://bugs.mysql.com/bug.php?id=21404