mysql - 对表结构感到困惑

Question

我正在开发一个库存系统，在 MariaDB 中保存有关产品的信息。产品存储在表“article”中，每个产品都有一个唯一的 ID。

我有另一个表“stocks”，其中包含有关文章的信息，即金额和插入日期。插入日期很重要，因为同一篇文章应该可以在数据库中出现两次，但插入日期不同。

例如，您周一购买了两个苹果并将它们放入数据库中。星期三，您再次购买了两个苹果并将它们也放入数据库中。数据库中总共有 4 个苹果，但位于两个单独的行中，并且注册日期不同。这种区别对于我的系统来说是一个重要功能，因为我希望能够知道产品已在数据库中存在了多少天。

我的表格如下所示:

文章

+------------+--------------+----------+
| article_id | article_name | category |
+------------+--------------+----------+
|          1 | Shiny Apple  | Fruit    |
+------------+--------------+----------+

股票

+----------+------------+--------+-------------------+
| stock_id | article_id | amount | registration_date |
+----------+------------+--------+-------------------+
|        1 |          1 |      2 | 2017-10-23        |
|        2 |          1 |      2 | 2017-10-25        |
+----------+------------+--------+-------------------+

现在我的问题是，自动递增的 stock_id 使得同一篇文章有​​可能两次具有相同的注册日期，这是我不想要的。

例如，我可以多次执行以下查询并获取具有相同日期的多行，尽管注册日期是 PK 的一部分

INSERT INTO `stocks` (`article_id`, `amount`, `registration_date`)
VALUES (1, 3, CURDATE())
ON DUPLICATE KEY UPDATE amount = amount + 1

预期的行为是，如果您在同一天多次插入文章，则“金额”字段应该递增，而不是插入新行。

如果 stock_id 不是主键的一部分，则这是可能的，但是它不会自动递增，因为 MySQL/MariaDB 不允许自动递增字段不投注 PK 的一部分。

另一种选择是完全删除 stock_id 字段，但为了方便起见，我有点需要它，所以我想知道是否有更好的方法。

我想过在程序中解决这个问题(检查股票中是否存在文章，检查日期等)，但这对我来说似乎不太优雅。

我觉得一定有一个明显且简单的解决方案，但我看不到。如有任何帮助，我们将不胜感激。

Answer 1

我认为您正在寻找文章和日期的唯一索引/约束:

alter table stocks add constraint unq_stocks_article_date unique (article, registration_date);

您想要的更新将如下所示:

INSERT INTO stocks (article_id, amount, registration_date)
    VALUES (1, 3, CURDATE())
    ON DUPLICATE KEY UPDATE amount = amount + values(amount);

增量基于传递到插入中的值。