数据库同步优化技巧分享
<p>场景1： 需要将某张表的某些字段全部同步到另一张表的某些字段．</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_354521">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">table1(a,b,c,d)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">SELECT</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">a,b,c,d </code><code class="sql keyword">FROM</code> <code class="sql plain">table2</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">WHERE</code> <code class="sql color1">NOT</code> <code class="sql plain">EXISTS(</code><code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">table1 </code><code class="sql keyword">WHERE</code> <code class="sql plain">table2.`a` =table1.`b`)</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
当最后的子查询存在记录时候，则略过．</p>
<p>
场景2: 表中新增一个字段，需要重新计算并更新至表中．</p>
<p>
如果用脚本来走的话，不使用事务没秒只能更新10条左右，如果有相应算法，可以先 生成更新语句，然后更新．</p>
<p>
场景3 关联表，某表字段批量更新至某字段</p>
<p>
UPDATE oto_payment AS a INNER JOIN oto_customer AS c ON a.openid=c.openid<br>
SET a.customer_id=c.customer_id;</p>
頁:
[1]