mysql 在实体内约束唯一的增量ID生成

np8igboo 于 2023-05-16 发布在 Mysql

关注(0)|答案(2)|浏览(113)

我们正在为我们的客户（企业客户）建立一个票务系统，我们正在努力想出一种方法，有一个顺序，唯一的门票ID生成系统，也是用户友好的。
唯一ID生成是一个众所周知的问题，我们已经研究了其他公司使用的一些解决方案，如Flickr或Twitter，但它们并不能满足我们“用户友好”的目的。它们太长了，我们的客户无法记住或通过电子邮件或电话进行通信。
我们也不能使用普通的GUID，因为我们需要顺序排序。
我们最初考虑将ID生成为客户ID和票据ID本身的组合。例如，客户1的第一个票据将是C1-T1，然后是C1-T2。客户2的第一个票据将是C2-T1，然后是C2-T2。
这当然是可行的。我们可以简单地查看最后一个customer_id-ticket_id组合，然后将其添加1，但它涉及到一个DB查询，并且我们还需要使用一个锁，以便并发事务不会重用相同的递增的票证ID。这实质上意味着将其移动到异步流，因为我们不能妨碍任何同步流。保持同步意味着拥有高容量的客户可能会等待很长时间才能完成API调用，因为一堆并发事务（来自同一客户的先前票据）仍在等待。
但是业务需求是需要立即生成票证ID以供用户使用。
因此，虽然我们有一些解决方案可以使用，但每一个都有一些缺点。它们要么阻碍了面向客户的API的延迟，要么它们不是立即可用的，要么它们太长而不对用户友好。
我们现在被困住了，没有好的线索。
因此，我们想从社区中了解是否有任何方法可以生成一个连续的，唯一的ID，也足够短（最多8-9个字符）。

mysql

来源：https://stackoverflow.com/questions/76209620/unique-incremental-id-generation-constrained-within-an-entity

2条答案

按热度按时间

xuo3flqw1#

这与Mitch的答案类似，但没有SP的细节，并使用MySQL文档中的LAST_INSERT_ID（）示例。
门票的简化表-

CREATE TABLE tickets (
    customer_id INT UNSIGNED NOT NULL,
    ticket_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (customer_id, ticket_id)
);

每个客户的票证序列表-

CREATE TABLE customer_ticket_sequence (
    customer_id INT UNSIGNED NOT NULL PRIMARY KEY,
    seq INT UNSIGNED NOT NULL
);

-- initialise sequences for customers 1, 2 & 3
INSERT INTO customer_ticket_sequence VALUES (1, 0), (2, 0), (3, 0);

现在，我们不再执行SELECT和UPDATE，而是使用一个update语句，将新的序列值存储在LAST_INSERT_ID（）中，为插入到票据中做好准备-

UPDATE customer_ticket_sequence SET seq = LAST_INSERT_ID(seq + 1) WHERE customer_id = 1;
INSERT INTO tickets VALUES (1, LAST_INSERT_ID());

db<>fiddle

赞(0）回复(0）举报 2023-05-16

jucafojl2#

除非有大量的写入（>1000/s），否则专用“ID”表上的临时锁不太可能是昂贵的。
在保持高吞吐量的同时，这种情况的一个示例是：

DELIMITER ;
-- Schema
CREATE TABLE Customers (
    CustomerID INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(50) NOT NULL,
    PRIMARY KEY (CustomerID)
);

CREATE TABLE CustomerSequences (
    CustomerID INT NOT NULL,
    Sequence varchar(50) NOT NULL,
    NextValue INT NOT NULL,
    PRIMARY KEY (CustomerID, Sequence),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
);

-- Sample Data
INSERT INTO Customers (Name) VALUES ('Customer 1'), ('Customer 2');
INSERT INTO CustomerSequences (CustomerID, Sequence, NextValue)
SELECT CustomerID, 'Ticket', 1
FROM Customers;
INSERT INTO CustomerSequences (CustomerID, Sequence, NextValue)
SELECT CustomerID, 'Asset', 1
FROM Customers;

DELIMITER $$
CREATE PROCEDURE GetCustomerSequenceNo (IN cid int, IN seq varchar(50), OUT val int)
BEGIN
    START TRANSACTION;

    SELECT NextValue into val 
    FROM CustomerSequences 
    WHERE CustomerID = cid and Sequence = seq 
    FOR UPDATE;
    
    UPDATE CustomerSequences 
    SET NextValue = val + 1
    WHERE CustomerID = cid and Sequence = seq;
    
    COMMIT;
END$$
DELIMITER ;

-- Example use
select * from CustomerSequences;

call GetCustomerSequenceNo(1, 'Ticket', @Ticket11ID);
select @Ticket11ID;
call GetCustomerSequenceNo(1, 'Ticket', @Ticket12ID);
select @Ticket12ID;
call GetCustomerSequenceNo(2, 'Ticket', @Ticket21ID);
select @Ticket21ID;

select * from CustomerSequences;

您可以将ID作为一个单独的事务来获取，该事务与您可能出于业务目的而启动的长期事务不同。这与AUTO_INCREMENT的行为相匹配，可能会导致间隙-但在大多数情况下，这不是问题。
如果要维护单独的序列的项目数量较少且固定，则可以改用SEQUENCE模式对象。不过，对成百上千的客户这样做可能会惹恼一些DBA。

赞(0）回复(0）举报 2023-05-16

我来回答

mysql 在实体内约束唯一的增量ID生成

2条答案

相关问题

热门标签

最新问答