主页 / dba / 问题 / 46204
Accepted
Arash Mousavi
Asked: 2013-07-12 23:26:51 +0800 CST

最有效的订购后数据库设计

  • 772

我有一个包含 post_order 列的 posts 表。我在其中存储每个帖子的顺序。当我将一行的顺序从 25 更改为 15 时，我应该将所有行从 15 更新到末尾。这对几行很好，但在几千行时最差。

有没有更好的排序帖子设计，效率更高？

mysql database-design

1 个回答

  1. Best Answer

    您可以将您的post_order列更改为 a并使用您想要结束的帖子float中的值通过计算更新值。post_order

    例子：

    PostID, PostOrder
1       1
2       2
3       3

    如果你想将 PostID = 3 移动到 1 和 2 之间进行排序，那将是

    PostID, PostOrder
1       1
3       1.5
2       2

    这是一个 SQL Fiddle，其中包含一些使用存储过程移动帖子的代码，您可以在其中传递PostID要移动PostID的帖子和您想要结束的帖子。

    SQL小提琴

    MySQL 5.5.30 架构设置：

    create table Post
(
  PostID int primary key,
  PostOrder float unsigned not null unique,
  check (PostOrder > 0)
);

insert into Post(PostID,  PostOrder) values (1, 1);
insert into Post(PostID,  PostOrder) values (2, 2);
insert into Post(PostID,  PostOrder) values (3, 3);
insert into Post(PostID,  PostOrder) values (4, 4);
insert into Post(PostID,  PostOrder) values (5, 5);
insert into Post(PostID,  PostOrder) values (6, 6);
insert into Post(PostID,  PostOrder) values (7, 7);
insert into Post(PostID,  PostOrder) values (8, 8);
insert into Post(PostID,  PostOrder) values (9, 9);
insert into Post(PostID,  PostOrder) values (10, 10);

//

create procedure MovePost(MovePostID int, AfterPostID int)
begin

  declare AfterPostOrder float;
  declare NextPostOrder float; 


  set AfterPostOrder = (select PostOrder 
                        from Post
                        where PostID = AfterPostID);

  if AfterPostOrder is null then
    -- Move first
    set AfterPostOrder = 0;
  end if;

  set NextPostOrder = (select min(PostOrder) 
                       from Post 
                       where PostOrder > AfterPostOrder);

  if NextPostOrder is null then
    -- Move last
    set NextPostOrder = (select max(PostOrder) + 2
                         from Post);
  end if;


  update Post
  set PostOrder = (AfterPostOrder + NextPostOrder) / 2
  where PostID = MovePostID;


end

    查询 1：

    call MovePost (7, 3);    -- Move 7 after 3
call MovePost (8, 3);    -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10);   -- Move 2 after 10

select * 
from Post
order by PostOrder

    结果：

    | POSTID | POSTORDER |
----------------------
|      9 |       0.5 |
|      1 |         1 |
|      3 |         3 |
|      8 |      3.25 |
|      7 |       3.5 |
|      4 |         4 |
|      5 |         5 |
|      6 |         6 |
|     10 |        10 |
|      2 |        11 |
    • 6

相关问题