有没有办法执行“INSERT...ON DUPLICATE KEY UPDATE”操作? 在 Zend Framework 1.5 中?

发布于 2024-07-09 14:12:39 字数 184 浏览 12 评论 0原文

我想在 Zend Framework 1.5 中使用 ON DUPLICATE KEY UPDATE,这可能吗?

例子

INSERT INTO sometable (...)
VALUES (...)
ON DUPLICATE KEY UPDATE ...

I would like to use ON DUPLICATE KEY UPDATE in Zend Framework 1.5, is this possible?

Example

INSERT INTO sometable (...)
VALUES (...)
ON DUPLICATE KEY UPDATE ...

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

童话里做英雄 2024-07-16 14:12:39

我在 Zend 工作,特别是在 Zend_Db 上工作了很多。

否,ON DUPLICATE KEY UPDATE 语法不支持 API。 对于这种情况,您必须简单地使用 query() 并自行形成完整的 SQL 语句。

我不建议将值插入到 SQL 中,如 harvejs 所示。 使用查询参数。

编辑:您可以使用 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");

I worked for Zend and specifically worked on Zend_Db quite a bit.

No, there is no API support for the ON DUPLICATE KEY UPDATE syntax. For this case, you must simply use query() and form the complete SQL statement yourself.

I do not recommend interpolating values into the SQL as harvejs shows. Use query parameters.

Edit: You can avoid repeating the parameters by using VALUES() expressions.

$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");
眼泪都笑了 2024-07-16 14:12:39

作为侧边栏,您可以使用 VALUES() 简化 ON DUPLICATE KEY UPDATE 子句并减少脚本需要执行的处理量:

$sql = 'INSERT INTO ... ON DUPLICATE KEY UPDATE id = VALUES(id), col2 = VALUES(col2), col3 = VALUES(col3)';

请参阅 http://dev.mysql.com/doc/refman/5.1/en /insert-on-duplicate.html 了解更多信息。

As a sidebar, you can simplify the ON DUPLICATE KEY UPDATE clause and reduce the amount of processing your script needs to do by using 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.html for more information.

治碍 2024-07-16 14:12:39

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

@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.

$sql = 'INSERT INTO sometable SET id = :id, col2 = :col2, col3 = :col3
    ON DUPLICATE KEY UPDATE id = :id, col2 = :col2, col3 = :col3';
ま昔日黯然 2024-07-16 14:12:39
$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)));
    }

}

用法:

例如。 Model_Db_Contractors.php

class Model_Db_Contractors extends Model_Db_Abstract 
{

    protected $_name = 'contractors';
    protected $_primaryKey = 'contractor_id';

    ...
}

IndexController.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);
 }
}
$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

class Model_Db_Contractors extends Model_Db_Abstract 
{

    protected $_name = 'contractors';
    protected $_primaryKey = 'contractor_id';

    ...
}

IndexController.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);
 }
}
若有似无的小暗淡 2024-07-16 14:12:39

改用它:

REPLACE INTO sometable SET field ='value'.....

如果存在则更新,如果不存在则插入。 这是标准 mysql api 的一部分。

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.

早茶月光 2024-07-16 14:12:39

更新 Pawel 的答案以支持单独插入和更新数据,并且还支持 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);
     }
}

Update to Pawel's Answer to support separate insert and update data, and also supports Zend Db Expressions

  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);
     }
}
故乡的云 2024-07-16 14:12:39

你可以简单地做这样的事情:

在你的id上设置唯一索引

,然后

try {
   do insert here
} catch (Exception $e) {
   do update here
}

you can simply do something like this:

set unique index on your id

and then

try {
   do insert here
} catch (Exception $e) {
   do update here
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文