校验值为最新的5个

Question

我有下表：

CREATE TABLE `visitor_tokens` (
  `id` int(7) UNSIGNED NOT NULL,
  `visitor` int(7) UNSIGNED NOT NULL,
  `token` varchar(150) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `visitor_tokens`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `visitor_tokens`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

现在，我从客户端获得了一个token和visitor。我想检查表中是否存在该token (并且属于visitor)。此外，此token必须是visitor的5个最新令牌(由created_at列指示)。

这是我的尝试：SELECT null FROM visitor_tokens WHERE visitor=? AND token=?

现在我缺少的部分是严格的token是5个最新的令牌。意思是，如果visitor有7个令牌(按created_at升序排列)：[1, 2, 3, 4, 5, 6, 7]，我想检查访问者是否有令牌2，我不会得到NULL。对于3,4,5,6,7，我会选择NULL。(NULL表示在给定条件下确实成功地找到了令牌)。对于访问者令牌：[1,2,3]，我会为所有3个都获取NULL。

Answer 1

使用解析函数，我们可以尝试：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) rn
    FROM visitor_tokens
    WHERE visitor = 123
)

SELECT *
FROM cte
WHERE token = ? AND rn <= 5;

如果上面的select返回一条记录，那么根据您的逻辑，该令牌是有效的。

为了展示上面是如何处理样本数据的，考虑一下CTE是什么样子的：

visitor | token | created_at | rn
123     | 7     | 2021-01-07 | 1
123     | 6     | 2021-01-06 | 2
123     | 5     | 2021-01-05 | 3
123     | 4     | 2021-01-04 | 4
123     | 3     | 2021-01-03 | 5
123     | 2     | 2021-01-02 | 6
123     | 1     | 2021-01-01 | 7

因此，如果我们使用令牌4搜索访问者123，我们会找到一条记录，因为令牌4是该访问者最近的第四个令牌。另一方面，搜索令牌2将不会返回任何内容，因为它不是最近的5个。