播放列表下歌曲位置的數據庫設計

我有一個音樂流媒體應用程序，其中有播放列表。每個播放列表最多有 100 首歌曲。我有一個名為 PlaylistSongMapping 的映射表，它具有以下架構

+------+------------+--------+
|SongId| PlaylistId |Position|
+------+------------+--------+
|   1  |     10     |    2   |
|   2  |     10     |    1   |
|   3  |     10     |    3   | 
|   5  |     10     |    4   | 
|   6  |     11     |    1   |
+------+------------+--------+

播放列表中每首歌曲的位置使用位置列進行管理。在播放列表中，我需要訂單更改功能。目前的邏輯是更新 Position 列。問題是如果我需要將 SongId: 3 的歌曲移動到第一個位置，我需要更新 SongId 1、2、3 的行。當播放列表中有更多歌曲時，這個數字會很大。是否有更好的邏輯，以便更新查詢的數量非常少。

除了Position欄位，您可以使用欄位作為LastSongId您可以SongId在播放列表中添加上一首歌曲的位置。

所以當你想改變你的訂單時，你只需要更新三行。

第一行：你的歌曲有新的順序

第二行/第三行：你改變位置前後的歌曲

您的新架構：

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    NULL  |
|   2  |     10     |    1     |
|   3  |     10     |    2     | 
|   5  |     10     |    3     | 
|   6  |     11     |    NULL  |
+------+------------+----------+

如果您將 SongId 3 更改為位置 1，您將擁有：

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    3     |
|   2  |     10     |    1     |
|   3  |     10     |    NULL  | 
|   5  |     10     |    2     | 
|   6  |     11     |    NULL  |
+------+------------+----------+

如果您以某種方式留下空白，則可以將大規模更新推遲到以後。使用帶間隙的整數：

-- init
update PlaylistSongMapping set Position = 10000 * position;

-- move songId = 3 to first position
update PlaylistSongMapping set Position = 5000 
where SongId = 3
 and PlaylistId = 10;

另一種選擇是使用小數，但想法保持不變。

另一種選擇是添加二級訂單屬性：

ALTER TABLE PlaylistSongMapping
   ADD COLUMN Position_secondary INT DEFAULT 1000 NOT NULL;

-- move songId = 3 to first position
update PlaylistSongMapping 
   set Position = 1
     , Position_secondary = 500  
where SongId = 3
 and PlaylistId = 10;

-- Get songs in order:
select * from PlaylistSongMapping
order by PlaylistId, Position, Position_secondary

引用自：https://dba.stackexchange.com/questions/226572