3 追蹤者

資料庫存取物件 ¶

建立資料庫連線
執行 SQL 查詢
引用資料表與欄位名稱
執行交易
複製與讀寫分離
使用資料庫結構描述

Yii DAO（資料庫存取物件）建立在 PDO 之上，提供物件導向 API 以存取關聯式資料庫。它是其他更進階資料庫存取方法（包括查詢建構器和 Active Record）的基礎。

當使用 Yii DAO 時，您主要需要處理純 SQL 和 PHP 陣列。因此，它是存取資料庫最有效率的方式。然而，由於不同資料庫的 SQL 語法可能有所不同，因此使用 Yii DAO 也表示您必須額外努力才能建立與資料庫無關的應用程式。

在 Yii 2.0 中，DAO 開箱即用支援下列資料庫

MySQL
MariaDB
SQLite
PostgreSQL：8.4 或更高版本
CUBRID：9.3 或更高版本。
Oracle
MSSQL：2008 或更高版本。

注意：適用於 PHP 7 的 pdo_oci 新版本目前僅以原始碼形式存在。請按照社群提供的說明進行編譯，或使用 PDO 模擬層。

建立資料庫連線 ¶

若要存取資料庫，您首先需要透過建立 yii\db\Connection 的實例來連線到資料庫

$db = new yii\db\Connection([
    'dsn' => 'mysql:host=localhost;dbname=example',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
]);

由於資料庫連線通常需要在不同地方存取，因此常見的做法是將其配置為應用程式組件，如下所示

return [
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=example',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ],
    ],
    // ...
];

然後，您可以透過運算式 Yii::$app->db 存取資料庫連線。

提示：如果您的應用程式需要存取多個資料庫，您可以配置多個資料庫應用程式組件。

配置資料庫連線時，您應始終透過 dsn 屬性指定其資料來源名稱 (DSN)。不同資料庫的 DSN 格式有所不同。請參閱 PHP 手冊以取得更多詳細資訊。以下是一些範例

MySQL、MariaDB：mysql:host=localhost;dbname=mydatabase
SQLite：sqlite:/path/to/database/file
PostgreSQL：pgsql:host=localhost;port=5432;dbname=mydatabase
CUBRID：cubrid:dbname=demodb;host=localhost;port=33000
MS SQL Server（透過 sqlsrv 驅動程式）：sqlsrv:Server=localhost;Database=mydatabase
MS SQL Server（透過 dblib 驅動程式）：dblib:host=localhost;dbname=mydatabase
MS SQL Server（透過 mssql 驅動程式）：mssql:host=localhost;dbname=mydatabase
Oracle：oci:dbname=//127.0.0.1:1521/mydatabase

請注意，如果您透過 ODBC 連線到資料庫，則應配置 yii\db\Connection::$driverName 屬性，以便 Yii 可以知道實際的資料庫類型。例如，

'db' => [
    'class' => 'yii\db\Connection',
    'driverName' => 'mysql',
    'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
    'username' => 'root',
    'password' => '',
],

除了 dsn 屬性之外，您通常還需要配置 username 和 password。請參閱 yii\db\Connection 以取得完整的可配置屬性清單。

資訊：當您建立資料庫連線實例時，直到您執行第一個 SQL 或明確呼叫 open() 方法，才會建立與資料庫的實際連線。

提示：有時您可能想要在建立資料庫連線後立即執行一些查詢，以初始化某些環境變數（例如，設定時區或字元集）。您可以透過為資料庫連線的 afterOpen 事件註冊事件處理常式來執行此操作。您可以像這樣直接在應用程式配置中註冊處理常式
'db' => [
    // ...
    'on afterOpen' => function($event) {
        // $event->sender refers to the DB connection
        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
    }
],

對於 MS SQL Server，二進制資料的正確處理需要額外的連線選項

'db' => [
 'class' => 'yii\db\Connection',
    'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase',
    'attributes' => [
        \PDO::SQLSRV_ATTR_ENCODING => \PDO::SQLSRV_ENCODING_SYSTEM
    ]
],

執行 SQL 查詢 ¶

一旦您擁有資料庫連線實例，您就可以透過執行下列步驟來執行 SQL 查詢

使用純 SQL 查詢建立 yii\db\Command；
綁定參數（可選）；
呼叫 yii\db\Command 中的 SQL 執行方法之一。

以下範例顯示從資料庫擷取資料的各種方式

// return a set of rows. each row is an associative array of column names and values.
// an empty array is returned if the query returned no results
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
            ->queryAll();

// return a single row (the first row)
// false is returned if the query has no result
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
           ->queryOne();

// return a single column (the first column)
// an empty array is returned if the query returned no results
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
             ->queryColumn();

// return a scalar value
// false is returned if the query has no result
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post')
             ->queryScalar();

注意：為了保持精確度，從資料庫擷取的資料都以字串表示，即使對應的資料庫欄位類型是數值也是如此。

綁定參數 ¶

從帶有參數的 SQL 建立資料庫命令時，您幾乎應始終使用綁定參數的方法來防止 SQL 注入攻擊。例如，

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValue(':id', $_GET['id'])
           ->bindValue(':status', 1)
           ->queryOne();

在 SQL 陳述式中，您可以嵌入一個或多個參數預留位置（例如，上述範例中的 :id）。參數預留位置應為以冒號開頭的字串。然後，您可以呼叫下列參數綁定方法之一來綁定參數值

bindValue()：綁定單個參數值
bindValues()：在一次呼叫中綁定多個參數值
bindParam()：類似於 bindValue()，但也支援綁定參數參考。

以下範例顯示綁定參數的替代方式

$params = [':id' => $_GET['id'], ':status' => 1];

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValues($params)
           ->queryOne();
           
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status', $params)
           ->queryOne();

參數綁定是透過預處理語句實作的。除了防止 SQL 注入攻擊之外，它還可以透過準備一次 SQL 陳述式並使用不同參數多次執行來提高效能。例如，

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id');

$post1 = $command->bindValue(':id', 1)->queryOne();
$post2 = $command->bindValue(':id', 2)->queryOne();
// ...

由於 bindParam() 支援依參考綁定參數，因此上述程式碼也可以寫成如下

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id')
              ->bindParam(':id', $id);

$id = 1;
$post1 = $command->queryOne();

$id = 2;
$post2 = $command->queryOne();
// ...

請注意，您在執行之前將預留位置綁定到 $id 變數，然後在每次後續執行之前更改該變數的值（這通常透過迴圈完成）。以這種方式執行查詢可能比為每個不同的參數值執行新查詢有效率得多。

資訊：參數綁定僅用於需要將值插入包含純 SQL 的字串中的位置。在較高抽象層（如查詢建構器和 Active Record）的許多位置，您通常會指定一個值陣列，這些值將轉換為 SQL。在這些位置，參數綁定由 Yii 在內部完成，因此無需手動指定參數。

執行非 SELECT 查詢 ¶

先前章節中介紹的 queryXyz() 方法都處理從資料庫擷取資料的 SELECT 查詢。對於不傳回資料的查詢，您應改為呼叫 yii\db\Command::execute() 方法。例如，

Yii::$app->db->createCommand('UPDATE post SET status=1 WHERE id=1')
   ->execute();

yii\db\Command::execute() 方法會傳回 SQL 執行影響的列數。

對於 INSERT、UPDATE 和 DELETE 查詢，您可以分別呼叫 insert()、update()、delete() 來建構對應的 SQL，而不是撰寫純 SQL。這些方法將正確引用資料表和欄位名稱並綁定參數值。例如，

// INSERT (table name, column values)
Yii::$app->db->createCommand()->insert('user', [
    'name' => 'Sam',
    'age' => 30,
])->execute();

// UPDATE (table name, column values, condition)
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE (table name, condition)
Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute();

您也可以呼叫 batchInsert() 一次插入多列，這比一次插入一列有效率得多

// table name, column names, column values
Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
])->execute();

另一個有用的方法是 upsert()。Upsert 是一種原子操作，如果資料庫表中尚不存在列（符合唯一約束），則將列插入資料庫表中，如果存在則更新它們

Yii::$app->db->createCommand()->upsert('pages', [
    'name' => 'Front page',
    'url' => 'https://example.com/', // url is unique
    'visits' => 0,
], [
    'visits' => new \yii\db\Expression('visits + 1'),
], $params)->execute();

上面的程式碼將原子地插入新的頁面記錄或遞增其訪問計數器。

請注意，上述方法僅建立查詢，您始終必須呼叫 execute() 才能實際執行它們。

引用資料表與欄位名稱 ¶

在撰寫與資料庫無關的程式碼時，正確引用資料表和欄位名稱通常令人頭痛，因為不同的資料庫具有不同的名稱引用規則。為了克服這個問題，您可以使用 Yii 引入的下列引用語法

[[欄位名稱]]：將要引用的欄位名稱括在雙方括號中；
{{資料表名稱}}：將要引用的資料表名稱括在雙大括號中。

Yii DAO 會使用 DBMS 特定語法自動將此類結構轉換為對應的引用欄位或資料表名稱。例如，

// executes this SQL for MySQL: SELECT COUNT(`id`) FROM `employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{employee}}")
            ->queryScalar();

使用資料表前綴 ¶

如果您的資料庫資料表名稱大多共享一個通用前綴，您可以使用 Yii DAO 提供的資料表前綴功能。

首先，透過應用程式配置中的 yii\db\Connection::$tablePrefix 屬性指定資料表前綴

return [
    // ...
    'components' => [
        // ...
        'db' => [
            // ...
            'tablePrefix' => 'tbl_',
        ],
    ],
];

然後，在您的程式碼中，每當您需要引用名稱包含此前綴的資料表時，請使用語法 {{%資料表名稱}}。配置資料庫連線時，百分比字元將自動替換為您指定的資料表前綴。例如，

// executes this SQL for MySQL: SELECT COUNT(`id`) FROM `tbl_employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{%employee}}")
            ->queryScalar();

執行交易 ¶

當依序執行多個相關查詢時，您可能需要將它們包裝在交易中，以確保資料庫的完整性和一致性。如果任何查詢失敗，資料庫將回滾到如同未執行任何這些查詢的狀態。

以下程式碼顯示使用交易的典型方式

Yii::$app->db->transaction(function($db) {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
});

上面的程式碼等效於以下程式碼，後者讓您對錯誤處理程式碼有更多控制權

$db = Yii::$app->db;
$transaction = $db->beginTransaction();
try {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
    
    $transaction->commit();
} catch(\Exception $e) {
    $transaction->rollBack();
    throw $e;
} catch(\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}

透過呼叫 beginTransaction() 方法，會啟動新的交易。交易表示為儲存在 $transaction 變數中的 yii\db\Transaction 物件。然後，要執行的查詢會包含在 try...catch... 區塊中。如果所有查詢都成功執行，則呼叫 commit() 方法來提交交易。否則，如果觸發並捕獲到異常，則呼叫 rollBack() 方法來回滾在交易中失敗查詢之前由查詢所做的變更。然後 throw $e 將重新拋出異常，就好像我們沒有捕獲它一樣，因此正常的錯誤處理程序將負責處理它。

注意：在上面的程式碼中，我們有兩個 catch 區塊，以與 PHP 5.x 和 PHP 7.x 相容。\Exception 實作了 \Throwable 介面，因為 PHP 7.0，因此如果您的應用程式僅使用 PHP 7.0 及更高版本，則可以跳過 \Exception 部分。

指定隔離等級 ¶

Yii 也支援為您的交易設定隔離等級。預設情況下，當啟動新交易時，它將使用資料庫系統設定的預設隔離等級。您可以按如下方式覆寫預設隔離等級，

$isolationLevel = \yii\db\Transaction::REPEATABLE_READ;

Yii::$app->db->transaction(function ($db) {
    ....
}, $isolationLevel);
 
// or alternatively

$transaction = Yii::$app->db->beginTransaction($isolationLevel);

Yii 為最常見的隔離等級提供了四個常數

yii\db\Transaction::READ_UNCOMMITTED - 最弱的等級，可能會發生髒讀、不可重複讀和幻讀。
yii\db\Transaction::READ_COMMITTED - 避免髒讀。
yii\db\Transaction::REPEATABLE_READ - 避免髒讀和不可重複讀。
yii\db\Transaction::SERIALIZABLE - 最強的等級，避免上述所有命名問題。

除了使用上述常數指定隔離等級外，您也可以使用具有 DBMS 支援的有效語法的字串。例如，在 PostgreSQL 中，您可以使用 "SERIALIZABLE READ ONLY DEFERRABLE"。

請注意，某些 DBMS 僅允許為整個連線設定隔離等級。即使您未指定任何隔離等級，任何後續交易都將獲得相同的隔離等級。使用此功能時，您可能需要為所有交易明確設定隔離等級，以避免設定衝突。在撰寫本文時，只有 MSSQL 和 SQLite 受此限制影響。

注意：SQLite 僅支援兩個隔離等級，因此您只能使用 READ UNCOMMITTED 和 SERIALIZABLE。使用其他等級將導致拋出例外。

注意：PostgreSQL 不允許在交易開始之前設定隔離等級，因此您無法在啟動交易時直接指定隔離等級。在這種情況下，您必須在交易開始後呼叫 yii\db\Transaction::setIsolationLevel()。

巢狀交易 ¶

如果您的 DBMS 支援 Savepoint，您可以像這樣巢狀多個交易

Yii::$app->db->transaction(function ($db) {
    // outer transaction
    
    $db->transaction(function ($db) {
        // inner transaction
    });
});

或者，

$db = Yii::$app->db;
$outerTransaction = $db->beginTransaction();
try {
    $db->createCommand($sql1)->execute();

    $innerTransaction = $db->beginTransaction();
    try {
        $db->createCommand($sql2)->execute();
        $innerTransaction->commit();
    } catch (\Exception $e) {
        $innerTransaction->rollBack();
        throw $e;
    } catch (\Throwable $e) {
        $innerTransaction->rollBack();
        throw $e;
    }

    $outerTransaction->commit();
} catch (\Exception $e) {
    $outerTransaction->rollBack();
    throw $e;
} catch (\Throwable $e) {
    $outerTransaction->rollBack();
    throw $e;
}

複製與讀寫分離 ¶

許多 DBMS 支援資料庫複製，以獲得更好的資料庫可用性和更快的伺服器回應時間。透過資料庫複製，資料從所謂的主伺服器複製到從伺服器。所有寫入和更新都必須在主伺服器上進行，而讀取也可以在從伺服器上進行。

為了利用資料庫複製並實現讀寫分離，您可以配置 yii\db\Connection 組件，如下所示

[
    'class' => 'yii\db\Connection',

    // configuration for the master
    'dsn' => 'dsn for master server',
    'username' => 'master',
    'password' => '',

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]

上面的配置指定了具有單個主伺服器和多個從伺服器的設定。其中一個從伺服器將被連線並用於執行讀取查詢，而主伺服器將用於執行寫入查詢。透過此配置，可以自動完成此類讀寫分離。例如，

// create a Connection instance using the above configuration
Yii::$app->db = Yii::createObject($config);

// query against one of the slaves
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();

// query against the master
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

資訊：透過呼叫 yii\db\Command::execute() 執行的查詢被視為寫入查詢，而透過 yii\db\Command 的「query」方法之一完成的所有其他查詢都是讀取查詢。您可以透過 Yii::$app->db->slave 取得目前作用中的從伺服器連線。

Connection 組件支援從伺服器之間的負載平衡和容錯移轉。首次執行讀取查詢時，Connection 組件將隨機選擇一個從伺服器並嘗試連線到它。如果發現從伺服器「已失效」，它將嘗試另一個從伺服器。如果沒有可用的從伺服器，它將連線到主伺服器。透過配置伺服器狀態快取，「已失效」的伺服器可以被記住，以便在特定時間段內不再嘗試使用它。

資訊：在上面的配置中，為每個從伺服器指定了 10 秒的連線逾時。這表示如果從伺服器在 10 秒內無法連線，則將其視為「已失效」。您可以根據您的實際環境調整此參數。

您也可以配置具有多個從伺服器的多個主伺服器。例如，

[
    'class' => 'yii\db\Connection',

    // common configuration for masters
    'masterConfig' => [
        'username' => 'master',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of master configurations
    'masters' => [
        ['dsn' => 'dsn for master server 1'],
        ['dsn' => 'dsn for master server 2'],
    ],

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]

上面的配置指定了兩個主伺服器和四個從伺服器。Connection 組件也支援主伺服器之間的負載平衡和容錯移轉，就像它在從伺服器之間所做的那樣。不同之處在於，如果沒有可用的主伺服器，將拋出例外。

注意：當您使用 masters 屬性配置一個或多個主伺服器時，用於指定資料庫連線的所有其他屬性（例如，dsn、username、password）以及 Connection 物件本身都將被忽略。

預設情況下，交易使用主伺服器連線。並且在交易中，所有資料庫操作都將使用主伺服器連線。例如，

$db = Yii::$app->db;
// the transaction is started on the master connection
$transaction = $db->beginTransaction();

try {
    // both queries are performed against the master
    $rows = $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
    $db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

    $transaction->commit();
} catch(\Exception $e) {
    $transaction->rollBack();
    throw $e;
} catch(\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}

如果您想使用從伺服器連線啟動交易，您應該明確地這樣做，如下所示

$transaction = Yii::$app->db->slave->beginTransaction();

有時，您可能想要強制使用主伺服器連線來執行讀取查詢。這可以使用 useMaster() 方法來實現

$rows = Yii::$app->db->useMaster(function ($db) {
    return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});

您也可以直接將 Yii::$app->db->enableSlaves 設定為 false，以將所有查詢導向到主伺服器連線。

使用資料庫結構描述 ¶

Yii DAO 提供了一整套方法，讓您可以操作資料庫結構描述，例如建立新資料表、從資料表中刪除欄位等。這些方法列出如下

createTable()：建立資料表
renameTable()：重新命名資料表
dropTable()：移除資料表
truncateTable()：移除資料表中的所有列
addColumn()：新增欄位
renameColumn()：重新命名欄位
dropColumn()：移除欄位
alterColumn()：變更欄位
addPrimaryKey()：新增主鍵
dropPrimaryKey()：移除主鍵
addForeignKey()：新增外鍵
dropForeignKey()：移除外鍵
createIndex()：建立索引
dropIndex()：移除索引

這些方法可以像這樣使用

// CREATE TABLE
Yii::$app->db->createCommand()->createTable('post', [
    'id' => 'pk',
    'title' => 'string',
    'text' => 'text',
]);

上面的陣列描述了要建立的欄位的名稱和類型。對於欄位類型，Yii 提供了一組抽象資料類型，可讓您定義與資料庫無關的結構描述。這些會根據資料庫轉換為 DBMS 特定類型定義，資料表在其中建立。請參閱 createTable() 方法的 API 文件以取得更多資訊。

除了變更資料庫結構描述外，您還可以透過資料庫連線的 getTableSchema() 方法檢索有關資料表定義的資訊。例如，

$table = Yii::$app->db->getTableSchema('post');

該方法傳回一個 yii\db\TableSchema 物件，其中包含有關資料表的欄位、主鍵、外鍵等的資訊。所有這些資訊主要由查詢建構器和 Active Record 使用，以協助您撰寫與資料庫無關的程式碼。

重要概念：依賴注入容器

前往頂端

查詢建構器

發現錯字或您認為此頁面需要改進？
在 github 上編輯 !