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

将一个MyISAM 引擎的表转为InnoDB引擎时出现如下错误：

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

mysql> CREATE TABLE `t_myisam` (

`id` int(10) NOT NULL AUTO_INCREMENT,
`a` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (a,id) ) 
ENGINE= MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)


mysql> ALTER TABLE `t_myisam` ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

参考上述表结构重新创建一个InnDB引擎的表也不行。

mysql> CREATE TABLE `t_innodb` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`a` varchar(10) NOT NULL DEFAULT '',
 PRIMARY KEY ( a,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key



改变索引的顺序重新建表
CREATE TABLE `t_innodb` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY ( id,a)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

原来在InnoDB引擎中，自增长的列必须是索引，而且必须是索引的第一个列。