php Yii INSERT ... 重复更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9004269/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Yii INSERT ... ON DUPLICATE UPDATE
提问by Nathan H
I am working on a Yii project. How can I use the ON DUPLICATE feature of MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) when doing a save() on a Yii model?
我正在做一个 Yii 项目。在 Yii 模型上执行 save() 时，如何使用 MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html)的 ON DUPLICATE 功能？
My MySQL is as follows:
我的MySQL如下：
CREATE TABLE `ck_space_calendar_cache` (
`space_id` int(11) NOT NULL,
`day` date NOT NULL,
`available` tinyint(1) unsigned NOT NULL DEFAULT '0',
`price` decimal(12,2) DEFAULT NULL,
`offer` varchar(45) DEFAULT NULL,
`presale_date` date DEFAULT NULL,
`presale_price` decimal(12,2) DEFAULT NULL,
`value_x` int(11) DEFAULT NULL,
`value_y` int(11) DEFAULT NULL,
PRIMARY KEY (`space_id`,`day`),
KEY `space` (`space_id`),
CONSTRAINT `space` FOREIGN KEY (`space_id`) REFERENCES `ck_space` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
My PHP is a follows:
我的 PHP 如下：
$cache = new SpaceCalendarCache();
$cache->attributes = $day; //Some array with attributes
$cache->save();
If there is a duplicate in my primary key (sapce_id,day), I don't want it to complain, I just want it to update with the latest data.
如果我的主键 (sapce_id,day) 中有重复项，我不希望它抱怨，我只希望它使用最新数据进行更新。
I know how to do it in raw SQL, I was just wondering if there is a clean Yii way to do it.
我知道如何在原始 SQL 中做到这一点，我只是想知道是否有一种干净的 Yii 方法来做到这一点。
采纳答案by Nathan H
I overrode beforeValidate() where I checked if a duplicate exists. If one does, I set $this->setIsNewRecord(false);
我覆盖了 beforeValidate() ，在那里我检查了是否存在重复项。如果有，我设置$this->setIsNewRecord(false);
Seems to work. Not sure how performant it is.
似乎工作。不确定它的性能如何。
回答by Uday Sawant
You are using models in Yii, its quite simple .. try to load you model where you suspect to have duplicate entries, if you find the entry the model is loaded else null is return. now if your model is null simply create new model. rest is your normal code to insert a new record.
您在 Yii 中使用模型，它非常简单……尝试在您怀疑有重复条目的地方加载模型，如果您找到模型已加载的条目，否则返回 null。现在，如果您的模型为空，只需创建新模型。rest 是插入新记录的正常代码。
//try to load model with available id i.e. unique key
$model = someModel::model()->findByPk($id);
//now check if the model is null
if(!$model) $model = new someModel();
//Apply you new changes
$model->attributes = $attributes;
//save
$model->save();
Refer to post controllers update method in sample app Yii blog. I might be wrong with spelling of function names, sorry for that.
请参阅示例应用 Yii 博客中的 post 控制器更新方法。函数名称的拼写可能有误，抱歉。
回答by Kostas Filios
I'm repeating two main points from previous answers I think you should keep:
我从以前的答案中重复了两个要点，我认为您应该保留：
Don't (try to) use "on duplicate key update" since its MySQL-only, as txyoji points out.
Prefer the
select->ifnot found theninsert->elseinsert demonstrated by Uday Sawant.
正如 txyoji 指出的那样，不要（尝试）使用“重复密钥更新”，因为它仅限于 MySQL。
更喜欢Uday Sawant 演示的
select->ifnot found theninsert->elseinsert。
There's another point here, though: Concurrency. Although for low traffic applications the probability that you'll get in trouble is minimal (still never zero), I think we always be careful about this.
不过，这里还有一点：并发。尽管对于低流量应用程序，您遇到麻烦的可能性很小（仍然永远不会为零），但我认为我们始终要小心这一点。
From a transactional point of view, "INSERT .. ON DUPLICATE UPDATE"is not equivalent to selecting into your application's memory and then inserting or updating. The first is a single transaction, then second is not.
从事务的角度来看，"INSERT .. ON DUPLICATE UPDATE"并不等同于选择到应用程序的内存中然后插入或更新。第一个是单笔交易，第二个不是。
Here's a bad scenario:
这是一个糟糕的场景：
- You do select your record using
findByPk()which returns null- Some other transaction (from some other user request) inserts a record with the id you just failed to select
- At the next instant you try to insert it again
- 您确实选择了使用
findByPk()which 返回 null 的记录- 其他一些事务（来自其他一些用户请求）插入了一条记录，其中包含您刚刚选择失败的 ID
- 在下一刻，您尝试再次插入
In this case you'll either get an exception (if you're working with a unique key, as you do here) or a duplicate entry. Duplicate entries are much harder to pick up (usually nothing seems weird until your users see duplicate records).
在这种情况下，您将收到异常（如果您使用唯一键，就像您在此处所做的那样）或重复条目。重复条目更难找到（通常在您的用户看到重复记录之前，没有什么奇怪的）。
The solution here is to set a strict isolation level, for example "serializable", and then begin a transaction.
这里的解决方案是设置一个严格的隔离级别，例如“serializable”，然后开始一个事务。
Here's an example for yii:
下面是一个 yii 的例子：
Yii::app()->db->createCommand('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
$trn = Yii::app()->db->beginTransaction();
try {
// Try to load model with available id i.e. unique key
// Since we're in serializable isolation level, even if
// the record does not exist the RDBMS will lock this key
// so nobody can insert it until you commit.
// The same shold for the (most usual) case of findByAttributes()
$model = someModel::model()->findByAttributes(array(
'sapce_id' => $sapceId,
'day' => $day
));
//now check if the model is null
if (!$model) {
$model = new someModel();
}
//Apply you new changes
$model->attributes = $attributes;
//save
$model->save();
// Commit changes
$trn->commit();
} catch (Exception $e) {
// Rollback transaction
$trn->rollback();
echo $e->getMessage();
}
You can see more about isolation levels at least in the following links and see what every isolation level has to offer in data integrity in exchange for concurrency
您至少可以在以下链接中查看有关隔离级别的更多信息，并了解每个隔离级别在数据完整性方面必须提供什么以换取并发
http://technet.microsoft.com/en-us/library/ms173763.aspx
http://technet.microsoft.com/en-us/library/ms173763.aspx
回答by txyoji
The "On Duplicate Key Update" feature is specific to MySQL's dialect of SQL. Its unlikely to be implemented in any data abstraction layer. ZendDB and Propel don't have an equivalent.
“On Duplicate Key Update”功能特定于 MySQL 的 SQL 方言。它不太可能在任何数据抽象层中实现。ZendDB 和 Propel 没有等价物。
You can simulate the behavior by attempting an insert in a try/catch and update if insert fails with the proper error code. (duplicate key error).
您可以通过尝试在 try/catch 和 update 中尝试插入来模拟行为，如果插入失败并显示正确的错误代码。（重复键错误）。
回答by Dan Blows
I agree with @txyoji's analysis of the problem, but I would use a different solution.
我同意@txyoji 对问题的分析，但我会使用不同的解决方案。
You can extend the save()method of the model to look for an existing record, and update it, or insert a new row if it doesn't.
您可以扩展save()模型的方法以查找现有记录并更新它，如果没有，则插入新行。
回答by Sebastian Viereck
you have to use try catch like that:
你必须像这样使用 try catch：
try{
$model->save();
}
catch(CDbException $e){
$model->isNewRecord = false;
$model->save();
}
