如何使用INSERT INTO SELECT从playlist表向playlistvideos表插入外键数据

架构老林

2026-8-24

正确的SQL插入语句实现

问题说明

需要从playlist表获取外键值插入到playlistvideos表，原尝试代码存在语法错误，现有表结构及期望结果如下：

playlist表结构

playlistID | name      | userfk | icon
1          | Favourite | Armistice | Heart

videos表结构

videoID | videoTitle| videoLink | Thumbnail
2       | JWD       | JWD.mp4   | JWD.jpg

期望插入后playlistvideos表结果

videoID | playlistFK| videoTitle | videoLink | Thumbnail
2       | 1         | JWD        | JWD.mp4   | JWD.jpg

正确SQL语句

方式一：关联双表查询插入（推荐，避免硬编码视频信息）

直接从videos表读取视频数据，从playlist表匹配对应的外键，无需手动填写视频字段：

INSERT INTO playlistvideos(videoID, playlistFK, videoTitle, videoLink, thumbnail)
SELECT 
    v.videoID, 
    p.playlistID, 
    v.videoTitle, 
    v.videoLink, 
    v.thumbnail
FROM videos v
CROSS JOIN playlist p
WHERE v.videoID = 2
  AND p.userfk = 'Armistice' 
  AND p.icon = 'Heart';

方式二：固定视频信息，子查询获取外键

如果不需要关联videos表，可通过子查询动态获取playlistID：

INSERT INTO playlistvideos(videoID, playlistFK, videoTitle, videoLink, thumbnail)
VALUES (
    2,
    (SELECT playlistID FROM playlist WHERE userfk = 'Armistice' AND icon = 'Heart'),
    'JWD',
    'JWD.mp4',
    'JWD.jpg'
);