如何用SQL批量删除通讯系统中过期24小时的Base64 Token？

Token生成与解析逻辑 #

我开发的通讯稿系统通过GUID与当前日期生成唯一的Base64格式Token，对应的C#生成和解析代码如下：

Token生成代码 #

public static string GetUniqueToken()
{
  byte[] time = BitConverter.GetBytes(DateTime.UtcNow.ToBinary());
  byte[] key = Guid.NewGuid().ToByteArray();
  return Convert.ToBase64String(time.Concat(key).ToArray());
}

Token解析代码 #

public static DateTime GetDateFromToken(string token)
{
  byte[] data = Convert.FromBase64String(token);
  return DateTime.FromBinary(BitConverter.ToInt64(data, 0));
}

问题描述 #

原本我通过C#循环遍历所有记录，判断Token是否过期（超过24小时）后删除，但这种方式效率极低，因此希望用单条SQL语句批量删除过期Token。我编写的SQL代码如下：

DECLARE @limitDate DATETIME;
SET @limitDate = DATEADD(HOUR, -48, GETDATE());

DELETE FROM newsletter
WHERE DATEADD(SECOND, 
    CONVERT(BIGINT, CONVERT(VARBINARY(MAX), SUBSTRING(token, 37, 8), 2)) / 10000, '19700101') < @limitDate;

生成的Token示例：

Akias1XZ20jFoDOnWSZfRqgFHdv+UUVP

执行上述SQL时触发报错：

Msg 8114, Level 16, State 5, Line 6

Error converting data type nvarchar to varbinary.

正确SQL实现方案 #

方案1（兼容SQL Server所有版本） #

使用XML函数解码Base64，通过CTE预先解码Token避免重复计算，再反转字节序还原出DateTime的二进制值，最后计算过期时间：

DECLARE @expireHours INT = 24; -- 设置过期时长，此处为24小时
DECLARE @limitDate DATETIME = DATEADD(HOUR, -@expireHours, GETDATE());

WITH DecodedTokens AS (
  SELECT 
    token,
    -- 将Base64字符串解码为二进制数据
    CAST(N'' AS XML).value('xs:base64Binary(sql:column("token"))', 'VARBINARY(MAX)') AS decoded_bytes
  FROM newsletter
)
DELETE FROM newsletter
WHERE EXISTS (
  SELECT 1 
  FROM DecodedTokens dt
  WHERE dt.token = newsletter.token
  AND DATEADD(MILLISECOND, 
    -- 反转前8字节的字节序，转换为bigint后计算对应的DateTime
    CONVERT(BIGINT, 
      CONVERT(VARBINARY(8), 
        SUBSTRING(dt.decoded_bytes,8,1) + SUBSTRING(dt.decoded_bytes,7,1) +
        SUBSTRING(dt.decoded_bytes,6,1) + SUBSTRING(dt.decoded_bytes,5,1) +
        SUBSTRING(dt.decoded_bytes,4,1) + SUBSTRING(dt.decoded_bytes,3,1) +
        SUBSTRING(dt.decoded_bytes,2,1) + SUBSTRING(dt.decoded_bytes,1,1)
      )
    ) / 10000, '0001-01-01'
  ) < @limitDate
);

方案2（适用于SQL Server 2016及以上版本） #

使用SQL Server 2016新增的STRING_DECODE函数直接解码Base64，效率更高：

DECLARE @expireHours INT = 24;
DECLARE @limitDate DATETIME = DATEADD(HOUR, -@expireHours, GETDATE());

WITH DecodedTokens AS (
  SELECT 
    token,
    STRING_DECODE(token, 'base64') AS decoded_bytes
  FROM newsletter
)
DELETE FROM newsletter
WHERE EXISTS (
  SELECT 1 
  FROM DecodedTokens dt
  WHERE dt.token = newsletter.token
  AND DATEADD(MILLISECOND, 
    CONVERT(BIGINT, 
      CONVERT(VARBINARY(8), 
        SUBSTRING(dt.decoded_bytes,8,1) + SUBSTRING(dt.decoded_bytes,7,1) +
        SUBSTRING(dt.decoded_bytes,6,1) + SUBSTRING(dt.decoded_bytes,5,1) +
        SUBSTRING(dt.decoded_bytes,4,1) + SUBSTRING(dt.decoded_bytes,3,1) +
        SUBSTRING(dt.decoded_bytes,2,1) + SUBSTRING(dt.decoded_bytes,1,1)
      )
    ) / 10000, '0001-01-01'
  ) < @limitDate
);

错误原因说明 #

原SQL的错误在于：直接截取Base64字符串的子串并尝试用CONVERT(VARBINARY, ..., 2)转换，该转换方式仅适用于十六进制字符串，而Base64是完全不同的编码格式，无法直接转换为二进制数据，必须先完整解码Base64为二进制后再处理。

内容的提问来源于stack exchange，提问作者SwissDev