mysql字段是自動增長類型結果卻不是連續的
今天建了一張表，設置其"id"屬性為自動增加類型，但是在查看所有數據的時候，卻發現"id"的值不是連續增加的。建表的語法如下：
mysql> create table person_list
-> (
-> id int auto_increment unique,
-> name varchar(15) not null,
-> sex varchar(15) default "中",
-> ID_C varchar(20) not null unique,
-> vocation varchar(8),
-> salary float default 0.0
-> )charset=utf8;
Query OK, 0 rows affected (0.09 sec)
mysql> desc person_list;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(15) | NO | | NULL | |
| sex | varchar(15) | YES | | 中 | |
| ID_C | varchar(20) | NO | UNI | NULL | |
| vocation | varchar(8) | YES | | NULL | |
| salary | float | YES | | 0 | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql>
之後我往這張表裡面添加了一些數據，從中可以看出我所說的問題
mysql> select * from person_list;
+----+-----------+------+--------------------+--------------+---------+
| id | name | sex | ID_C | vocation | salary |
+----+-----------+------+--------------------+--------------+---------+
| 1 | 諸葛亮 | 男 | 360428199908230544 | 蜀國 | 8654.56 |
| 2 | 關羽 | 男 | 360428198908230544 | 蜀國 | 86.236 |
| 3 | 貂蟬 | 女 | 361428198908231479 | 美女集團 | 4566.02 |
| 4 | 西施 | 女 | 795410365478901234 | 美女集團 | 124.1 |
| 5 | 雷軍 | 男 | 469787036544025984 | 小米公司 | 1784.1 |
| 6 | 喬布斯 | 男 | 456982103749651024 | 蘋果公司 | 88888.9 |
| 7 | 李白 | 中 | 147852036978410210 | NULL | 0 |
| 10 | 喬布兒 | 男 | 456902103749651024 | 蘋果公司 | 88888.9 |
| 13 | 喬布兒 | 男 | 056902103749651024 | 蘋果公司 | 88888.9 |
+----+-----------+------+--------------------+--------------+---------+
9 rows in set (0.00 sec)
mysql>
如果我現在再插入一條記錄，那麼這個新增加的記錄的"id"值會是14
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
Query OK, 1 row affected (0.05 sec)
mysql> select * from person_list;
+----+-----------+------+--------------------+--------------+---------+
| id | name | sex | ID_C | vocation | salary |
+----+-----------+------+--------------------+--------------+---------+
| 1 | 諸葛亮 | 男 | 360428199908230544 | 蜀國 | 8654.56 |
| 2 | 關羽 | 男 | 360428198908230544 | 蜀國 | 86.236 |
| 3 | 貂蟬 | 女 | 361428198908231479 | 美女集團 | 4566.02 |
| 4 | 西施 | 女 | 795410365478901234 | 美女集團 | 124.1 |
| 5 | 雷軍 | 男 | 469787036544025984 | 小米公司 | 1784.1 |
| 6 | 喬布斯 | 男 | 456982103749651024 | 蘋果公司 | 88888.9 |
| 7 | 李白 | 中 | 147852036978410210 | NULL | 0 |
| 10 | 喬布兒 | 男 | 456902103749651024 | 蘋果公司 | 88888.9 |
| 13 | 喬布兒 | 男 | 056902103749651024 | 蘋果公司 | 88888.9 |
| 14 | 馬雲 | 男 | 444989898784562304 | 淘寶公司 | 54588.9 |
+----+-----------+------+--------------------+--------------+---------+
10 rows in set (0.00 sec)
mysql> 然後我故意輸入10條錯誤的語句（這個表的ID_C值不能重復）
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲","男","444989898784562304","淘寶公司",54588.85124);
ERROR 1062 (23000): Duplicate entry '444989898784562304' for key 'ID_C'之後我又增加1條正確的記錄，結果發現該新記錄的"ID"值是25，最後一條
跟 倒數第二條 的"ID"值剛好是11-1=10
mysql> insert into person_list (name,sex,ID_C,vocation,salary) value ("馬雲的兒子","男","444989898784562305","淘寶公司",54588.85124);
Query OK, 1 row affected (0.04 sec)
mysql> select * from person_list;
+----+-----------------+------+--------------------+--------------+---------+
| id | name | sex | ID_C | vocation | salary |
+----+-----------------+------+--------------------+--------------+---------+
| 1 | 諸葛亮 | 男 | 360428199908230544 | 蜀國 | 8654.56 |
| 2 | 關羽 | 男 | 360428198908230544 | 蜀國 | 86.236 |
| 3 | 貂蟬 | 女 | 361428198908231479 | 美女集團 | 4566.02 |
| 4 | 西施 | 女 | 795410365478901234 | 美女集團 | 124.1 |
| 5 | 雷軍 | 男 | 469787036544025984 | 小米公司 | 1784.1 |
| 6 | 喬布斯 | 男 | 456982103749651024 | 蘋果公司 | 88888.9 |
| 7 | 李白 | 中 | 147852036978410210 | NULL | 0 |
| 10 | 喬布兒 | 男 | 456902103749651024 | 蘋果公司 | 88888.9 |
| 13 | 喬布兒 | 男 | 056902103749651024 | 蘋果公司 | 88888.9 |
| 14 | 馬雲 | 男 | 444989898784562304 | 淘寶公司 | 54588.9 |
| 25 | 馬雲的兒子 | 男 | 444989898784562305 | 淘寶公司 | 54588.9 |
+----+-----------------+------+--------------------+--------------+---------+
11 rows in set (0.00 sec)
mysql> 所以我估摸著，在插入記錄 語句錯誤的時候 自動增長類型的值會依舊增加，不是只有正確的時候才會+1。
