logo标签列表

MySQL唯一键不起作用

mysqlsql
10

我正在构建一个基于6个不同因素跟踪库存的类型化清单表。我正在使用类似于这个的I查询：

INSERT INTO inventory ( productid, factor1, factor2, factor3, factor4, factor5, factor6, quantity, serial_number)
VALUES (242332,1,1,1,'V67',3.30,'NEW',10,NULL)
ON DUPLICATE KEY UPDATE `quantity` = VALUES(`quantity`) + quantity;
CREATE TABLE inventory (
  id INT(11) NOT NULL AUTO_INCREMENT,
  productid INT(11) NOT NULL,
  factor1 FLOAT(10,2) DEFAULT '0.00',
  factor2 FLOAT(10,2) DEFAULT '0.00',
  factor3 FLOAT(10,2) DEFAULT '0.00',
  factor4 FLOAT(10,2) DEFAULT '0.00',
  factor5 FLOAT(10,2) DEFAULT '0.00',
  factor6 FLOAT(10,2) DEFAULT '0.00',
  serial_number VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY product_factors_serial (productid,factor1,factor2,factor3,factor4,factor5,factor6,serial_number)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 
CREATE TABLE `inventory` (
    `stockid` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `productid` int(11) unsigned NOT NULL,
    `factor1` int(11) unsigned NOT NULL,
    `factor2` int(11) unsigned NOT NULL,
    `factor3` int(11) unsigned NOT NULL,
    `factor4` varchar(8) NOT NULL,
    `factor5` decimal(10,2) NOT NULL,
    `factor6` enum('A','B','C','D','NEW') NOT NULL,
    `quantity` int(11) NOT NULL,
    `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `serial_number` varchar(11) DEFAULT NULL,
    PRIMARY KEY (`stockid`),
    UNIQUE KEY `serial_number` (`serial_number`),
    UNIQUE KEY `productid_2` (`productid`,`factor1`,`factor2`,`factor3`,`factor4`,`factor5`,`factor6`,`serial_number`),
    KEY `productid` (`productid`),
    KEY `factor1` (`factor1`),
    KEY `factor2` (`factor2`),
    KEY `factor3` (`factor3`),
    CONSTRAINT `books_stock_ibfk_2` FOREIGN KEY (`productid`) REFERENCES `produx_products` (`productid`),
    CONSTRAINT `books_stock_ibfk_5` FOREIGN KEY (`factor1`) REFERENCES `table_factor1` (`factorid`),
    CONSTRAINT `books_stock_ibfk_6` FOREIGN KEY (`factor2`) REFERENCES `table_factor2` (`factorid`),
    CONSTRAINT `books_stock_ibfk_7` FOREIGN KEY (`factor3`) REFERENCES `table_factor3` (`factorid`)
) 
ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1

更深入一些：

这个表的目的是为了保存库存数量。我认为这很简单明了。这些数量之间的区别如下：

  • 因素1 = storeid（拥有此数量的商店的唯一标识符）。

  • 因素2 = supplierid（我们得到该数量的供应商的唯一标识符）

  • 因素3 = warehouseid（它所在的仓库的唯一标识符）

  • 因素4 = locationid（位置的唯一字符串。它在货架上被物理绘制出来）

  • 因素5 = cost（我们支付每个数量的费用）

  • 因素6 = condition（枚举 ['NEW'，'USED'，'RENTAL'，'PREORDER']。前三个很容易，第四个是我们订购的数量，想要出售，但尚未收到。）

我知道这是一个繁重的键，但我被迫保持这种方式。我已经听到很多人建议将成本或条件移动到产品表中。我不能这样做。成本并不总是相同的，因为我们从拍卖或其他地方购买很多变化很大的物品。

我希望这有助于更好地解释我的意图。

2
作为一般性评论，你的那个键相当重要。通常建议使用合成键（如果序列号不唯一，它就不是一个好的序列号）。你的创建表语句会很有帮助。show create table inventory - barry-johnson
我马上会发布创建语句。此外，还有第二个专门针对“serial_number”的唯一键。由于该表包含序列化和非序列化库存，因此“serial_number”也必须是该键的一部分。否则，每组因素只能有一个非序列化库存。 - wesleywmd
查看规范化（至少在因素1到3方面） - Strawberry
1
@halfer。谢谢。这不是报复，而是请求更多信息。 - wesleywmd
是的，我们大多数的库存位置都是4英尺 x 4英尺 x 4英尺的托盘货架。因此，在那个空间中的一个托盘可以容纳相当多的产品，但不一定是相同的产品。我们会将物品存放在任何合适的地方。就像我在编辑中解释的那样，成本必须与库存条目保持一致，因为我们在拍卖等地方购买，我们的成本可能真的很高。 - wesleywmd
显示剩余11条评论
1个回答
19
Mysql允许在唯一约束中有多个NULL。在你的serial_number列中，用一个值代替NULL并触发约束，如下所示：

http://sqlfiddle.com/#!2/9dbd19/1

一个唯一索引允许对能够包含NULL值的列进行多个NULL值的控制。

文档

将该列设为非空，并使用空字符串''。
1
哇！！sqlfiddle.com！！我喜欢我喜欢。谢谢你送给我的新玩具！ - wesleywmd
好的。这更详细地解释了我的问题。但我该如何解决呢？并非所有库存都有序列号，只有序列化库存才有。序列化库存始终会有一个计数为1。我使用存储过程来确保这一点。 - wesleywmd
我不得不删除UNIQUE KEY（'serial_number'），因为您的解决方案使其变得多余。现在我唯一关心的是，为什么INSERT语句返回2行更改而不是1行？这是因为重复键调用还是实际上更改了2行？ - wesleywmd
1
从手册中得知：使用ON DUPLICATE KEY UPDATE，每行受影响的行数为1，如果该行被插入为新行，则为1，如果更新现有行，则为2。 - Turophile
太好了！这正是我希望 SQL 做的事情。谢谢你。 - wesleywmd
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接