如何在ZF2中执行INSERT INTO SELECT查询

问题描述

在ZF2中执行INSERT INTO SELECT查询的最佳方式是什么？

What’s the best way to perform an INSERT INTO SELECT query in ZF2?

我需要在ZF2中开发一个从一个表中选择一个记录子集的函数，将这些记录插入另一个表。如果我在SQL中进行编程，则该语句将如下所示：

I need to develop a function in ZF2 that selects a subset of records from one table and inserts those records into another table. If I were programming in SQL, the statement would look like this:

INSERT INTO target (tgt_col1, tgt_col2)
SELECT 'flag' as marker, src_col2 FROM source
WHERE src_col1='mycriteria'

I搜索了Doctrine文档，找不到INSERT方法。我在Doctrine标签下发布了一个缺乏回应使我相信INSERT INTO SELECT对于Doctrine来说太复杂了。

I’ve searched the Doctrine docs and cannot find an INSERT method. I’ve posted a similar question under the Doctrine tag and the lack of response leads me to believe that INSERT INTO SELECT is too complex for Doctrine to handle.

在ZF2中，我似乎可以使用 Zend\Db\Sql\Sql 。但是，对于该解决方案， SELECT 和 INSERT 是两个单独的函数，它看起来像 INSERT 函数一次处理单个记录。因此，一个 Zend\Db\Sql\Sql 解决方案将需要1）一组语句从源表中选择数据，2）也许是一个函数将该数据对象转换为数组，以及3）一个 foreach 函数循环遍历数据数组，并通过4个一组插入语句将每个记录单独添加到目标表中。相比之下，SQL中可能出现的单一语句似乎比较麻烦。

In ZF2 it appears that I "could" useZend\Db\Sql\Sql. However, for that solution, SELECT and INSERT are two separate functions and it looks like the INSERT function only handles a single record at a time. Therefore, a Zend\Db\Sql\Sql solution would require 1) a group of statements to select the data from the source table, 2) perhaps a function to convert that data object to an array, and 3) a foreach function to cycle through the data array and individually add each record to the target table via 4) a group of insert statements. This seems cumbersome compared to the single statement that’s possible in SQL.

有更好的方法吗？

推荐答案

如果它的 TableGateway 那么它绝对适用于最新版本的ZF2，即2.3.2

If its TableGateway then it definitely works with the latest version of ZF2 i.e. 2.3.2

参考相册模块 -

1. 创建了一个重复的表的相册并命名为 album_old 。


2. 清空专辑表。

1. Created a duplicate table of album and named it album_old.
2. Emptied the album table.

所以现在相册表是空的， album_old 具有这些值。

So now album table is empty and album_old has the values.

要从 album_old中复制记录到相册，这样做 -

To copy the records from album_old to album, did this -

use Zend\Db\Sql\Select;
....
use Zend\Db\Sql\Insert;

$select = new Select('album_old');
$select->columns(array('artist', 'title', 'cover', 'extra'));

$insert = new Insert();
$insert->into('album');
$insert->columns(array('artist', 'title', 'cover', 'extra'));

$insert->values($select);
//OR
$insert->select($select);

$this->tableGateway->insertWith($insert);

所有来自 album_old 的值已插入相册表。

All the values from album_old were inserted into album table.

参考：

这里你会发现函数值（）可以采用数组或 instanceof选择

，并有一个新的 select（）功能。

Ref: https://github.com/zendframework/zf2/blob/master/library/Zend/Db/Sql/Insert.php
Here you will find that the function values() can take array or instanceof Select
and there is a new select() function too.

这篇关于如何在ZF2中执行INSERT INTO SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！