我如何存储订单?

2021-12-26 00:00:00 sorting mysql database-design

我有一个包含任务的应用程序，您可以对它们重新排序.现在我正在考虑如何最好地储存它们.我是否应该为订单号设置一个列并在每次更改时重新计算所有这些?请告诉我一个不需要我更新所有订单号的版本，因为这非常耗时(从执行的角度来看).

I have an app which has tasks in it and you can reorder them. Now I was woundering how to best store them. Should I have a colomn for the ordernumber and recalculate all of them everytime I change one? Please tell me a version which doesn't require me to update all order numbers since that is very time consuming (from the executions point of view).

如果我必须把最上面的一个放在订单的顶部，然后把它拖到底部，这尤其糟糕.

This is especially bad if I have to put one that is at the very top of the order and then drag it down to the bottom.

  • 姓名(订单号)

--

  • 1例(1)
  • 2例(2)
  • 3例(3)
  • 4例(4)
  • 5例(5)

--

  • 2例 (1) *
  • 3例 (2) *
  • 4例(3)*
  • 5例(4)*
  • 1例 (5) *

*必须在数据库中更改

还有一些任务可能会因为完成而被删除

also some tasks may get deleted due to them being done

推荐答案

您可以将订单保留为文字，并使用词法排序:

You may keep orders as literals, and use lexical sort:

1. A
2. Z

添加任务:

1. A
3. L
2. Z

添加更多:

1. A
4. B
3. L
2. Z

在 1 和 4 之间移动 2:

Move 2 between 1 and 4:

1. A
2. AL
4. B
3. L

等

您一次只更新一条记录:只需在第一个不同的记录之间取一个平均字母:如果您在 A 和 C 之间放置，则取 B，如果你在ALGJ 和ALILFG 之间，你取ALH.

You update only one record at a time: just take an average letter between the first ones that differ: if you put between A and C, you take B, if you put between ALGJ and ALILFG, you take ALH.

现有的字母旁边的字母算作现有的与 Z 旁边的字母连接.IE.如果需要放在ABHDFG和ACSDF之间，则算在ABH和AB(Z+)之间，并写出AB(letter 35/2)，即ABP.

Letter next to existing counts as existing concatenated with the one next to Z. I. e. if you need put between ABHDFG and ACSDF, you count it as between ABH and AB(Z+), and write AB(letter 35/2), that is ABP.

如果字符串长度用完，您可能总是执行完整的重新排序.

If you run out of string length, you may always perform a full reorder.

更新:

您也可以将数据保存为链接列表.

You can also keep your data as a linked list.

请参阅我博客中有关如何在 MySQL 中执行此操作的文章:

See the article in my blog on how to do it in MySQL:

  • 排序列表

简而言之:

/* This just returns all records in no particular order */

SELECT  *
FROM    t_list

id      parent
------- --------
1       0
2       3
3       4
4       1

/* This returns all records in intended order */

SELECT  @r AS _current,
        @r := (
        SELECT  id
        FROM    t_list
        WHERE   parent = _current
        )
FROM    (
        SELECT  @r := 0
        ) vars,
        t_list

_current id
-------  --------
0        1
1        4
4        3
3        2

移动项目时，您最多需要更新 4 行.

When moving the items, you'll need to update at most 4 rows.

这似乎是保持经常更新的有序列表的最有效方法.

This seems to be the most efficient way to keep an ordered list that is updated frequently.

相关文章