mysql自增长联合主键

        今天因为业务需求设计了一个数据库表，表里面包含type和id两个字段，我期望id是根据type来自增长的，也就是说对于不同的type，id都是从1开始自增长的，所以表示这样创建的（数据库用的mysql）：

create table mytable(type int, id int auto_increment, ..., PRIMARY KEY  (`type`,`id`))ENGINE=InnoDB;
 
 

但是mysql提示错误，错误信息如下：

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

        网上查了下原因，应该是存储引擎的影响。我创建表时用的存储引擎是InnoDB，但是InnoDB似乎要求auto_increment类型的字段在数据库中必须拥有独立的索引，不能是联合索引，所以这样创建表也可以了：

create table mytable(type int, id int auto_increment unique, ..., PRIMARY KEY  (`type`,`id`))ENGINE=InnoDB;
 
 

        这样虽然表格建立起来了，但是业务上还没有达到我的要求，以为这样id虽然还是自增长的，但是它不是根据type的不同从1开始，而是根据记录的增加一直增长下去。于是为了达到需求只有换了一种存储引擎：

create table mytable(type int, id int auto_increment, ..., PRIMARY KEY  (`type`,`id`))ENGINE=MyIsAM;
 
 

然后执行如下语句

 
 

   
   

    
    

   
   
   
   

    
    

     
     insert into mytable(type) values(1); 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     insert into mytable(type) values(1); 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     insert into mytable(type) values(2); 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     insert into mytable(type) values(2); 
    
    
   
   
 
 

查看数据可以发现里面的数据如期望的那样：

+------+----+
| type | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    2 |  1 |
|    2 |  2 |
+------+----+

     最后还有一点需要注意的是自增长的字段不要放在索引的第一列，不然还是不会随着type的改变重新开始自增长，例如如果这样创建表：

create table mytable(type int, id int auto_increment, ..., PRIMARY KEY  (`id`,`type`))ENGINE=MyIsAM;
 
 

这个时候再执行上面的插入语句之后结果如下：

+------+----+
| type | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    2 |  3 |
|    2 |  4 |
+------+----+

    可以发现自增长的方式是受索引影响的。