How to set and change the position of elements in the table?

Question

MySQL

Hello!
The question is. Each user on the site has a list, no matter which one.
Each element from the list has its own position, let's say pulling out the elements from the table, we sort them by the id field, where each element has its own unique id.
It is necessary to make it so that the user can change the position of an element in the list.
But I don't understand how to do it.

There is only one idea, this is to set a separate field for the position in the table, let it be called position, and every time a person changes the position of an element in the list, it is necessary to pull out the entire list of the user and calculate the position for each element and update each entry from the list in a loop.
And if there are 100 of these lists? I don’t think that this is normal, although I admit that this is ok, therefore I can’t imagine how it could be otherwise.

If another thought is about weights, set some field weight in the list table.
Pull out all the id and weight elements and carry out some calculations taking into account the existing weights. But at the same time, do not change the weight of all elements, but only set the correct weight for a particular element.

That is, let's say we give each element a weight of 100 each time and double this number for a new list element. And then if we add some new element and want to put it first, then we make everything less by one. But there is some kind of ceiling here, that if we put a new element first every time, we will be able to put only 100 elements, although this may be a completely crazy idea.

Can anyone suggest the best way to solve this problem?

Answer 1

two updates:

апдейт `блабла` 
сет `сорт` = позиция 
где `ид` = ид_итема

апдейт `блабла` 
сет `сорт` = `сорт`+1 
где `сорт` > позиция 
и `ид_группы` = ид_группы

Answer 2

pulling elements from the table, we sort them by the id field, where each element has its own unique id.

replace with

pulling out the elements from the table, we sort them by the sort field, where each element has its own serial number.