MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT,
  project_number INT
  );
CREATE TABLE generate_project_number (
  project_id INT,
  project_number INT AUTO_INCREMENT,
  PRIMARY KEY (project_id, project_number)
  ) ENGINE = MyISAM;
CREATE TRIGGER generate_project_number
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO generate_project_number (project_id) VALUES (NEW.project_id);
  SET NEW.project_number = LAST_INSERT_ID();
  DELETE FROM generate_project_number 
  WHERE project_id = NEW.project_id
    AND project_number < NEW.project_number;
END
INSERT INTO orders (project_id) VALUES (1), (1), (2), (3), (1);
SELECT * FROM orders;
SELECT * FROM orders ORDER BY 2, 3;
SELECT * FROM generate_project_number;

Records: 5  Duplicates: 0  Warnings: 0




id
project_id
project_number


1
1
1

2
1
2

3
2
1

4
3
1

5
1
3




id
project_id
project_number


1
1
1

2
1
2

5
1
3

3
2
1

4
3
1




project_id
project_number


1
3

2
1

3
1