为 SQL Server 表中的用户生成随机令牌(唯一 ID)
我想在 sql server 中创建一个表并用数据(人们的信息)填充它.目标是让每个人在第一行都有一个唯一的 ID，该 ID 必须以固定字母 GM 开头，后跟 AZ 或 2-9数字，名字的首字母，姓氏的首字母和 AZ 或 2-9.
I want to create a table in sql server and fill it up with data (people's info). The goal is to get every person a unique ID in the first row which has to start with fixed alphabets GM followed by A-Z or 2-9 numeric , initial of First name, Initial of Last name and A-Z or 2-9.
推荐答案
注意:根据您所要求的逻辑，我认为您无法在不检查表中不包含生成的值的情况下生成唯一值，唯一的方法是使用 NEWID() 函数生成 GUID，否则我认为总是存在重复风险
Note: With the logic you are requesting i don't think you can produce Unique value without check that the table doesn't contains the generated value, the only way is to use NEWID() function that generate a GUID, else i think that there is always a duplication risk
我不知道这是否是最好的方法，但我可以说它提供了预期的输出.您可以创建此函数并使用它来生成标识符:
I don't know if this is the best way to do that, but i can say that it gives the expected output. You can create this function and use it to generate the identifier:
ALTER FUNCTION dbo.CreateIdentifier
(
-- Add the parameters for the function here
@Firstname varchar(255),
@Lastname varchar(255),
@random1 decimal(18,10) ,
@random2 decimal(18,10)
)
RETURNS varchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @S VARCHAR(10)
DECLARE @S1 VARCHAR(1)
DECLARE @S2 VARCHAR(1)
DECLARE @len INT
DECLARE @Random1Fixed INT
DECLARE @Random2Fixed INT
declare @alphabet varchar(36) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ23456789'
SET @alphabet = REPLACE(@alphabet,LEFT(@Lastname,1),'')
SET @alphabet = REPLACE(@alphabet,LEFT(@Firstname,1),'')
SELECT @len = len(@alphabet)
SET @Random1Fixed = ROUND(((@len - 1 -1) * @random1 + 1), 0)
SET @Random2Fixed = ROUND(((@len - 1 -1) * @random2 + 1), 0)
SET @S1 = substring(@alphabet, convert(int, @Random1Fixed ), 1)
SET @S2 = substring(@alphabet, convert(int, @Random2Fixed), 1)
SET @S = 'GM' + @S1 + LEFT(@Firstname,1) + LEFT(@Lastname,1) + @S2
RETURN @S
END
您可以将其用作以下内容
And you can use it as the following
SELECT dbo.CreateIdentifier('John','Wills',RAND(),RAND())
我将 RAND() 作为参数传递，因为它不能在函数中使用
I am passing RAND() as parameter because it cannot be used within a function
相关文章