我需要在 MySQL 中自动增加一个不是主键的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1151314/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I need to auto_increment a field in MySQL that is not primary key
提问by littleK
Right now, I have a table whose primary key is an auto_incrementfield. However, I need to set the primary key as username, date(to ensure that there cannot be a duplicate username with a date).
现在，我有一个主键是一个auto_increment字段的表。但是，我需要将主键设置为username, date（以确保不能有带有日期的重复用户名）。
I need the auto_incrementfield, however, in order to make changes to row information (adding and deleting).
auto_increment但是，我需要该字段才能更改行信息（添加和删除）。
What is normally done with this situation?
这种情况通常怎么办？
Thanks!
谢谢！
采纳答案by Artem Russakovskii
Just set a unique index on composite of (username, date).
只需在（用户名，日期）的组合上设置唯一索引。
ALTER TABLE `table` ADD UNIQUE INDEX `name` (`username`, `date`);
Alternatively, you can try to
或者，您可以尝试
ALTER TABLE `table` DROP PRIMARY KEY, ADD PRIMARY KEY(`username`,`date`);
and I think in the latter case you need those columns to be declared NOT NULL.
我认为在后一种情况下，您需要将这些列声明为 NOT NULL。
回答by Abiaeme Johnson
I know this is old question, here is how i solved the problem -
我知道这是个老问题，这是我解决问题的方法-
ALTER TABLE `student_info` ADD `sn` INT(3) UNIQUE NOT NULL AUTO_INCREMENT FIRST
回答by nos
Use something like:
使用类似的东西：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
user VARCHAR(32) NOT NULL,
thedate DATE NOT NULL,
UNIQUE(user,thedate)
);
If you already have the table, and just want to add a unique constraint on user+thedate, run
如果您已经拥有该表，并且只想在 user+thedate 上添加唯一约束，请运行
ALTER TABLE users ADD UNIQUE KEY user_date_idx (user, thedate);
回答by zombat
Change your current primary key to be a unique key instead:
将您当前的主键改为唯一键：
ALTER TABLE table DROP PRIMARY KEY, ADD UNIQUE KEY(username,date);
The auto_increment will function normally after that without any problems. You should also place a unique key on the auto_increment field as well, to use for your row handling:
之后 auto_increment 将正常运行，没有任何问题。您还应该在 auto_increment 字段上放置一个唯一键，以用于行处理：
ALTER TABLE table ADD UNIQUE KEY(id);
