如何保持数据库表中记录的顺序
我正在创建一个数据库表，该表将存储最终显示在网页上的菜单链接。
我的问题是我想控制菜单项的顺序。我可以有一个名为 order 的字段，但每次我有一个新的菜单链接时，我都必须插入 order 并将所有具有更高 order 的记录更改为 +1。
例如，假设我想要链接（按此顺序）：
Home About Products Shopping
我可以有一个名为 MenuLinks 的表，并具有以下列：名称、订单
我的数据将如下所示：
Name Order Home 1 About 2 Products 3 Shopping 4
但如果我现在想添加一个名为 ContactUs 的新链接，但我想显示在主页下方。
谁能想到一种更好的方法来存储需要在数据库表中排序的列表，而无需这种繁琐的维护工作。
i am creating a database table that is going to store menu links that will ultimately show up on a web page.
My issue is that i want to control the order of the menu items. I could have a field called order but everytime i have a new menu link i would have to insert the order and change all of the records with higher order to +1.
For example, lets say i want the links ( in this order):
Home About Products Shopping
i could have a table called MenuLinks and have the columns: Name, Order
my data would look like this:
Name Order Home 1 About 2 Products 3 Shopping 4
but if i wanted to now add a new link called ContactUs but i wanted to show up right under home.
can anyone think of a better way to store a list that requires ordering in a database table without this tedious maintenance effort.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我觉得这与使用数组与链表的一般问题有关。
如何存储引用同一个表中下一条记录的外键？这是类似链表的方法。
对于您的示例，没有太多选项卡，因此基于数组的方法应该可以正常工作。
但对于拥有数百条记录的人来说，使用自引用外键可能会很有用。
添加和删除行类似于链表插入和删除。
更新：
修改后的表
顺序将是 1 > 5> 2> 3> 4 由下一栏确定。
您还可以使用前一列，这将使其类似于双向链表。
I feel this is related to the general problem of using an array vs a linked list.
How about storing a foreign key referencing the next record in the same table? This is the linked list like approach.
For your example there are not too many tabs so an array based approach should work fine.
But for someone having hundreds of records it may be useful to use a self-referential foreign key.
Adding and deleting rows will be akin to linked list insertion and deletion.
Update:
Modified table
The order will be 1 > 5 > 2 > 3 > 4 as determined by the next column.
You can also use a prev column which will make it similar to a doubly linked list.
如果没有 ORDER BY，您就无法保证数据的顺序 - 通常，如果没有 ORDER BY，它将基于插入顺序。
遗憾的是，没有适合用户自定义排序顺序的约定。
人们可以使用像 ROW_NUMBER 这样的分析/窗口/排名函数，但这取决于数据和数据库支持（MySQL 不支持分析函数，Oracle 9i+/PostgreSQL 8.4+/SQL Server 2005+ 支持）。但是，如果您希望以“B”开头的条目出现在“A”等之前，则分析函数没有帮助。
您的选择是使用两个语句插入单个记录：
...或删除现有记录，然后按新顺序重新插入列表。
Without an ORDER BY, you can't guarantee the order of the data - typically, without an ORDER BY it will be based on insertion order.
Sadly, there's no convention that works well for a user customizable sort order.
One could get away with using analytic/windowing/ranking functions like ROW_NUMBER, but it depends on data and database support (MySQL doesn't support analytic functions, Oracle 9i+/PostgreSQL 8.4+/SQL Server 2005+ do). But analytic functions don't help if you want an entry starting with "B" to appear before "A"/etc.
Your options are to either use two statements to insert a single record:
...or delete the existing records, and re-insert the list in the new order.
您应该考虑到，当您使用链接列表时，当您想要重新排序其中一项时，您还必须更新其他记录，并且这需要在根本不快的事务中完成。（您需要事务，因为所有更新必须完整完成，否则不得更新）
对于此问题还有另一种解决方案，适用于小列表。
要使用此方法，您需要为每条记录指定一个编号。例如：
数字较小的行位于列表的开头，数字最大的行将是列表的最后一项
现在这里有一个技巧，如果您想重新排序 Products 行并将其插入 Home 和 About 之间，您所要做的就是将产品的编号字段更改为首页和关于编号之间的编号
首页编号为 5，关于编号为 10，因此产品的编号字段将为 (5+10)/2 = 7.5
，现在您可以根据数字字段对最终列表进行排序
you should consider that when you use a linked list then when you want to reorder one of the items then you have to update other records as well and this needs to be done in a transaction which is not fast at all.(you need transaction because all the updates must be done completely or none of them must be updated)
there is an other solution for this problem that works on small lists.
to use this method you give each of your records a number. for example:
rows with smaller Number are at the begginnig of the list and the row with the biggest number will be the last item of your list
now here is the trick, if you wanted to reorder the Products row and insert it between the Home and About all you have to do is to change the Number field of your Product to be equal to the number between Home and About Number
the Home number is 5 and the About number is 10 so the Number field of Product will be (5+10)/2 = 7.5
and now you can sort the final list, based on the Number field
更好的解决方案是按顺序创建元素 ID 数组，并将其转换为 json 数组，然后将其按原样存储到文件或表或任何您想要的位置。然后，您可以从数据库中获取对象并将数组映射回元素列表。您一方面保留订单，另一方面保留数据。如果从数据库中删除一个元素，您可以从数组中删除不映射到任何内容的元素。如果您向数据库添加新元素，则还需要将它们添加到数组中，但如果不这样做，也不会损坏任何内容。
长话短说：
1。将订单作为 id 的 json 数组保存到文件或表中或任何您想要的位置。
2.将元素保存在数据库中，而不必担心它们的顺序。
3.当您需要时，取回 id 数组并将它们映射到数据库中各自的元素。
A better solution is to create an array of your elements IDs in order and turn that into a json array and store it as it is into a file or a table or wherever you want. You can then fetch your objects from the database and map your array back to a list of elements. You keep on one side the order and, on the other, the data. if you remove an element from the database you can remove elements from your array that map to nothing. If you add new element to the database you need to add them to your array as well but if you don't, nothing gets broken.
long story short:
1. Save the order as a json array of ids into a file or a table or wherever you want.
2. Save your elements in your database without worrying about their order.
3.When you need, get back your array of ids and map them to their respective element from the database.
您可以添加 2 列（之后、之前）
例如联系我们是在主页之后和关于之前，如果您想在联系我们和主页之间添加新的链接，则只需更改 3 条记录即可。
列更改为（新链接名称）之前的主页，
然后您插入新链接，然后在列更改为（新链接名称）后联系我们。
抱歉我英语不好。
You can add 2 columns (after,before)
For example contact us is after home and before about, if you want add a new link between contact us and home you just need to change 3 records.
Homes before column changes to (new link name),
Then you insert new link and then contact us after column changes to (new link name).
Sorry im not good in English.