INSERT SELECTでWITHを利用したい

TL;DR

MySQL8で、INSERT SELECTでWITHを利用したい場合、以下のように書けばよいです。

INSERT INTO a (
  WITH c AS (
    SELECT * FROM b
  )
  SELECT * FROM c
);

WITH RECURSIVE (再帰SQL) も利用することができます。

INSERT INTO e (
  WITH RECURSIVE r AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM r WHERE seq < 5
  )
  SELECT * FROM r
);

環境

mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

稼働確認用DDL

WITH

CREATE TABLE a (
  col text
);
CREATE TABLE b (
  col text
);

INSERT INTO b VALUES ('col1');
INSERT INTO b VALUES ('col2');
INSERT INTO b VALUES ('col3');
INSERT INTO b VALUES ('col4');
INSERT INTO b VALUES ('col5');

INSERT INTO a (
  WITH c AS (
    SELECT * FROM b
  )
  SELECT * FROM c
);

SELECT * FROM a;

このSQLが以下のような結果セットを返すことを確認する。

col
col1
col2
col3
col4
col5

WITH RECURSIVE

CREATE TABLE e (
  seq INT
);

INSERT INTO e (
  WITH RECURSIVE r AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM r WHERE seq < 5
  )
  SELECT * FROM r
);

SELECT * FROM e;

このSQLが以下のような結果セットを返すことを確認する。

seq
1
2
3
4
5