Find which numbers in [1, 161] are not in the result set?

Question

How to find which two number in [1, 161] is not in the result set?

Can I find this two numbers using sql command?

mysql> select blog_id from wp_blogs;

...

|     149 |
|     150 |
|     151 |
|     152 |
|     153 |
|     154 |
|     155 |
|     156 |
|     157 |
|     158 |
|     159 |
|     160 |
|     161 |
+---------+
159 rows in set (0.25 sec)

The sequence is from 1 to 161, there should be 161 numbers, but only 159 numbers are in the resultset.

mysql> select count(blog_id) from wp_blogs;
+----------------+
| count(blog_id) |
+----------------+
|            159 |
+----------------+
1 row in set (0.24 sec)

How to find the missing numbers?

Answer 1

To find gaps in a number range:

Test table and data:

mysql> CREATE TABLE wp_blogs
    -> (
    ->   blog_id INTEGER
    -> );

mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
mysql> insert into wp_blogs values(8);
mysql> insert into wp_blogs values(10);

Query:

mysql> SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
    ->     FROM wp_blogs AS a, wp_blogs AS b
    ->     WHERE a.blog_id < b.blog_id
    ->     GROUP BY a.blog_id
    ->     HAVING start < MIN(b.blog_id);
+-------+------+
| start | end  |
+-------+------+
|     3 |    3 |
|     5 |    5 |
|     9 |    9 |
+-------+------+