MySQL:#1075-错误的表格定义；自动增量与另一个键? [英] MySQL: #1075 - Incorrect table definition; autoincrement vs another key?

问题描述

这是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 列从未在查询中使用，它只是为了视觉上的方便(因此很容易看到表的增长方式). 成员ID 是实际密钥，是唯一的，并且成员ID 用于查询中以识别任何成员(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(成员ID)创建此表时，出现错误消息:

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; 

这篇关于MySQL:#1075-错误的表格定义；自动增量与另一个键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！