Mysql how to make an alternate numbering field?

Question

PHP

I have a table with the following structure:

CREATE TABLE `items` (
`id` int AUTO_INCREMENT ,
`category_id`  int,
PRIMARY KEY (`id`)
);

And with this dataset:

id   category_id
1      1 
2      1
3      1 
4      2 
5      3
6      2
7      3
8      2
9      3

After some time, there was a need for an alternative numbering of records based on the category. That is, serial numbering in the table above, depending on the category . Like this:

id   category_id    alt_id
1      1              1
2      1              2
3      1              3
4      2              1
5      3              1
6      2              2
7      3              2
8      2              3
9      3              3

This must be a field, since the numbering must be preserved when deleting rows and must be available when a single record is selected .
Records cannot change the category, this situation should not be taken into account.

• What is the best way to do this numbering when adding new records?
  
• How it is better to make such numbering for already added records?
  

Answer 1

hang trigger

DELIMITER $$
CREATE TRIGGER `items_BEFORE_INSERT` BEFORE INSERT ON `items` FOR EACH ROW
BEGIN
  DECLARE oldmax INT;
  SELECT IFNULL(MAX(`alt_id`), 0) 
    FROM `items` 
    WHERE `category_id` = NEW.`category_id` 
        INTO oldmax;
  SET NEW.`alt_id` = oldmax+1;
END$$
DELIMITER ;

Answer 2

For the old ones:
For new ones - the answer is Rsa97