根据另一个ID使用auto_increment设置一个ID-可能吗? [英] Make one ID with auto_increment depending on another ID - possible?

问题描述

我想为具有多个项目的项目服务器制作一个小型票务系统. 到目前为止，TicketID将在全球范围内进行计数，即，有一个项目A和一个具有TicketID 1的票证，以及另一个项目B，该项目的票证将获得TicketID 2-像这样:

I want to make a small ticket-system for a project server which has several projects. So far the TicketID will be counted globally, i.e. there is a project A and a ticket with TicketID 1 and another project B and the ticket for this project will get TicketID 2 - like this:

(TicketID, ProjectID)
(1, 1)
(2, 1)
(3, 1)
(4, 2)
(5, 2)
(6, 3)

但是我认为最好根据ProjectID来计算TicketID，例如:

But I think it would be better to count the TicketID depending on the ProjectID such as:

(TicketID, ProjectID)
(1, 1)
(2, 1)
(3, 1)
(1, 2)
(2, 2)
(1, 3)

这是桌子:

CREATE  TABLE IF NOT EXISTS tickets (
    TicketID    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ProjectID   INT UNSIGNED NOT NULL,
    ...
    PRIMARY KEY (TicketID, ProjectID) ,
    FOREIGN KEY (ProjectID) REFERENCES projects (ProjectId),    
    ...
);

是否可以根据带有SQL的ProjectID，使TicketID具有auto_increment的功能? 还是SQL无法解决，我必须用我的PHP代码手动设置ID?

Is it possible to make the TicketID with auto_increment depending on the ProjectID with SQL? Or is there no way with SQL and I have to set the IDs with my PHP-code manually?

推荐答案

为什么在项目表上没有下一个票证ID"字段-创建新票证时，获取该值，将其递增并将票证ID设置为以前的值?显然，这一切都在交易中.

Why not have a "next ticket id" field on the project table - when creating a new ticket, get this value, increment it and set the ticket id to the previous value? All in a transaction, obviously.

这篇关于根据另一个ID使用auto_increment设置一个ID-可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！