复合主键和自动增量列，但不是主键 [英] composite primary key and autoincrement column but NOT primary key

问题描述

我正在努力实现这一目标:

I'm trying to achieve this:

INSERT INTO `tbl_result` (`var1`, `var2`, `var3`, `year`, `result`) 
VALUES (%f, %f, %d, %d, %f)
ON DUPLICATE KEY UPDATE result=%f;

这些是我想唯一地存储在tbl_result数据库表中的实时计算器的结果，即使计算结果发生变化也要保持更新.在我看来，以上是最好的方法.

These are results of a realtime calculator that I want to uniquely store in the tbl_result database table, and maintain updated even if the calculation result changes. The above seems to me like the best way to do this.

但是要使其正常工作，var1，var2，var3，year必须是主键:

However for this to work var1, var2, var3, year must be primary keys:

CREATE  TABLE `tbl_result` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `var1` DECIMAL(6,2) UNSIGNED NOT NULL ,
  `var2` DECIMAL(3,2) UNSIGNED NOT NULL ,
  `var3` INT(11) UNSIGNED NOT NULL ,
  `year` INT(4) UNSIGNED NOT NULL ,
  `result` DECIMAL(8,4) NOT NULL ,
  PRIMARY KEY (`var1`, `var2`, `var3`, `year`) 
);

但是因为我还需要存储一个唯一的ID才能与多个用户交叉引用结果.我收到以下错误:

But because I also need to store a unique id to cross reference results with multiple users. I get the following error:

错误代码:1075.错误的表定义；只能有一个自动列，并且必须将其定义为键

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

MySql版本为5.5.31-0ubuntu0.12.04.1，是否可以使用guid值设置列的默认值?

MySql version is 5.5.31-0ubuntu0.12.04.1, is it possible to set the default of a column with a guid value?

谢谢，

P.S.在我看来，这似乎是一个可行的方案，是否可以向MySql报告建议?

P.S. this sounds like a plausible scenario to me, is it possible to report a suggestion to MySql?

推荐答案

对此的一种解决方案是使用ID作为主键，使用var1, var2, var3, year字段作为备用键(通过使用UNIQUE KEY约束

One solution to this is using the ID as your primary key, and the var1, var2, var3, year fields as an alternate key by using an UNIQUE KEY constraint

因此，您的表定义应如下所示:

So, your table definition shall look like this:

CREATE  TABLE `tbl_result` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `var1` DECIMAL(6,2) UNSIGNED NOT NULL ,
  `var2` DECIMAL(3,2) UNSIGNED NOT NULL ,
  `var3` INT(11) UNSIGNED NOT NULL ,
  `year` INT(4) UNSIGNED NOT NULL ,
  `result` DECIMAL(8,4) NOT NULL ,
  PRIMARY KEY (`ID`),
  UNIQUE KEY (`var1`, `var2`, `var3`, `year`) 
);

UNIQUE KEY约束将防止重复插入字段.

The UNIQUE KEY constraint will prevent from duplicate insertions of your fields.

这篇关于复合主键和自动增量列，但不是主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！