我想在DB中没有记录的情况下执行INSERT

Attempted translation of the above question from non-native English to English:

尝试将上述问题从非母语英语翻译成英语:

This is a question about the fastest method to use when inserting a value into a database when the value may already be in the database. INSERT will fail when the value is there and UPDATE will fail when it is not. Which of these choices is the most desirable?

这是一个关于在值可能已经存在于数据库中时将值插入数据库时使用的最快方法的问题。当值存在时,INSERT将失败,而当值不存在时,UPDATE将失败。哪些选择最合适?

Do SELECT first and choose INSERT or UPDATE.

首先执行SELECT并选择INSERT或UPDATE。

INSERT first and use UPDATE if it had a duplicate error.

首先插入并在出现重复错误时使用UPDATE。

If there are other good choices besides the above, please teach me.

除了以上还有其他好的选择,请教我。

The environment uses MySQL 4.1.

环境使用MySQL 4.1。

5 个解决方案

#1

If you want to do this in a single statement (sounds like what you want), I would recommend using the INSERT ... ON DUPLICATE KEY UPDATE syntax, as follows:

如果你想在一个语句中执行此操作(听起来像你想要的那样),我建议使用INSERT ... ON DUPLICATE KEY UPDATE语法,如下所示:

INSERT INTO table (id, someothervalue) VALUES (1, 'hi mom')
  ON DUPLICATE KEY UPDATE someothervalue = 'hi mom';

The initial INSERT statement will execute if there is no existing record with the specified key value (either primary key or unique). If a record already exists, the following UPDATE statement (someothervalue = 3) is executed.

如果没有具有指定键值(主键或唯一键)的现有记录,则将执行初始INSERT语句。如果记录已存在,则执行以下UPDATE语句(someothervalue = 3)。

This is supported in all versions of MySQL. For more info, see the MySQL Reference Manual page for INSERT ... ON DUPLICATE KEY UPDATE

所有版本的MySQL都支持此功能。有关更多信息,请参阅“MySQL参考手册”页面以了解INSERT ... ON DUPLICATE KEY UPDATE

#2

I would build a test database and a script to benchmark the choices.

我会构建一个测试数据库和一个脚本来对选择进行基准测试。

Just guessing at it, I believe doing the INSERT first and retrying with UPDATE is the faster way on average. This is because if the data is not already in the database then you saved another query. If your data is more often in the database then do an UPDATE first and retry with INSERT if it fails.

只是猜测它,我相信首先执行INSERT并且使用UPDATE重试是平均更快的方式。这是因为如果数据不在数据库中,那么您保存了另一个查询。如果数据更常出现在数据库中,则首先执行UPDATE,如果失败则重试INSERT。

However, an error may cause a transaction to abort and need rollback. If that is bad for you then you will need to do a SELECT first.

但是,错误可能导致事务中止并需要回滚。如果这对你不好,那么你需要先做一个SELECT。

I do not know about MySQL but I know that where I work we use a stored procedure on our PostgreSQL databases to do this. The stored procedure does a SELECT and then an INSERT or UPDATE.

我不知道MySQL,但我知道在我工作的地方,我们在PostgreSQL数据库上使用存储过程来执行此操作。存储过程执行SELECT,然后执行INSERT或UPDATE。

#3

In PHP, I would do a UPDATE first, then use mysql_affected_rows() to check if the row is updated. If not, then I would do an INSERT. Take note that the row must be changed for mysql_affected_rows() to return a result greater than 1.

在PHP中,我会首先执行UPDATE,然后使用mysql_affected_rows()来检查行是否已更新。如果没有,那么我会做一个INSERT。请注意,必须更改mysql_affected_rows()的行才能返回大于1的结果。

#4

Update: before I get a bunch more downvotes... it should be noted this was the first reply, before the question was edited and understood.)

更新:在我得到一些更多的downvotes之前...应该注意这是第一个回复,在问题被编辑和理解之前。)

In all honesty it sounds like you want to know what to do if the record already exists? or is new?

老实说,如果记录已经存在,你似乎想知道该怎么办?还是新的?

Therefore what you want to do (in pseudo code)

因此你想做什么(伪代码)

$count = SELECT COUNT FROM TABLE WHERE CONDITION;

if($count == 1){
  UPDATE...
} else {
  INSERT...
}

#5

Or you can use IF EXISTS syntax to check if there are rows.

或者您可以使用IF EXISTS语法来检查是否有行。

#1