Then you need to iterative logic in the retrieval of the list in order to get the items in order. If you want to perform lots of manipulations on the list and not many retrievals you may prefer to have an ID column pointing to the next item in the list, instead of using a position column. However, this requires a little more effort to work out when to increment the following elements, so you lose simplicity but gain performance if you will have many inserts.ĭepending on your requirements other options might appeal, such as: Since operations on the list can require multiple commands (eg an insert will require an INSERT and an UPDATE), ensure you always perform the commands within a transaction.Ī variation of this simple option is to have position incrementing by some factor for each item, say 100, so that when you perform an INSERT you don't always need to renumber the position of the following elements.
Insert into linked_list (list_id, position, data)
#Linked list stack update
Update linked_list set position = position + 1 where position >= 3 and list_id = 1 So to insert an item into list 1 at index 3: begin transaction
To manipulate the list just update the position and then insert/delete records as needed. create table linked_listĪlter table linked_list add primary key ( list_id, position ) Then you can use ORDER BY on the position column to retrieve in the desired order. The simplest option would be creating a table with a row per list item, a column for the item position, and columns for other data in the item.
0 kommentar(er)
