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

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

INSERT INTO table_listnames (name, address, tele)

VALUES ('Rupert', 'Somewhere', '022')

WHERE NOT EXISTS (

SELECT name FROM table_listnames WHERE name='value'

);

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

回答:

我实际上并不建议您这样做,因为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 |

+----+--------+-----------+------+

等等…


更新:

为了防止#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

回到顶部