Yii DAO (Database Access Objects) 建立在 PHP PDO 之上，提供統一介面存取 MySQL、PostgreSQL、SQLite、SQL Server、Oracle 等資料庫。本文系統整理 Yii 2.0 DAO 核心類別 (Connection, Command, DataReader, Transaction) 用法、最佳實務與效能優化技巧。
yii\db\Connection：資料庫連線管理、查詢快取、表前綴、主從分離。yii\db\Command：SQL 執行、參數綁定、原生 SQL 與 Query Builder 互通。yii\db\DataReader：前向游標逐行讀取大量資料 (低記憶體)。yii\db\Transaction：ACID 事務、巢狀事務 (Savepoint)、隔離等級。// config/main.php (Web) 或 config/console.php (Console)
return [
'components' => [
'db' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
'username' => 'db_user',
'password' => 'db_pass',
'charset' => 'utf8mb4',
// 連線池 / 效能
'enableSchemaCache' => true,
'schemaCacheDuration' => 3600,
'schemaCache' => 'cache', // 需配置 cache 組件
// 主從分離 (讀寫分離)
'slaveConfig' => [
'username' => 'slave_user',
'password' => 'slave_pass',
],
'slaves' => [
['dsn' => 'mysql:host=slave1;dbname=myapp'],
['dsn' => 'mysql:host=slave2;dbname=myapp'],
],
// 表前綴
'tablePrefix' => 'tbl_',
// 開發環境啟用 SQL 日誌
'enableLogging' => YII_DEBUG,
'enableProfiling' => YII_DEBUG,
],
// 可選：第二個資料庫連線
'db2' => [
'class' => 'yii\db\Connection',
'dsn' => 'pgsql:host=localhost;dbname=analytics',
'username' => '...',
'password' => '...',
],
],
];
使用：Yii::$app->db (主庫)、Yii::$app->db->slave (從庫)、Yii::$app->db2 (第二庫)。
$db = Yii::$app->db;
// 建立 Command
$cmd = $db->createCommand('SELECT * FROM {{%user}} WHERE status = :status AND created_at > :date');
$cmd->bindValues([
':status' => 1,
':date' => '2024-01-01',
]);
// 取得所有行 (陣列)
$users = $cmd->queryAll(); // [] 空陣列而非 null
// 取得第一行
$user = $cmd->queryOne(); // false 或陣列
// 取得單一欄位值
$count = $cmd->queryScalar(); // string|null
// 取得單一欄位陣列
$emails = $cmd->queryColumn(); // []
// 迭代大量資料 (低記憶體)
$reader = $cmd->query();
while (($row = $reader->read()) !== false) {
// 處理單行
}
$reader->close();
// 批次處理 (Yii 2.0.11+)
foreach ($db->createCommand('SELECT * FROM {{%huge_table}}')->queryBatch(100) as $rows) {
// $rows 為 100 筆陣列
}
// 單筆插入
$db->createCommand()->insert('{{%user}}', [
'username' => 'john',
'email' => 'john@example.com',
'created_at' => date('Y-m-d H:i:s'),
])->execute();
// 批次插入 (高效)
$db->createCommand()->batchInsert('{{%user}}', ['username', 'email', 'created_at'], [
['alice', 'alice@example.com', '2024-01-01'],
['bob', 'bob@example.com', '2024-01-02'],
// ... 千筆級
])->execute();
// 更新
$db->createCommand()->update('{{%user}}', ['last_login' => time()], 'id = 1')->execute();
// 刪除
$db->createCommand()->delete('{{%user}}', ['status' => 0])->execute();
// Upsert (MySQL 8.0+ / PostgreSQL 9.5+)
$db->createCommand()->upsert('{{%user}}', [
'email' => 'unique@example.com',
'username' => 'unique',
'login_count' => 1,
], [
'login_count' => new \yii\db\Expression('login_count + 1'),
], ['email'])->execute();
// 命名參數 (推薦，可讀性高)
$cmd = $db->createCommand('SELECT * FROM {{%post}} WHERE author_id = :aid AND status = :st');
$cmd->bindValue(':aid', 123);
$cmd->bindValue(':st', 1);
$cmd->bindParam(':kw', $keyword); // 綁定變數引用，適合迴圈重複執行
// 位置參數
$cmd = $db->createCommand('SELECT * FROM {{%post}} WHERE id IN (?,?,?)');
$cmd->bindValues([1, 2, 3]);
// 原生表達式 (不加引號)
$cmd = $db->createCommand('UPDATE {{%counter}} SET `count` = `count` + :inc');
$cmd->bindValue(':inc', new \yii\db\Expression('1'));
// LIKE 參數安全處理
$keyword = 'test%'; // 使用者輸入含 %
$safeKw = $db->quoteValue('%' . strtr($keyword, ['%' => '\%', '_' => '\_', '\\' => '\\\\']) . '%');
// 或使用 QueryBuilder
$query = (new \yii\db\Query())->from('{{%post}}')->where(['like', 'title', $keyword]);
$transaction = $db->beginTransaction();
try {
$db->createCommand()->insert('{{%order}}', $orderData)->execute();
$db->createCommand()->insert('{{%order_item}}', $items)->execute();
$db->createCommand()->update('{{%product}}', ['stock' => new \yii\db\Expression('stock - :qty')], ['id' => $pid])->execute();
// ...
$transaction->commit();
} catch (\Throwable $e) {
$transaction->rollBack();
throw $e;
}
// 簡寫 (Yii 2.0.14+)
$db->transaction(function ($db) use ($orderData, $items) {
$db->createCommand()->insert('{{%order}}', $orderData)->execute();
$db->createCommand()->insert('{{%order_item}}', $items)->execute();
});
// 隔離等級
$transaction = $db->beginTransaction(\yii\db\Transaction::SERIALIZABLE);
// READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
// Savepoint (巢狀事務)
$tx = $db->beginTransaction();
try {
$savepoint = $tx->savepoint('sp1');
// 子操作...
$tx->releaseSavepoint('sp1'); // 或 $tx->rollBackSavepoint('sp1');
$tx->commit();
} catch (\Throwable $e) {
$tx->rollBack();
throw $e;
}
// 設定前綴後，SQL 中使用 {{%table}} 自動展開
// tablePrefix = 'tbl_' → {{%user}} → `tbl_user`
// 取得 Schema 資訊
$schema = $db->getSchema();
$tables = $schema->getTableNames(); // 所有表名
$columns = $schema->getTableSchema('{{%user}}')->columns; // 欄位定義
$pk = $schema->getTableSchema('{{%user}}')->primaryKey; // 主鍵
// 重新整理 Schema 快取 (部署後執行)
Yii::$app->cache->flush(); // 或具體清除 schema cache key
use yii\db\Query;
$rows = (new Query())
->select(['id', 'title', 'view_count'])
->from('{{%post}}')
->where(['status' => 1])
->andWhere(['>', 'created_at', strtotime('-30 days')])
->orderBy(['view_count' => SORT_DESC])
->limit(10)
->offset(0)
->all($db); // 或 ->createCommand()->queryAll()
// JOIN
$query = (new Query())
->select(['p.title', 'c.name AS category'])
->from('{{%post}} p')
->innerJoin('{{%category}} c', 'p.category_id = c.id')
->where(['p.status' => 1]);
// GROUP BY / HAVING
$query->groupBy('c.id')->having(['>', 'COUNT(p.id)', 5]);
// UNION
$query1 = (new Query())->select('id, title')->from('{{%post}}')->where(['type' => 1]);
$query2 = (new Query())->select('id, title')->from('{{%post}}')->where(['type' => 2]);
$union = (new Query())->union([$query1, $query2])->all();
enableSchemaCache，生產環境設定 3600+ 秒。{{%table}} 避免硬編碼前綴，利於多環境部署。queryBatch() 或 DataReader，避免 queryAll() 佔滿記憶體。batchInsert() / upsert()，單筆循環 INSERT 極慢。EXPLAIN 分析慢查詢。$db->slave，寫入走 $db (master)。enableLogging/profiling。wait_timeout、啟用 PDO::ATTR_PERSISTENT 或重連邏輯。tablePrefix 設定與實際資料庫一致，或 SQL 中未使用 {{%table}}。upsert() 或先查後寫 (需交易鎖)。
