MySQL:如果表中不存在,则插入记录
我正在尝试执行以下查询:
INSERT INTO table_listnames (name, address, tele)VALUES ('Rupert', 'Somewhere', '022')
WHERE NOT EXISTS (
SELECT name FROM table_listnames WHERE name='value'
);
但这会返回错误。基本上,如果该记录的“名称”字段已经存在于另一条记录中,我不想插入一条记录-如何检查新名称是否唯一?
回答:
我实际上并不建议您这样做,因为UNIQUE
Piskvor和其他人建议的索引是一种更好的方法,但是您实际上可以做您想做的事情:
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 |
+----+---------+-----------+------+
以上是 MySQL:如果表中不存在,则插入记录 的全部内容, 来源链接: utcz.com/qa/413231.html