如何在 SQL 中自动生成唯一 ID，如 UID12345678?

2022-01-16 00:00:00 sql sql-server-2008 sql-server sql-server-2008-r2 sql-server-2012

我想自动生成带有每个定义代码的唯一 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:

相关文章