如果 postid 不存在，MySQL 将记录从一张表插入另一张表

Question

如果我想将 postid 从 posts 表插入 star_ ratings 表，是否有比这更好的方式编写查询star_atings 表中不存在 postid。

INSERT INTO star_ratings(post_id) 
SELECT postid FROM posts 
WHERE type in ('D', 'B') 
AND postid NOT IN (SELECT post_id FROM star_ratings) 
ORDER BY postid ASC

Answer 1

以下 EXISTS 查询可能比您现有的查询性能稍好:

INSERT INTO star_ratings (post_id)
SELECT p.postid
FROM posts p
WHERE
    p.type IN ('D', 'B') AND
    NOT EXSITS (SELECT 1 FROM star_ratings s WHERE p.post_id = s.post_id);