MySQL：如果表中不存在则插入记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3164505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Insert record if not exists in table
提问by Rupert
I am trying to execute the following query:
我正在尝试执行以下查询：
INSERT INTO table_listnames (name, address, tele)
VALUES ('Rupert', 'Somewhere', '022')
WHERE NOT EXISTS (
SELECT name FROM table_listnames WHERE name='value'
);
But this returns an error. Basically I don't want to insert a record if the 'name' field of the record already exists in another record - how to check if the new name is unique?
但这会返回错误。基本上，如果记录的“名称”字段已存在于另一条记录中，我不想插入记录-如何检查新名称是否唯一？
回答by Mike
I'm not actually suggesting that you do this, as the UNIQUEindex as suggested by Piskvor and others is a far better way to do it, but you can actually do what you were attempting:
我实际上并不是建议您这样做，因为UNIQUEPiskvor 和其他人建议的索引是一种更好的方法，但您实际上可以执行您尝试的操作：
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 a record:
插入一条记录：
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 |
+----+--------+-----------+------+
Try to insert the same record again:
尝试再次插入相同的记录：
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 a different record:
插入不同的记录：
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 |
+----+--------+-----------+------+
And so on...
等等...
Update:
更新：
To prevent #1060 - Duplicate column nameerror in case two values may equal, you must name the columns of the inner SELECT:
为了防止#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 |
+----+---------+-----------+------+
回答by Piskvor left the building
INSERT doesn't allow WHEREin the syntax.
What you can do: create a UNIQUE INDEXon the field which should be unique (name), then use either:
您可以做什么：UNIQUE INDEX在字段上创建一个应该是唯一的 ( name)，然后使用：
- normal
INSERT(and handle the error if the name already exists) INSERT IGNORE(which willfail silentlycause a warning (instead of error) if name already exists)INSERT ... ON DUPLICATE KEY UPDATE(which will execute theUPDATEat the end if name already exists, see documentation)
- 正常
INSERT（如果名称已经存在则处理错误） INSERT IGNORE（如果 name 已经存在，它将以静默方式失败导致警告（而不是错误））INSERT ... ON DUPLICATE KEY UPDATE（UPDATE如果名称已存在，将在最后执行，请参阅文档）
回答by Mahbub Tito
Worked :
工作过：
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');
回答by sdesvergez
MySQL provides a very cute solution :
MySQL 提供了一个非常可爱的解决方案：
REPLACE INTO `table` VALUES (5, 'John', 'Doe', SHA1('password'));
Very easy to use since you have declared a unique primary key (here with value 5).
非常容易使用，因为您已经声明了一个唯一的主键（此处值为 5）。
回答by Montaser El-sawy
INSERT IGNORE INTO `mytable`
SET `field0` = '2',
`field1` = 12345,
`field2` = 12678;
Here the mysql query, that insert records if not exist and will ignore existing similar records.
这里是 mysql 查询，如果不存在则插入记录并将忽略现有的类似记录。
----Untested----
回答by Arash Hatami
You can easily use the following way :
您可以轻松使用以下方式：
INSERT INTO ... ON DUPLICATE KEY UPDATE ...
By this way you can insert any newraw and if you have duplicate data, replace specific column ( best columns is timestamps ).
For example :
通过这种方式，您可以插入任何新的原始数据，如果您有重复的数据，请替换特定的列（最好的列是 timestamps ）。
例如 ：
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';
回答by Brian Hooper
If you really can't get a unique index on the table, you could try...
如果您确实无法在表上获得唯一索引，则可以尝试...
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;
回答by obsessiveCookie
To overcome similar problem, I have made the table I am inserting to have a unique column. Using your example, on creation I would have something like:
为了克服类似的问题，我让我插入的表有一个唯一的列。使用你的例子，在创作时我会有类似的东西：
name VARCHAR(20),
UNIQUE (name)
and then use the following query when inserting into it:
然后在插入时使用以下查询：
INSERT IGNORE INTO train
set table_listnames='Rupert'
回答by obsessiveCookie
This query works well:
此查询运行良好：
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
And you can create the table using following query:
您可以使用以下查询创建表：
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 ;
Note: Create table using second query before trying to use first query.
注意：在尝试使用第一个查询之前使用第二个查询创建表。
回答by Oms G
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 )
