MySQL的AUTO_INCREMENT欄位

資料表的結構如下：
CREATE TABLE `test1` (
`idpass` int(11) NOT NULL auto_increment,
`datapass` varchar(20) NOT NULL,
PRIMARY KEY (`idpass`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

因為欄位是AUTO_INCREMENT，所以只要資料表資料存在，
數值編號會累加，而且不是從現有資料累加，而是從這個table
曾有的資料累加。就像收據，這個號碼一旦使用了，就不能再使用。
除非你使用新的收據簿。

mysql> INSERT INTO `test`.`test1` (
-> `idpass`,`datapass`)VALUES (NULL ,'data-1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`test1` (
-> `idpass`,`datapass`)VALUES (NULL ,'data-2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`test1` (
-> `idpass`,`datapass`)VALUES (NULL ,'data-3');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`test1` (
-> `idpass`,`datapass`)VALUES (NULL ,'data-4');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test1;
+--------+----------+
| idpass | datapass |
+--------+----------+
| 1 | data-1 |
| 2 | data-2 |
| 3 | data-3 |
| 4 | data-4 |
+--------+----------+
4 rows in set (0.00 sec)
mysql> delete from test1 where idpass=4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+--------+----------+
| idpass | datapass |
+--------+----------+
| 1 | data-1 |
| 2 | data-2 |
| 3 | data-3 |
+--------+----------+
3 rows in set (0.00 sec)

mysql> INSERT INTO `test`.`test1` (
-> `idpass`,`datapass`)VALUES (NULL ,'data-x');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+--------+----------+
| idpass | datapass |
+--------+----------+
| 1 | data-1 |
| 2 | data-2 |
| 3 | data-3 |
| 5 | data-x |
+--------+----------+
4 rows in set (0.00 sec)

當我把資料表的內容清空後，數值就會重新開始計算。

mysql> TRUNCATE TABLE `test1`;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test`.`test1` (
-> `idpass`,`datapass`)VALUES (NULL ,'data-x');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+--------+----------+
| idpass | datapass |
+--------+----------+
| 1 | data-x |
+--------+----------+
1 row in set (0.00 sec)