sql - t-sql 字符串唯一 ID(Northwind 数据库)

Question

一段时间以来，我一直在努力解决这个问题，但毫无用处。

我在 mssql 数据库中有一个表，我想使用存储过程插入新行

CREATE TABLE "Customers" (
"CustomerID" NCHAR(5) NOT NULL,
"CompanyName" NVARCHAR(40) NOT NULL,
"ContactName" NVARCHAR(30) NULL,
"ContactTitle" NVARCHAR(30) NULL,
"Address" NVARCHAR(60) NULL,
"City" NVARCHAR(15) NULL,
"Region" NVARCHAR(15) NULL,
"PostalCode" NVARCHAR(10) NULL,
"Country" NVARCHAR(15) NULL,
"Phone" NVARCHAR(24) NULL,
"Fax" NVARCHAR(24) NULL,
PRIMARY KEY ("CustomerID")
);

问题是 CustomerID 字段，它包含每个记录的唯一字符串(ALFKI、BERGS、BERGS 等)

我想制作一个存储过程，它将插入一行新数据并创建一个唯一的 CustomerID。内置函数是不可能的，因为我需要字符串的长度为 5 个字符。

我有一个生成 5 个字符 ID 的程序，如下所示

begin

declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(max) = ''

while @i < 5
begin
        set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)

    set @i = @i + 1
end

Select (cast(@id as nvarchar(400)))

end

还有我尝试过但没有用的那个。它应该选择一个唯一的 id(set @id = 'ANATR' 是故意让它进入循环的

begin
declare @randID varchar(5) = ''
declare @selectID varchar(20) = ''
declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(10) = ''

while @i < 5
begin
   set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)           
    set @i = @i + 1
end
select @id
set @id = 'ANATR'

SET @selectID = (SELECT CustomerID FROM CUSTOMERS WHERE CustomerID = @id)


while @selectID <> 'NULL'
begin
    set @id = ''
    while @i < 5
        begin
            set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)           
            set @i = @i + 1
        end

    SET @selectID = (SELECT CustomerID FROM CUSTOMERS WHERE CustomerID = @id)   

    SELECT @id
end


end

这是我目前的插入过程

CREATE PROCEDURE [dbo].[InsertCustomers]

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30) = NULL,

@ContactTitle nvarchar(30) = NULL,

@Address nvarchar(60) = NULL,

@City nvarchar(15) = NULL,

@Region nvarchar(15) = NULL,

@PostalCode nvarchar(10) = NULL,

@Country nvarchar(15) = NULL,

@Phone nvarchar(24) = NULL,

@Fax nvarchar(24) = NULL

)

AS

SET NOCOUNT OFF;

 INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

Answer 1

这里的主要问题是，从生成的字符串中检测冲突并重试的增量成本会随着生成的字符串越来越多而增加(因为您必须读取所有这些字符串以确保您没有生成重复项)。同时，命中重复项的几率上升，这意味着表格越大，这个过程就会越慢。

为什么需要在运行时生成唯一字符串？提前构建它们。 This article和 this post都是关于随机数的，但是基本概念是一样的。您构建了一组独特的字符串，并在需要时从堆栈中取出一个。在应用程序的整个生命周期中，您发生冲突的可能性始终保持在 0%(前提是您建立了足够多的唯一值堆栈)。在您自己的设置中预先支付碰撞成本，而不是随着时间的推移逐渐增加(并且以用户等待这些尝试最终产生唯一编号为代价)。

这将生成 100,000 个唯一的 5 字符字符串，一次性成本很低，大约 1 秒(在我的机器上):

;WITH 
 a(a) AS 
 (
   SELECT TOP (26) number + 65 FROM master..spt_values 
   WHERE type = N'P' ORDER BY number
 ),
 b(a) AS 
 (
   SELECT TOP (10) a FROM a ORDER BY NEWID()
 )
SELECT DISTINCT CHAR(b.a) + CHAR(c.a) + CHAR(d.a) + CHAR(e.a) + CHAR(f.a)
FROM b, b AS c, b AS d, b AS e, b AS f;

这还不够吗？通过将 TOP (10) 更改为 TOP (20)，您可以生成大约 112 万个唯一值。这花了 18 秒。还不够吗？ TOP (24) 将在大约 2 分钟内为您提供将近 800 万。当您生成更多字符串时，它的成本将成倍增加，因为 DISTINCT 必须执行您想要执行的相同重复检查 every single time您添加了一个客户。

因此，创建一个表:

CREATE TABLE dbo.StringStack
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  String CHAR(5) NOT NULL UNIQUE
);

插入那个集合:

;WITH 
 a(a) AS 
 (
   SELECT TOP (26) number + 65 FROM master..spt_values 
   WHERE type = N'P' ORDER BY number
 ),
 b(a) AS 
 (
   SELECT TOP (10) a FROM a ORDER BY NEWID()
 )
INSERT dbo.StringStack(String)
SELECT DISTINCT CHAR(b.a) + CHAR(c.a) + CHAR(d.a) + CHAR(e.a) + CHAR(f.a)
FROM b, b AS c, b AS d, b AS e, b AS f;

然后只需创建一个过程，在需要时从堆栈中弹出一个:

CREATE PROCEDURE dbo.AddCustomer
  @CustomerName VARCHAR(64) /* , other params */
AS
BEGIN
  SET NOCOUNT ON;
  
  DELETE TOP (1) dbo.StringStack
    OUTPUT deleted.String, @CustomerName /* , other params */
    INTO dbo.Customers(CustomerID, CustomerName /*, ...other columns... */);
END
GO

没有愚蠢的循环，不需要检查您生成的 CustomerID 是否存在，等等。您唯一需要构建的是某种类型的检查，它会在您需要时通知您越来越低。

顺便说一句，这些是 CustomerID 的糟糕标识符。顺序代理键有什么问题，比如 IDENTITY 列？一个包含所有这些努力的 5 位随机字符串，比系统可以更轻松地为您生成的唯一数字更好吗？