代码之家 › 专栏 › 技术社区 › Adam Franco

在MySQL中盲目使用INSERT是否有缺点?

mysql sql

Adam Franco · 技术社区 · 18 年前

1.盲插入,收到重复密钥错误时更新:

// Try to insert as a new value
INSERT INTO my_prefs 
(user_id, pref_key, pref_value)
VALUES (1234, 'show_help', 'true');

// If a duplicate-key error occurs run an update query
UPDATE my_prefs 
SET pref_value = 'true'
WHERE user_id=1234 AND pref_key='show_help';

2.检查是否存在,然后选择或更新:

// Check for existence
SELECT COUNT(*) 
FROM my_prefs
WHERE user_id=1234 AND pref_key='show_help';

// If count is zero, insert
INSERT INTO my_prefs 
(user_id, pref_key, pref_value) 
VALUES (1234, 'show_help', 'true');

// If count is one, update
UPDATE my_prefs 
SET pref_value = 'true' 
WHERE user_id=1234 AND pref_key='show_help';

第一种方法似乎更可取,因为它只需要一个查询来进行新插入,两个查询来进行更新,而第二种方法总是需要两个查询。是否有什么我遗漏的东西会使盲目插入成为一个坏主意?

9 回复 | 直到 18 年前

Ken 18 年前

请查看中的ON DUPLICATE KEY语法 http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Vinko Vrsalovic 18 年前

还有第三种MySQL方式,这是RDBMS中的首选方式

INSERT INTO my_prefs 
(user_id, pref_key, pref_value) 
VALUES (1234, 'show_help', 'true')
ON DUPLICATE KEY 
UPDATE pref_value = 'true'

sabiland 18 年前

就我个人而言,我从来不喜欢基于异常的编程(期望在应用程序的正常操作中出现异常),对我来说,第二个示例更具可读性/可维护性。

Georgi 18 年前

如果您希望通过插入Doublete来避免“异常”,并且希望使用标准SQL(并且您的编程语言/数据库返回更新行的计数),那么请使用以下“SQL”-命令(伪代码):

int i = SQL("UPDATE my_prefs ...");
if(i==0) {
    SQL("INSERT INTO my_prefs ...");
}

Scott Lamb 18 年前

如果您不关心到非MySQL数据库的可移植性,“ON replicate”听起来不错(行为正是您想要的)。

如果从不删除行,“盲插入”似乎是合理和健壮的。(如果插入案例失败是因为该行存在,那么之后的更新应该会成功,因为该行仍然存在。但是如果删除了行,则此假设为错误-您需要重试逻辑。)在没有“On DUPLICATE”的其他数据库上,如果发现延迟是坏的,可以考虑优化:通过将此逻辑放入存储过程中,可以避免已经存在的情况下的数据库往返。

如果存在并发插入,“检查是否存在”很难正确执行。可以在SELECT和更新之间添加行。事务甚至没有真正的帮助——我认为即使在隔离级别“serializable”,您也会偶尔看到“由于并发更新而无法序列化访问”错误(或者类似于MySQL的错误消息)。您需要重试逻辑,因此我认为上面建议使用此方法避免“基于异常的编程”的人是错误的,因为出于同样的原因,建议首先进行更新的人也是错误的。

Paul Kroll 18 年前

您可以使用REPLACE,或者如果使用更新的MySQL,您可以选择使用“ INSERT ... ON DUPLICATE KEY UPDATE "

J.J. 18 年前

据我所知,第一种方式是首选方式。

JeeBee 18 年前

在DAO模型中,可以有一个id字段。

如果设置为null/-1/或其他值,则数据未被持久化。
当您持久化它(或从数据库中检索)时,将其设置为数据库中的id值。
缺陷:与数据库不同步,等等。我肯定还有更多,但我真的应该做一些工作。。。

Sabyasachi Mishra 11 年前

只要你使用 MySQL ON DUPLICATE 关键词。例如:

INSERT INTO my_prefs (user_id, pref_key, pref_value) VALUES (1234, 'show_help', 'true') 
ON DUPLICATE KEY UPDATE (pref_key, pref_value) VALUES ('show_help', 'true');