怎樣通過set_local_infile_handler函數實現MySQL的本地文件導入？詳細方法解析

gitbox 2025-09-30

在PHP中，如果你需要從本地文件導入數據到MySQL數據庫，通常會使用LOAD DATA LOCAL INFILE語句。然而，由於安全性原因，該功能在某些環境下可能被禁用或限制。 PHP提供了一個替代方式——通過mysqli::set_local_infile_handler()方法自定義讀取本地文件的行為。這不僅增強了安全性，還賦予開發者更大的靈活性。

本文將詳細講解如何使用set_local_infile_handler()函數實現MySQL的本地文件導入，並配合一個完整的實例進行說明。

一、基礎概念

mysqli::set_local_infile_handler()是PHP中mysqli擴展提供的方法，用於在執行LOAD DATA LOCAL INFILE時，通過用戶自定義的回調函數讀取本地文件內容。

基本語法：

 <span><span>mysqli::</span><span><span class="hljs-variable constant_">set_local_infile_handler</span></span><span> ( </span><span><span class="hljs-keyword">callable</span></span><span> </span><span><span class="hljs-variable">$read_func</span></span><span> ) : </span><span><span class="hljs-keyword">bool</span></span><span>
</span></span>

其中$read_func是一個用戶定義的函數，它用於處理數據的讀取和傳輸。

二、啟用local_infile支持

在使用之前，需要確保以下幾項被正確配置：

MySQL服務器端啟用了local_infile ：

 <span><span><span class="hljs-keyword">SHOW</span></span><span> VARIABLES </span><span><span class="hljs-keyword">LIKE</span></span><span> </span><span><span class="hljs-string">'local_infile'</span></span><span>;
</span></span>

若為OFF ，需在服務器配置文件my.cnf中設置：

 <span><span><span class="hljs-section">[mysqld]</span></span><span>
</span><span><span class="hljs-attr">local_infile</span></span><span>=</span><span><span class="hljs-number">1</span></span><span>
</span></span>

PHP配置中啟用了mysqli.allow_local_infile ：
查看php.ini是否包含以下內容：

 <span><span><span class="hljs-attr">mysqli.allow_local_infile</span></span><span> = </span><span><span class="hljs-literal">On</span></span><span>
</span></span>

連接數據庫時，添加MYSQLI_CLIENT_LOCAL_FILES選項：

 <span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-title function_ invoke__">mysqli_init</span></span><span>();
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">options</span></span><span>(MYSQLI_OPT_LOCAL_INFILE, </span><span><span class="hljs-literal">true</span></span><span>);
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">real_connect</span></span><span>(</span><span><span class="hljs-string">"host"</span></span><span>, </span><span><span class="hljs-string">"user"</span></span><span>, </span><span><span class="hljs-string">"password"</span></span><span>, </span><span><span class="hljs-string">"database"</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, MYSQLI_CLIENT_LOCAL_FILES);
</span></span>

三、創建自定義的讀取函數

該函數的作用是將本地文件內容通過緩衝的方式提供給MySQL。下面是一個讀取CSV文件的示例：

 <span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">localInfileReader</span></span><span>(</span><span><span class="hljs-params"><span class="hljs-variable">$stream</span></span></span><span>, &amp;</span><span><span class="hljs-variable">$buffer</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>)
{
    </span><span><span class="hljs-built_in">static</span></span><span> </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;

    </span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
        </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fopen</span></span><span>(</span><span><span class="hljs-string">'/path/to/data.csv'</span></span><span>, </span><span><span class="hljs-string">'r'</span></span><span>);
        </span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
            </span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
        }
    }

    </span><span><span class="hljs-variable">$data</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fread</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>);
    </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$data</span></span><span> === </span><span><span class="hljs-literal">false</span></span><span>) {
        </span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
    }

    </span><span><span class="hljs-variable">$buffer</span></span><span> = </span><span><span class="hljs-variable">$data</span></span><span>;

    </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-title function_ invoke__">feof</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>)) {
        </span><span><span class="hljs-title function_ invoke__">fclose</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>);
        </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;
    }

    </span><span><span class="hljs-keyword">return</span></span><span> </span><span><span class="hljs-title function_ invoke__">strlen</span></span><span>(</span><span><span class="hljs-variable">$data</span></span><span>);
}
</span></span>

四、完整示例

以下是一個完整的示例，展示瞭如何使用set_local_infile_handler()實現本地文件數據的導入：

 <span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-title function_ invoke__">mysqli_init</span></span><span>();
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">options</span></span><span>(MYSQLI_OPT_LOCAL_INFILE, </span><span><span class="hljs-literal">true</span></span><span>);

</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">real_connect</span></span><span>(</span><span><span class="hljs-string">'localhost'</span></span><span>, </span><span><span class="hljs-string">'root'</span></span><span>, </span><span><span class="hljs-string">'password'</span></span><span>, </span><span><span class="hljs-string">'test_db'</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, MYSQLI_CLIENT_LOCAL_FILES)) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">'Connect Error ('</span></span><span> . </span><span><span class="hljs-title function_ invoke__">mysqli_connect_errno</span></span><span>() . </span><span><span class="hljs-string">') '</span></span><span> . </span><span><span class="hljs-title function_ invoke__">mysqli_connect_error</span></span><span>());
}

</span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">localInfileReader</span></span><span>(</span><span><span class="hljs-params"><span class="hljs-variable">$stream</span></span></span><span>, &amp;</span><span><span class="hljs-variable">$buffer</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>)
{
    </span><span><span class="hljs-built_in">static</span></span><span> </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;

    </span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
        </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fopen</span></span><span>(</span><span><span class="hljs-string">'/path/to/data.csv'</span></span><span>, </span><span><span class="hljs-string">'r'</span></span><span>);
        </span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
            </span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
        }
    }

    </span><span><span class="hljs-variable">$data</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fread</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>);
    </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$data</span></span><span> === </span><span><span class="hljs-literal">false</span></span><span>) {
        </span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
    }

    </span><span><span class="hljs-variable">$buffer</span></span><span> = </span><span><span class="hljs-variable">$data</span></span><span>;

    </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-title function_ invoke__">feof</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>)) {
        </span><span><span class="hljs-title function_ invoke__">fclose</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>);
        </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;
    }

    </span><span><span class="hljs-keyword">return</span></span><span> </span><span><span class="hljs-title function_ invoke__">strlen</span></span><span>(</span><span><span class="hljs-variable">$data</span></span><span>);
}

</span><span><span class="hljs-comment">// 設置讀取處理器</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">set_local_infile_handler</span></span><span>(</span><span><span class="hljs-string">"localInfileReader"</span></span><span>);

</span><span><span class="hljs-comment">// 執行導入語句</span></span><span>
</span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"LOAD DATA LOCAL INFILE 'dummy.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'"</span></span><span>;

</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$mysqli</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">echo</span></span><span> </span><span><span class="hljs-string">"導入失敗: ("</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;errno . </span><span><span class="hljs-string">") "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;error;
} </span><span><span class="hljs-keyword">else</span></span><span> {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"導入成功！"</span></span><span>;
}

</span><span><span class="hljs-comment">// 關閉處理器</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">set_local_infile_default</span></span><span>();

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

說明：

dummy.csv是一個佔位文件名，MySQL不會實際讀取該路徑，而是依賴你提供的回調函數。
回調函數內的真實文件路徑可以是任意合法的可讀路徑。

五、注意事項

回調函數必須能持續讀取並正確返回數據長度。
一定要關閉回調（ set_local_infile_default() ），避免後續操作中誤觸發。
使用LOAD DATA LOCAL INFILE前請確保數據庫用戶有足夠權限。

六、結語

通過set_local_infile_handler()方法，PHP提供了對LOAD DATA LOCAL INFILE更加安全和可控的支持方式。這種方法尤其適用於對數據輸入有嚴格驗證需求的場景。雖然使用略顯複雜，但在實際生產環境中，它為開發者提供了寶貴的靈活性和安全保障。

掌握這一技巧，將為你處理大規模數據導入任務增添有力工具。

相關標籤:
MySQL