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:
- Move any foo entry to a position (scanned by bar_id) < Ul>
- Make sure that
sort_index
is always 1 indexed and no gap - You should be able to move items in the beginning and list And the end of Rule # 2 should still be implemented
- 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
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.)
Comments
Post a Comment