使用带有 INSERT SELECT 的 RECURSIVE CTE 通过 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;
以上会产生语法错误.请注意,如果删除插入行,则 10 行将按预期显示,SELECT 语句中的值为 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.
有人知道在 INSERT/SELECT 查询中使用 CTE 的限制或任何解决方法吗?
Does anyone know restrictions concerning use of CTE in INSERT/SELECT queries, or any workaround?
更新:以下两个查询有效,一个来自@elenst 回复,另一个来自提供@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)
这篇关于使用带有 INSERT SELECT 的 RECURSIVE CTE 通过 MariaDB 生成表数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!