AUTO_INCREMENT on a secondary column in a multiple-column index

MySQL中可以通过AUTO_INCREMENT产生自增长序列。
但是在MyISAM和InnoDB存储引擎中使用有点区别。
在MySQL 5.5.8中测试(默认存储引擎InnoDB),例如:

mysql> create table tbl_test (id int not null auto_increment,firstname varchar(32),lastname varchar(32),primary key (firstname,id));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

在InnoDB中AUTO_INCREMENT可以看作只有一个链。因此建表时报错, 看了MyISAM的举例就很容易理解.

mysql> create table tbl_test (id int not null auto_increment,firstname varchar(32),lastname varchar(32),primary key (firstname,id)) engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tbl_test(firstname,lastname) values('zhou','digoal'),('Zhou','digoal'),('zhou','Digoal');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbl_test;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | zhou      | digoal   |
|  1 | Zhou      | digoal   |
|  2 | zhou      | Digoal   |
+----+-----------+----------+
3 rows in set (0.00 sec)

# 从结果来看 AUTO_INCREMENT 根据firstname的值 , 被拆成了多条链 ( 或者说有几个firstname的值就有几个链 ) 。
# 换个例子可能更好说明问题:

mysql> create table tbl_test1 (id int not null auto_increment,firstname varchar(32),lastname varchar(32),primary key (firstname,lastname,id)) engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tbl_test1(firstname,lastname) values('zhou','digoal'),('Zhou','digoal'),('zhou','Digoal');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into tbl_test1(firstname,lastname) values('zhou','digoal'),('Zhou','digoal'),('zhou','Digoal');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into tbl_test1(firstname,lastname) values('zhou','digoal'),('Zhou','digoal'),('zhou','Digoal');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into tbl_test1(firstname,lastname) values('zhou','digoal'),('Zhou','digoal'),('zhou','Digoal');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbl_test1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | Zhou      | digoal   |
|  2 | Zhou      | digoal   |
|  3 | Zhou      | digoal   |
|  4 | Zhou      | digoal   |
|  1 | zhou      | Digoal   |
|  2 | zhou      | Digoal   |
|  3 | zhou      | Digoal   |
|  4 | zhou      | Digoal   |
|  1 | zhou      | digoal   |
|  2 | zhou      | digoal   |
|  3 | zhou      | digoal   |
|  4 | zhou      | digoal   |
+----+-----------+----------+
12 rows in set (0.00 sec)

# AUTO_INCREMENT 根据firstname,lastname的值 , 被拆成了多条链 ( 或者说有几个firstname,lastname的值组合就有几个链 ) 。

# 来看看把tbl_test的引擎转换为InnoDB会怎么样?

mysql> show table status like 'tbl_test' \G
*************************** 1. row ***************************
           Name: tbl_test
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 9
 Avg_row_length: 24
    Data_length: 216
Max_data_length: 281474976710655
   Index_length: 3072
      Data_free: 0
 Auto_increment: 9
    Create_time: 2011-01-17 15:52:50
    Update_time: 2011-01-17 15:52:54
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

转换报错 :

mysql> alter table tbl_test engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

# 新建一个KEY就可以转换为InnoDB引擎.

mysql> create index idx_id on tbl_test(id);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table tbl_test engine=innodb;
Query OK, 9 rows affected (0.08 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> show table status like 'tbl_test' \G
*************************** 1. row ***************************
           Name: tbl_test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 10485760
 Auto_increment: 9
    Create_time: 2011-01-17 15:55:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> describe tbl_test
    -> ;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(32) | NO   | PRI |         |                |
| lastname  | varchar(32) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show create table tbl_test;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                             |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_test | CREATE TABLE `tbl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `lastname` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`firstname`,`id`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into tbl_test(firstname,lastname) values('zhou','digoal'),('Zhou','digoal'),('zhou','Digoal');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbl_test;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | Zhou      | digoal   |
|  4 | Zhou      | digoal   |
|  7 | Zhou      | digoal   |
| 10 | Zhou      | digoal   |
|  1 | zhou      | digoal   |
|  2 | zhou      | Digoal   |
|  3 | zhou      | digoal   |
|  5 | zhou      | Digoal   |
|  6 | zhou      | digoal   |
|  8 | zhou      | Digoal   |
|  9 | zhou      | digoal   |
| 11 | zhou      | Digoal   |
+----+-----------+----------+
12 rows in set (0.00 sec)

# 转换为 InnoDB之后,AUTO_INCREMENT 变成单链自增。