sql-server - 在 SQL Server 上插入数据时如何填补身份空白？

Question

首先我想说的是，我知道 SQL 身份特性为什么以及如何工作来缓存将要使用的身份值，并且它不会是无缝的。因此，在服务器重新启动后（例如），它可以根据身份数据类型跳转 1000/10000 个值，我知道身份应该是没有意义的。

然而，多年前，我在旧的 SQL Server 版本上开发了一个链接缩短器，它的行为与上述不同。此链接缩短器使用 Id 列生成 url 的缩短部分。

升级 SQL Server 版本后，差距开始出现。

包含要缩短的链接的表格类似于以下内容（注意差距）：

Id     | Url
---------------------------------
1      | http://foo.bar
2      | http://bar.foo     
10001  | http://google.com
20001  | http://stackoverflow.com

然后我的应用程序将上述链接缩短如下（这不是数据库表）：

Id     | ShortenedUrl
---------------------------------
1      | foo.bar/a 
2      | foo.bar/b
10001  | foo.bar/fZ2sh
20001  | foo.bar/bbSz1

（只是为了说明，算法并不像1 = a，2 = b那么简单）

所以现在我有一个问题：拥有链接缩短器的全部目的是生成短链接。现在我有 4 个多余的字符。

我不能只使用该row_number() OVER (ORDER BY [ID])功能，因为我已经有生产中的数据会丢失/错误生成。

关于在生成下一个缩短链接时使用间​​隙的方法，我有哪些可能性？

更多信息：

我可以创建另一列，从 Id 列导入数据（对于已经存在的），并通过一个过程（称为数据插入）开始使用新逻辑，锁定需要锁定的任何内容以获得下一个可用的间隙编号新行。

此表当前仅用于 MVC 应用程序（没有指向它的外键）。当前正在通过直接插入插入数据，但我可以更新应用程序以使用（新）过程。换句话说，表结构可以改变，其中包含的信息不能。

标签： sql-server

Answer 1

一种选择是用序列替换身份。您仍然需要修复错误数据，但这可以让您将 URL 与密钥分开。

但据我所见，现有的缩短器需要一些工作。
使用好的算法，值没有理由20001需要超过 3 个字符。
如果您使用两个额外的“特殊”字符，您可以为缩短的 url 中的每个字符（AZ、az、0-9 和我们的特殊字符 . 和 _）获得 64 个可能的值，这足以存储 6 位数据. 我们已经通过这种方式进行了 base-64 编码，但是由于您只有数字，因此您可以获得更高的效率。

取一个数字20001，将其转换为二进制：

100111000100001

向左填充零，因此二进制位数是六的倍数：

000100111000100001

将其分成六组：

000100  111000   100001

这些组中的每一个都应映射到单个可能的字符。要获取原始 ID#，请反向执行相同操作。

使用此方案，您将262143在需要将第 4 个字符添加到 URL16777215之前、在添加第 5 个字符之前以及1073741823在添加第 6 个字符之前到达。

为了好玩，这里有一些 C# 代码来进行转换：

private static char[] charMap = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
                                 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
                                 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                                 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd',
                                 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n',
                                 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x',
                                 'y', 'z', '.', '_'};

public static string GetURLKey(int id)
{
    if (id <= 0) return "0";

    var bt = new byte[32];
    int pos = 32;
    while (id > 0)
    {
        pos--;
        bt[pos] = (byte)(id % 2);
        id >>= 1;
    }
    var bts = new Span<byte>(bt, pos, 32-pos);

    var r = 6 - (bts.Length % 6);
    if (r == 6) r = 0;

    return string.Create((bts.Length + r) / 6, bts.ToArray(), (span, arr) =>
    {
        int curChar = 0, charPos = r, bitArrPos = 0, curVal = 0;

        while (bitArrPos < arr.Length)
        {
            while (bitArrPos < arr.Length && charPos < 6)
            {
                curVal *= 2;
                curVal += (int)arr[bitArrPos];

                bitArrPos++;
                charPos++;
            }
            span[curChar] = charMap[curVal];
            curVal = 0;
            charPos = 0;
            curChar++;
        }

    });
}