CODEWITHSUNDEEP
mysqlsqlmyisam
Paul
Paul

Reputation: 141827

How generate a set of integers in MySQL

Each of my user's have a set number of slots for storage. They can delete from any slot and there is a maximum number of slots (undetermined yet but will be somewhere in the range of 30 - 100). So if a user has an item in their first 5 slots, and then they delete from slots 4 and 2. They will have items still in slots 0, 1, and 3. I want to find their first empty slot, so in this example: slot 2.

I discovered a way to do this, but it seems kind of hackish and could possibly be optimized a lot.

This is how I currently do it:

// Make set of ints from 0 to max size
$slots = 'SELECT 0 as `x`';
for($i = 1; $i < $max; $i++)
    $slots .= ' UNION SELECT '.$i.' as `x`';

$q = $db->prepare(' 
    SELECT  MIN(`x`)
    FROM (
        '.$slots.'
    ) as `slots` 
    WHERE 
        `x` NOT IN (
            SELECT  `slot`
            FROM `'.$table.'`
            WHERE
                `user` = ?
            )
');

$q->setFetchMode(PDO::FETCH_NUM);
$q->execute(array($user));

So if MAX = 5 the query would become:

SELECT  MIN(`x`)
FROM (
    SELECT 0 as `x` UNION
    SELECT 1 as `x` UNION
    SELECT 2 as `x` UNION
    SELECT 3 as `x` UNION
    SELECT 4 as `x`
) as `slots` 
WHERE 
    `x` NOT IN (
         SELECT `slot`
         FROM `my_table`
     WHERE
             user = 1
)

Basically I am generating a subquery to create te equivalent of PostgreSQL's generate_series(0, MAX - 1) Like I previously said, MAX will be between 30 and 100.

Some other ideas I had to do this are to either:

Are either of those better, or is there another way that is better?

Upvotes: 1

Views: 573

Answers (4)

Ian Clelland
Ian Clelland

Reputation: 44152

You can find the first empty slot by joining the table to itself, with the condition that the second table instance has slot numbers one higher than the first:

Select a.slot+1
From my_table a
Left Join my_table b
On (a.user = 1 and b.user = 1 and a.slot = b.slot - 1)
Where b.slot is null and a.slot < 100
Order by a.slot
Limit 1

Replace the constants 1 and 100 with the actual user id and maximum slot number, and this will give you the first available slot for that user, or no rows if every slot is full.

Upvotes: 3

xQbert
xQbert

Reputation: 35333

UNTESTED: But the concept seems correct...

Select min(a.`rNum`) 
FROM (Select `slot`,@rownum:=@rownum+1 as rNum,userID
from `slots`, (SELECT @rownum:=0) 
order by `slot`) as A
where A.userID=1 and A.`slot`<> a.rNum

Inner select would return

1 1 1
3 2 1
5 3 1

So outer would return

2

Updated to address rowNum = null taken from: http://blog.gomilko.com/2007/04/28/mysql-rownum-imitation

Upvotes: 0

mu is too short
mu is too short

Reputation: 434685

Don't use an "inline" table like this:

FROM (
    SELECT 0 as `x` UNION
    SELECT 1 as `x` UNION
    SELECT 2 as `x` UNION
    SELECT 3 as `x` UNION
    SELECT 4 as `x`
) as `slots`

Build a real table called slots and populate it once with values from 1 to 100 (or whatever). Then you can do things like this:

select min(s.slot)
from slots s
left outer join your_table t on s.slot = t.slot and t.user = 1
where t.slot is null

to get the first open slot in the sub-table of your_table where user = 1.

Upvotes: 2

prodigitalson
prodigitalson

Reputation: 60413

I think maybe you are over thinking this. Why keep track of a sequence just rely on the total number of slots used/unused and do your operations based on the primary key. If youre worried about a count query everytime then store the number used in the user table.

Upvotes: 0

Related Questions