lundi 24 septembre 2018

MySQL Predictable Random Ordering

I am very surprised that I can't figure this out.

I'm currently outputting a table from MySQL in a somewhat random order. I say somewhat because there is a formula that is partially reliant on RAND(). In any case, we can assume the order is effectively random for my question.

This was all working great, except I want to keep the same order for a "session". I don't want it to keep jumping around while actively using the data. I have been trying to figure out how to have MySQL generate the same sequence a second time.

I know that you can do RAND(N) where N is a seed, but as far as I can tell that will be the exact same number each time. So basically there will be no random factor at all if I use that.

What I would like is a way I can feed a seed into my ORDER BY and always get a reliable output order. For the same seed, I will get the same order, and if I feed in a different seed, it will be a different random order.

The best I could come up with is that I could create an additional table cell with a RAND for each row and use that for sorting. There are a few issues:

  • Additional memory is used in the database.
  • It doesn't work for multiple users, because I'd need a separate column for each user.

I have to think about this, but I'm pretty certain that there is a clever solution here that doesn't involve me adding an additional column to the database. Has anyone else ever encountered the need to do something like this?




Aucun commentaire:

Enregistrer un commentaire