时间戳

首页 > 解决方案 > 控制 ID 序列但接受自定义 ID 而不重复

sql - 控制 ID 序列但接受自定义 ID 而不重复

问题描述

我需要为不应重复的订单建议一个 ID（前缀 + 序号）。问题是用户也可以键入他自己的 ID。

由于表很大，我考虑创建一个仅包含最后建议的 ID 和前缀的辅助表，以便更轻松地搜索新号码。

例子：

Suggested: 20-001   
Suggested: 20-002  
User typed: EX901
User typed: 20-004 (!)
Suggested: 20-003
Suggested: 20-005 (!)
User typed: 20-002 (Invalid)

我认为我可以创建一个表和一个序列来管理 ID：

CREATE SEQUENCE OrderIdSequence
       START WITH 1
       INCREMENT BY 1;
GO;

CREATE TABLE dbo.Order_Ids (
    ID_Order_Ids int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Prefix varchar(3) DEFAULT ('') NOT NULL, --Current year as YY.
    Suggested varchar(4) DEFAULT ('') NOT NULL, --Number generated by the sequence.
    Typed varchar(255) DEFAULT ('') NOT NULL, --User typed ID.
    Used BIT NOT NULL Default 0
)
ON [PRIMARY]

当用户键入一个已经存在的 ID 作为前缀 + 建议编号时，我将在保存表单时进行验证。

我的问题是如何获取下一个序列号，如果它已经作为“类型”列存在则跳过：

前缀（当前年份为 'YY'）+ '-' + 序列。

是否可以插入获取下一个序列，并检查该数字是否已经存在？也许存储过程会更好？

我设法创造的东西：

不确定这是否是最好的方法，但这是我的程序：

CREATE PROCEDURE [dbo].[SuggestOrderProcedure] AS
BEGIN
    DECLARE @sequential int = 0;
    DECLARE @prefix varchar(3) = '';
    SELECT @prefix = FORMAT(GETDATE(), 'yy-');

    -- searches for a sequential number that is not being used.
    WHILE @sequential < 1 Or (Select Count(*) FROM Order_Ids WHERE RTRIM(LTRIM(Typed)) = CONCAT(@prefix, RIGHT('0000' + @sequential, 4))) > 0
    BEGIN
       SELECT @sequential = NEXT VALUE FOR dbo.OrderIdSequence;
    END

    -- Inserts the suggested ID into the table Order_Ids.
    INSERT INTO Order_Ids (Prefix, Suggested) OUTPUT Inserted.ID_Order_Ids, Inserted.Prefix, Inserted.Suggested VALUES(@prefix, RIGHT('0000' + @sequential, 4));
    return
END

奇怪的是，在执行 via 时Dapper，该Suggested列返回时没有前导零。

标签： sqlsql-server

解决方案

我的建议是以下过程。

首先，为表创建一个标识列。默认 id 应该是一个简单的数字——不多也不少。

其次，允许用户指定一个id，但它是一个备用id。它可以是用户想要的任何东西，除了数字。您可以通过check约束来强制执行此操作。

第三，将备用 ID 存储在单独的表中，如下所示：

create table alternate_ids as (
    alternate_id varchar(255) primary key,
    user_id int,
    constraint fk_alternate_user foreign key (user_id) references users(user_id),
    constraint chk_alternate_id check (alternate_id like '%[^0-9]]%')
);

当您查询用户时，您需要检查备用 id，如果您有字符串，user_id如果没有。或者，您可以为每个用户填充alternate_ids一个字符串表示形式，而无需替代。user_id

推荐阅读