Richard, Say, that's really good! I'm still not used to taking advantage of those variables. Clever. Also, I think you can remove all but the rows showing gaps by adding a group by and having clause...
select id,@b:=IF(id=@a+1,id,concat(@a+1,"..",id-1)) range,@a:=id, @flag:=IF(@b=id,id,"MISSING") from seq group by id having id <> range; > -----Original Message----- > From: Richard Clarke [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 30, 2002 7:48 PM > To: [EMAIL PROTECTED] > Subject: Re: Finding holes in autoinc sequences > > > Or another approach could be (using same example tables) > > mysql> set @a:=0; set @b:=0; set @c:=0; > mysql> select > id,@b:=IF(id=@a+1,id,concat(@a+1,"..",id-1)),@a:=id,@flag:=IF( > @b=id,id,"MISS > ING") from seq; > +----+-------------------------------------------+--------+--- > -------------- > --------------+ > | id | @b:=IF(id=@a+1,id,concat(@a+1,"..",id-1)) | @a:=id | > @flag:=IF(@b=id,id,"MISSING") | > +----+-------------------------------------------+--------+--- > -------------- > --------------+ > | 1 | 1 | 1 | 1 > | > | 2 | 2 | 2 | 2 > | > | 3 | 3 | 3 | 3 > | > | 4 | 4 | 4 | 4 > | > | 5 | 5 | 5 | 5 > | > | 6 | 6 | 6 | 6 > | > | 7 | 7 | 7 | 7 > | > | 8 | 8 | 8 | 8 > | > | 9 | 9 | 9 | 9 > | > | 12 | 10..11 | 12 | MISSING > | > | 13 | 13 | 13 | 13 > | > | 14 | 14 | 14 | 14 > | > | 15 | 15 | 15 | 15 > | > | 16 | 16 | 16 | 16 > | > | 17 | 17 | 17 | 17 > | > | 18 | 18 | 18 | 18 > | > | 19 | 19 | 19 | 19 > | > | 22 | 20..21 | 22 | MISSING > | > | 24 | 23..23 | 24 | MISSING > | > | 25 | 25 | 25 | 25 > | > | 26 | 26 | 26 | 26 > | > | 27 | 27 | 27 | 27 > | > | 28 | 28 | 28 | 28 > | > | 29 | 29 | 29 | 29 > | > +----+-------------------------------------------+--------+--- > -------------- > --------------+ > 24 rows in set (0.00 sec) > > To get the MISSING entries you would need to create a > temporary table from > that query then select where the @flag column = "MISSING". > Maybe this is more efficient than the join previously suggested. > Maybe mysql team would let HAVING be applied to the > 'variably' created rows > you could then add having flag_alias = "MISSING" project out > just the rows > that are missing (saving the need for temporary tables). > > you could also plug the 10..11 20..21 etc statements into a > perl foreach > loop (if you were using perl) to automatically create the > inner numbers. > > Ric > > p.s. maybe there is a way of tricking it into using a having > statement to > project out the "MISSING" rows. > > > ----- Original Message ----- > From: "Kevin Fries" <[EMAIL PROTECTED]> > To: "'mos'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Thursday, May 30, 2002 10:56 PM > Subject: RE: Finding holes in autoinc sequences > > > > A simple and fast way to find the gaps is to use a self > LEFT JOIN, such > as: > > > > select a.id from seq a left join seq b on a.id + 1 = b.id > > where b.id is null; > > > > > > The result set will show any row in seq where there was no > record with ID > > one greater than that record's value. > > You'll get back the last row, and any rows which don't have > a next-higher > > neighbor. > > > > To get my example above to work, use: > > > > create table seq ( id int auto_increment not null primary key); > > > > insert into seq values(); ** repeat to get rows 1..30. ** > > > > delete from seq where id between 10 and 11; > > delete from seq where id between 20 and 21; > > delete from seq where id = 23; > > > > select a.id from seq a left join seq b on a.id + 1 = b.id > > where b.id is null; > > > > +----+------+ > > | id | id | > > +----+------+ > > | 9 | NULL | > > | 19 | NULL | > > | 22 | NULL | > > | 30 | NULL | > > +----+------+ > > > > Note that this doesn't show where the gaps end. You can > further enhance > the > > query by looking backward as well...getting fancy output: > > > > select a.id, case when c.id is null and b.id is null then > '<>' else (case > > when c.id is null then '<' else (case when b.id is null > then '>' else '' > > end) end) end gaps > > from seq a left join seq b on a.id + 1 = b.id left join seq > c on a.id - 1 > = > > c.id > > where b.id is null or c.id is null; > > > > +----+------+ > > | id | gaps | > > +----+------+ > > | 1 | < | > > | 9 | > | > > | 12 | < | > > | 19 | > | > > | 22 | <> | > > | 24 | < | > > | 30 | > | > > +----+------+ > > > > Note here that there's a gap between 9 and 12, between 19 > and 22, and > > between 22 and 24. > > There's also a gap before 1, and one after 30, but this > just tells us > where > > the range ends. > > > > nulled Outer joins are very handy. > > > > Kevin Fries > > > > > > > > > > > > > -----Original Message----- > > > From: mos [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, May 30, 2002 1:18 PM > > > To: [EMAIL PROTECTED] > > > Subject: Finding holes in autoinc sequences > > > > > > > > > I'm going to be tackling this problem in a few days and I > > > wanted to bounce > > > it off of a few MySQL heads first to see if it generates any > > > ideas. (or > > > sparks?<g>) > > > > > > Here's is the problem. I have an auto-inc column and rows > > > will get deleted > > > from the table and of course it will create a hole in the > > > sequence which is > > > fine. But I want to track which rows have been deleted by > finding the > > > holes. (I will probably keep track of the deleted rows > as they get > > > deleted, but occasionally I will need to verify this by > > > scanning the table.) > > > > > > Example: > > > > > > Original squence of Rcd_Id: 1,2,3,4,5,6,7,8,9,10 > > > > > > After deleting rows 5 and 9 we get: > > > Rcd_Id: 1,2,3,4,6,7,8,10 > > > > > > Now is there any SQL statement that I can use to quickly and > > > efficiently > > > find the 2 missing rows in this example? The only thing I've > > > come up with > > > is to write a PHP program to loop through the Rcd_Id's in > > > order and see > > > which ones are missing (the query would fetch 10k rows at a > > > time so it > > > doesn't consume too much memory). The table could get rather > > > large (>1m > > > rows) and I need something that doesn't consume a lot of > > > memory or time. > > > Does anyone have any ideas? TIA > > > > > > Mike > > > > > > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
