mysql修改列表数字,如何获取MySQL中的数字列表？

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.

解决方案

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.