blocks|key|2147800|text|有很多事情需要考虑，并且可能有更好的方法来完成你的任务，但是最直接的答案(基于你提供的信息)是扩展QueryBuilder。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2147801|简单地创建一个具有适当名称空间的应用程序组件，并如上面所示扩展QueryBuilder。|2147802|如果您在表中使用ActiveRecord模型，而不是直接执行sql命令，那么还有其他方法可以实现您想要的目标。|2147803|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

There are lots of things to consider and possibly better ways to accomplish your task but the most direct answer (based on the info you provided) would be to extend the QueryBuilder. 

Simple create an app component with an appropriate namespace and extend QueryBuilder as you have shown above.

If instead you use an ActiveRecord model with your table rather than doing sql commands directly there are other ways you can accomplish your desired goal.

blocks|key|1629460|text|一年多过去了，我在这里发现了一个悬而未决的问题。我看到这有一些牵引力，所以增加一个正确的答案是好的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1629461|在Yii2中，其思想是查找模型，检查是否存在，然后保存。该框架将自动更新或插入，下面是我从Viewlike.us+-+Yii2插入并忽略重复项获得的一个片段|offset|length|1629462|$model+=+YourModel::findOne(ID)；//+findOne()搜索表键，您可以使用find()->where('='，'column'，‘name’)->one()来标识模型。if(!+$model+)：$model=新的YourModel()；endif；$model->列_1=+'text'；$model->列_2=+123；//整数$YourModel->save()；|blockquote|1629463|entityMap|0|LINK|mutability|MUTABLE|url|https://www.viewlike.us/yii2-insert-ignore-duplicates/^0|0|19|Q|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@$D|S|E|T|1|U]]|A|$]]|$1|F|3|G|5|H|7|V|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

More than one year passed and I found this open question here. I see this has some traction, so it's good to add a proper answer.

In Yii2 the idea is to find the model, check if exists and then save. The framework will automatically update or insert, here's a snippet I got from <a href="https://www.viewlike.us/yii2-insert-ignore-duplicates/" rel="nofollow noreferrer">Viewlike.us - Yii2 Insert and Ignore duplicates</a>:

<blockquote>
<pre><code>$model = YourModel::findOne(ID); // findOne() searches for the table key, you can use find()-&gt;where('=', 'column', 'name')-&gt;one(); to identify the model.

if(!$model):
 $model = new YourModel();
endif;

$model-&gt;column_1 = 'text';
$model-&gt;column_2 = 123; // integer
$model-&gt;save();
</code></pre>
</blockquote>

blocks|key|1629465|text|您问题的关键是方法$db->createCommand()。在内部，它使用$commandClass公共属性，默认情况下是yii\db\Command。您可以在应用程序配置中更改它，以覆盖batchInsert()等命令方法。以下是我的应用程序的示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1629466|'db'+=>+[
++++'class'+=>+'yii\db\Connection',
++++'charset'+=>+'utf8',
++++'enableSchemaCache'+=>+YII_ENV_PROD,
++++'commandClass'+=>+'app\db\MyCommand'
],|code-block|syntax|javascript|1629467|您的命令类看起来可能是：|1629468|class+MyCommand+extends+yii\db\Command
{
++++public+function+batchInsert($table,+$columns,+$rows)
++++{
++++++++parent::batchInsert($table,+$columns,+$rows);
++++++++$sql+=+$this->getRawSql();
++++++++$sql+.=+'+ON+DUPLICATE+KEY+UPDATE';
++++++++$this->setRawSql($sql);

++++++++return+$this;
++++}
}|1629469|但我更愿意引入单独的方法，例如batchInsertUpdate()或其他方法。|1629470|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

The key to your question is method $db->createCommand(). Internally it uses $commandClass public property that is by default yii\db\Command. You can change it in your app config to override command methods like batchInsert() and others. Following is a sample from my app:

<pre><code>'db' =&gt; [
 'class' =&gt; 'yii\db\Connection',
 'charset' =&gt; 'utf8',
 'enableSchemaCache' =&gt; YII_ENV_PROD,
 'commandClass' =&gt; 'app\db\MyCommand'
],
</code></pre>

And your command class might look like:

<pre><code>class MyCommand extends yii\db\Command
{
 public function batchInsert($table, $columns, $rows)
 {
 parent::batchInsert($table, $columns, $rows);
 $sql = $this-&gt;getRawSql();
 $sql .= ' ON DUPLICATE KEY UPDATE';
 $this-&gt;setRawSql($sql);

 return $this;
 }
}
</code></pre>

But I would rather introduce separate method, e.g. batchInsertUpdate() or something.

blocks|key|2147897|text|从Yi2.0.14开始，就有一个upsert()方法可以用来解决这个问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2147898|$db->createCommand()
++++->upsert(
++++++++'sdg_fb_apps',
++++++++[
++++++++++++'fb_id'+=>+$app->id,
++++++++++++'page_id'+=>+$page_id,
++++++++++++'name'+=>+$app->name,
++++++++++++'app_id'+=>+$app_id,
++++++++++++'app_name'+=>+$app_name,
++++++++],
++++++++false
++++)
++++->execute();|code-block|syntax|javascript|2147899|entityMap|0|LINK|mutability|MUTABLE|url|https://www.yiiframework.com/doc/api/2.0/yii-db-command#upsert()-detail^0|G|8|G|8|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Since Yii 2.0.14 there is a <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-command#upsert()-detail" rel="nofollow noreferrer"><code>upsert()</code></a> method which may be used for this.

<pre><code>$db-&gt;createCommand()
 -&gt;upsert(
 'sdg_fb_apps',
 [
 'fb_id' =&gt; $app-&gt;id,
 'page_id' =&gt; $page_id,
 'name' =&gt; $app-&gt;name,
 'app_id' =&gt; $app_id,
 'app_name' =&gt; $app_name,
 ],
 false
 )
 -&gt;execute();
</code></pre>

blocks|key|333624|text|对于Yii+1+(Yii+2应该类似)，我继承了CActiveRecord，然后将插入函数复制到insertIgnoreDuplicates，在createInsertCommand调用之后进行了以下更改：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|333625|/**
+++++*+Inserts+ignore+duplicates+a+row+into+the+table+based+on+this+active+record+attributes.
+++++*+If+the+table's+primary+key+is+auto-incremental+and+is+null+before+insertion,
+++++*+it+will+be+populated+with+the+actual+value+after+insertion.
+++++*+Note,+validation+is+not+performed+in+this+method.+You+may+call+{@link+validate}+to+perform+the+validation.
+++++*+After+the+record+is+inserted+to+DB+successfully,+its+{@link+isNewRecord}+property+will+be+set+false,
+++++*+and+its+{@link+scenario}+property+will+be+set+to+be+'update'.
+++++*+@param+array+$attributes+list+of+attributes+that+need+to+be+saved.+Defaults+to+null,
+++++*+meaning+all+attributes+that+are+loaded+from+DB+will+be+saved.
+++++*+@return+boolean+whether+the+attributes+are+valid+and+the+record+is+inserted+successfully.
+++++*+@throws+CDbException+if+the+record+is+not+new
+++++*/
++++public+function+insertIgnoreDuplicates($attributes+=+null)
++++{
++++++++try+{
++++++++++++return+$this->insert($attributes);
++++++++}+catch+(CDbException+$ex)+{
++++++++++++if+($ex->code+==+23000)+//+ignore+duplicated+key+
++++++++++++{
++++++++++++}+else+{
++++++++++++++++throw+$ex;
++++++++++++}
++++++++}
++++}|code-block|syntax|javascript|333626|用法：|333627|$x+=+new+record();
$x->attr1++=+'val';
$x->insertIgnoreDuplicates();|333628|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For Yii 1 (Yii 2 should be similar) I inherited CActiveRecord then I copied the insert function to insertIgnoreDuplicates,
I did the following changes after createInsertCommand call:
<pre><code>/**
 * Inserts ignore duplicates a row into the table based on this active record attributes.
 * If the table's primary key is auto-incremental and is null before insertion,
 * it will be populated with the actual value after insertion.
 * Note, validation is not performed in this method. You may call {@link validate} to perform the validation.
 * After the record is inserted to DB successfully, its {@link isNewRecord} property will be set false,
 * and its {@link scenario} property will be set to be 'update'.
 * @param array $attributes list of attributes that need to be saved. Defaults to null,
 * meaning all attributes that are loaded from DB will be saved.
 * @return boolean whether the attributes are valid and the record is inserted successfully.
 * @throws CDbException if the record is not new
 */
 public function insertIgnoreDuplicates($attributes = null)
 {
 try {
 return $this-&gt;insert($attributes);
 } catch (CDbException $ex) {
 if ($ex-&gt;code == 23000) // ignore duplicated key 
 {
 } else {
 throw $ex;
 }
 }
 }
</code></pre>
<hr />
Usage:
<pre><code>$x = new record();
$x-&gt;attr1 = 'val';
$x-&gt;insertIgnoreDuplicates();
</code></pre>

I'm relatively new to Yii2 and so far I'm enjoying it very much.
I'm however finding an issue related with Database Inserts, more specifically MySQL.

The question is fairly simple: I'm inserting values into a table and I need to check for duplicates so my idea was to use an INSERT IGNORE or ON DUPLICATE KEY UPDATE.

This is the code I'm using for the insert:

<pre><code>$db-&gt;createCommand()
-&gt;insert('sdg_fb_apps', [
'fb_id' =&gt; $app-&gt;id,
'page_id' =&gt; $page_id,
'name' =&gt; $app-&gt;name,
'app_id' =&gt; $app_id,
'app_name' =&gt; $app_name,
])
-&gt;execute();
</code></pre>

I have to unique keys: id and app_id and if I run this code I'll obviously be thrown an Exception from Yii.

There's this extension but as far as I understood only works with Yii 1.0: <a href="http://www.yiiframework.com/extension/rdbcommand" rel="nofollow">http://www.yiiframework.com/extension/rdbcommand</a>

I also found this code snippet on <a href="https://github.com/yiisoft/yii2/issues/5138" rel="nofollow">GitHub</a> that looks like this:

<pre><code>class MyQueryBuilder extends yii\db\mysql\QueryBuilder
{
 public function batchInsert($table, $columns, $rows)
 {
 $sql = parent::batchInsert($table, $columns, $rows);
 $sql .= 'ON DUPLICATE KEY UPDATE';
 return $sql;
 }
}
</code></pre>

I didn't understand however how to extend the Querybuilder, can it be through a component?

I kind of accomplished what I was looking for by doing a query to see if the app_id was already in the database before and then only insert if they don't exist, however this is supposed to have to scale to huge numbers and this method might not be the best.

Any guidance will be appreciated, thanks in advance.

Yii2 Insert Ignore / Insert duplicates

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我对Yii2比较陌生，到目前为止我非常喜欢它。不过，我发现了一个与数据库插入有关的问题，更具体地说，是MySQL。问题很简单:我正在向表中插入值，并且需要检查副本，所以我的想法是在重复的键更新()、插入、忽略()或(重复键更新)上使用。这是我用于插入的代码：$db->createCommand()->insert('s...

问Yii2插入忽略/插入重复项
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Yii2插入忽略/插入重复项EN