MySQL:如果表中不存在，请插入记录

我正在尝试执行以下查询:

INSERT INTO table_listnames (name, address, tele)
VALUES ('Rupert', 'Somewhere', '022')
WHERE NOT EXISTS (
SELECT name FROM table_listnames WHERE name='value'
);

但是这会返回一个错误。基本上我不想插入一个记录，如果记录的“名称”字段已经存在于另一个记录-如何检查新名称是否唯一?

904098 次浏览
小开

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

小开

INSERT语法中不允许WHERE。

你可以做的是:在字段上创建一个UNIQUE INDEX，它应该是唯一的(name)，然后使用:

  • normal INSERT(如果名称已经存在，则处理错误)
  • INSERT IGNORE(如果name已经存在，静默失败将导致警告(而不是错误))
  • INSERT ... ON DUPLICATE KEY UPDATE(如果name已经存在，它将在结尾执行UPDATE， 看文档)
小开
最佳答案

我实际上并不是建议你这样做，因为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 - Duplicate column name错误，必须为内部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;
小开

Brian Hooper: 你差不多说到点子上了，但是你的语法有个错误。你的插入永远不会起作用。我测试了我的数据库，这是正确的答案:

INSERT INTO podatki (datum,ura,meritev1,meritev1_sunki,impulzi1,meritev2,meritev2_sunki,impulzi2)
SELECT '$datum', '$ura', '$meritve1','$sunki1','$impulzi1','$meritve2','$sunki2','$impulzi2'
FROM dual
WHERE NOT EXISTS (SELECT datum,ura
FROM podatki
WHERE datum='$datum' and ura='$ura'

我给你们的是y表的例子。插入几乎和Bian Hooper写的一样，除了我把select FROM DUAL ont从其他表中放出来。

小开 
INSERT IGNORE INTO `mytable`
SET `field0` = '2',
`field1` = 12345,
`field2` = 12678;

这里的mysql查询，即插入记录，如果不存在，将忽略现有的类似记录。

----Untested----
小开

有效:

INSERT INTO users (full_name, login, password)
SELECT 'Mahbub Tito','tito',SHA1('12345') FROM DUAL
WHERE NOT EXISTS
(SELECT login FROM users WHERE login='tito');
小开

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

name VARCHAR(20),
UNIQUE (name)

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

INSERT IGNORE INTO train
set table_listnames='Rupert'
小开 
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 )
小开

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

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

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

小开

我遇到了一个问题，迈克建议的方法部分有效，我有一个错误复制列名= '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 `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 ;

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

小开

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

< p >注意: 我必须在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;
< p > MySQL: 创建表__abc0 ( OID int(11) NOT NULL AUTO_INCREMENT， name varchar(32) COLLATE utf8_unicode_ci NOT NULL， 主键(__abc0)， 唯一键__abc0 (__abc1) (InnoDB AUTO_INCREMENT=1;

.

或

< p >该软件: 创建表[names] ( [oid] int identity (1,1) not null， [name] NVARCHAR (32) NOT NULL， 主键集群化([oid] asc) ）; CREATE UNIQUE NONCLUSTERED INDEX [Index_Names_Name] ON [names]([name] ASC)
小开

你可以简单地使用以下方法:

INSERT INTO ... ON DUPLICATE KEY UPDATE ...
这样，你可以插入任何新原始数据，如果你有重复的数据，替换一个特定的列(最好的列是时间戳)。
例如:

CREATE TABLE IF NOT EXISTS Devices (
id         INT(6)       NOT NULL AUTO_INCREMENT,
unique_id  VARCHAR(100) NOT NULL PRIMARY KEY,
created_at VARCHAR(100) NOT NULL,
UNIQUE KEY unique_id (unique_id),
UNIQUE KEY id (id)
)
CHARACTER SET utf8
COLLATE utf8_unicode_ci;


INSERT INTO Devices(unique_id, time)
VALUES('$device_id', '$current_time')
ON DUPLICATE KEY UPDATE time = '$current_time';
小开

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

小开

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

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


提交答案