sql - MySQL update a sorting index column to move items -

If I have the following table & amp; Data allows us to use sort_index for sorting:

  create table 'foo' (`id`inT (11) tap etanement,` bar_id` INT 11) default tap, `sort_index` INT (11) default faucet, primary key (` ID`)); INSERT VALUES (1,1), (1,2), (1,3), (1,4), (2,1), (2, 2, 2) in `Foo` (` bar_id`, `sort_index`) 2), (2,3), (2,4), (2,5);  

I want to be able to do the following things in the most efficient way:

1. Move any foo entry to a position (scanned by bar_id) < Ul>
2. Make sure that sort_index is always 1 indexed and no gap
3. You should be able to move items in the beginning and list And the end of Rule # 2 should still be implemented
4. This should be done in complete queries and Do not be as short as possible (as the set can be very large and individual UPDATE s is not ideal

What do I do I am trying to make it clear that the table is empty, so we have the following data:

  id | bar_id | Sort_index 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 4 | 1 | 4 | 5 | 2 | 1 | | | 2 | 2 | 7 | 2 | 3 | 8 | 2 | 4 | 9 | 2 | 5 |  

Then if we follow the following tricks

To foo 1, sort_index 3

fu7 to sort_index 1

foo 5 to sort_index 5

us less Receive the following data:

  id | Bar_ID | Sort_index 1 | 1 | 3 2 1 | 1 3 1 | 2 4 | 1 | 4 5 2 | 5 6 | 2 | 2 7 | 2 | 1 8 | 2 | 39 2 | 4  

and SELECT * foo by ORDER bar_id, sort_index; gives us:

  id | Bar_ID | Sort_index 2 | 1 | 1 3 1 | 2 1 1 | 3 4 1 | 4 7 | 2 | 1 6 | 2 | 2 8 | 2 | 39 2 | 4 5 2 | 5  

You should be able to do this in a single query: with some lines UPDATE foo SET sort_index = sort_index + 1's WHERE bar_id == b and sort_index & lt; S1 and sort_index & gt; = S2 , where bar_id is the line to move b , s1 current sortedx < / Code> and s2 is the sort_index that you want to transfer it to, then you can simply change the sort_index of the line.

You probably want to put two questions inside the transaction. In addition, it can speed up things if you have created an index on sort_index in any way on CREATE INDEX foo_index ON foo (sort_index) .

(By the way, I'm assuming that you do not want a duplicate sort_index value within any bar_id , and that clearly The relative sequence of rows should never be changed if you do not need it, the solution is also simple.)