mysql - 插入多个值并忽略非关键字段上的重复项

Question

如果我想将多个值插入到带有字段的表中:

ID | name | content  
---+------+------------------
 1 | john | some content  
 2 | bob  | another content

现在我想插入多个值:

INSERT INTO persons (name,content)
VALUES
("Foo","meh"),
("Moo","beh"),
("bob","huh"),
("dude","haha")

结果应该是:

ID | name | content  
---+------+------------------
 1 | john | some content  
 2 | bob  | another content
 3 | Foo  | meh
 4 | Moo  | beh
 5 | dude | haha

查询忽略名称重复项，因此不插入“bob”|“huh”，名称不是关键字段。我在任何地方都找不到它的语法，我知道它应该很简单。

我正在使用 MySQL 数据库。

Answer 1

使用LEFT JOIN判断记录是否已经在表中

SqlFiddleDemo

CREATE TABLE persons(ID INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100),
      content VARCHAR(100));

INSERT INTO persons (name, content)
VALUES ('john', 'some content'), ('bob', 'another content');

INSERT INTO persons (name,content)
SELECT t.name, t.content
FROM
(
  SELECT "Foo" AS name,"meh" AS content
  UNION ALL
  SELECT "Moo","beh"
  UNION ALL
  SELECT "bob","huh"
  UNION ALL
  SELECT "dude","haha"
) AS t
LEFT JOIN persons p
ON p.name = t.name
WHERE p.id IS NULL;

SELECT *
FROM persons;

同样使用 NOT EXISTS

INSERT INTO persons (name,content)
SELECT t.name, t.content
FROM
(
  SELECT "Foo" AS name,"meh" AS content
  UNION ALL
  SELECT "Moo","beh"
  UNION ALL
  SELECT "bob","huh"
  UNION ALL
  SELECT "dude","haha"
) AS t
WHERE NOT EXISTS 
     (SELECT 1 FROM persons p WHERE p.name = t.name)

编辑:

添加UNIQUE KEY并使用INSERT IGNORE INTO

SqlFiddleDemo

CREATE TABLE persons(ID INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) UNIQUE KEY,
      content VARCHAR(100));

INSERT INTO persons (name, content)
VALUES ('john', 'some content'), ('bob', 'another content');


INSERT IGNORE INTO persons (name,content)
VALUES
("Foo","meh"),
("Moo","beh"),
("bob","huh"),
("dude","haha");

SELECT *
FROM persons;