应用程序中的一个标准问题是,如果一条记录不存在,则插入一条记录,如果存在则更新一条记录。在PRIMARY KEY
未知的情况下,通常通过发出SELECT
,然后运行INSERT
或UPDATE
(如果找到记录)来解决此问题。
然而,据我所知,至少有三种方法可以将记录插入数据库,即使该记录已经存在。就我个人而言,如果一个新的插入请求已经存在,我宁愿删除它,但是在某些情况下,您可能宁愿删除数据库中的记录而使用新的。
CREATE TABLE IF NOT EXISTS `table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`foo` int(10) unsigned NOT NULL,
`bar` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `row` (`foo`,`bar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
以下是三种方法:
INSERT IGNORE INTO table (foo, bar) VALUES (2,3);
INSERT INTO table (foo, bar) VALUES (2,3) ON DUPLICATE KEY UPDATE;
REPLACE INTO table (foo, bar) VALUES (2,3);
这些方法应该在什么时候使用?
谁能举例说明正确的用法?
-
INSERT
应该用于当你只是想插入一个新的行让我们假设你正在存储日志条目,你想记录每一个事件,使用
INSERT
。
-
INSERT IGNORE
应该用于当你只是想有一个特定的键存在于表中,不管它是当前插入创建它,或者如果它已经存在。假设你有一个电话号码表和使用次数,你发现一个新的电话号码,你不确定它是否存在,但你希望它在表中。
你使用
INSERT IGNORE
来确保它在那里
-
REPLACE INTO
应该在您想要确保表中存在特定的键时使用,如果它存在,您希望使用新的值,而不是现有的值。您有另一个包含电话号码的表,这次您找到了一个新号码和一个要与之关联的名称。
您可以使用
REPLACE INTO
查找和更新完整的记录,或者只是插入新的信息。
-
INSERT INTO ... ON DUPLICATE KEY UPDATE ...
请注意,这是不是
REPLACE INTO
的另一种编写方法,当您想要确保特定键存在时,应该使用上述方法,但是如果它确实更新的一些列,而不是所有列。例如,如果您要存储来自某个IP的访问次数,以及用户访问过的第一个页面。
INSERT INTO visitors (ip,visits,first_page) VALUES (<ip>,1,<current_page>) ON DUPLICATE KEY visits = visits +1;
在你的问题中,你有
INSERT INTO `table` (foo, bar) VALUES (2,3) ON DUPLICATE KEY UPDATE;
只有当行索引为UNIQUE时才有效:
CREATE TABLE IF NOT EXISTS `table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`foo` int(10) unsigned NOT NULL,
`bar` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `row` (`foo`,`bar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
否则,为什么只有索引呢?
ON DUPLICATE KEY
子句还允许您更新非索引列。
对于REPLACE
,请记住REPLACE
实际上是DELETE
和INSERT
。