如何限制每个用户每月插入一行？

Question

我想每月做一次比赛。我有用户和产品。每个用户这个月都可以投票选择他/她最喜欢的产品。我的投票表是这样的：

+---------+-----------+------------+
| user    | product   | date       |
+---------+-----------+------------+
| 1       | 1         | 2018-09-03 |
| 1       | 2         | 2018-09-03 |
| 1       | 2         | 2018-08-28 |
+---------+-----------+------------+

用户可以喜欢相同的产品，但不能在同一个月。

我的问题是，我无法找到一个有效的主键来防止重复投票。

如果我使用PK (user,product)，用户不能为同一产品投票超过一次。如果我使用PK (user,product,date)，用户可以每天投票一次相同的产品。

怎样才能在同一个月内避免重复投票？

注意：date应该是一个有效的DateTime列。

Answer 1

MySQL不允许键中的函数，只允许列引用。因此，如果要受唯一键的限制，则需要另一列，该列只包含事件的年份和月份。最简单、最直接的方法是将date值降为类似于YYYY-MM-01的值。如果使用的是MySQL 5.7或更高版本，则可以将该列声明为已生成的列，并定义一个表达式，将日期部分转换为常量01值。

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

现在您可以创建UNIQUE INDEX permonth (user, product, rdate)了。这是在物理层面上建立的独特性。

在逻辑层次上，唯一性可以通过(至少)两种常见的方式来实现。

首先，您可以定义一个BEFORE INSERT触发器，该触发器将执行日期的扩展比较并拒绝插入重复的内容。但是触发器并不是一个非常干净和容易的工具，因为它们的含蓄性，我的建议是尽量避免触发器。

更健壮的方法是将insert语句封装到存储的例程中，执行call SafeVoteInsert( userID, productID, date )而不是普通的INSERT vote VALUES ...。