将RECURSIVE CTE与INSERT SELECT一起使用可通过MariaDB生成表数据 [英] Using RECURSIVE CTE with INSERT SELECT to generate table data with MariaDB

问题描述

我正在Windows 7上使用Maria DB版本10.2.9，

I'm using Maria DB version 10.2.9 on windows 7,

MariaDB > select @@version;
+----------------+
| @@version      |
+----------------+
| 10.2.9-MariaDB |
+----------------+

我正在尝试对INSERT SELECT使用递归CTE来创建一些测试数据。为简单起见，下面是要填充的单列表：

I'm trying to use recursive CTE with INSERT SELECT to create some test Data. For simplicity follows below a single column table to be populated:

CREATE TABLE cte_populated
(
  id INT NOT NULL PRIMARY KEY
)
  ENGINE = InnoDB;

生成1到10值的CTE：

And the CTE which generates values 1 to 10:

WITH  RECURSIVE int_seq AS (
SELECT 1 AS val
UNION ALL
SELECT val + 1
FROM int_seq
WHERE val  < 10
)
INSERT cte_populated(id)
SELECT int_seq.val FROM int_seq;

以上内容会产生语法错误。
请注意，如果删除了插入行，则SELECT语句中的10行将按预期显示，值从1到10。

The above generates a syntax error. Note that if the insert line is removed then the 10 rows will appear as expected with values 1 to 10 from the SELECT statement.

有人知道与限制有关的限制吗？在插入/选择查询中使用CTE，还是有其他解决方法？

Does anyone know restrictions concerning use of CTE in INSERT/SELECT queries, or any workaround?

Update：
以下两个查询有效，一个来自@elenst答复，另一个from提供@PM 77注释中的链接：

Update : The following two queries work, both the one from @elenst reply and the one from provided the link in @PM 77 comment:

INSERT cte_populated(id)
WITH  RECURSIVE int_seq AS (
SELECT 1 AS val
UNION ALL
SELECT val + 1
FROM int_seq
WHERE val  < 10)
SELECT int_seq.val as id FROM int_seq;

以下是链接，由@PM 77提供。

The following is an adaptation from the link provided by @PM 77.

INSERT INTO cte_populated
WITH  RECURSIVE int_seq(val) AS (
SELECT 1
UNION ALL
SELECT 1 + val FROM int_seq WHERE val  < 10)
SELECT * FROM int_seq;

推荐答案

这里没有限制，您只需要这样做语法相反：

There is no restriction here, you just need to do it the other way round syntax-wise:

INSERT cte_populated(id)
WITH  RECURSIVE int_seq AS (
SELECT 1 AS val
UNION ALL
SELECT val + 1
FROM int_seq
WHERE val  < 10
)
SELECT int_seq.val FROM int_seq;

更新：响应声称它仍然会导致错误的说法，并添加实际的客户端输出：

Update: to respond to the claim that it still causes the error, adding the actual client output:

MariaDB [test]> CREATE TABLE `cte_populated` (
    ->   `id` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.21 sec)

MariaDB [test]> INSERT cte_populated(id)
    -> WITH  RECURSIVE int_seq AS (
    -> SELECT 1 AS val
    -> UNION ALL
    -> SELECT val + 1
    -> FROM int_seq
    -> WHERE val  < 10)
    -> SELECT int_seq.val as id FROM int_seq;
Query OK, 10 rows affected (0.04 sec)
Records: 10  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM cte_populated;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

MariaDB [test]> select @@version;
+----------------+
| @@version      |
+----------------+
| 10.2.9-MariaDB |
+----------------+
1 row in set (0.00 sec)

这篇关于将RECURSIVE CTE与INSERT SELECT一起使用可通过MariaDB生成表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！