Few years ago I came accross an issue, when I needed to find smallest continuous integer in a sequence. My data were like 1, 2, 3, 100, 101, 102, 520, 521, 530 and the task to find lets say next continuous bigger than 100. Long story short, fastest and the final SQL was:
SELECT t1.num+1 FROM table AS t1 WHERE NOT EXISTS (SELECT t2.num FROM table AS t2 WHERE t1.num+1 = t2.num) AND t1.num>=100 GROUP BY t1.num ORDER BY t1.num LIMIT 1
Today, the issue was a little more difficult. I have to find smallest continuous 2D sequence in any direction.
The data are:
x y -1000 -1000 -200 -100 -100 -100 -100 0 0 100 100 100 ...
This data creates some kind of grid. By direction it is meant that you want to find the closest unset grid position starting from [0, 0] going to any direction defined by random vector [53, -45]. Grid size is 100. This issue required also dynamic table name what brings prepared statements into the scene. There may exist even a simplier solution, but this procedure executes in a 0.000something sec. when crawling more than a few thousands of entries.
CREATE PROCEDURE `getEmpty`(IN `tbl` VARCHAR(50), IN `ix` FLOAT, IN `iy` FLOAT, OUT `ox` INT, OUT `oy` INT) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' label:BEGIN DECLARE px INT DEFAULT 0; DECLARE py INT DEFAULT 0; WHILE TRUE DO SET @res = NULL; SET @dsql = CONCAT('SELECT x INTO @res FROM ', tbl, ' WHERE x = FLOOR(', px,' / 100) * 100 AND y = FLOOR(', py, ' / 100) * 100'); PREPARE stm from @dsql; EXECUTE stm; DEALLOCATE PREPARE stm; IF @res IS NULL THEN SELECT FLOOR(px / 100) * 100, FLOOR(py / 100) * 100 INTO ox, oy; LEAVE label; END IF; SET px = px + ix; SET py = py + iy; END WHILE; END
Now all you have to do is call created procedure:
CALL getEmpty("myTable", RAND() * 200 - 100, RAND() * 200 - 100, @x, @y); SELECT @x, @y;
While testing it on my favourite HeidiSQL tool, everything worked like a charm. The real usage issue appeared when I tried it in PHP I received an error message saying:
1312: can't return a result set in the given context
The error is caused by prepared statements. PHP MySQL extension doesn’t support features like prepared statements and stored procedures. For using these features you have to use mysqli extension. So I switched to mysqli and everything worked.