可排序的项目MySQL结构

Question

I have a list of items in a file table, which way is the most practical to sort records based on how the user has positioned them? There could be possibly hundreds of records per user. Perhaps there is a better way to do this and I've boxed my thinking in?

Option 1: I have a table with the order included. I can easily sort by the order column, however when I add a new field or re-order them on the front-end, I may have to update MANY records, *eg: If there are 50 files, changing file_id 1 to position 50, could renumber the order of every row in turn requiring 50 updates..*

table: file
file_id | name  | order
------------------------
1       | test  | 2
2       | other | 1

Option 2: Organize the file table based off an order table. This requires much more work and error checking, and it's a bit more difficult. Yet I would be updating one row with CSV values and transforming them into Arrays to sort the file column.

table: file
file_id | name 
----------------------
1       | test
2       | other

table: order
file_order 
----------------------
2,1

Answer 1

for option 1 you could use these SQL requests :

i added a field user_id to your file table

to make the 50th file as 3rd position:

UPDATE table SET order=3 WHERE id=50 AND user_id = xxx

UPDATE table SET order=order+1 WHERE id>3 AND id<50 AND user_id = xxx

to make the 3rd as 50th position:

UPDATE table SET order=50 WHERE id=3 AND user_id = xxx

UPDATE table SET order=order-1 WHERE id>3 AND id<50 AND user_id = xxx