使用CTE插入MySQL记录遇语法错误，求正确实现写法

问题场景 #

原本通过重复子查询向film_actor表插入记录，为避免重复执行相同子查询尝试用CTE简化，但出现语法错误。

错误的CTE写法 #

WITH 
mySubQuery AS(
SELECT film_id FROM film WHERE film.title="WEST LION"
),
INS AS (
INSERT INTO film_actor(actor_id, film_id) 
VALUES ((SELECT actor_id FROM actor WHERE actor.first_name = "DAN" AND actor.last_name="TORN"), mySubQuery),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "MAE" AND actor.last_name="HOFFMAN"), mySubQuery),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "SCARLETT" AND actor.last_name="DAMON"), mySubQuery)
)
  SELECT * FROM mySubQuery,INS;

报错信息 #

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO film_actor(actor_id, film_id) 
VALUES((SELECT actor_id FROM acto' at line 6

可正常运行的子查询写法（对比用） #

INSERT INTO film_actor(actor_id, film_id) VALUES 
    ((SELECT actor_id FROM actor WHERE actor.first_name = "DAN" AND actor.last_name="TORN"), (SELECT film_id FROM film WHERE film.title="WEST LION")),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "MAE" AND actor.last_name="HOFFMAN"), (SELECT film_id FROM film WHERE film.title="WEST LION")),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "SCARLETT" AND actor.last_name="DAMON"), (SELECT film_id FROM film WHERE film.title="WEST LION"));

正确的CTE写法 #

MySQL中CTE不能直接在CTE定义里包含INSERT语句，正确的做法是用CTE预先查询出需要的film_id和所有目标actor_id，再通过INSERT...SELECT完成批量插入：

WITH 
mySubQuery AS (
    SELECT film_id FROM film WHERE title = "WEST LION"
),
targetActors AS (
    SELECT actor_id FROM actor 
    WHERE (first_name = "DAN" AND last_name = "TORN")
       OR (first_name = "MAE" AND last_name = "HOFFMAN")
       OR (first_name = "SCARLETT" AND last_name = "DAMON")
)
INSERT INTO film_actor(actor_id, film_id)
SELECT ta.actor_id, ms.film_id
FROM targetActors ta, mySubQuery ms;

写法说明 #

• 用mySubQuery一次性查询出目标影片的film_id，避免重复执行子查询
• 用targetActors一次性筛选出所有需要关联的演员actor_id，比多次单独查询更简洁
• 通过INSERT...SELECT将两个CTE的结果做笛卡尔积（每个演员都关联同一部影片），完成批量插入

内容的提问来源于stack exchange，提问作者Aamir Mohiuddin