我遇到了一个问题，迈克建议的方法部分有效，我有一个错误复制列名= '0'，并更改了您的查询的语法如下'

     $tQ = "INSERT  INTO names (name_id, surname_id, sum, sum2, sum3,sum4,sum5) 
                SELECT '$name', '$surname', '$sum', '$sum2', '$sum3','$sum4','$sum5' 
FROM DUAL
                WHERE NOT EXISTS (
                SELECT sum FROM names WHERE name_id = '$name' 
AND surname_id = '$surname') LIMIT 1;";

问题出在列名上。sum3等于sum4, mysql抛出重复的列名，我用这种语法写了代码，它工作得很好，

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

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中测试

这个查询可以在PHP代码中使用。

我在这个表中有一个ID列，所以我需要检查除了这个ID列之外的所有列的复制:

#need to change values
SET @goodsType = 1, @sybType=5, @deviceId = asdf12345SDFasdf2345;    


INSERT INTO `devices` (`goodsTypeId`, `goodsId`, `deviceId`) #need to change tablename and columnsnames
SELECT * FROM (SELECT @goodsType, @sybType, @deviceId) AS tmp
WHERE NOT EXISTS (
    SELECT 'goodsTypeId' FROM `devices` #need to change tablename and columns names
    WHERE `goodsTypeId` = @goodsType
        AND `goodsId` = @sybType
        AND `deviceId` = @deviceId
) LIMIT 1;

现在只有在SET字符串中配置的值不存在的情况下才会添加新项

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

注意: 我必须在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 `user` ( `username` , `password` )
    SELECT * FROM (SELECT 'ersks', md5( 'Nepal' )) AS tmp
    WHERE NOT EXISTS (SELECT `username` FROM `user` WHERE `username` = 'ersks' 
    AND `password` = md5( 'Nepal' )) LIMIT 1

你可以使用下面的查询来创建表:

CREATE TABLE IF NOT EXISTS `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(30) NOT NULL,
    `password` varchar(32) NOT NULL,
    `status` tinyint(1) DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

注意:在尝试使用第一个查询之前，使用第二个查询创建表。