如何在多結果集查詢中高效使用mysqli_stmt::store_result？

gitbox 2025-09-15

什麼是多結果集查詢？

多結果集查詢是指一次SQL 查詢返回多個結果集的情況。通常情況下，這種查詢通過; （分號）分隔多個SQL 語句進行。例如：

 <span><span><span class="hljs-keyword">SELECT</span></span><span> </span><span><span class="hljs-operator">*</span></span><span> </span><span><span class="hljs-keyword">FROM</span></span><span> table1;
</span><span><span class="hljs-keyword">SELECT</span></span><span> </span><span><span class="hljs-operator">*</span></span><span> </span><span><span class="hljs-keyword">FROM</span></span><span> table2;
</span></span>

上面的查詢將返回兩個結果集。對於MySQL 來說，可以通過mysqli_multi_query()函數來執行多結果集查詢，並通過mysqli_next_result()切換到下一個結果集。

mysqli_stmt::store_result()的作用

在mysqli擴展中， mysqli_stmt::store_result()是用來將查詢結果從服務器存儲到客戶端的函數。這意味著查詢結果集的數據會完全從數據庫服務器加載到客戶端的內存中。對於需要多次訪問數據的場景， store_result()可以提高性能，因為它避免了每次讀取數據時都要與數據庫服務器交互。

多結果集查詢中的挑戰

在進行多結果集查詢時，每個結果集都會獨立地返回數據。如果不正確地使用store_result() ，可能會導致以下幾個問題：

內存佔用高：如果查詢結果集非常大，調用store_result()會將整個結果集加載到內存中，這可能會導致內存佔用過高。
結果集未完全讀取：在多結果集查詢中，如果沒有適當地調用mysqli_next_result()和store_result() ，可能會導致某些結果集未能完全讀取，導致後續查詢出錯。
執行效率問題：由於每次查詢都要從數據庫加載完整的數據集，過度使用store_result()會帶來性能上的負擔。

高效使用mysqli_stmt::store_result()的方法

1. 使用mysqli_multi_query()執行多結果集查詢

首先，必須使用mysqli_multi_query()函數來執行多結果集查詢。該函數允許我們執行多個SQL 語句並返回多個結果集。使用時，需要注意一個關鍵點： mysqli_multi_query()會返回所有的查詢結果，但它不會自動處理結果集。你需要手動通過mysqli_next_result()來遍歷每個結果集。

 <span><span><span class="hljs-variable">$conn</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-variable">$host</span></span><span>, </span><span><span class="hljs-variable">$user</span></span><span>, </span><span><span class="hljs-variable">$password</span></span><span>, </span><span><span class="hljs-variable">$dbname</span></span><span>);

</span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"SELECT * FROM table1; SELECT * FROM table2;"</span></span><span>;

</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$conn</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">multi_query</span></span><span>(</span><span><span class="hljs-variable">$sql</span></span><span>)) {
    </span><span><span class="hljs-keyword">do</span></span><span> {
        </span><span><span class="hljs-comment">// 獲取當前結果集</span></span><span>
        </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$result</span></span><span> = </span><span><span class="hljs-variable">$conn</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">store_result</span></span><span>()) {
            </span><span><span class="hljs-keyword">while</span></span><span> (</span><span><span class="hljs-variable">$row</span></span><span> = </span><span><span class="hljs-variable">$result</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">fetch_assoc</span></span><span>()) {
                </span><span><span class="hljs-comment">// 處理每行數據</span></span><span>
                </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Data: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>[</span><span><span class="hljs-string">'column_name'</span></span><span>] . </span><span><span class="hljs-string">"\n"</span></span><span>;
            }
            </span><span><span class="hljs-variable">$result</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">free</span></span><span>();  </span><span><span class="hljs-comment">// 釋放當前結果集</span></span><span>
        }
    } </span><span><span class="hljs-keyword">while</span></span><span> (</span><span><span class="hljs-variable">$conn</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">next_result</span></span><span>());  </span><span><span class="hljs-comment">// 獲取下一個結果集</span></span><span>
}

</span><span><span class="hljs-variable">$conn</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span></span>

2. 使用store_result()時注意內存管理

由於store_result()會將整個查詢結果集加載到內存中，因此在處理大量數據時，可能會出現內存不足的情況。為了高效管理內存，建議在每次處理完一個結果集後，使用$result->free()釋放內存空間。

3. 僅在必要時使用store_result()

並非所有查詢都需要使用store_result() ，特別是在只需要一次性讀取查詢結果時。對於簡單的SELECT 查詢，如果只需獲取部分數據並且查詢結果較小，可以考慮直接使用bind_result()或fetch()來獲取數據，而不是使用store_result() 。這樣可以減少內存消耗。

 <span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$conn</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"SELECT * FROM table1"</span></span><span>);
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">bind_result</span></span><span>(</span><span><span class="hljs-variable">$column1</span></span><span>, </span><span><span class="hljs-variable">$column2</span></span><span>);
</span><span><span class="hljs-keyword">while</span></span><span> (</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">fetch</span></span><span>()) {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Data: "</span></span><span> . </span><span><span class="hljs-variable">$column1</span></span><span> . </span><span><span class="hljs-string">", "</span></span><span> . </span><span><span class="hljs-variable">$column2</span></span><span> . </span><span><span class="hljs-string">"\n"</span></span><span>;
}
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span></span>

4. 使用分頁查詢減少結果集大小

如果某個結果集非常大，可以考慮將查詢拆分成多個分頁查詢。通過限制每次查詢返回的記錄數，減少單次加載的數據量，從而提高效率並降低內存佔用。

 <span><span><span class="hljs-variable">$page</span></span><span> = </span><span><span class="hljs-number">1</span></span><span>;
</span><span><span class="hljs-variable">$perPage</span></span><span> = </span><span><span class="hljs-number">10</span></span><span>;
</span><span><span class="hljs-variable">$offset</span></span><span> = (</span><span><span class="hljs-variable">$page</span></span><span> - </span><span><span class="hljs-number">1</span></span><span>) * </span><span><span class="hljs-variable">$perPage</span></span><span>;

</span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"SELECT * FROM table1 LIMIT <span class="hljs-subst">$offset</span></span></span><span>, </span><span><span class="hljs-subst">$perPage</span></span><span>";
</span><span><span class="hljs-variable">$result</span></span><span> = </span><span><span class="hljs-variable">$conn</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">query</span></span><span>(</span><span><span class="hljs-variable">$sql</span></span><span>);

</span><span><span class="hljs-keyword">while</span></span><span> (</span><span><span class="hljs-variable">$row</span></span><span> = </span><span><span class="hljs-variable">$result</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">fetch_assoc</span></span><span>()) {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Data: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>[</span><span><span class="hljs-string">'column_name'</span></span><span>] . </span><span><span class="hljs-string">"\n"</span></span><span>;
}
</span></span>

5. 監控查詢執行時間

為了確保在多結果集查詢中高效使用store_result() ，可以在代碼中加入執行時間的監控，幫助判斷是否有某個查詢造成了性能瓶頸。如果查詢過慢，可以考慮優化SQL 語句，或者分步執行。

 <span><span><span class="hljs-variable">$start_time</span></span><span> = </span><span><span class="hljs-title function_ invoke__">microtime</span></span><span>(</span><span><span class="hljs-literal">true</span></span><span>);

</span><span><span class="hljs-comment">// 執行查詢</span></span><span>

</span><span><span class="hljs-variable">$end_time</span></span><span> = </span><span><span class="hljs-title function_ invoke__">microtime</span></span><span>(</span><span><span class="hljs-literal">true</span></span><span>);
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Query Time: "</span></span><span> . (</span><span><span class="hljs-variable">$end_time</span></span><span> - </span><span><span class="hljs-variable">$start_time</span></span><span>) . </span><span><span class="hljs-string">" seconds"</span></span><span>;
</span></span>

相關標籤:
mysqli_stmt