如何像UID12345678一样在SQL中自动生成唯一ID? [英] How to automatically generate unique id in SQL like UID12345678?

问题描述

我想自动生成带有每个定义的代码的唯一 ID.

I want to automatically generate unique id with per-defined code attach to it.

例如:

UID12345678
CUSID5000

我尝试了 uniqueidentifier 数据类型，但它生成的 ID 不适合用户 ID.

I tried uniqueidentifier data type but it generate a id which is not suitable for a user id.

有人有什么建议吗?

推荐答案

在我看来唯一可行的解​​决方案是使用

The only viable solution in my opinion is to use

• 一个 ID INT IDENTITY(1,1) 列，用于让 SQL Server 处理数值的自动递增
• 一个计算的、持久化的列，用于将该数值转换为您需要的值

• an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
• a computed, persisted column to convert that numeric value to the value you need

试试这个:

CREATE TABLE dbo.tblUsers
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   UserID AS 'UID' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8) PERSISTED,
   .... your other columns here....
  )

现在，每次您在 tblUsers 中插入一行而不指定 ID 或 UserID 的值时:

Now, every time you insert a row into tblUsers without specifying values for ID or UserID:

INSERT INTO dbo.tblUsersCol1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

然后 SQL Server 将自动且安全地增加您的 ID 值，并且 UserID 将包含类似 UID00000001 的值, UID00000002,...... 等等 - 自动、安全、可靠、无重复.

then SQL Server will automatically and safely increase your ID value, and UserID will contain values like UID00000001, UID00000002,...... and so on - automatically, safely, reliably, no duplicates.

更新:UserID 列被计算 - 但它仍然当然有一个数据输入，快速浏览对象资源管理器会发现:

Update: the column UserID is computed - but it still OF COURSE has a data type, as a quick peek into the Object Explorer reveals:

这篇关于如何像UID12345678一样在SQL中自动生成唯一ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！