sql - How do I get a list of numbers in MySQL?

Question

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.

Answer 1

This should work until you need more than 195 years , at which point you'll need to add a UNION ALL:

SELECT Year 
FROM   (   SELECT @i:= @i + 1 AS YEAR
           FROM   INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
                  ( SELECT @i:= 1899) AS i
        ) As Y
WHERE   Year BETWEEN 1900 AND 2012
ORDER BY Year;

Although I am assuming that the COLLATION_CHARACTER_SET_APPLICABILITY System table has a default size of 195 based on my trusty testing ground SQL Fiddle