Select unexisting value from MySQL

There is a table "tbl" with unique values ​​"val" from a certain range (eg, from 100 to 999).

Need to choose the guaranteed value, which does not yet exist in the table without the aid of an additional table with all existing values ​​over the left association.

For example.

Helper table fill (whole range) with values from 1 to 9.

Basic table structure:

CREATE TABLE `ranger` (
  `val` int(2) unsigned NOT NULL,
  UNIQUE KEY `val` (`val`)
) ENGINE=MyISAM;

INSERT INTO `ranger` (`val`) VALUES (1), (2), (4), (5), (6), (7), (8);

To select a non-existent value from r开发者_开发技巧anger:

SELECT 
    val
FROM 
    ranger_helper
WHERE 
    val NOT IN(SELECT val FROM ranger)
ORDER BY 
    RAND() 
LIMIT 
    1

---

SELECT id FROM myTable WHERE id NOT IN (100,101,102,103...,999);

could work if you can derive the range easily externally

Another approach could be to create a stored procedure and either derive the range with a WHILE loop or query for each value in the table.

---

There is no number generator in MySQL. See this question and answer:

How do I make a row generator in MySQL?

At the very best, you can derive it from the range using an anti-join:

select id from foo where id not in (...);