How do I get a list of numbers in MySQL?
mysql, sql
Solution
I had similar problem a few years ago. My solution was:
1. Sequence table
I created a table filled with integer sequence from 0 to < as much as it will be required >:
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4);
INSERT INTO numbers SELECT n+5 FROM numbers;
INSERT INTO numbers SELECT n+10 FROM numbers;
INSERT INTO numbers SELECT n+20 FROM numbers;
INSERT INTO numbers SELECT n+40 FROM numbers;
etc.
It is executed only once, so can be created from outside of your app, even by hand.
2. Select data of a needed type and range
For integers it is obvious - i.e. range 1..99:
SELECT n FROM numbers WHERE n BETWEEN 1 AND 99;
Dates - 2h intervals from now to +2 days:
SELECT date_add(now(),INTERVAL 2*n HOUR) FROM numbers WHERE n BETWEEN 0 AND 23;
So in your case it could be:
SELECT n+1900 AS n_year FROM numbers WHERE n BETWEEN 0 AND 112;
Then `JOIN` it on `n_year`.
Problem
I've got a database of movies, and I'd like a list of years where I don't have a movie for that year. So all I need is a list (1900 .. 2012) and then I can JOIN and IN and NOT IN on that all I want. I've got: ``` CREATE PROCEDURE build_years(p1 SMALLINT) BEGIN CREATE TEMPORARY TABLE year (year SMALLINT(5) UNSIGNED); label1: LOOP INSERT INTO year VALUES (p1); SET p1 = p1 + 1; IF p1 > 2012 THEN LEAVE label1; END IF; END LOOP; END ``` But that seems so unSQL and only marginally less kludgy then running Python code to create the same table. I'd really like something that didn't use a stored procedure, didn't use looping and didn't use an actual table, in that order of concern.