Get all numbers in range in MySQL

Question

I'm trying to get all the numbers in a range where I have two integer columns in MySQL.

Here is an example of the structure:

+----+-------+------+
| id |     a |    b |
+----+-------+------+
|  1 |  1971 | 1975 |
|  2 |  1975 | 1975 |
|  3 |  1980 | 1982 |
+----+-------+------+

What I'd like is something like this:

SELECT id, RANGE(a,b) as range FROM table;

And the output should be like this:

+----+--------------------------+
| id | range                    |
+----+--------------------------+
|  1 | 1971 1972 1973 1974 1975 |
|  2 | 1975                     |
|  3 | 1980 1981 1982           |
+----+--------------------------+

Also, the function needs to be have reasonable performance as there are 100,000+ rows in the table. I was initially doing something in PHP but it's proving to be way too slow to select and parse every single row. I haven't tried anything yet since I am completely stumped about how to do something like in SQL.

Also, I did look at this question but it's a different enough case that I couldn't figure out how to apply the solutions provided.

"it's proving to be way to [sic] slow to select and parse every single row" my guess is you're doing it wrong — Strawberry, Jan 24 '17 at 17:04
@Strawberry - no the php was fine. The pure SQL method I outlined below is way faster though. — But those new buttons though.., Jan 24 '17 at 18:01

score 0 · Accepted Answer · edited May 23 '17 at 12:16

Solved my own problem adapting the concept in this answer.

First create a table of years with an index (this gets you 1900 - 2099 which is adequate for my purposes):

CREATE TEMPORARY TABLE years (
    years INT(11) NOT NULL,
    PRIMARY KEY (years)
) AS
(
    SELECT
    (1900 + HUNDREDS.val + TENS.val + ONES.val) AS `years`
    FROM
    ( SELECT 0 val UNION ALL SELECT 1 val UNION ALL SELECT 2 val UNION ALL SELECT 3 val UNION ALL SELECT 4 val UNION ALL SELECT 5 val UNION ALL SELECT 6 val UNION ALL SELECT 7 val UNION ALL SELECT 8 val UNION ALL SELECT 9 val ) ONES
    CROSS JOIN
    ( SELECT 0 val UNION ALL SELECT 10 val UNION ALL SELECT 20 val UNION ALL SELECT 30 val UNION ALL SELECT 40 val UNION ALL SELECT 50 val UNION ALL SELECT 60 val UNION ALL SELECT 70 val UNION ALL SELECT 80 val UNION ALL SELECT 90 val ) TENS
    CROSS JOIN
    ( SELECT 0 val UNION ALL SELECT 100 val ) HUNDREDS
)

Next simply join and group concat the years like this:

SELECT id, GROUP_CONCAT(y.years SEPARATOR ' ') as `range`
FROM table t
    LEFT JOIN years y ON (y.years >= t.a AND y.years <= t.b)
GROUP BY t.id;

This is extremely fast - 77ms to parse 130,000 rows. Hope this helps someone.

Get all numbers in range in MySQL

1 Answers1