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

Often I want to add a value to a table or update the value if its key already exists. This can be accomplished in several ways, assuming a primary or unique key is set on the 'user_id' and 'pref_key' columns in the example:

通常我想在表中添加一个值,或者如果它的键已经存在则更新该值。这可以通过多种方式实现,假设在示例中的'user_id'和'pref_key'列上设置了主键或唯一键:

1. Blind insert, update if receiving a duplicate key error:

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, then select or update:

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

The first way seems to be preferable as it will require only one query for new inserts and two for an update, where as the second way will always require two queries. Is there anything I'm missing though that would make it a bad idea to blindly insert?

第一种方式似乎更可取,因为它只需要一个查询用于新插入,两个用于更新,其中第二种方式总是需要两个查询。有什么我想念的,虽然盲目插入会是一个坏主意吗?

9 个解决方案

#1

Will there be concurrent INSERTs to these rows? DELETEs?

这些行会有并发INSERT吗?删除?

"ON DUPLICATE" sounds great (the behavior is just what you want) provided that you're not concerned about portability to non-MySQL databases.

“ON DUPLICATE”听起来很棒(行为正是你想要的)只要你不关心非MySQL数据库的可移植性。

The "blind insert" seems reasonable and robust provided that rows are never deleted. (If the INSERT case fails because the row exists, the UPDATE afterward should succeed because the row still exists. But this assumption is false if rows are deleted - you'd need retry logic then.) On other databases without "ON DUPLICATE", you might consider an optimization if you find latency to be bad: you could avoid a database round trip in the already-exists case by putting this logic in a stored procedure.

如果永远不会删除行,那么“盲插”似乎是合理且可靠的。 (如果由于行存在而INSERT大小写失败,则后续的UPDATE应该成功,因为该行仍然存在。但是如果删除行,则假设为假 - 那么您需要重试逻辑。)在没有“ON DUPLICATE”的其他数据库上,如果发现延迟不好,可以考虑进行优化:通过将此逻辑放在存储过程中,可以避免已存在的情况下数据库往返。

The "check for existence" is tricky to get right if there are concurrent INSERTs. Rows could be added between your SELECT and your UPDATE. Transactions won't even really help - I think even at isolation level "serializable", you'll see "could not serialize access due to concurrent update" errors occasionally (or whatever the MySQL equivalent error message is). You'll need retry logic, so I'd say the person above who suggests using this method to avoid "exception-based programming" is wrong, as is the person who suggests doing the UPDATE first for the same reason.

如果存在并发INSERT,那么“检查存在”是非常棘手的。可以在SELECT和UPDATE之间添加行。事务甚至不会真正帮助 - 我认为即使在隔离级别“可序列化”,您也会看到“由于并发更新而无法序列化访问”错误(或者无论MySQL等效错误消息是什么)。你需要重试逻辑,所以我要说上面建议使用这种方法来避免“基于异常的编程”的人是错误的,因为建议首先出于同样的原因进行UPDATE的人也是错误的。

9 个解决方案

#1

更多相关文章

随机推荐