如何在序列中的同一插入上插入序列号？

Question

How can I insert a number on a table that is the count of records from the same FK plus 1.

An explanation: there is a table that will hold the number of phases (1,2,3...). When a new phase is created, I would like to make the column number being the sum of the number os the phases that already exists.

I tried using COUNT

On planId 204, There will be 3 phases. On the html form I only want to get the name and year. The user is not responsible for the number of the phase. It is a sequential number when it is added.

INSERT INTO 
        phase (
        idPlan,
        name,
        number,
        constructionYear
        )
        VALUES (
        204,
        'jj',
         HERE IS THE SEQUENTIAL NUMBER,
        2001
        )

CREATE TABLE `phase` (
 `idPhase` int(11) NOT NULL AUTO_INCREMENT,
 `idPlan` int(11) NOT NULL,
 `number` int(11) NOT NULL,
 `constructionYear` year(4) NOT NULL,
 `name` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`idPhase`),
 KEY `idPlan` (`idPlan`),
 KEY `idPlan_2` (`idPlan`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Answer 1

You can achieve this with a trigger on INSERT:

CREATE TRIGGER `phase_insert`
BEFORE INSERT ON `phase` FOR EACH ROW 
SET NEW.number = (SELECT COALESCE(MAX(number),0)+1 FROM phase WHERE idPlan=NEW.idPlan)

This will find the maximum value of number before an INSERT on that value of idPlan increment it by 1 and then insert that value. In the case where there is no entry for the iDPlan value, the COALESCE(MAX(number),0) will return 0 so 1 will be inserted.

e.g. in an empty table

INSERT INTO phase (idPlan, name, number, constructionYear)
    VALUES (204, 'jj', 0, 2001), (204, 'xx', 0, 2002);
SELECT * FROM phase

Output:

idPhase     idPlan  number  constructionYear    name
3           204     1       2001                jj
4           204     2       2002                xx