执行 SQL 查询
一旦拥有了 DB Connection 实例， 可以按照下列步骤来执行 SQL 查询：
使用纯SQL查询来创建出 yii\db\Command;
绑定参数 (可选的);
调用 yii\db\Command 里 SQL 执行方法中的一个。
下列例子展示了几种不同的从数据库取得数据的方法：
// 返回多行. 每行都是列名和值的关联数组.
// 如果该查询没有结果则返回空数组
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
->queryAll();
// 返回一行 (第一行)
// 如果该查询没有结果则返回 false
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
->queryOne();
// 返回一列 (第一列)
// 如果该查询没有结果则返回空数组
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
->queryColumn();
// 返回一个标量值
// 如果该查询没有结果则返回 false
$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 变量， 然后在之后的每次执行前改变变量的值（这通常是用循环来完成的）。 以这种方式执行查询比为每个不同的参数值执行一次新的查询要高效得多得多。
执行非查询语句
上面部分中介绍的 queryXyz() 方法都处理的是从数据库返回数据的查询语句。 对于那些不取回数据的语句， 应该调用的是 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 语句， 不再需要写纯SQL语句了， 可以直接调用 insert()、 update()、 delete()， 来构建相应的 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();
上述的方法只是构建出语句， 总是需要调用 execute() 来真正地执行它们。
引用表和列名称
当写与数据库无关的代码时， 正确地引用表和列名称总是一件头疼的事， 因为不同的数据库有不同的名称引用规则， 为了克服这个问题， 可以使用下面由 Yii 提出的引用语法。
[[column name]]: 使用两对方括号来将列名括起来;
{{table name}}: 使用两对大括号来将表名括起来。
Yii DAO 将自动地根据数据库的具体语法来将这些结构转化为对应的被引用的列或者表名称。 例如，
// 在 MySQL 中执行该 SQL : 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_',
],
],
];
接着在的代码中， 当需要涉及到一张表名中包含该前缀的表时， 应使用语法 {{%table_name}}。 百分号将被自动地替换为在配置 DB 组件时指定的表前缀。 例如，
// 在 MySQL 中执行该 SQL: 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;
}
通过调用 beginTransaction() 方法， 一个新事务开始了。 事务被表示为一个存储在 $transaction 变量中的 yii\db\Transaction 对象。 然后，被执行的语句都被包含在一个 try...catch... 块中。 如果所有的语句都被成功地执行了， commit() 将被调用来提交这个事务。 否则， 如果异常被触发并被捕获， rollBack() 方法将被调用， 来回滚事务中失败语句之前所有语句所造成的改变。 throw $e 将重新抛出该异常， 就好像我们没有捕获它一样， 因此正常的错误处理程序将处理它。
指定隔离级别
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 - 最强的隔离级别， 避免了上述所有的问题。
除了使用上述的常量来指定隔离级别， 还可以使用数据库所支持的具有有效语法的字符串。 比如，在 PostgreSQL 中， 可以使用 SERIALIZABLE READ ONLY DEFERRABLE。
请注意，一些数据库只允许为整个连接设置隔离级别， 即使之后什么也没指定， 后来的事务都将获得与之前相同的隔离级别。 使用此功能时，需要为所有的事务明确地设置隔离级别来避免冲突的设置。 在本文写作之时， 只有 MSSQL 和 SQLite 受这些限制的影响。
注意: SQLite 只支持两种隔离级别， 所以只能使用 READ UNCOMMITTED 和 SERIALIZABLE。 使用其他级别将导致异常的抛出。
注意: PostgreSQL 不支持在事务开启前设定隔离级别， 因此，不能在开启事务时直接指定隔离级别。 必须在事务开始后再调用 yii\db\Transaction::setIsolationLevel()。
嵌套事务
如果数据库支持保存点， 可以像下面这样嵌套多个事务：
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;
}
复制和读写分离
许多数据库支持数据库复制来获得更好的数据库可用性， 以及更快的服务器响应时间。 通过数据库复制功能， 数据从所谓的主服务器被复制到从服务器。 所有的写和更新必须发生在主服务器上， 而读可以发生在从服务器上。
为了利用数据库复制并且完成读写分离，可以按照下面的方法来配置 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'],
],
]
上述的配置指定了一主多从的设置。 这些从库其中之一将被建立起连接并执行读操作， 而主库将被用来执行写操作。 这样的读写分离将通过上述配置自动地完成。 比如，
// 使用上述配置来创建一个 Connection 实例
Yii::$app->db = Yii::createObject($config);
// 在从库中的一个上执行语句
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
// 在主库上执行语句
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();
Info: 通过调用 yii\db\Command::execute() 来执行的语句都被视为写操作， 而其他所有通过调用 yii\db\Command 中任一 "query" 方法来执行的语句都被视为读操作。 可以通过 Yii::$app->db->slave 来获取当前有效的从库连接。
Connection 组件支持从库间的负载均衡和失效备援， 当第一次执行读操作时， Connection 组件将随机地挑选出一个从库并尝试与之建立连接， 如果这个从库被发现为”挂掉的“， 将尝试连接另一个从库。 如果没有一个从库是连接得上的， 那么将试着连接到主库上。 通过配置 server status cache， 一个“挂掉的”服务器将会被记住， 因此，在一个 yii\db\Connection::serverRetryInterval 内将不再试着连接该服务器。
Info: 在上面的配置中， 每个从库都共同地指定了 10 秒的连接超时时间,如果一个从库在 10 秒内不能被连接上， 它将被视为“挂掉的”。 可以根据实际环境来调整该参数。
也可以配置多主多从。例如，
[
'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 属性来配置一个或多个主库时， 所有其他指定数据库连接的属性 (例如 dsn, username, password) 与 Connection 对象本身将被忽略。
默认情况下， 事务使用主库连接， 一个事务内， 所有的数据库操作都将使用主库连接， 例如，
$db = Yii::$app->db;
// 在主库上启动事务
$transaction = $db->beginTransaction();
try {
// 两个语句都是在主库上执行的
$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();
有时，或许想要强制使用主库来执行读查询。 这可以通过 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 提供了一套抽象数据类型来允许定义出数据库无关的模式。 这些将根据表所在数据库的种类， 被转换为特定的类型定义。
除了改变数据库模式， 也可以通过 DB Connection 的 getTableSchema() 方法来检索某张表的定义信息。 例如，
$table = Yii::$app->db->getTableSchema('post');
该方法返回一个 yii\db\TableSchema 对象， 它包含了表中的列、主键、外键，等等的信息。 所有的这些信息主要被 query builder 和 active record 所使用，来帮助写出数据库无关的代码。
网友评论