sql - 从歌曲表中创建一个随机排序的播放列表并返回当前、下一首和上一首歌曲的名称？

Question

考虑下表:

Song
----
SongID GUID Primary Key
Name Varchar
dateAdded DateTime

我正在创建一个从 Song 表播放歌曲的网站。我想为站点的每个访问者生成一个随机排序的播放列表，该列表在不使用任何服务器端存储(无 session 样式或数据库存储)的情况下持续存在于请求中。播放器具有播放、下一首歌曲和上一首歌曲按钮。跨请求保留此数据的最有效方法是什么？对此信息最有效的查询是什么？我有一个有效的解决方案(在 mySql 中)，但我想看看是否有更有效的方法。另外，我不想让答案偏向于使用我的解决方案。请在您的答案中包含一个查询。

Answer 1

更新:这是我遵循的要求，详细说明:

无论表中有多少首歌曲或播放列表中的当前位置，客户端需要存储的信息(例如在 cookie 中)必须具有恒定大小。

使用 'next' 100 次，然后使用 'prev' 100 次应该会产生 100 首歌曲的某些序列(可能包括重复)，然后颠倒该确切序列，无论在任何 'next' 或 'prev 之间可能已进行任何插入' 行动。 (“上一首歌曲总是指上一首歌曲。”)

将跳过“在生成/初始化时”出现在播放列表中并已被删除的歌曲。

不难改变我在下面的答案以返回一个未找到的指标。

我认为您还需要一条信息:除了您的 GUID(或者您可以用它替换它)之外，还需要一个整数位置的辅助歌曲键。然后，结合一个 PRNG ，您可以进行最简单快速的查找。伪代码:

def next_song(initial_seed, current_prng_index, high_song_index):
  """Returns the next song and the parameters to be later passed
  to next/prev_song."""
  while True:
    current_prng_index += 1
    current_seed = PRNG_advance(initial_seed, current_prng_index)
    song_index = song_index_from_seed(current_seed, high_song_index)
    song_id = (SELECT SongID FROM Songs WHERE SongIndex=song_index)
    if song_id: # test, somehow, for non-deleted songs
      return song_id, (initial_seed, current_prng_index, high_song_index)
      # include values that must be passed on the next call
# prev is the same, except uses -= 1

def song_index_from_seed(seed, highest):
  return seed % (highest + 1)
  # simple, but better possibilities might exist for your data

def new_playlist():
  """Returns the parameters to be passed to next/prev_song."""
  high_song_index = (SELECT MAX(SongIndex) FROM Songs)
  return get_a_random_number(), 0, high_song_index

这将在下一首歌曲中逐渐变慢，并且不允许倒退(没有一些创造力)。如果您找到合适的可逆 PRNG(虽然在好的 PRNG 中并不常见，AFAIK):

def next_song(current_seed, high_song_index):
  while True:
    current_seed = PRNG_next(current_seed)
    song_index = song_index_from_seed(current_seed, high_song_index)
    song_id = (SELECT SongID FROM Songs WHERE SongIndex=song_index)
    if song_id: # test, somehow, for non-deleted songs
      return song_id, (current_seed, high_song_index)
      # include values that must be passed on the next call
# prev is the same, except uses PRNG_prev

这通过跳过这些歌曲来处理删除(或者如果你从不删除那甚至不是问题)，但否则无论删除多少都不会改变顺序。插入由 song_index_from_seed 函数处理，通过限制索引，使新歌曲永远不会被“看到”。如果所有可用歌曲都被删除，这也是一个无限循环，但我认为这段代码可以处理所有其他极端情况。

(将下一个/上一个版本重构为围绕一个通用函数的简单包装器并不难，但为了增加清晰度，这里省略了。)

我已经有效地用我的位置索引替换了你的 dateAdded，但这是一个改进，因为你不需要像位置索引是通过排序计算的那样将删除的行保留为虚拟对象(但仍然不能重用它们的索引)添加日期。

使用这样的 PRNG 看起来很幼稚，但确实有效；并且您必须注意所选 PRNG 和 song_index_from_seed 组合的行为是否符合您的预期:某些初始种子可能会生成“非随机”播放列表。 (不是因为它们不是随机的，而是因为听众期望混合歌曲，而不仅仅是 4, 4 、 9, 9 、...)