Question 113076 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: in designing a loot drop table, is it possible to specify a number of times the query repeats itself and outputs each result on the same table

as part of teaching myself SQL, I’m coding a loot drop table that I hope to use in D&D campaigns.

the simplest form of the query is:

SELECT rarity,
     CASE
        WHEN item=common THEN (SELECT item FROM common.table)
        WHEN item=uncommon THEN (SELECT item FROM unommon.table)
        ...etc
     END AS loot
 FROM rarity.table
ORDER BY RAND()*(1/weight)
LIMIT 1

the idea is that the query randomly chooses a rarity from the rarity.table based on a weighted probability. There are 10 types of rarity, each represented on the rarity.table as a single row and having a column for probabilistic weight.

If I want to randomly output 1 item (limit 1), this works great.

However, attempting to output more than 1 item at a time isn’t probabilistic in that the query can only put out 1 row of each rarity. If say I want to roll 10 items (limit 10) for my players, it will just output all 10 rows, producing 1 item from each rarity, and never multiple of the higher weighted rarities.

I have tried something similar, creating a different rarity.table that was 1000 rows long, and instead of having a ‘weight’ column representing probabilistic weight in rows, ex. common is rows 1-20, uncommon rows 21-35…etc.
Then writing the query as

ORDER BY RAND()
LIMIT x

— (where x is the number of items I want to output)

and while this is better in some ways, it results are still limited by the number of rows for each rarity. I.E. if I set limit to 100, it again just gives me the whole table without taking probability into consideration. This is fine in that I probably won’t be rolling 100 items at once, but feels incorrect that the output will always be limited to
20 common items, 15 uncommon, etc. This is also MUCH slower, as my actual code has a lot of case and sub-case statements.

So, my thought moved on to if is possible to run the query with a limit 1, but to set the query to run x number of times, and then include each result on the same table, preserving probability and not being limited by the number of rows in the table. However, I haven’t figured out how to do so.

Any thoughts on how to achieve these results? Or maybe a better approach?
Please let me know if I can clarify anything.

Thank you!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    while I'm sure there is room for improvement / optimization, I actually figured out a solution for myself in case anyone is interested.

    Instead of the first query being the rarity table, I made a new table that is thousands of entries long, called rolls.table, and first query this table. Here, the limit function works as a way to select the number of rolls I want to make.

    Then, every time this table outputs a row the query selects from the rarity.table independently.

    Does that make sense?

    I'll work with this for now, but would love to hear how to make it better.... it takes like 20 seconds for the output table to load haha.


  2. 0

    A big no-no is having several virtually identical tables (common and uncommon) as separate tables. Instead, have one table with an extra column to distinguish the types. That will let your sample query be written more simply, possibly with a JOIN.

    attempting to output more than 1 item at a time isn’t probabilistic in that the query can only put out 1 row of each rarity

    Let’s try to tackle that with something like

    SELECT ... WHERE ... 'common'   ORDER BY ... LIMIT 1
UNION
SELECT ... WHERE ... 'uncommon' ORDER BY ... LIMIT 1
...

    If you don’t want the entire list like that, then do

    (
     ((the UNION above))
) ORDER BY RAND()  LIMIT 3;  -- to pick 3 of the 10

    Yes, it looks inefficient. But ORDER BY RAND() LIMIT 1 is inherently inefficient — it fetches the entire table, shuffles the rows, then peels off one row.

    Munch on those. There are other possibilities.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search