php 有没有办法在 Zend Framework 1.5 中执行“INSERT...ON DUPLICATE KEY UPDATE”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/302544/
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
Is there a way to do an "INSERT...ON DUPLICATE KEY UPDATE" in Zend Framework 1.5?
提问by danielrsmith
I would like to use ON DUPLICATE KEY UPDATEin Zend Framework 1.5, is this possible?
我想ON DUPLICATE KEY UPDATE在 Zend Framework 1.5 中使用，这可能吗？
Example
例子
INSERT INTO sometable (...)
VALUES (...)
ON DUPLICATE KEY UPDATE ...
回答by Bill Karwin
I worked for Zend and specifically worked on Zend_Db quite a bit.
我为 Zend 工作过，专门为 Zend_Db 工作过不少。
No, there is no API support for the ON DUPLICATE KEY UPDATEsyntax. For this case, you must simply use query()and form the complete SQL statement yourself.
不，没有对ON DUPLICATE KEY UPDATE语法的API 支持。对于这种情况，您必须query()自己使用并形成完整的 SQL 语句。
I do not recommend interpolating values into the SQL as harvejs shows. Use query parameters.
我不建议如 harvejs 所示将值插入到 SQL 中。使用查询参数。
Edit: You can avoid repeating the parameters by using VALUES()expressions.
编辑：您可以通过使用VALUES()表达式来避免重复参数。
$sql = "INSERT INTO sometable (id, col2, col3) VALUES (:id, :col2, :col3)
ON DUPLICATE KEY UPDATE col2 = VALUES(col2), col3 = VALUES(col3)";
$values = array("id"=>1, "col2"=>327, "col3"=>"active");
回答by danielrsmith
As a sidebar, you can simplify the ON DUPLICATE KEY UPDATEclause and reduce the amount of processing your script needs to do by using VALUES():
作为侧边栏，您可以使用以下命令简化ON DUPLICATE KEY UPDATE子句并减少脚本所需的处理量VALUES()：
$sql = 'INSERT INTO ... ON DUPLICATE KEY UPDATE id = VALUES(id), col2 = VALUES(col2), col3 = VALUES(col3)';
See http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.htmlfor more information.
有关更多信息，请参阅http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html。
回答by Sergei Morozov
@Bill Karwin: great solutions! But it would be greater if to use named placeholders (":id", ":col1", …) instead of questions signs. Than you wouldn'n need to duplicate values by array_marge. Also if to use "SET" syntax of "INSERT" instead of "VALUES", the code gets simplier to be generated automatically for any set of fields.
@Bill Karwin：很好的解决方案！但是如果使用命名占位符（“:id”、“:col1”、...）而不是问号会更好。比你不需要通过 array_marge 复制值。此外，如果使用“INSERT”的“SET”语法而不是“VALUES”，则代码会更简单地为任何字段集自动生成。
$sql = 'INSERT INTO sometable SET id = :id, col2 = :col2, col3 = :col3
ON DUPLICATE KEY UPDATE id = :id, col2 = :col2, col3 = :col3';
回答by Pawel
$arrayData = array('column1' => value1, 'column2' => value2, ...)
class Model_Db_Abstract extends Zend_Db_Table_Abstract
{
protected $_name;
protected $_primaryKey;
public function insertOrUpdate($arrayData)
{
$query = 'INSERT INTO `'. $this->_name.'` ('.implode(',',array_keys($arrayData)).') VALUES ('.implode(',',array_fill(1, count($arrayData), '?')).') ON DUPLICATE KEY UPDATE '.implode(' = ?,',array_keys($arrayData)).' = ?';
return $this->getAdapter()->query($query,array_merge(array_values($arrayData),array_values($arrayData)));
}
}
USAGE:
用法：
eg. Model_Db_Contractors.php
例如。Model_Db_Contractors.php
class Model_Db_Contractors extends Model_Db_Abstract
{
protected $_name = 'contractors';
protected $_primaryKey = 'contractor_id';
...
}
IndexController.php
索引控制器.php
class IndexController extends Zend_Controller_Action
{
public function saveAction()
{
$contractorModel = new Model_Db_Contractors();
$aPost = $this->getRequest()->getPost();
/* some filtering, checking, etc */
$contractorModel->insertOrUpdate($aPost);
}
}
回答by Rajdeep Rath
Use this instead:
改用这个：
REPLACE INTO sometable SET field ='value'.....
This will update if exists or just insert if not. This is a part of the standard mysql api.
如果存在，这将更新，否则将插入。这是标准 mysql api 的一部分。
回答by Vinod Sai
Update to Pawel's Answer to support separate insert and update data, and also supports Zend Db Expressions
更新 Pawel's Answer 以支持单独的插入和更新数据，并且还支持 Zend Db 表达式
class Model_Db_Abstract extends Zend_Db_Table_Abstract
{
protected $_name;
protected $_primaryKey;
public function insertOrUpdate($arrayData)
{
$insertDataValuesForQuery = [];
$queryParams = [];
foreach ($insertData as $key => $value) {
if (gettype($value) == "object") {
array_push($insertDataValuesForQuery, $value->__toString());
continue;
}
array_push($insertDataValuesForQuery, "?");
array_push($queryParams, $value);
}
$updateDataValuesForQuery = [];
foreach ($updateData as $key => $value) {
if (gettype($value) == "object") {
array_push($updateDataValuesForQuery, $key . " = " . $value->__toString());
continue;
}
array_push($updateDataValuesForQuery, $key . " = ?");
array_push($queryParams, $value);
}
$query = 'INSERT INTO ' . $this->_name . ' (' . implode(',', array_keys($insertData)) . ') VALUES (' . implode(',', $insertDataValuesForQuery) . ') ON DUPLICATE KEY UPDATE ' . implode(' , ', $updateDataValuesForQuery);
return $this->getAdapter()->query($query, $queryParams);
}
}
回答by Peter
you can simply do something like this:
你可以简单地做这样的事情：
set unique index on your id
在您的 id 上设置唯一索引
and then
进而
try {
do insert here
} catch (Exception $e) {
do update here
}
