How Can I Select a Weighted Random Entry in MySQL?

MySQL: Weighted Random Entry Selection

Problem:

Your MySQL table contains a column "Multiplier" that assigns weights to entries. The task is to select a random entry while considering these weights.

Solution:

While RAND() can help select a random entry, weighting the selection requires an innovative approach. The answer lies in modifying the table setup to ensure balanced weighting.

According to experts, a query using ORDER BY -LOG(1.0 - RAND()) / Multiplier effectively achieves the weighted selection. Although the mathematical rationale behind this formula is not immediately apparent, it has been empirically proven to yield accurate results.

However, this approach faces a limitation. Assigning a weight of 0 to an entry to disable it would result in division by zero. To circumvent this issue, you could utilize a WHERE clause to filter out entries with a Multiplier greater than 0.

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