您正在插入而不是更新结果。 您可以在主列中定义名称列或将其设置为惟一的。

我有一个类似的问题，我需要插入多个如果不存在。所以从上面的例子中，我得出了这个组合……放在这里，以防有人需要。

注意: 我必须在MSSQL要求的地方定义name…MySQL也使用*。

INSERT INTO names (name)
SELECT name
FROM
(
  SELECT name
  FROM
  (
     SELECT 'Test 4' as name
  ) AS tmp_single
  WHERE NOT EXISTS
  (
     SELECT name FROM names WHERE name = 'Test 4'
  )
  UNION ALL
  SELECT name
  FROM
  (
     SELECT 'Test 5' as name
  ) AS tmp_single
  WHERE NOT EXISTS
  (
     SELECT name FROM names WHERE name = 'Test 5'
  )
) tmp_all;

MySQL: 创建表名( OID int(11) NOT NULL AUTO_INCREMENT name varchar(32) COLLATE utf8_unicode_ci NOT NULL 主键oid， 唯一键名称 InnoDB AUTO_INCREMENT=1;

or

该软件: 创建表[names] ( [oid] int identity (1,1) not null， [name] NVARCHAR (32) NOT NULL， 主键集群化([oid] asc) ）; 创建唯一的非聚集索引[Index_Names_Name] ON [names]([name] ASC);

我并不是建议你这样做，因为Piskvor和其他人建议的UNIQUE索引是一个更好的方法，但你实际上可以做你正在尝试的事情:

CREATE TABLE `table_listnames` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

插入一个记录:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
+----+--------+-----------+------+

尝试再次插入相同的记录:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
+----+--------+-----------+------+

插入一条不同的记录:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'John'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
|  2 | John   | Doe       | 022  |
+----+--------+-----------+------+

等等……

更新:

为了防止#1060 -重复列名错误，如果两个值可能相等，你必须命名内部SELECT的列:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Unknown' AS name, 'Unknown' AS address, '022' AS tele) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+---------+-----------+------+
| id | name    | address   | tele |
+----+---------+-----------+------+
|  1 | Rupert  | Somewhere | 022  |
|  2 | John    | Doe       | 022  |
|  3 | Unknown | Unknown   | 022  |
+----+---------+-----------+------+

insert into customer_keyskill(customerID, keySkillID)
select  2,1 from dual
where not exists ( 
    select  customerID  from customer_keyskill 
    where customerID = 2 
    and keySkillID = 1 )

为了克服类似的问题，我修改了表，使其具有唯一的列。用你的例子，在创建时，我会有这样的东西:

name VARCHAR(20),
UNIQUE (name)

然后在插入时使用以下查询:

INSERT IGNORE INTO train
set table_listnames='Rupert'

这不是答案，只是个便条。如果插入的值是重复的，则像接受的答案中那样的查询将不起作用，就像下面这样:

INSERT INTO `addr` (`email`, `name`) 
  SELECT * FROM (SELECT 'user@domain.tld', 'user@domain.tld') AS tmp 
  WHERE NOT EXISTS (
    SELECT `email` FROM `addr` WHERE `email` LIKE 'user@domain.tld'
  );

Error
SQL query: Copy Documentation

MySQL said: Documentation

#1060 - Duplicate column name 'user@domain.tld'

相反，Mahbub Tito回答的问题很好:

INSERT INTO `addr` (`email`, `name`) 
  SELECT 'user@domain.tld', 'user@domain.tld' 
  WHERE NOT EXISTS (
    SELECT `email` FROM `addr` WHERE `email` LIKE 'user@domain.tld'
  );

1 row inserted.

在MariaDB中测试