返回文章列表
玄貓（BlackCat）

PHP 使用 MySQLi 與 PDO 資料函式庫操作

本文探討 PHP 中兩種常用的 MySQL 資料函式庫操作方法：MySQLi 和 PDO。文章比較了兩者的特性、優缺點，並提供程式碼範例說明如何使用 MySQLi 和 PDO 進行資料函式庫連線、查詢、新增資料、呼叫預存程式等操作，同時也涵蓋了安全性考量，例如避免硬編碼資料函式庫憑證。

PHP 資料函式庫
MySQL MySQLi PDO 資料函式庫連線 SQL 查詢 預處理陳述式

在 PHP 開發中，與 MySQL 資料函式庫互動是常見的需求。MySQLi 和 PDO 是兩種主流的資料函式庫操作介面，各有其優勢與適用場景。MySQLi 作為 MySQL 專用擴充，效能表現出色，提供程式式和物件導向兩種風格。PDO 則具備更佳的跨資料函式庫相容性，採用物件導向設計，簡化了程式碼維護。選擇哪種方式取決於專案的具體需求，例如是否需要支援多種資料函式庫、效能要求以及程式碼風格偏好。考量安全性時，應避免將資料函式庫憑證寫死在程式碼中，建議使用設定檔或環境變數儲存敏感資訊，以提升應用程式的安全性。

secure_file_priv 設定

secure_file_priv 的設定決定了 MySQL 可以讀寫檔案的目錄。如果設定為 NULL，則 MySQL 不能讀寫任何檔案。如果設定為空字串 (''），則 MySQL 可以讀寫任意目錄下的檔案。但是，如果設定為特定的目錄路徑，例如 C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\，則 MySQL 只能讀寫該目錄下的檔案。

MySQL Shell

MySQL Shell (mysqlsh) 是一個新的 MySQL 命令列工具，可以執行 SQL、Python 或 JavaScript 命令。它提供了一個更方便的方式來執行 SQL 指令碼和載入大型資料檔。

載入資料檔

使用 mysqlsh 可以更快速地載入大型資料檔。它提供了一個名為 import-table 的工具，可以平行載入資料表。

程式設計語言整合

MySQL 可以與多種程式設計語言整合，包括 PHP、Python 和 Java。這些語言提供了不同的方式來連線 MySQL 資料函式庫和執行 SQL 命令。

PHP

PHP 是一個開源的程式設計語言，常用於網頁開發。它可以使用 PDO 或 MySQLi 擴充功能來連線 MySQL 資料函式庫。

Python

Python 是一個開源的程式設計語言，常用於資料分析和機器學習。它可以使用 mysql-connector-python 函式庫來連線 MySQL 資料函式庫。

Java

Java 是一個開源的程式設計語言，常用於企業級應用開發。它可以使用 mysql-connector-java 函式庫來連線 MySQL 資料函式庫。

內容解密：

在這個章節中，我們討論瞭如何設定 MySQL 的連線和安全性。首先，我們介紹了 secure_file_priv 變數的設定，然後介紹了 MySQL Shell 和其工具 import-table。最後，我們討論瞭如何使用 PHP、Python 和 Java 來連線 MySQL 資料函式庫和執行 SQL 命令。

圖表翻譯：

這個流程圖展示瞭如何設定 MySQL 的連線和安全性。首先，設定 secure_file_priv 變數，然後設定 MySQL Shell 和其工具 import-table。最後，使用 PHP、Python 或 Java 來連線 MySQL 資料函式庫和執行 SQL 命令。

使用 PDO 連線 MySQL 資料函式庫

要使用 PHP 連線 MySQL 資料函式庫，我們需要建立一個 PDO 物件。以下是建立 PDO 物件的範例：

$conn = new PDO(
    'mysql:host=localhost;dbname=topography',
    'top_app',
    'pQ3fgR5u5'
);

在這個範例中，我們建立了一個 PDO 物件，並傳入三個引數：主機名稱、資料函式庫名稱和使用者密碼。

執行 SQL 查詢

要執行 SQL 查詢，我們可以使用 PDO 的 query() 方法。以下是範例：

$sql = 'select mountain_name, location, height from mountain';
$stmt = $conn->query($sql);

在這個範例中，我們定義了一個 SQL 查詢，並使用 query() 方法執行查詢。查詢結果會儲存到 $stmt 變數中。

取得查詢結果

要取得查詢結果，我們可以使用 fetch() 方法。以下是範例：

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['mountain_name']. ' | '. $row['location']. ' | '. $row['height']. '<br />';
}

在這個範例中，我們使用 fetch() 方法取得查詢結果，並將結果儲存到 $row 變數中。然後，我們使用 echo 陳述式將結果輸出到畫面上。

關閉連線

最後，我們需要關閉連線，以避免資源浪費。以下是範例：

$conn = null;

在這個範例中，我們將 $conn 變數設為 null，以關閉連線。

使用 Prepared Statement 新增資料

要使用 Prepared Statement 新增資料，我們需要建立一個 Prepared Statement 物件。以下是範例：

$new_mountain = 'K2';
$new_location = 'Asia';
$new_height = 28252;

$stmt = $conn->prepare(
    'insert into mountain (mountain_name, location, height) values (:mountain, :location, :height)'
);

$stmt->bindParam(':mountain', $new_mountain, PDO::PARAM_STR);
$stmt->bindParam(':location', $new_location, PDO::PARAM_STR);
$stmt->bindParam(':height', $new_height, PDO::PARAM_INT);

$stmt->execute();

在這個範例中，我們建立了一個 Prepared Statement 物件，並定義了一個 SQL 查詢。然後，我們使用 bindParam() 方法將變數繫結到查詢中。最後，我們使用 execute() 方法執行查詢。

圖表翻譯：

在這個圖表中，我們展示了使用 PDO 連線 MySQL 資料函式庫的流程。首先，我們建立了一個 PDO 物件，然後執行了一個 SQL 查詢。查詢結果會儲存到變數中，然後輸出到畫面上。最後，我們關閉連線，以避免資源浪費。

內容解密：

在這個範例中，我們使用了 PDO 連線 MySQL 資料函式庫，並執行了一個 SQL 查詢。查詢結果會儲存到變數中，然後輸出到畫面上。 Prepared Statement 是一個安全的方式來新增資料，因為它可以避免 SQL Injection 攻擊。

使用PDO進行資料函式庫操作

PDO（PHP Data Objects）是一種強大的資料庫存取技術，允許您以物件導向的方式與資料函式庫進行互動。以下是使用PDO進行資料函式庫操作的範例：

準備SQL指令

首先，您需要準備一個SQL指令，例如：

$stmt = $conn->prepare('insert into mountain (mountain_name, location, height) values (:mountain_name, :location, :height)');

在這個範例中，我們使用:mountain_name、:location和:height作為命名佔位符。

###繫結引數

接下來，您需要繫結實際值到佔位符上：

$stmt->bindParam(':mountain_name', $new_mountain, PDO::PARAM_STR);
$stmt->bindParam(':location', $new_location, PDO::PARAM_STR);
$stmt->bindParam(':height', $new_height, PDO::PARAM_INT);

在這個範例中，我們綁定了$new_mountain、$new_location和$new_height變數到對應的佔位符上，並指定了資料型別（字串或整數）。

執行SQL指令

最後，您可以執行SQL指令：

$stmt->execute();

這將會執行SQL指令，並將新資料插入到資料函式庫中。

呼叫儲存程式

PDO也允許您呼叫儲存程式。以下是範例：

$location = 'Asia';
$stmt = $conn->prepare('call p_get_mountain_by_loc(:location)');
$stmt->bindParam(':location', $location, PDO::PARAM_STR);
$stmt->execute();

在這個範例中，我們呼叫了一個名為p_get_mountain_by_loc的儲存程式，並傳遞了$location變數作為引數。

使用MySQLi

MySQLi是MySQL的改進版本，提供了物件導向的API。以下是使用MySQLi進行資料函式庫操作的範例：

$conn = new mysqli('localhost', 'top_app', 'pQ3fgR5u5', 'topography');
$sql = 'select mountain_name, location, height from mountain';
$result = $conn->query($sql);

在這個範例中，我們建立了一個MySQLi連線，並執行了一個SQL指令來選取資料表中的資料。

安全性考量

在實際應用中，應該避免硬編碼資料函式庫憑證，而是應該使用組態檔案或環境變數來儲存敏感資訊。例如：

$conn = new PDO(
    'mysql:host='. getenv('DB_HOST'). ';dbname='. getenv('DB_NAME'),
    getenv('DB_USER'),
    getenv('DB_PASSWORD')
);

這樣可以避免敏感資訊被公開。

使用物件導向MySQLi顯示山脈資料表中的資料

首先，我們需要建立一個到MySQL的連線，然後使用該連線執行查詢。結果會被儲存到一個PHP變數中，然後我們可以迭代結果中的每一行，並使用fetch_assoc()方法將每一行的資料以關聯陣列的形式取得。

$conn = new mysqli('localhost', 'top_app', 'pQ3fgR5u5', 'topography');
$result = $conn->query("SELECT * FROM mountain");
while ($row = $result->fetch_assoc()) {
    echo $row['mountain_name']. ' | '. $row['location']. ' | '. $row['height']. '<br />';
}
$conn->close();

將新資料插入山脈資料表中

接下來，我們會建立一個PHP程式，使用物件導向MySQLi將新資料插入山脈資料表中。首先，我們建立一個到MySQL的連線，然後準備一個SQL指令，使用問號作為佔位符。接著，我們使用bind_param()方法將實際值繫結到佔位符上，最後執行該SQL指令。

$conn = new mysqli('localhost', 'top_app', 'pQ3fgR5u5', 'topography');
$new_mountain = 'Makalu';
$new_location = 'Asia';
$new_height = 27766;
$stmt = $conn->prepare('INSERT INTO mountain (mountain_name, location, height) VALUES (?,?,?)');
$stmt->bind_param('ssi', $new_mountain, $new_location, $new_height);
$stmt->execute();
$conn->close();

呼叫儲存程式

最後，我們會建立一個PHP程式，使用物件導向MySQLi呼叫一個儲存程式。首先，我們建立一個到MySQL的連線，然後準備一個SQL指令，呼叫儲存程式。接著，我們使用bind_param()方法將實際值繫結到佔位符上，然後執行該SQL指令。

$conn = new mysqli('localhost', 'top_app', 'pQ3fgR5u5', 'topography');
$location = 'Asia';
$stmt = $conn->prepare('CALL p_get_mountain_by_loc(?)');
$stmt->bind_param('s', $location);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['mountain_name']. ' | '. $row['location']. ' | '. $row['height']. '<br />';
}
$conn->close();

內容解密：

上述程式碼展示瞭如何使用物件導向MySQLi與MySQL資料函式庫進行互動。首先，我們建立了一個到MySQL的連線，然後使用該連線執行查詢或插入資料。結果會被儲存到一個PHP變數中，然後我們可以迭代結果中的每一行，並使用fetch_assoc()方法將每一行的資料以關聯陣列的形式取得。

圖表翻譯：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title PHP 使用 MySQLi 與 PDO 資料函式庫操作

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

上述流程圖展示了使用物件導向MySQLi與MySQL資料函式庫進行互動的流程。首先，我們建立了一個到MySQL的連線，然後執行查詢，取得結果，迭代結果，顯示資料，最後關閉連線。

使用MySQLi和PDO進行資料庫存取

在PHP中，MySQLi和PDO是兩種常用的資料庫存取方法。MySQLi是一種專門為MySQL資料函式庫設計的擴充套件，而PDO（PHP Data Objects）則是一種更為通用的資料庫存取介面，可以支援多種不同的資料函式庫系統。

MySQLi

MySQLi提供了兩種不同的程式設計風格：物件導向（OO）和程式式（Procedural）。物件導向風格使用箭頭運算元（->）來存取方法和屬性，而程式式風格則使用以mysqli_開頭的函式。

物件導向MySQLi

以下是使用物件導向MySQLi來呼叫儲存過程的範例：

$conn = new mysqli('localhost', 'top_app', 'pQ3fgR5u5', 'topography');
$stmt = $conn->prepare('CALL p_get_mountain_by_loc(?)');
$stmt->bind_param('s', 'Asia');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['mountain_name']. ' | '. $row['location']. ' | '. $row['height']. '<br />';
}
$conn->close();

程式式MySQLi

以下是使用程式式MySQLi來選擇資料表的範例：

$conn = mysqli_connect('localhost', 'top_app', 'pQ3fgR5u5', 'topography');
$sql = 'SELECT mountain_name, location, height FROM mountain';
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['mountain_name']. ' | '. $row['location']. ' | '. $row['height']. '<br />';
}
mysqli_close($conn);

PDO

PDO是一種更為通用的資料庫存取介面，可以支援多種不同的資料函式庫系統。以下是使用PDO來選擇資料表的範例：

$dsn = 'mysql:host=localhost;dbname=topography';
$username = 'top_app';
$password = 'pQ3fgR5u5';
$pdo = new PDO($dsn, $username, $password);
$sql = 'SELECT mountain_name, location, height FROM mountain';
$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['mountain_name']. ' | '. $row['location']. ' | '. $row['height']. '<br />';
}
$pdo = null;

比較MySQLi和PDO

MySQLi和PDO都有其優缺點。MySQLi是一種專門為MySQL資料函式庫設計的擴充套件，因此它的效能和功能可能更好。但是，PDO是一種更為通用的資料庫存取介面，可以支援多種不同的資料函式庫系統，因此它可能更適合於需要支援多種資料函式庫系統的應用程式。

使用MySQLi的優點

  • 專門為MySQL資料函式庫設計，因此效能和功能可能更好
  • 提供了兩種不同的程式設計風格：物件導向和程式式

使用MySQLi的缺點

  • 只能支援MySQL資料函式庫系統
  • 程式式風格可能不如物件導向風格方便使用

使用PDO的優點

  • 可以支援多種不同的資料函式庫系統
  • 提供了一種統一的資料庫存取介面

使用PDO的缺點

  • 可能不如MySQLi那樣專門為MySQL資料函式庫設計，因此效能和功能可能不佳
  • 需要額外的設定和組態

從資料函式庫連線安全到程式語言整合的全面檢視顯示，MySQL 提供了多元且彈性的資料庫存取機制。本文深入剖析了 secure_file_priv 設定的重要性，有效控管檔案讀寫許可權，降低安全風險。同時，MySQL Shell 的 import-table 工具顯著提升了大型資料檔案載入的效率，對於資料密集型應用至關重要。此外，PHP、Python 和 Java 等程式語言的整合，透過 PDO 與 MySQLi 等擴充，展現了 MySQL 在跨平臺開發的靈活性，滿足不同開發情境的資料函式庫操作需求。然而，選擇 MySQLi 或 PDO 的決策並非一成不變，需考量專案的規模、效能需求以及跨資料函式庫相容性等因素。對於注重效能且僅使用 MySQL 的專案，MySQLi 的專屬最佳化能帶來更佳表現；而對於需要跨資料函式庫支援的應用，PDO 的通用性則更具優勢。展望未來，隨著資料函式庫技術的演進，預期 MySQL 將持續強化安全機制，並提供更便捷的開發工具，以滿足日益增長的資料處理與分析需求。玄貓認為，開發者應深入理解不同資料函式庫連線方式的特性，才能在實務應用中做出最佳的技術選型，充分發揮 MySQL 的效能與彈性。