Mysql как сделать поле альтернативной нумерации?

Question

[Rukis]

Есть таблица с таким строением:

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

И вот таким набором данных:

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

Через некоторое время появилась необходимость в альтернативной нумерации записей исходя из категории. То есть порядковая нумерация в представленной выше таблице, в зависимости от категории. Вот так:

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

Это должно быть именно поле, поскольку нумерация должна сохраняться при удалении строк и должна быть доступна при селекте одной записи.

Записи не могут менять категорию, такую ситуацию учитывать не надо.

• Как лучше сделать такую нумерацию при добавлении новых записей?
  
• Как лучше сделать такую нумерацию для уже добавленных записей?
  

Answer 1

[Rsa97]

Повесьте триггер

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 ;

Comments

[Rukis]

Спасибо, вариант. Что насчет существующих записей?

[Rsa97]

Rukis: Создать новую таблицу, повесить на неё триггер, перенести все записи со старой (INSERT FROM SELECT), удалить старую таблицу, переименовать новую в items.

Answer 2

[vyrkmod]

Для старых:

UPDATE items INNER JOIN (SELECT items.id, COUNT(joined.id) as counted_id FROM items LEFT JOIN items AS joined ON (joined.category_id = items.category_id AND joined.id <= items.id) GROUP BY items.id) as i ON items.id = i.id SET alt_id=counted_id

Для новых - ответ Rsa97