如何在MySQL中创建行生成器?

Is there a way to generate an arbitrary number of rows that can be used in a JOIN similar to the Oracle syntax:

是否有一种方法可以生成任意数量的行，这些行可以用于类似于Oracle语法的联接:

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10

7 个解决方案

#1

Hate to say this, but MySQL is the only RDBMS of the big four that doesn't have this feature.

我不想这么说，但是MySQL是四大中唯一没有这个功能的RDBMS。

In Oracle:

在Oracle中:

SELECT  *
FROM    dual
CONNECT BY
        level < n

In MS SQL (up to 100 rows):

在MS SQL(多达100行):

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < n
        )
SELECT  *
FROM    hier

or using hint up to 32768

或者使用暗示到32768。

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < 32768
        )
SELECT  *
FROM    hier
OPTION (MAXRECURSION 32767) -- 32767 is the maximum value of the hint

In PostgreSQL:

在PostgreSQL:

SELECT  *
FROM    generate_series (1, n)

In MySQL, nothing.

在MySQL中,什么都没有。

#2

In MySql, it is my understand that you can get more than one row with a SELECT with no table (or DUAL).

在MySql中，我的理解是，您可以获得多于一行的选择，没有表(或双)。

Therefore, to get multiple rows, you do need a real or temporary table with at least the required number of rows.

因此，要获得多行，您需要一个真正的或临时的表，至少需要行数。

However, you do not need to build a temporary table as you can use ANY existing table which has at least the number of rows required. So, if you have a table with at least the required number of rows, use:

但是，您不需要构建一个临时表，因为您可以使用任何现有表，其中至少有需要的行数。因此，如果您有一个至少需要行数的表，请使用:

SELECT  @curRow := @curRow + 1 AS row_number
FROM    sometable 
JOIN    (SELECT @curRow := 0) r
WHERE   @curRow<100;

Just replace "sometable" with the name of any table of yours with at least the required number of rows.

只需将“sometable”替换为您的任何表的名称，并至少使用所需的行数。

PS: The "r" is a table "alias": I could have used "AS r". Any subquery in a FROM or JOIN clause creates a "derived table" which, as with all tables, must have a name or alias. (See MySql manual: 13.2.9.8. Subqueries in the FROM Clause)

“r”是一个表“别名”:我可以使用“AS r”。FROM或JOIN子句中的任何子查询都创建一个“派生表”，与所有表一样，必须有一个名称或别名。(见MySql手册:13.2.9.8。FROM子句中的子查询

#3

Have you considered a LEFT OUTER JOIN?

你考虑过左外加入吗?

#4

I don't know if this helps but you can number the rows from each select statement with sth. like:

我不知道这是否有帮助，但是你可以从每一个select语句中编号，比如:

SET @NUM = 0;

设置@NUM = 0;

SELECT @NUM:=@NUM+1 rowNumber, * FROM ...

选择@NUM:=@NUM+1 rowNumber， * FROM…

And later join them on this one. At large databases this can be very slow.

之后加入他们。在大型数据库中，这可能非常缓慢。

#5

If I'm understanding you, you want a list of consequtive numbers?

如果我理解了你，你想要一个结果数的列表吗?

Just make the list:

把列表:

create table artificial_range (id int not null primary key auto_increment, idn int);
insert into artificial_range (idn) values (0); --first row
insert into artificial_range(idn) select idn from artificial_range; --2nd
insert into artificial_range(idn) select idn from artificial_range; -- now 4 rows
insert into artificial_range(idn) select idn from artificial_range; --8
insert into artificial_range(idn) select idn from artificial_range; --16
insert into artificial_range(idn) select idn from artificial_range; --32
insert into artificial_range(idn) select idn from artificial_range; --64
insert into artificial_range(idn) select idn from artificial_range; --128

... etc, until you have, say, 1024.

…等等，直到1024。

update artificial_range set idn = id - 1 ;

-- now you have a series staring at 1 (id) and a series starting at 0

——现在你有一个系列从0开始，盯着1 (id)和一个系列。

Now join to it, or join to transformations of it:

现在加入它，或加入它的转换:

    create view days_this_century as 
select date_add('2000-01-01', interval a.idn day) as cdate 
from artificial_range;

#6

I had a table with a column (c5) that contained a number x, I needed a SQL expression that repeated the same row x numbers of times.

我有一个包含一个数字x的列(c5)的表，我需要一个SQL表达式来重复相同的行x次数。

My table A contained:

我的表一个包含:

c1  c2  c3  c4  c5
16  1   2   16  3
16  1   2   17  2 
16  1   2   18  1

And I needed:

我需要:

c1  c2  c3  c4  c5  n
16  1   2   16  3   1
16  1   2   16  3   2
16  1   2   16  3   3
16  1   2   17  2   1
16  1   2   17  2   2
16  1   2   18  1   1

I solved that with the expression:

我用表达式来解它:

SELECT
    c1, c2, c3, c4, c5, row_number AS n
FROM
    (
        SELECT
            @curRow := @curRow + 1 AS row_number
        FROM
            tablea
        JOIN (SELECT @curRow := 0) r
        WHERE
            @curRow < (
                SELECT
                    max(field1)
                FROM
                    tablea
            )
    ) AS vwtable2
LEFT JOIN tablea d ON vwtable2.row_number <= tablea.field1;

#7

Since this is currently one of the first results in Google for "mysql row generator", I'll add an update.

由于这是目前谷歌中“mysql行生成器”的第一个结果，我将添加一个更新。

If your flavor of MySQL happens to be MariaDB, they have this feature. It's called the "Sequence Storage engine" and it's used like this:

如果你的MySQL风格恰好是MariaDB，他们有这个功能。它被称为"序列存储引擎"它是这样使用的:

select * from seq_1_to_10;

With the results:

结果:

+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
+-----+
10 rows in set (0.00 sec)

Until version 10.0 it was a separate plugin that needed to be explicitly installed, but from 10.0 onwards it's built in. Enjoy!

在10.0版本之前，它是一个单独的插件，需要显式安装，但从10.0开始，它就内置了。享受吧!

#1