这个查询可以在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字符串中配置的值不存在的情况下才会添加新项

我并不是建议你这样做，因为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 table_listnames (name, address, tele)
    SELECT 'Rupert', 'Somewhere', '022'
        FROM some_other_table
        WHERE NOT EXISTS (SELECT name
                              FROM table_listnames
                              WHERE name='Rupert')
        LIMIT 1;

这个查询很有效:

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 ;

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

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

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

MySQL提供了一个非常可爱的解决方案:

REPLACE INTO `table` VALUES (5, 'John', 'Doe', SHA1('password'));

非常容易使用，因为您已经声明了一个唯一的主键(这里的值为5)。