我正在尝试执行以下查询:
INSERT INTO table_listnames (name, address, tele)
VALUES ('Rupert', 'Somewhere', '022')
WHERE NOT EXISTS (
SELECT name FROM table_listnames WHERE name='value'
);
但是这会返回一个错误。基本上我不想插入一个记录，如果记录的“名称”字段已经存在于另一个记录-如何检查新名称是否唯一?
当前回答
MySQL提供了一个非常可爱的解决方案:
REPLACE INTO `table` VALUES (5, 'John', 'Doe', SHA1('password'));
非常容易使用，因为您已经声明了一个唯一的主键(这里的值为5)。
其他回答
这个查询可以在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字符串中配置的值不存在的情况下才会添加新项
为了克服类似的问题，我修改了表，使其具有唯一的列。用你的例子，在创建时，我会有这样的东西:
name VARCHAR(20),
UNIQUE (name)
然后在插入时使用以下查询:
INSERT IGNORE INTO train
set table_listnames='Rupert'
我并不是建议你这样做，因为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 |
+----+---------+-----------+------+
MySQL提供了一个非常可爱的解决方案:
REPLACE INTO `table` VALUES (5, 'John', 'Doe', SHA1('password'));
非常容易使用，因为您已经声明了一个唯一的主键(这里的值为5)。
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 )
