Это таблица content :
ContentID | CategoryID | Position | Other1 | Other2 =================================================== 1 | 1 | NULL | abcd | efgh 2 | 1 | NULL | abcd | efgh 3 | 1 | NULL | abcd | efgh 4 | 2 | NULL | abcd | efgh 5 | 2 | NULL | abcd | efgh 6 | 2 | NULL | abcd | efgh
Это запросы, которые я буду запускать:
SELECT ContentID FROM content WHERE CategoryID = 1 ORDER BY Position SELECT ContentID FROM content WHERE CategoryID = 2 ORDER BY Position
Теперь я хочу реализовать перемещение вверх, перемещение вниз, перемещение вверх и переход к нижней функции для контента. Все, что мне нужно сделать, это заполнить столбец «Позиция» цифрами:
ContentID | CategoryID | Position ================================= 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 4 | 2 | 1 5 | 2 | 2 6 | 2 | 3
Можно ли добиться этого посредством единого запроса в MySQL? Что-то вроде:
UPDATE content SET Position = <ROW_NUMBER> WHERE CategoryID = 1 ORDER BY Position UPDATE content SET Position = <ROW_NUMBER> WHERE CategoryID = 2 ORDER BY Position
Это должно работать
update content, ( select @row_number:=ifnull(@row_number, 0)+1 as new_position, ContentID from content where CategoryID=1 order by position ) as table_position set position=table_position.new_position where table_position.ContentID=content.ContentID;
Но я бы предпочел применить это сначала, чтобы отключить определенную пользователем переменную
set @row_number:=0;
Вы можете сделать это в одном утверждении, как это
update content, ( select @row_number:=ifnull(@row_number, 0)+1 as new_position, ContentID from content where CategoryID=1 order by position ) as table_position, ( select @row_number:=0 ) as rowNumberInit set position=table_position.new_position where table_position.ContentID=content.ContentID;
Вот решение, которое сработало для меня (надеюсь, что это поможет кому-то):
-- The following query re-populates the "Position" column with sequential numbers so that: -- a) sequence is reset to 1 for each "group" -- b) sequence is based on row number relative to each group depending on how ORDER BY is specified -- c) sequence does not disturb the original order but -- ca) fixes NULLs so that they are moved to top -- cb) fixes duplicate position values depending on how ORDER BY is specified -- ContentID is the primary key -- CategoryID is a foreign key -- Position column contains relative position of a record SET @current_group = NULL; SET @current_count = NULL; UPDATE content SET Position = CASE WHEN @current_group = CategoryID THEN @current_count := @current_count + 1 WHEN @current_group := CategoryID THEN @current_count := 1 END ORDER BY CategoryID, Position -- <Column 3>, <Column 4>, ...
Я думаю, было бы очень утомительно запускать дополнительные запросы все время, когда вы выполняете некоторые операции над таблицей. Я бы создал триггер, который запускается каждый раз, когда вы хотите вставить / обновить что-то в таблице.
В вашем случае было бы целесообразно использовать BEFORE UPDATE и BEFORE INSERT триггер. Если вы также хотите сохранить его в чистоте после удаления etntry, добавьте триггер AFTER DELETE .
Начальное:
UPDATE content as uc SET Position = ( SELECT count(*) FROM content as sc WHERE sc.CategoryId = uc.CategoryId AND sc.Position is not null) WHERE uc.Position is null ORDER BY uc.ContentId
Перед вставкой:
UPDATE content SET Position = Position+1 WHERE Position >= newPos AND CategoryId = newCat