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

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

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

但这会返回错误.基本上我不想插入记录,如果记录的'name'字段已存在于另一条记录中 - 如何检查新名称是否唯一？

1> Mike..：

---

我实际上并没有建议你这样做,因为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 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  |
+----+--------+-----------+------+

等等...

---

@cburgmer我遇到了同样的问题`#1060 - 重复的列名`.你找到了解决方案吗？编辑:找到它.在每个值后面添加一个AS:`SELECT*FROM(SELECT'Rupert'作为名称,'Rupert'作为朋友,'022'作为数字)AS tmp`

---

谢谢你的帮助.我的实际问题要复杂得多,而且列不能是唯一的,我不能依赖主键.但这正是我所寻找的.

---

如果你想在不同的字段中插入两次相同的值(例如内部选择中的'SELECT'Humbert','Humbert','1'`),这似乎不起作用.我收到一个`ERROR 1060(42S21):重复的列名`

---

@VijayRamamurthy:这是有效的,因为您插入了Select语句的结果.仔细阅读查询 - >`WHERE`语句属于`SELECT`查询.Select查询返回单个数据行(插入数据)或根本不返回任何数据(未插入任何内容)

---

@Piskovar:同意.@Rupert:如果可能的话,你应该索引内部select语句中引用的列(在本例中为`name`).另请注意,您可以执行`SELECT'Dohn','Doe','022'FROM table_listnames`,而不是'SELECT*FROM(SELECT'John','Doe','022')AS tmp` - 但这样做只有`table_listnames`已包含一行或多行才能工作.我怀疑速度是否有所不同,所以它可能不是一个问题.

2> Piskvor cc-b..：

---

INSERT WHERE在语法中不允许.

你可以做什么:UNIQUE INDEX在字段上创建一个唯一的(name),然后使用:

正常INSERT(如果名称已存在,则处理错误)

INSERT IGNORE(如果名称已经存在,它将无声地失败导致警告(而不是错误))

INSERT ... ON DUPLICATE KEY UPDATE(UPDATE如果名称已存在,将在最后执行,请参阅文档)

---

ime，在“ INSERT IGNORE”的情况下，警告消息与键“。+” $`的正则表达式“ ^ Duplicate entry'。+”匹配。

3> Mahbub Tito..：

---

工作:

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');

---

我从MySQL学到了"双重",但在MySQL的开源分支MariaDB中却没有

---

来自@ HighlyIrregular的维基百科链接"如果不需要表,其他几个数据库(包括Microsoft SQL Server,MySQL,PostgreSQL,SQLite和Teradata)可以完全省略FROM子句.这样就不需要任何虚拟表." 在没有FROM DUAL的情况下,相同的查询似乎在MySql(5.7,如果这很重要)中有效.

4> 小智..：

---

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

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

由于您已声明了唯一的主键(此处值为5),因此非常易于使用.

---

但它会更新记录(查看受影响的行)

5> Montaser El-..：

---

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

这里是mysql查询,即插入记录(如果不存在)并将忽略现有的类似记录.

----Untested----

---

看起来像插入和更新语法的混合.你的意思是``INSERT IGNORE INTO`mytable`(`field0`,`field1`,`field2`)值('2',12345,12678);``？

---

它对我没有用,INSERT IGNORE INTO emAssignedTagsForEvent SET eventId ='2',defaultTagId ='1';

6> Arash Hatami..：

---

您可以轻松使用以下方式:

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';

7> Brian Hooper..：

---

如果你真的无法在桌面上获得唯一索引,你可以尝试......

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;

8> obsessiveCoo..：

---

为了克服类似的问题,我已经使我插入的表具有唯一的列.使用你的例子,在创作时我会有类似的东西:

name VARCHAR(20),
UNIQUE (name)

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

INSERT IGNORE INTO train
set table_listnames='Rupert'

9> 小智..：

---

此查询效果很好:

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 ;

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