播放列表下歌曲位置的数据库设计

Question

我有一个音乐流应用程序，在那里我有播放列表。每个播放列表最多有100首歌曲。我有一个名为PlaylistSongMapping的映射表，它具有以下模式

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

播放列表中每首歌曲的位置使用Position列进行管理。在播放列表中，我需要订单变更功能。当前的逻辑是更新Position列。问题是，如果我需要将带有SongId: 3的歌曲移动到第一个位置，我需要更新SongId 1,2,3的行。当播放列表中有更多的歌曲时，这个数字将是一个很大的数字。是否有更好的逻辑使更新查询的数量减少。

Answer 1

如果您需要一个SP，在移动该职位时更新该表中的所有相关位置，这将完成以下工作：

USE Sandbox;
GO

--Small sample set
CREATE TABLE dbo.Playlist (SongID int,
                           PlaylistID int,
                           Position int);
INSERT INTO dbo.Playlist (SongID,
                      PlaylistID,
                      Position)
VALUES(1,1,1),
      (3,1,2),
      (5,1,3),
      (2,1,4),
      (2,2,1),
      (10,2,2);
GO

--Check data
SELECT *
FROM dbo.Playlist;
GO

--CREATE the SP
CREATE PROC dbo.MoveSongPosition @PlaylistID int,
                             @SongID int,
                             @NewPosition int
AS BEGIN

    UPDATE P
    SET Position = CASE WHEN SongID = @SongID THEN @NewPosition ELSE Position +1 END
    FROM dbo.Playlist P
    WHERE P.PlaylistID = @PlaylistID
      AND (P.SongID = @SongID
       OR  P.Position BETWEEN @NewPosition AND (SELECT sq.Position
                                               FROM dbo.Playlist sq
                                               WHERE sq.SongID = @SongID
                                                 AND sq.PlaylistID = @PlaylistID));
END
GO

--Run and test the SP
EXEC dbo.MoveSongPosition @PlaylistID = 1,
                          @SongID = 2,
                          @NewPosition = 2;
GO

--Check the new data
SELECT *
FROM dbo.Playlist;
GO

--Clean up
DROP PROC dbo.MoveSongPosition;
DROP TABLE dbo.Playlist;

这个解决方案假设您使用的是Server；自从我的评论之后，您还没有更新您的标记，所以我们不知道您是真正使用的。

编辑:逻辑上的改变，相信我已经改正了。

CREATE PROC dbo.MoveSongPosition @PlaylistID int,
                                 @SongID int,
                                 @NewPosition int
AS BEGIN

    UPDATE P
    SET Position = CASE WHEN P.SongID = @SongID THEN @NewPosition
                        WHEN P.Position = @NewPosition THEN P.Position + V.Direction
                        WHEN P.Position < @NewPosition THEN P.Position - V.Direction
                        WHEN P.Position > @NewPosition THEN P.Position + V.Direction
                   END
    FROM dbo.Playlist P
         CROSS APPLY (SELECT ca.Position
                      FROM dbo.Playlist ca
                      WHERE ca.PlaylistID = P.PlaylistID
                        AND ca.SongID = @SongID) CS
         CROSS APPLY (VALUES(CASE WHEN CS.Position < @NewPosition THEN -1
                                  WHEN @NewPosition < CS.Position THEN 1
                                  ELSE 0 END)) V(Direction)
    WHERE P.PlaylistID = @PlaylistID
      AND ((P.Position >= @NewPosition AND P.Position <= CS.Position
       OR   (P.Position >= CS.Position AND P.Position <= @NewPosition)));
END

Answer 2

理论上更优化(但更难)

如果您认为该列是一个排序顺序，而不是一个确切的位置，您可能会有一些有差距的策略，例如10，20，30。然后，如果你想把第三首歌移到第一个位置，你可以给它编号5，或者你甚至可以给它一个负数。

问题是，您仍然需要读取所有数据，编号的实现变得更加复杂，因为现在您需要检查是否需要更新任何内容，或者所有内容，或者仅仅是列表的一部分。

相当简单(基本上就是现在的)

所以，如果我是你，我会保持简单，只需更新所有需要更新。毕竟，每个播放列表没有那么多行(即使每个播放列表允许1000或更多的歌曲)，而且如果您使这是一个大容量更新，数据库应该能够很好地处理这一点。您可以一次移动所有行，然后将一行更新到其新位置。这样，您只需要两条语句:一条用于插入、更新或删除一首特定歌曲，另一条用于移动列表的全部或部分以保持后续编号。

非常简单(到目前为止可能仍然足够快)

在过去，我以一种更懒惰的方式实现了这一点，在这种情况下，我保留了一个带有间隙的排序顺序，其中有2、4、6、8.然后，当我想要在某个位置更新或插入时，我可以使用sortorder = position*2-1对行进行插入或更新，或者简单地删除任何行：

update Song
set 
  SortOrder = :NewPosition*2-1
where
  SortOrder = :OldPosition*2;

在此之后，我只需更新所有行以再次修复编号，无论我的修改在哪里，都会根据排序顺序生成一个新的序列。

这将意味着我对已经正确的行进行了多余的更新，但是它非常非常简单，而且非常快(因为数据库擅长这样的东西)，而且它也有一些自动修复的效果，因为整个列表每次都会被重新编号，纠正过去的任何错误。这取决于您的数据库如何准确地实现这一点。我使用的是Oracle，它非常好，并生成这样的序列。在MySQL中，它稍微麻烦一些，但仍然没有那么难。

Answer 3

“问题是如果我需要用SongId: 3将歌曲移动到第一个位置，我需要更新SongId 1、2、3的行”

为此，您不应该需要超过两个语句。

您可以通过适当地使用增量/递减来更新一个语句中的所有位置值(除了要移动到第一个位置的位置值)，然后使用第二个语句更新您要移动的特定歌曲。

update PlaylistSongMapping 
set Position = Position + 1 
where 
  Playlistid = 10 
  and position < 3 and position >= 1; 

update PlaylistSongMapping 
set position = 1 
where songId = 3;

这假设您知道要移动的歌曲的Id (因此可以确定它的当前位置，这是第一个查询中where子句所需的)--但是根据您的描述，您应该知道这一点。

请注意，在上面的示例中，position >= 1并不是绝对必要的，因为1是列表中的第一个位置，但是这段代码旨在涵盖一个更一般的情况--例如，如果您想将它移到位置2，您就不希望在增量中的位置1处包含歌曲，因此在这种情况下，您将编写position >= 2。