MySQL - 将 MyISAM 转换为 InnoDB 出现错误 1075

Question

我正在尝试将表从 MyISAM 转换为 InnoDB，这是定义，我收到错误 #1075 - 表定义不正确； auto列只能有一个，必须定义为key

该表有一个 AutoIncrement 值并且该字段被索引并且它与 MyISAM 一起工作。我是 InnoDB 的新手，所以这可能是个愚蠢的问题

CREATE TABLE `cart_item` (
 `cart_id` int(10) unsigned NOT NULL DEFAULT '0',
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `design_number` int(10) unsigned NOT NULL,
 `logo_position_id` smallint(5) unsigned NOT NULL,
 `subst_style_id` varchar(10) DEFAULT NULL,
 `style_id` varchar(10) NOT NULL DEFAULT '',
 `subst_color_id` smallint(5) unsigned DEFAULT NULL,
 `color_id` smallint(5) unsigned NOT NULL,
 `size_id` smallint(5) unsigned NOT NULL,
 `qty` mediumint(8) unsigned NOT NULL,
 `active` enum('y','n') NOT NULL DEFAULT 'y',
 `date_last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `last_modified_by_id` mediumint(5) unsigned NOT NULL,
 `date_last_locked` datetime DEFAULT NULL,
 `last_locked_by_id` smallint(5) unsigned NOT NULL,
 `date_added` datetime NOT NULL,
 `subsite_logo_group_id` int(11) NOT NULL,
 `bundle` varchar(32) NOT NULL,
 `color_stop_1` varchar(4) DEFAULT NULL,
 PRIMARY KEY (`cart_id`,`id`),
 KEY `color_id` (`color_id`),
 KEY `style_id` (`style_id`),
 KEY `size_id` (`size_id`),
 KEY `design_number` (`design_number`),
 KEY `subsite_logo_group_id` (`subsite_logo_group_id`),
 KEY `date_added` (`date_added`),
 KEY `bundle` (`bundle`)
) ENGINE=InnoDB

Answer 1

您在 MyISAM 表上所做的，无法用 InnoDB 完成。请参阅我对(类似)问题的回答: creating primary key based on date

MySQL 文档，在 Using AUTO_INCREMENT 部分，解释一下:

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

---

您可能会在 InnoDB 中获得类似的行为，但使用 AUTO_INCREMENT 则不会。您必须为插入处理(每个 cart_id)自动增量使用一些花哨的触发器或存储过程。