mysql - 这个mysql查询有什么问题？

Question

这个有效:

CREATE TABLE shoutbox_shout (
  shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL DEFAULT 0,
  shout_date INT UNSIGNED NOT NULL DEFAULT 0,
  message MEDIUMTEXT NOT NULL,
  KEY shout_date (shout_date)
)

...虽然这样:

CREATE TABLE shoutbox_shout (
  shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL DEFAULT 0,
  shout_date INT UNSIGNED NOT NULL DEFAULT 0,
  message MEDIUMTEXT NOT NULL,
  KEY shout_date (shout_date)
)

...结果:

Error Code: 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

我添加了主键，但仍然出现错误。

Answer 1

报价 Bug #45987, ERROR 1075 (42000): Incorrect table definition :

Actually, this is not a server bug, just a difference between MyISAM (assumed as default by that manual page) and InnoDB storage engine documented elsewhere: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

MyISAM 不再是默认引擎； InnoDB 是。

还有:http://bugs.mysql.com/bug.php?id=60104

结论

InnoDB 不支持 AUTO_INCREMENT 但没有为表定义主键，但 MyISAM 支持。选择您要使用的引擎(MyISAM 或 InnoDB)，并相应地处理 CREATE TABLE 语句。