插入新的数据库条目是否比先检查该条目是否存在更快？
有人告诉我，直接运行插入并让插入失败比检查数据库条目是否存在然后插入（如果缺少）要快。
我还被告知，大多数数据库都针对读取而不是写入进行了深度优化，所以快速检查不是比慢速插入更快吗？
这是预期碰撞次数的问题吗？ （即，仅当条目已存在的可能性较低时，插入速度才会更快。）这是否取决于我正在运行的数据库类型？就此而言，拥有一种不断向我的错误日志添加插入错误的方法是不好的做法吗？
谢谢。
I was once told that it is faster to just run an insert and let the insert fail than to check if a database entry exists and then inserting if it is missing.
I was also told that that most databases are heavily optimized for reading reading rather than writing, so wouldn't a quick check be faster than a slow insert?
Is this a question of the expected number of collisions? (IE it's faster to insert only if there is a low chance of the entry already existing.) Does it depend on the database type I am running? And for that matter, is it bad practice to have a method that is going to be constantly adding insert errors to my error log?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果插入由于索引违规而失败，那么它最多会比检查记录是否存在稍微慢一些。 （两者都需要检查索引是否包含该值。）如果插入要成功，则发出两个查询比发出一个查询要慢得多。
If the insert is going to fail because of an index violation, it will be at most marginally slower than a check that the record exists. (Both require checking whether the index contains the value.) If the insert is going to succeed, then issuing two queries is significantly slower than issuing one.
您可以使用 INSERT IGNORE ，这样如果键已经存在，则插入命令将被忽略，否则将插入新行。这样，您需要发出一个查询，该查询会检查重复值并插入新值。
仍然要小心 INSERT IGNORE，因为它会将每个错误变成警告。阅读这篇文章以了解插入忽略
忽略重复键？
You can use
INSERT IGNOREso that if the key already exist, the insert command would just be ignored, else the new row will be inserted. This way you need to issue a single query, which checks the duplicate values as well inserts new values too.still Be careful with INSERT IGNORE as it turns EVERY error into a warning. Read this post for insert ignore
On duplicate key ignore?
我认为 INSERT IGNORE INTO .... 可以用在这里，要么插入，要么忽略。
如果使用 IGNORE 关键字，则执行 INSERT 语句时发生的错误将被视为警告。例如，如果没有 IGNORE，重复表中现有 UNIQUE 索引或 PRIMARY KEY 值的行会导致重复键错误，并且语句将中止。使用 IGNORE，该行仍然不会插入，但不会发出错误。
I think INSERT IGNORE INTO .... can be used here, either it will insert or ignore it.
If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.
如果要删除旧值并插入新值，可以使用 REPLACE 您可以使用 REPLACE 而不是 INSERT 来覆盖旧行。
否则使用 INSERT IGNORE 因为它会插入或忽略。
If you want to delete the old value and insert a new value you can use REPLACE You can use REPLACE instead of INSERT to overwrite old rows.
Else use the INSERT IGNORE as it will either inserts or ignores.
如果您的意图是插入新记录或更新记录（如果已存在）那么执行 UPSERT 怎么样？
查看 - http://vadivel。 blogspot.com/2011/09/upsert-insert-and-update-in-sql-server.html
我们可以尝试直接更新它，而不是检查记录是否存在。如果没有匹配的记录，那么@@RowCount将为0。基于此我们可以将其作为新记录插入。 [在 SQL Server 2008 中，您可以为此使用 MERGE 概念]
编辑：请注意，我知道这适用于 MS SQL Server，但我不知道 MySQL 或 ORACLE
If your intension is to Insert if its a new record OR Update the record if it already exists then how about doing an UPSERT?
Check out - http://vadivel.blogspot.com/2011/09/upsert-insert-and-update-in-sql-server.html
Instead of checking whether the record exists or not we can try to Update it directly. If there is no matching record then @@RowCount would be 0. Based on that we can Insert it as a new record. [In SQL Server 2008 you can use MERGE concept for this]
EDIT: Please note, I know this works for MS SQL Server and I don't know about MySQL or ORACLE