How to Select a Weighted Random Row in MySQL?

Random Selection with Weighted Results in MySQL

This issue involves selecting a random entry from a MySQL table where rows are weighted based on a "Multiplier" column. Conventional approaches using RAND() don't account for this weighting.

To address this, a user suggested an approach involving ORDER BY -LOG(1.0 - RAND()) / Multiplier. This formula effectively adjusts the randomization process to give weighted rows a higher probability of being selected.

The formula calculates a value that represents the "randomness" for each row, where a lower value indicates a higher probability. By dividing this value by the "Multiplier," rows with a higher multiplier are effectively given a lower "randomness" and thus a higher chance of being chosen.

The downside of this method is that it's not possible to disable a row by setting the multiplier to 0, as it would result in a division by zero. However, this limitation can be overcome by filtering the rows with WHERE Multiplier > 0 to exclude those with a zero multiplier.

The above is the detailed content of How to Select a Weighted Random Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!