MySQL:#1075 - 不正确的表定义；自动增量与另一个键?

2021-11-20 00:00:00 mysql auto-increment primary-key mysql-error-1075

这是 MySQL 5.3.X+ db 中的一个表:

Here is a table in MySQL 5.3.X+ db:

CREATE TABLE members` (
  `id` int(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
  `memberid` VARCHAR( 30 ) NOT NULL ,
  `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `firstname` VARCHAR( 50 ) NULL ,
  `lastname` VARCHAR( 50 ) NULL ,
  UNIQUE (memberid),
  PRIMARY KEY (id) 
) ENGINE = MYISAM;

Id 列在查询中从不使用，它只是为了视觉方便(所以很容易看到表是如何增长的).Memberid 是一个实际的键，是唯一的，memberid 用于在查询中识别任何成员 (WHERE memberid='abcde').

Id column is never used in queries, it is just for visual convenience (so it's easy to see how the table grows). Memberid is an actual key, is unique, and memberid is used in queries to identify any member (WHERE memberid='abcde').

我的问题是:如何保持auto_increment，但将memberid设为主键?那可能吗?当我尝试使用 PRIMARY KEY (memberid) 创建此表时，出现错误:

My question is: how to keep auto_increment, but make memberid as a primary key? Is that possible? When I try to create this table with PRIMARY KEY (memberid), I get an error:

1075 - 不正确的表定义；自动列只能有一个，并且必须定义为键

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

如果性能非常重要(尽管磁盘空间不是)，最好的选择是什么(希望有一种方法可以保留 id 列，以便性能良好并且查询通过 memberid 而不是通过 id 识别任何用户)?

What is the best choice (Hopefully, there is a way to keep id column so performance is good and queries identify any user by memberid, not by id), if the performance is very important (although the disk space is not)?

推荐答案

你可以有一个不是 PRIMARY KEY 的自动递增列，只要有是一个索引(键):

You can have an auto-Incrementing column that is not the PRIMARY KEY, as long as there is an index (key) on it:

CREATE TABLE members ( 
  id int(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
  memberid VARCHAR( 30 ) NOT NULL , 
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
  firstname VARCHAR( 50 ) NULL , 
  lastname VARCHAR( 50 ) NULL , 
  PRIMARY KEY (memberid) ,
  KEY (id)                          --- or:    UNIQUE KEY (id)
) ENGINE = MYISAM;

相关文章