SQL 如何像UID12345678一样在SQL中自动生成唯一ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20674282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to automatically generate unique id in SQL like UID12345678?
提问by Nishantha
I want to automatically generate unique id with per-defined code attach to it.
我想自动生成带有每个定义的代码的唯一 id 附加到它。
ex:
前任：
UID12345678
CUSID5000
I tried uniqueidentifierdata type but it generate a id which is not suitable for a user id.
我尝试了uniqueidentifier数据类型，但它生成了一个不适合用户 ID 的 ID。
Any one have suggestions?
有人有建议吗？
回答by marc_s
The only viable solution in my opinion is to use
我认为唯一可行的解决方案是使用
- an
ID INT IDENTITY(1,1)column to get SQL Server to handle the automatic increment of your numeric value - a computed, persistedcolumn to convert that numeric value to the value you need
- 一
ID INT IDENTITY(1,1)列让 SQL Server 处理数值的自动递增 - 一个计算的、持久的列，用于将该数值转换为您需要的值
So try this:
所以试试这个：
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....
)
Now, every time you insert a row into tblUserswithout specifying values for IDor UserID:
现在，每次插入一行tblUsers而不为IDor指定值时UserID：
INSERT INTO dbo.tblUsersCol1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)
then SQL Server will automatically and safelyincrease your IDvalue, and UserIDwill contain values like UID00000001, UID00000002,...... and so on - automatically, safely, reliably, no duplicates.
那么 SQL Server 将自动且安全地增加您的ID值，UserID并将包含诸如UID00000001、UID00000002、......等值- 自动、安全、可靠、无重复。
Update:the column UserIDis computed- but it still OF COURSEhas a data type, as a quick peek into the Object Explorer reveals:
更新：列UserID的计算-但它仍然课程的有一个数据类型，作为一个快速不期而遇的对象资源管理器显示：
回答by Nishantha
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,
[Name] VARCHAR(50) NOT NULL,
)
marc_s's Answer Snap
marc_s 的回答快照
回答by Julius Depulla
Reference:https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-2017
参考：https: //docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-2017
-- Creating a table using NEWID for uniqueidentifier data type.
-- 使用 NEWID 为 uniqueidentifier 数据类型创建表。
CREATE TABLE cust
(
CustomerID uniqueidentifier NOT NULL
DEFAULT newid(),
Company varchar(30) NOT NULL,
ContactName varchar(60) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(30) NOT NULL,
StateProvince varchar(10) NULL,
PostalCode varchar(10) NOT NULL,
CountryRegion varchar(20) NOT NULL,
Telephone varchar(15) NOT NULL,
Fax varchar(15) NULL
);
GO
-- Inserting 5 rows into cust table.
INSERT cust
(CustomerID, Company, ContactName, Address, City, StateProvince,
PostalCode, CountryRegion, Telephone, Fax)
VALUES
(NEWID(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL,
'90110', 'Finland', '981-443655', '981-443655')
,(NEWID(), 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP',
'08737-363', 'Brasil', '(14) 555-8122', '')
,(NEWID(), 'Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL,
'1010', 'Argentina', '(1) 135-5555', '(1) 135-4892')
,(NEWID(), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL,
'8010', 'Austria', '7675-3425', '7675-3426')
,(NEWID(), 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68');
GO
回答by Nishantha
If you want to add the id manuallyyou can use,
如果你想手动添加id ，你可以使用，
PadLeft()or String.Format()method.
string id;
char x='0';
id=id.PadLeft(6, x);
//Six character string id with left 0s e.g 000012
int id;
id=String.Format("{0:000000}",id);
//Integer length of 6 with the id. e.g 000012
Then you can append this with UID.
然后你可以用 UID 附加它。
回答by narendra
Table Creating
表创建
create table emp(eno int identity(100001,1),ename varchar(50))
Values inserting
值插入
insert into emp(ename)values('narendra'),('ajay'),('anil'),('raju')
Select Table
选择表
select * from emp
Output
输出
eno ename
100001 narendra
100002 rama
100003 ajay
100004 anil
100005 raju
