在 MySQL 中创建链表或类似队列?
我有一个需要按特定顺序显示的项目表，但该顺序可以更改.可以在开头、结尾或中间添加项目，并且可以重新排列项目.我如何设置表格以跟踪该订单，以便易于修改，但也可以通过单个查询按顺序获取列表?
I have a table of items that need to be displayed in a certain order, but that order can be changed. Items can be added at the beginning, end, or in the middle, and items can be rearranged. How can I set up the table to keep track of that order in such a way that it's easy to modify but the list can also be fetched in order with a single query?
例如，我可以有一个NEXT_ID"列来执行链接列表样式，但是我将如何运行 SELECT 查询以按 NEXT_ID 链的顺序获取行?
For example, I could have a "NEXT_ID" column to do it linked list-style, but then how would I run a SELECT query to get the rows in order of the NEXT_ID chain?
为我可能遗漏的超级明显的解决方案提前道歉.
Apologies in advance for the super-obvious solution I'm probably missing.
推荐答案
我经常遇到这个问题，我用一个简单的解决方案解决了它:一个名为 Sort Order(或 DisplayOrder，无论你的船真正漂浮的东西)的额外列.这让我可以灵活地使用自动生成、自动递增的 ID 列并具有特殊的预定义排序.
I have this problem often, and I solved it with a simple solution : an extra column called Sort Order (or DisplayOrder, whatever floats your boat really) . This allows me the flexibility to use auto-generated, auto-incremented ID column and have a special pre-defined sort.
在我的例子中，我需要它们按字母顺序从数据库中出来，除了像其他"和不适用"这样的项目总是在最后.
In my case, I need them to come out of the database with an alphabetical order except that some items like "Other" and "N/A" are always last.
ProdID ProdText SortOrder
2 "Anchovies" 1
3 "Rivet" 2
4 "N/A" 4
5 "Other" 3
SELECT ProdID, ProdText ORDER BY Sort Order
相关文章