執(zhí)行 SQL 查詢(xún)
一旦擁有了 DB Connection 實(shí)例叠赐， 可以按照下列步驟來(lái)執(zhí)行 SQL 查詢(xún)：
使用純SQL查詢(xún)來(lái)創(chuàng)建出 yii\db\Command;
綁定參數(shù) (可選的);
調(diào)用 yii\db\Command 里 SQL 執(zhí)行方法中的一個(gè)路呜。
下列例子展示了幾種不同的從數(shù)據(jù)庫(kù)取得數(shù)據(jù)的方法：
// 返回多行. 每行都是列名和值的關(guān)聯(lián)數(shù)組.
// 如果該查詢(xún)沒(méi)有結(jié)果則返回空數(shù)組
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
->queryAll();
// 返回一行 (第一行)
// 如果該查詢(xún)沒(méi)有結(jié)果則返回 false
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
->queryOne();
// 返回一列 (第一列)
// 如果該查詢(xún)沒(méi)有結(jié)果則返回空數(shù)組
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
->queryColumn();
// 返回一個(gè)標(biāo)量值
// 如果該查詢(xún)沒(méi)有結(jié)果則返回 false
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post')
->queryScalar();
注意: 為了保持精度赞草， 即使對(duì)應(yīng)的數(shù)據(jù)庫(kù)列類(lèi)型為數(shù)值型离赫， 所有從數(shù)據(jù)庫(kù)取得的數(shù)據(jù)都被表現(xiàn)為字符串滥玷。
綁定參數(shù)
當(dāng)使用帶參數(shù)的 SQL 來(lái)創(chuàng)建數(shù)據(jù)庫(kù)命令時(shí)杠巡， 幾乎總是應(yīng)該使用綁定參數(shù)的方法來(lái)防止 SQL 注入攻擊床估，例如：
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
->bindValue(':id', $_GET['id'])
->bindValue(':status', 1)
->queryOne();
在 SQL 語(yǔ)句中， 可以嵌入一個(gè)或多個(gè)參數(shù)占位符(例如痰哨，上述例子中的 :id )胶果。 一個(gè)參數(shù)占位符應(yīng)該是以冒號(hào)開(kāi)頭的字符串。 之后可以調(diào)用下面綁定參數(shù)的方法來(lái)綁定參數(shù)值：
bindValue()：綁定一個(gè)參數(shù)值
bindValues()：在一次調(diào)用中綁定多個(gè)參數(shù)值
bindParam()：與 bindValue() 相似斤斧，但是也支持綁定參數(shù)引用早抠。
下面的例子展示了幾個(gè)可供選擇的綁定參數(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ù)是通過(guò) 預(yù)處理語(yǔ)句 實(shí)現(xiàn)的。 除了防止 SQL 注入攻擊撬讽， 它也可以通過(guò)一次預(yù)處理 SQL 語(yǔ)句蕊连， 使用不同參數(shù)多次執(zhí)行， 來(lái)提升性能游昼。 例如：
$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id');
$post1 = $command->bindValue(':id', 1)->queryOne();
$post2 = $command->bindValue(':id', 2)->queryOne();
// ...
因?yàn)?bindParam() 支持通過(guò)引用來(lái)綁定參數(shù)甘苍， 上述代碼也可以像下面這樣寫(xiě)：
$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id')
->bindParam(':id', $id);
$id = 1;
$post1 = $command->queryOne();
$id = 2;
$post2 = $command->queryOne();
// ...
請(qǐng)注意，在執(zhí)行語(yǔ)句前將占位符綁定到 $id 變量烘豌， 然后在之后的每次執(zhí)行前改變變量的值（這通常是用循環(huán)來(lái)完成的）载庭。 以這種方式執(zhí)行查詢(xún)比為每個(gè)不同的參數(shù)值執(zhí)行一次新的查詢(xún)要高效得多得多。
執(zhí)行非查詢(xún)語(yǔ)句
上面部分中介紹的 queryXyz() 方法都處理的是從數(shù)據(jù)庫(kù)返回?cái)?shù)據(jù)的查詢(xún)語(yǔ)句廊佩。 對(duì)于那些不取回?cái)?shù)據(jù)的語(yǔ)句囚聚， 應(yīng)該調(diào)用的是 yii\db\Command::execute() 方法。 例如标锄，
Yii::$app->db->createCommand('UPDATE post SET status=1 WHERE id=1')
->execute();
yii\db\Command::execute() 方法返回執(zhí)行 SQL 所影響到的行數(shù)顽铸。
對(duì)于 INSERT, UPDATE 和 DELETE 語(yǔ)句， 不再需要寫(xiě)純SQL語(yǔ)句了料皇， 可以直接調(diào)用 insert()跋破、 update()簸淀、 delete()， 來(lái)構(gòu)建相應(yīng)的 SQL 語(yǔ)句毒返。 這些方法將正確地引用表和列名稱(chēng)以及綁定參數(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();
也可以調(diào)用 batchInsert() 來(lái)一次插入多行， 這比一次插入一行要高效得多：
// table name, column names, column values
Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
])->execute();
上述的方法只是構(gòu)建出語(yǔ)句拧簸， 總是需要調(diào)用 execute() 來(lái)真正地執(zhí)行它們劲绪。
引用表和列名稱(chēng)
當(dāng)寫(xiě)與數(shù)據(jù)庫(kù)無(wú)關(guān)的代碼時(shí)， 正確地引用表和列名稱(chēng)總是一件頭疼的事盆赤， 因?yàn)椴煌臄?shù)據(jù)庫(kù)有不同的名稱(chēng)引用規(guī)則贾富， 為了克服這個(gè)問(wèn)題， 可以使用下面由 Yii 提出的引用語(yǔ)法牺六。
[[column name]]: 使用兩對(duì)方括號(hào)來(lái)將列名括起來(lái);
{{table name}}: 使用兩對(duì)大括號(hào)來(lái)將表名括起來(lái)颤枪。
Yii DAO 將自動(dòng)地根據(jù)數(shù)據(jù)庫(kù)的具體語(yǔ)法來(lái)將這些結(jié)構(gòu)轉(zhuǎn)化為對(duì)應(yīng)的被引用的列或者表名稱(chēng)。 例如淑际，
// 在 MySQL 中執(zhí)行該 SQL : SELECT COUNT(`id`) FROM `employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{employee}}")
->queryScalar();
使用表前綴
如果的數(shù)據(jù)庫(kù)表名大多都擁有一個(gè)共同的前綴畏纲，可以使用 Yii DAO 所提供的表前綴功能。
首先春缕，通過(guò)應(yīng)用配置中的 yii\db\Connection::$tablePrefix 屬性來(lái)指定表前綴：
return [
// ...
'components' => [
// ...
'db' => [
// ...
'tablePrefix' => 'tbl_',
],
],
];
接著在的代碼中盗胀， 當(dāng)需要涉及到一張表名中包含該前綴的表時(shí)， 應(yīng)使用語(yǔ)法 {{%table_name}}锄贼。 百分號(hào)將被自動(dòng)地替換為在配置 DB 組件時(shí)指定的表前綴票灰。 例如，
// 在 MySQL 中執(zhí)行該 SQL: SELECT COUNT(`id`) FROM `tbl_employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{%employee}}")
->queryScalar();
執(zhí)行事務(wù)
當(dāng)順序地執(zhí)行多個(gè)相關(guān)的語(yǔ)句時(shí)宅荤， 或許需要將它們包在一個(gè)事務(wù)中來(lái)保證數(shù)據(jù)庫(kù)的完整性和一致性屑迂。 如果這些語(yǔ)句中的任何一個(gè)失敗了， 數(shù)據(jù)庫(kù)將回滾到這些語(yǔ)句執(zhí)行前的狀態(tài)冯键。
下面的代碼展示了一個(gè)使用事務(wù)的典型方法：
Yii::$app->db->transaction(function($db) {
$db->createCommand($sql1)->execute();
$db->createCommand($sql2)->execute();
// ... executing other SQL statements ...
});
上述代碼等價(jià)于下面的代碼惹盼， 但是下面的代碼給予了對(duì)于錯(cuò)誤處理代碼的更多掌控：
$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;
}
通過(guò)調(diào)用 beginTransaction() 方法， 一個(gè)新事務(wù)開(kāi)始了琼了。 事務(wù)被表示為一個(gè)存儲(chǔ)在 $transaction 變量中的 yii\db\Transaction 對(duì)象。 然后夫晌，被執(zhí)行的語(yǔ)句都被包含在一個(gè) try...catch... 塊中雕薪。 如果所有的語(yǔ)句都被成功地執(zhí)行了， commit() 將被調(diào)用來(lái)提交這個(gè)事務(wù)晓淀。 否則所袁， 如果異常被觸發(fā)并被捕獲， rollBack() 方法將被調(diào)用凶掰， 來(lái)回滾事務(wù)中失敗語(yǔ)句之前所有語(yǔ)句所造成的改變燥爷。 throw $e 將重新拋出該異常蜈亩， 就好像我們沒(méi)有捕獲它一樣， 因此正常的錯(cuò)誤處理程序?qū)⑻幚硭?/p>
指定隔離級(jí)別
Yii 也支持事務(wù)設(shè)置隔離級(jí)別前翎。 默認(rèn)情況下稚配， 當(dāng)開(kāi)啟一個(gè)新事務(wù)， 它將使用數(shù)據(jù)庫(kù)所設(shè)定的隔離級(jí)別港华。 也可以向下面這樣重載默認(rèn)的隔離級(jí)別道川，
$isolationLevel = \yii\db\Transaction::REPEATABLE_READ;
Yii::$app->db->transaction(function ($db) {
....
}, $isolationLevel);
// or alternatively
$transaction = Yii::$app->db->beginTransaction($isolationLevel);
Yii 為四個(gè)最常用的隔離級(jí)別提供了常量：
yii\db\Transaction::READ_UNCOMMITTED - 最弱的隔離級(jí)別，臟讀立宜、不可重復(fù)讀以及幻讀都可能發(fā)生冒萄。
yii\db\Transaction::READ_COMMITTED - 避免了臟讀。
yii\db\Transaction::REPEATABLE_READ - 避免了臟讀和不可重復(fù)讀橙数。
yii\db\Transaction::SERIALIZABLE - 最強(qiáng)的隔離級(jí)別尊流， 避免了上述所有的問(wèn)題。
除了使用上述的常量來(lái)指定隔離級(jí)別灯帮， 還可以使用數(shù)據(jù)庫(kù)所支持的具有有效語(yǔ)法的字符串崖技。 比如，在 PostgreSQL 中施流， 可以使用 SERIALIZABLE READ ONLY DEFERRABLE响疚。
請(qǐng)注意，一些數(shù)據(jù)庫(kù)只允許為整個(gè)連接設(shè)置隔離級(jí)別瞪醋， 即使之后什么也沒(méi)指定忿晕， 后來(lái)的事務(wù)都將獲得與之前相同的隔離級(jí)別。 使用此功能時(shí)银受，需要為所有的事務(wù)明確地設(shè)置隔離級(jí)別來(lái)避免沖突的設(shè)置践盼。 在本文寫(xiě)作之時(shí)， 只有 MSSQL 和 SQLite 受這些限制的影響宾巍。
注意: SQLite 只支持兩種隔離級(jí)別咕幻， 所以只能使用 READ UNCOMMITTED 和 SERIALIZABLE。 使用其他級(jí)別將導(dǎo)致異常的拋出顶霞。
注意: PostgreSQL 不支持在事務(wù)開(kāi)啟前設(shè)定隔離級(jí)別肄程， 因此，不能在開(kāi)啟事務(wù)時(shí)直接指定隔離級(jí)別选浑。 必須在事務(wù)開(kāi)始后再調(diào)用 yii\db\Transaction::setIsolationLevel()蓝厌。
嵌套事務(wù)
如果數(shù)據(jù)庫(kù)支持保存點(diǎn)， 可以像下面這樣嵌套多個(gè)事務(wù)：
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;
}
復(fù)制和讀寫(xiě)分離
許多數(shù)據(jù)庫(kù)支持?jǐn)?shù)據(jù)庫(kù)復(fù)制來(lái)獲得更好的數(shù)據(jù)庫(kù)可用性拓提， 以及更快的服務(wù)器響應(yīng)時(shí)間。 通過(guò)數(shù)據(jù)庫(kù)復(fù)制功能隧膘， 數(shù)據(jù)從所謂的主服務(wù)器被復(fù)制到從服務(wù)器代态。 所有的寫(xiě)和更新必須發(fā)生在主服務(wù)器上寺惫， 而讀可以發(fā)生在從服務(wù)器上。
為了利用數(shù)據(jù)庫(kù)復(fù)制并且完成讀寫(xiě)分離，可以按照下面的方法來(lái)配置 yii\db\Connection 組件：
[
'class' => 'yii\db\Connection',
// 主庫(kù)的配置
'dsn' => 'dsn for master server',
'username' => 'master',
'password' => '',
// 從庫(kù)的通用配置
'slaveConfig' => [
'username' => 'slave',
'password' => '',
'attributes' => [
// 使用一個(gè)更小的連接超時(shí)
PDO::ATTR_TIMEOUT => 10,
],
],
// 從庫(kù)的配置列表
'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'],
],
]
上述的配置指定了一主多從的設(shè)置。 這些從庫(kù)其中之一將被建立起連接并執(zhí)行讀操作麸折， 而主庫(kù)將被用來(lái)執(zhí)行寫(xiě)操作航攒。 這樣的讀寫(xiě)分離將通過(guò)上述配置自動(dòng)地完成。 比如，
// 使用上述配置來(lái)創(chuàng)建一個(gè) Connection 實(shí)例
Yii::$app->db = Yii::createObject($config);
// 在從庫(kù)中的一個(gè)上執(zhí)行語(yǔ)句
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
// 在主庫(kù)上執(zhí)行語(yǔ)句
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();
Info: 通過(guò)調(diào)用 yii\db\Command::execute() 來(lái)執(zhí)行的語(yǔ)句都被視為寫(xiě)操作， 而其他所有通過(guò)調(diào)用 yii\db\Command 中任一 "query" 方法來(lái)執(zhí)行的語(yǔ)句都被視為讀操作。 可以通過(guò) Yii::$app->db->slave 來(lái)獲取當(dāng)前有效的從庫(kù)連接豆挽。
Connection 組件支持從庫(kù)間的負(fù)載均衡和失效備援， 當(dāng)?shù)谝淮螆?zhí)行讀操作時(shí)券盅， Connection 組件將隨機(jī)地挑選出一個(gè)從庫(kù)并嘗試與之建立連接帮哈， 如果這個(gè)從庫(kù)被發(fā)現(xiàn)為”掛掉的“， 將嘗試連接另一個(gè)從庫(kù)锰镀。 如果沒(méi)有一個(gè)從庫(kù)是連接得上的娘侍， 那么將試著連接到主庫(kù)上。 通過(guò)配置 server status cache泳炉， 一個(gè)“掛掉的”服務(wù)器將會(huì)被記住憾筏， 因此，在一個(gè) yii\db\Connection::serverRetryInterval 內(nèi)將不再試著連接該服務(wù)器花鹅。
Info: 在上面的配置中氧腰， 每個(gè)從庫(kù)都共同地指定了 10 秒的連接超時(shí)時(shí)間,如果一個(gè)從庫(kù)在 10 秒內(nèi)不能被連接上， 它將被視為“掛掉的”刨肃。 可以根據(jù)實(shí)際環(huán)境來(lái)調(diào)整該參數(shù)古拴。
也可以配置多主多從。例如真友，
[
'class' => 'yii\db\Connection',
// 主庫(kù)通用的配置
'masterConfig' => [
'username' => 'master',
'password' => '',
'attributes' => [
// use a smaller connection timeout
PDO::ATTR_TIMEOUT => 10,
],
],
// 主庫(kù)配置列表
'masters' => [
['dsn' => 'dsn for master server 1'],
['dsn' => 'dsn for master server 2'],
],
// 從庫(kù)的通用配置
'slaveConfig' => [
'username' => 'slave',
'password' => '',
'attributes' => [
// use a smaller connection timeout
PDO::ATTR_TIMEOUT => 10,
],
],
// 從庫(kù)配置列表
'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'],
],
]
上述配置指定了兩個(gè)主庫(kù)和兩個(gè)從庫(kù)黄痪。 Connection 組件在主庫(kù)之間， 也支持如從庫(kù)間般的負(fù)載均衡和失效備援盔然。 唯一的差別是桅打， 如果沒(méi)有主庫(kù)可用，將拋出一個(gè)異常愈案。
注意: 當(dāng)使用 masters 屬性來(lái)配置一個(gè)或多個(gè)主庫(kù)時(shí)挺尾， 所有其他指定數(shù)據(jù)庫(kù)連接的屬性 (例如 dsn, username, password) 與 Connection 對(duì)象本身將被忽略。
默認(rèn)情況下刻帚， 事務(wù)使用主庫(kù)連接潦嘶， 一個(gè)事務(wù)內(nèi)涩嚣， 所有的數(shù)據(jù)庫(kù)操作都將使用主庫(kù)連接崇众， 例如掂僵，
$db = Yii::$app->db;
// 在主庫(kù)上啟動(dòng)事務(wù)
$transaction = $db->beginTransaction();
try {
// 兩個(gè)語(yǔ)句都是在主庫(kù)上執(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;
}
如果想在從庫(kù)上開(kāi)啟事務(wù)，應(yīng)該明確地像下面這樣做：
$transaction = Yii::$app->db->slave->beginTransaction();
有時(shí)顷歌，或許想要強(qiáng)制使用主庫(kù)來(lái)執(zhí)行讀查詢(xún)锰蓬。 這可以通過(guò) useMaster() 方法來(lái)完成：
$rows = Yii::$app->db->useMaster(function ($db) {
return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});
也可以明確地將 `Yii::$app->db->enableSlaves` 設(shè)置為 false 來(lái)將所有的讀操作指向主庫(kù)連接。
操縱數(shù)據(jù)庫(kù)模式
Yii DAO 提供了一套完整的方法來(lái)操縱數(shù)據(jù)庫(kù)模式眯漩， 如創(chuàng)建表芹扭、從表中刪除一列，等等赦抖。這些方法羅列如下：
createTable()：創(chuàng)建一張表
renameTable()：重命名一張表
dropTable()：刪除一張表
truncateTable()：刪除一張表中的所有行
addColumn()：增加一列
renameColumn()：重命名一列
dropColumn()：刪除一列
alterColumn()：修改一列
addPrimaryKey()：增加主鍵
dropPrimaryKey()：刪除主鍵
addForeignKey()：增加一個(gè)外鍵
dropForeignKey()：刪除一個(gè)外鍵
createIndex()：增加一個(gè)索引
dropIndex()：刪除一個(gè)索引
這些方法可以如下地使用：
// CREATE TABLE
Yii::$app->db->createCommand()->createTable('post', [
'id' => 'pk',
'title' => 'string',
'text' => 'text',
]);
上面的數(shù)組描述要?jiǎng)?chuàng)建的列的名稱(chēng)和類(lèi)型舱卡。 對(duì)于列的類(lèi)型， Yii 提供了一套抽象數(shù)據(jù)類(lèi)型來(lái)允許定義出數(shù)據(jù)庫(kù)無(wú)關(guān)的模式队萤。 這些將根據(jù)表所在數(shù)據(jù)庫(kù)的種類(lèi)轮锥， 被轉(zhuǎn)換為特定的類(lèi)型定義。
除了改變數(shù)據(jù)庫(kù)模式要尔， 也可以通過(guò) DB Connection 的 getTableSchema() 方法來(lái)檢索某張表的定義信息舍杜。 例如，
$table = Yii::$app->db->getTableSchema('post');
該方法返回一個(gè) yii\db\TableSchema 對(duì)象赵辕， 它包含了表中的列既绩、主鍵、外鍵还惠，等等的信息饲握。 所有的這些信息主要被 query builder 和 active record 所使用，來(lái)幫助寫(xiě)出數(shù)據(jù)庫(kù)無(wú)關(guān)的代碼吸重。
