自增長列必須是索引列，否則無法建立成功表，對myisma和innodb都一樣
(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10)) engine=innodb;
ERROR 1075 (42000):
(localhost@testdb)[root]>
(localhost@testdb)[root]>

(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10),index(id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10)) engine=myisam;
ERROR 1075 (42000):
(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10),index(id)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]>
(localhost@testdb)[root]>
(localhost@testdb)[root]>

建立成功後id列沒有插入資料，但是可以自動增長
(localhost@testdb)[root]> insert into test5(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+
3 rows in set (0.00 sec)
索引
(localhost@testdb)[root]> (localhost@testdb)[root]> show index from test5;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test5 | 1 | id | 1 | id | A | NULL | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

刪除表裡的資料，在插入資料id列會依據原來的值繼續增長
(localhost@testdb)[root]> delete from test5;
Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]>
(localhost@testdb)[root]>
(localhost@testdb)[root]> select * from test5;
Empty set (0.00 sec)

(localhost@testdb)[root]> insert into test5(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;
+----+------+
| id | name |
+----+------+
| 4 | aa |
| 5 | bb |
| 6 | cc |
+----+------+
3 rows in set (0.00 sec)

truncate 表裡的資料後在插入資料，id列會從1開始增長。
(localhost@testdb)[root]> truncate table test5;
Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;
Empty set (0.00 sec)

(localhost@testdb)[root]> insert into test5(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+
3 rows in set (0.00 sec)

(localhost@testdb)[root]>

對於複合索引的自增長列
myisam引擎的自增長列，在索引中是非前導列可以建立成功
innodb引擎的自增長列，在索引中必須是前導列，表才能建立成功

(localhost@testdb)[root]> create table test4 (id1 int auto_increment,id2 int,name varchar(10),index(id2,id1)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]>
(localhost@testdb)[root]>

(localhost@testdb)[root]> drop table test4;
Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]> create table test4 (id1 int auto_increment,id2 int,name varchar(10),index(id2,id1)) engine=innodb;
ERROR 1075 (42000):
(localhost@testdb)[root]>
(localhost@testdb)[root]>

(localhost@testdb)[root]> create table test4 (id1 int auto_increment,id2 int,name varchar(10),index(id1,id2)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

(localhost@testdb)[root]>
(localhost@testdb)[root]>
(localhost@testdb)[root]>

mysql自增長列

相關文章