我們都知道，在show create table 的時候:
mysql> SHOW CREATE TABLE tasks;
------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`task` VARCHAR(30) DEFAULT NULL,
`assignee` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
AUTO_INCREMENT=3表示下一次insert操作時，id的值將為3。 然後將一直增大下去。
所以它才被稱作自增字段嘛。
可是您見過，這個AUTO_INCREMENT越變越小的時候麼？
mysql> USE test;
DATABASE changed
mysql>
mysql> CREATE TABLE `tasks` (
-> `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `task` VARCHAR(30) DEFAULT NULL,
-> `assignee` VARCHAR(30) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 ROWS affected (0.01 sec)
插入三條記錄：
mysql>
mysql> INSERT INTO tasks (task, assignee) VALUES ('write post', 'Sam');
Query OK, 1 ROW affected (0.00 sec)
mysql> INSERT INTO tasks (task, assignee) VALUES ('read post', 'User 1');
Query OK, 1 ROW affected (0.00 sec)
mysql> INSERT INTO tasks (task, assignee) VALUES ('tweet post', 'Sam');
Query OK, 1 ROW affected (0.02 sec)
mysql>
mysql> SHOW CREATE TABLE tasks;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`task` VARCHAR(30) DEFAULT NULL,
`assignee` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)
AUTO_INCREMENT=4，很合理。
mysql>
mysql>
下面刪除二條記錄，id為2和3。
mysql> DELETE FROM tasks WHERE id=3;
Query OK, 1 ROW affected (0.00 sec)
mysql> DELETE FROM tasks WHERE id=2;
Query OK, 1 ROW affected (0.00 sec)
mysql> SHOW CREATE TABLE tasks;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`task` VARCHAR(30) DEFAULT NULL,
`assignee` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)
可以看到, AUTO_INCREMENT仍然為4.
然後按ctrl+z，返回shell：
mysql>
[2]+ Stopped ./mysql -uroot -p
[root@H209 bin]#
[root@H209 bin]#
關閉mysqld
[root@H209 bin]# ./mysqladmin -uroot -p shutdown
Enter password:
130206 14:50:24 mysqld_safe mysqld FROM pid file /home/mysql569/DATA/H209.pid ended
[3]- Done ./mysqld_safe
[root@H209 bin]#
運行mysqld
[root@H209 bin]# ./mysqld_safe &
[3] 16472
[root@H209 bin]# 130206 14:50:29 mysqld_safe Logging TO '/home/mysql569/data/H209.err'.
130206 14:50:29 mysqld_safe Starting mysqld daemon WITH DATABASES FROM /home/mysql569/DATA
[root@H209 bin]#
返回mysql:
[root@H209 bin]# fg
./mysql -uroot -p
mysql>
mysql> SHOW CREATE TABLE tasks;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying TO reconnect...
Connection id: 1
CURRENT DATABASE: test
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
