執(zhí)行 SQL 查詢
一旦擁有了 DB Connection 實例， 可以按照下列步驟來執(zhí)行 SQL 查詢：
使用純SQL查詢來創(chuàng)建出 yii\db\Command;
綁定參數(shù) (可選的);
調用 yii\db\Command 里 SQL 執(zhí)行方法中的一個。
下列例子展示了幾種不同的從數(shù)據(jù)庫取得數(shù)據(jù)的方法：
// 返回多行. 每行都是列名和值的關聯(lián)數(shù)組.
// 如果該查詢沒有結果則返回空數(shù)組
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
->queryAll();
// 返回一行 (第一行)
// 如果該查詢沒有結果則返回 false
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
->queryOne();
// 返回一列 (第一列)
// 如果該查詢沒有結果則返回空數(shù)組
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
->queryColumn();
// 返回一個標量值
// 如果該查詢沒有結果則返回 false
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post')
->queryScalar();
注意: 為了保持精度， 即使對應的數(shù)據(jù)庫列類型為數(shù)值型， 所有從數(shù)據(jù)庫取得的數(shù)據(jù)都被表現(xiàn)為字符串。
綁定參數(shù)
當使用帶參數(shù)的 SQL 來創(chuàng)建數(shù)據(jù)庫命令時， 幾乎總是應該使用綁定參數(shù)的方法來防止 SQL 注入攻擊，例如：
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
->bindValue(':id', $_GET['id'])
->bindValue(':status', 1)
->queryOne();
在 SQL 語句中， 可以嵌入一個或多個參數(shù)占位符(例如，上述例子中的 :id )。 一個參數(shù)占位符應該是以冒號開頭的字符串。 之后可以調用下面綁定參數(shù)的方法來綁定參數(shù)值：
bindValue()：綁定一個參數(shù)值
bindValues()：在一次調用中綁定多個參數(shù)值
bindParam()：與 bindValue() 相似，但是也支持綁定參數(shù)引用。
下面的例子展示了幾個可供選擇的綁定參數(shù)的方法：
$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();
綁定參數(shù)是通過 預處理語句 實現(xiàn)的。 除了防止 SQL 注入攻擊， 它也可以通過一次預處理 SQL 語句， 使用不同參數(shù)多次執(zhí)行， 來提升性能。 例如：
$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id');
$post1 = $command->bindValue(':id', 1)->queryOne();
$post2 = $command->bindValue(':id', 2)->queryOne();
// ...
因為 bindParam() 支持通過引用來綁定參數(shù)， 上述代碼也可以像下面這樣寫：
$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id')
->bindParam(':id', $id);
$id = 1;
$post1 = $command->queryOne();
$id = 2;
$post2 = $command->queryOne();
// ...
請注意，在執(zhí)行語句前將占位符綁定到 $id 變量， 然后在之后的每次執(zhí)行前改變變量的值（這通常是用循環(huán)來完成的）。 以這種方式執(zhí)行查詢比為每個不同的參數(shù)值執(zhí)行一次新的查詢要高效得多得多。
執(zhí)行非查詢語句
上面部分中介紹的 queryXyz() 方法都處理的是從數(shù)據(jù)庫返回數(shù)據(jù)的查詢語句。 對于那些不取回數(shù)據(jù)的語句， 應該調用的是 yii\db\Command::execute() 方法。 例如，
Yii::$app->db->createCommand('UPDATE post SET status=1 WHERE id=1')
->execute();
yii\db\Command::execute() 方法返回執(zhí)行 SQL 所影響到的行數(shù)。
對于 INSERT, UPDATE 和 DELETE 語句， 不再需要寫純SQL語句了， 可以直接調用 insert()、 update()、 delete()， 來構建相應的 SQL 語句。 這些方法將正確地引用表和列名稱以及綁定參數(shù)值。 例如,
// 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();
上述的方法只是構建出語句， 總是需要調用 execute() 來真正地執(zhí)行它們。
引用表和列名稱
當寫與數(shù)據(jù)庫無關的代碼時， 正確地引用表和列名稱總是一件頭疼的事， 因為不同的數(shù)據(jù)庫有不同的名稱引用規(guī)則， 為了克服這個問題， 可以使用下面由 Yii 提出的引用語法。
[[column name]]: 使用兩對方括號來將列名括起來;
{{table name}}: 使用兩對大括號來將表名括起來。
Yii DAO 將自動地根據(jù)數(shù)據(jù)庫的具體語法來將這些結構轉化為對應的被引用的列或者表名稱。 例如，
// 在 MySQL 中執(zhí)行該 SQL : SELECT COUNT(`id`) FROM `employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{employee}}")
->queryScalar();
使用表前綴
如果的數(shù)據(jù)庫表名大多都擁有一個共同的前綴，可以使用 Yii DAO 所提供的表前綴功能。
首先，通過應用配置中的 yii\db\Connection::$tablePrefix 屬性來指定表前綴：
return [
// ...
'components' => [
// ...
'db' => [
// ...
'tablePrefix' => 'tbl_',
],
],
];
接著在的代碼中， 當需要涉及到一張表名中包含該前綴的表時， 應使用語法 {{%table_name}}。 百分號將被自動地替換為在配置 DB 組件時指定的表前綴。 例如，
// 在 MySQL 中執(zhí)行該 SQL: SELECT COUNT(`id`) FROM `tbl_employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{%employee}}")
->queryScalar();
執(zhí)行事務
當順序地執(zhí)行多個相關的語句時， 或許需要將它們包在一個事務中來保證數(shù)據(jù)庫的完整性和一致性。 如果這些語句中的任何一個失敗了， 數(shù)據(jù)庫將回滾到這些語句執(zhí)行前的狀態(tài)。
下面的代碼展示了一個使用事務的典型方法：
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;
}
通過調用 beginTransaction() 方法， 一個新事務開始了。 事務被表示為一個存儲在 $transaction 變量中的 yii\db\Transaction 對象。 然后，被執(zhí)行的語句都被包含在一個 try...catch... 塊中。 如果所有的語句都被成功地執(zhí)行了， commit() 將被調用來提交這個事務。 否則， 如果異常被觸發(fā)并被捕獲， rollBack() 方法將被調用， 來回滾事務中失敗語句之前所有語句所造成的改變。 throw $e 將重新拋出該異常， 就好像我們沒有捕獲它一樣， 因此正常的錯誤處理程序將處理它。
指定隔離級別
Yii 也支持事務設置隔離級別。 默認情況下， 當開啟一個新事務， 它將使用數(shù)據(jù)庫所設定的隔離級別。 也可以向下面這樣重載默認的隔離級別，
$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 - 最弱的隔離級別，臟讀、不可重復讀以及幻讀都可能發(fā)生。
yii\db\Transaction::READ_COMMITTED - 避免了臟讀。
yii\db\Transaction::REPEATABLE_READ - 避免了臟讀和不可重復讀。
yii\db\Transaction::SERIALIZABLE - 最強的隔離級別， 避免了上述所有的問題。
除了使用上述的常量來指定隔離級別， 還可以使用數(shù)據(jù)庫所支持的具有有效語法的字符串。 比如，在 PostgreSQL 中， 可以使用 SERIALIZABLE READ ONLY DEFERRABLE。
請注意，一些數(shù)據(jù)庫只允許為整個連接設置隔離級別， 即使之后什么也沒指定， 后來的事務都將獲得與之前相同的隔離級別。 使用此功能時，需要為所有的事務明確地設置隔離級別來避免沖突的設置。 在本文寫作之時， 只有 MSSQL 和 SQLite 受這些限制的影響。
注意: SQLite 只支持兩種隔離級別， 所以只能使用 READ UNCOMMITTED 和 SERIALIZABLE。 使用其他級別將導致異常的拋出。
注意: PostgreSQL 不支持在事務開啟前設定隔離級別， 因此，不能在開啟事務時直接指定隔離級別。 必須在事務開始后再調用 yii\db\Transaction::setIsolationLevel()。
嵌套事務
如果數(shù)據(jù)庫支持保存點， 可以像下面這樣嵌套多個事務：
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;
}
$outerTransaction->commit();
} catch (\Exception $e) {
$outerTransaction->rollBack();
throw $e;
}
復制和讀寫分離
許多數(shù)據(jù)庫支持數(shù)據(jù)庫復制來獲得更好的數(shù)據(jù)庫可用性， 以及更快的服務器響應時間。 通過數(shù)據(jù)庫復制功能， 數(shù)據(jù)從所謂的主服務器被復制到從服務器。 所有的寫和更新必須發(fā)生在主服務器上， 而讀可以發(fā)生在從服務器上。
為了利用數(shù)據(jù)庫復制并且完成讀寫分離，可以按照下面的方法來配置 yii\db\Connection 組件：
[
'class' => 'yii\db\Connection',
// 主庫的配置
'dsn' => 'dsn for master server',
'username' => 'master',
'password' => '',
// 從庫的通用配置
'slaveConfig' => [
'username' => 'slave',
'password' => '',
'attributes' => [
// 使用一個更小的連接超時
PDO::ATTR_TIMEOUT => 10,
],
],
// 從庫的配置列表
'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'],
],
]
上述的配置指定了一主多從的設置。 這些從庫其中之一將被建立起連接并執(zhí)行讀操作， 而主庫將被用來執(zhí)行寫操作。 這樣的讀寫分離將通過上述配置自動地完成。 比如，
// 使用上述配置來創(chuàng)建一個 Connection 實例
Yii::$app->db = Yii::createObject($config);
// 在從庫中的一個上執(zhí)行語句
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
// 在主庫上執(zhí)行語句
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();
Info: 通過調用 yii\db\Command::execute() 來執(zhí)行的語句都被視為寫操作， 而其他所有通過調用 yii\db\Command 中任一 "query" 方法來執(zhí)行的語句都被視為讀操作。 可以通過 Yii::$app->db->slave 來獲取當前有效的從庫連接。
Connection 組件支持從庫間的負載均衡和失效備援， 當?shù)谝淮螆?zhí)行讀操作時， Connection 組件將隨機地挑選出一個從庫并嘗試與之建立連接， 如果這個從庫被發(fā)現(xiàn)為”掛掉的“， 將嘗試連接另一個從庫。 如果沒有一個從庫是連接得上的， 那么將試著連接到主庫上。 通過配置 server status cache， 一個“掛掉的”服務器將會被記住， 因此，在一個 yii\db\Connection::serverRetryInterval 內將不再試著連接該服務器。
Info: 在上面的配置中， 每個從庫都共同地指定了 10 秒的連接超時時間,如果一個從庫在 10 秒內不能被連接上， 它將被視為“掛掉的”。 可以根據(jù)實際環(huán)境來調整該參數(shù)。
也可以配置多主多從。例如，
[
'class' => 'yii\db\Connection',
// 主庫通用的配置
'masterConfig' => [
'username' => 'master',
'password' => '',
'attributes' => [
// use a smaller connection timeout
PDO::ATTR_TIMEOUT => 10,
],
],
// 主庫配置列表
'masters' => [
['dsn' => 'dsn for master server 1'],
['dsn' => 'dsn for master server 2'],
],
// 從庫的通用配置
'slaveConfig' => [
'username' => 'slave',
'password' => '',
'attributes' => [
// use a smaller connection timeout
PDO::ATTR_TIMEOUT => 10,
],
],
// 從庫配置列表
'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 屬性來配置一個或多個主庫時， 所有其他指定數(shù)據(jù)庫連接的屬性 (例如 dsn, username, password) 與 Connection 對象本身將被忽略。
默認情況下， 事務使用主庫連接， 一個事務內， 所有的數(shù)據(jù)庫操作都將使用主庫連接， 例如，
$db = Yii::$app->db;
// 在主庫上啟動事務
$transaction = $db->beginTransaction();
try {
// 兩個語句都是在主庫上執(zhí)行的
$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;
}
如果想在從庫上開啟事務，應該明確地像下面這樣做：
$transaction = Yii::$app->db->slave->beginTransaction();
有時，或許想要強制使用主庫來執(zhí)行讀查詢。 這可以通過 useMaster() 方法來完成：
$rows = Yii::$app->db->useMaster(function ($db) {
return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});
也可以明確地將 `Yii::$app->db->enableSlaves` 設置為 false 來將所有的讀操作指向主庫連接。
操縱數(shù)據(jù)庫模式
Yii DAO 提供了一套完整的方法來操縱數(shù)據(jù)庫模式， 如創(chuàng)建表、從表中刪除一列，等等。這些方法羅列如下：
createTable()：創(chuàng)建一張表
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',
]);
上面的數(shù)組描述要創(chuàng)建的列的名稱和類型。 對于列的類型， Yii 提供了一套抽象數(shù)據(jù)類型來允許定義出數(shù)據(jù)庫無關的模式。 這些將根據(jù)表所在數(shù)據(jù)庫的種類， 被轉換為特定的類型定義。
除了改變數(shù)據(jù)庫模式， 也可以通過 DB Connection 的 getTableSchema() 方法來檢索某張表的定義信息。 例如，
$table = Yii::$app->db->getTableSchema('post');
該方法返回一個 yii\db\TableSchema 對象， 它包含了表中的列、主鍵、外鍵，等等的信息。 所有的這些信息主要被 query builder 和 active record 所使用，來幫助寫出數(shù)據(jù)庫無關的代碼。
