Geek asks and answers

Reordering column data in mysql

I have a table like:

categoryID      categoryName
----------------------------
     1            A
     2            B
     3            C

Now I want the user to be able to order this data in accordance with his will. I want to remember his preferred order for the future. So I thought to add a column orderto the table above and make it a type of INTand AUTO_INCREMENT. So now I get the table as follows:

categoryID      categoryName    order
-------------------------------------
     1            A               1
     2            B               2
     3            C               3
     4            D               4

My problem is that now the user decides to bring categoryNamein order 4 (D in the example above) to 2 (above B in the example above) so that the table looks like this:

categoryID      categoryName    order
-------------------------------------
     1            A               1
     2            B               3
     3            C               4
     4            D               2

My question is: how should I assign new values ​​to a column orderwhen reordering occurs. Is there a way to do this without updating all the rows in the table?

, , , a FLOAT 1,5, 1,2. .

EDIT: (m, n), m, n - . (m-n) , ?

2: , FLOAT, sql, , id = 2 ().

select ((
    select `order` as nextHighestOrder
    from `categories`
    where `order` > (
        select `order` as targetOrder 
        from `categories` 
        where `categoryID`=2) 
        limit 1) + (
            select `order` as targetOrder 
            from `categories` 
            where `categoryID`=2)) / 2;

3.5, .

? , select order as targetOrder from categories where categoryID=9 .

+1
4

, , UPDATE, :

UPDATE categories
JOIN (
    SELECT 2 as categoryID, 3 as new_order
    UNION ALL
    SELECT 3 as categoryID, 4 as new_order
    UNION ALL
    SELECT 4 as categoryID, 2 as new_order) orders
USING (categoryId)
SET `order` = new_order;

( ):

UPDATE categories
SET `order` = ELT (FIND_IN_SET (categoryID, '2,3,4'),
                   3, 4, 2)
WHERE categoryID in (2,3,4);

UPD

, ( ), , ( between new_rank rank+1):

SET @id:=2, @cur_rank:=2, @new_rank:=4;

UPDATE t1
JOIN (
  SELECT categoryID, (rank - 1) as new_rank
  FROM t1
  WHERE rank between @cur_rank + 1 AND @new_rank
  UNION ALL
  SELECT @id as categoryID, @new_rank as new_rank
) as r
USING (categoryID)
SET rank = new_rank;
+4

Float reasanoble, -)

, , . - .

0

, , , 20 . .

, , , , , , , , 2- .

:

1,2,3
Move 3rd to 2nd
1,1.5,2
Move 3rd to 2nd
1,1.25,1.5
Move 3rd to 2nd
1,1.125,1.25

excel, , , 30 .

0

, , @newtover , 2 , .

, t1:

id      name    position
-------------------------------------
 1      A       1
 2      B       2
 3      C       3
 4      D       4
 5     -E-      5
 6      F       6

"E" id = 5 :

1) "E" "E" ( 2, 3, 4)

UPDATE t1 SET position=position+1 WHERE position BETWEEN 2 AND 4

2) Now there is no position in position 2, so the "E" can take place

UPDATE t1 SET position=2 WHERE id=5

Results sorted by position

id      name    position
-------------------------------------
 1      A       1
 5     -E-      2
 2      B       3
 3      C       4
 4      D       5
 6      F       6

Only 2 simple queries, no subqueries.

Constraint: Column position cannot be UNIQUE. But perhaps it should work with some changes as well.

Did not test this on large data sets.

0
source

More articles:


All Articles