如果数组元素不存在，如何将它们插入到db中

Here is my db schema:

下面是我的db模式:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE videos (
    videoId             INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title               VARCHAR(300) NOT NULL,
    url                 VARCHAR(300) NOT NULL,
    description         VARCHAR(300) NOT NULL,
    channelName         VARCHAR(300) NOT NULL
);

CREATE TABLE technology_video_map (
    videoId        INT,
    technologyName VARCHAR(50),

    PRIMARY KEY (videoId, technologyName),
    FOREIGN KEY (videoId) REFERENCES videos(videoId),
    FOREIGN KEY (technologyName) REFERENCES technologies(technologyName)
);

I want the user to submit a video:

我想让用户提交一个视频:

var input = {
  title: 'Grunt makes your web development better!',
  url: 'https://www.youtube.com/watch?v=TMKj0BxzVgw',
  description: 'If you\'re not using a task runner/build system like Grunt or Gulp...',
  channelName: 'LearnCode.academy',
  technologies: ['Grunt', 'JavaScript']
};

And I want to insert it into the db. Inserting only the video is easy enough for me:

我想把它插入到db中。只插入视频对我来说很简单:

var technologies = input.technologies; // save for later
delete input.technologies;
connection.query('INSERT INTO videos SET ?', input, function (err, result) {
    var videoId = result.insertId;
});

(connection comes from the brilliant node-mysql)

(连接来自卓越的node-mysql)

What I am having trouble with, is inserting the technologies into the db. I have identified that the first step is:

我遇到的麻烦是，将技术插入到db中。我认为，第一步是:

Insert elements in input.technologies into the technologies table if they do not already exist.
在输入中插入元素。如果技术表中还不存在技术，则将其放入技术表中。

I can only imagine doing this using a for loop or something which is yucky.

我只能想象用for循环来做这个。

The second step, I think, is:

我认为第二步是:

Insert a new record into the junction table, technology_video_map from the connection.query callback, as we need the videoId.
在连接表中插入一个新记录，从连接中插入techy_video_map。查询回调，因为我们需要这个视频。

Again, I cannot imagine an idiomatic way to do this using SQL. Can someone please guide me?

同样，我无法想象使用SQL实现这一点的惯用方法。有人能指引我吗?

2 个解决方案

#1

You can use INSERT IGNORE and join your input.technologies array. This will insert records that do not exist and will ignore records that already exist

可以使用INSERT IGNORE和join输入。阵列技术。这将插入不存在的记录，并将忽略已经存在的记录

var query = "INSERT IGNORE INTO technologies (technologyName) VALUES ('" + input.technologies.join("'),('") + "')";

#2

You have to check for its existence

你必须检查它的存在

if not exists(select technologyName from technoligies 
              where technologyName='$technologies')
insert (..)

If you are not using a stored procedure, then fire a query to fetch it. If it fails, then insert it.

如果不使用存储过程，则启动查询以获取它。如果失败，那么插入它。

#1