Yii-数据库

前言

Yii 包含了一个建立在 PHP PDO 之上的数据访问层 (DAO)。DAO为不同的数据库提供了一套统一的API。通过配置可以分析出用的是哪种数据库,然后使用对应的 Schema 来处理sql。各类之间的分工也比较明确 yii\db\Connection 用来连接数据库、创建事务, yii\db\Command 用来执行sql, yii\db\QueryBuilder 用来创建sql。
安全性也比较高,通过pdo预处理sql然后绑定参数,这就防止了sql注入的问题。

顺便说一下 AR 模式,因为继承 Model 所以同样拥有好用的字段验证,这在更新或者写入的时候非常好用,但是不好的地方是 AR 模型通过属性联表的操作,让操作变得不那么清晰。所以查询一般也就不推荐了。

db->createCommand() 执行sql时放注入写法

  1. 列名 [[field]] 将会被处理成 field;
  2. 表名 将会处理成 配置的表前缀+tablename 注意:这里省略了%,因为hexo的原因,不然会报错
  3. :name 将会被替换成传递的对应的参数,仅供参考,因为是通过pdo预处理执行的(这也是为什么会有防注入功能)
    1
    2
    3
    4
    5
    6
    7
    # 列名 [[field]] 将会被处理成 `field`;
    # 表名 {{%tablename}} 将会处理成 配置的表前缀+tablename
    # :name 将会被替换成传递的对应的参数,仅供参考,因为是通过pdo预处理执行的(这也是为什么会有防注入功能)
    $command = Yii::$app->db->createCommand('select [[ding]], [[pupu]] from {{%bunao}} where name = :name', [':name' => 'xxx']);
    # 获取sql语句
    # select `ding`, `pupu` from `meet_bunao` where name = 'xxx'
    echo $command->getRawSql();

清除表结构缓存

为了提高性能,yii会对表结构进行缓存,通过yii给的方法去修改表结构是会自动清除缓存的,但是也会存在手工修改表结构的,这时就需要清除一下表结构缓存

1
2
3
4
# 清除单个表结构缓存
Yii::$app->db->getSchema()->refreshTableSchema('tablename');
# 清除所有表结构缓存
Yii::$app->db->getSchema()->refresh();

Query 获取要执行的sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$command = (new \yii\db\Query())
->select(['id', 'email'])
->from('user')
->where(['last_name' => 'Smith'])
->limit(10)
->createCommand();
// 打印 SQL 语句,未绑定参数
echo $command->sql;
// 打印被绑定的参数
print_r($command->params);
// 也可以直接获取到完整的sql
echo $command->getRawSql();
// 返回查询结果的所有行
$rows = $command->queryAll();

批处理查询

先说一下为什么使用批量查询,当数据量非常大的时候,查询结果不可能全部放在内存中,需要一次一次的去数据库取,然后加载到内存中。
和limit有什么不同?
其实和limit的结果一样,但是有的时候更高效一些。首先limit你需要先计算分页,其次每次sql都数据库那边都要进行sql的预处理。而批量处理使用的是游标,sql就开始的那一句,只需要预处理一次,也不需要计算分页。但看一下下面的文档,批处理也会有一些问题。

官方文档这部分现在已经很完善了,下面全部摘自官方文档文档

当需要处理大数据的时候,像 yii\db\Query::all() 这样的方法就不太合适了, 因为它们会把所有查询的数据都读取到客户端内存上。为了解决这个问题, Yii 提供了批处理查询的支持。服务端先保存查询结果,然后客户端使用游标(cursor) 每次迭代出固定的一批结果集回来。

警告: MySQL 批处理查询的实现存在已知的局限性和变通方法。见下文。

批处理查询的用法如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use yii\db\Query;
$query = (new Query())
->from('user')
->orderBy('id');
foreach ($query->batch() as $users) {
// $users 是一个包含100条或小于100条用户表数据的数组
}
// or to iterate the row one by one
foreach ($query->each() as $user) {
// 数据从服务端中以 100 个为一组批量获取,
// 但是 $user 代表 user 表里的一行数据
}

yii\db\Query::batch()yii\db\Query::each() 方法将会返回一个实现了Iterator 接口 yii\db\BatchQueryResult 的对象,可以用在 foreach 结构当中使用。在第一次迭代取数据的时候, 数据库会执行一次 SQL 查询,然后在剩下的迭代中,将直接从结果集中批量获取数据。默认情况下, 一批的大小为 100,也就意味着一批获取的数据是 100 行。你可以通过给 batch() 或者 each() 方法的第一个参数传值来改变每批行数的大小。

相对于 yii\db\Query::all() 方法,批处理查询每次只读取 100 行的数据到内存。

如果你通过 yii\db\Query::indexBy() 方法为查询结果指定了索引字段, 那么批处理查询将仍然保持相对应的索引方案, 例如,

1
2
3
4
5
6
7
8
9
10
11
$query = (new \yii\db\Query())
->from('user')
->indexBy('username');
foreach ($query->batch() as $users) {
// $users 的 “username” 字段将会成为索引
}
foreach ($query->each() as $username => $user) {
// ...
}

MySQL中批量查询的局限性(Limitations of batch query in MySQL)

MySQL 是通过 PDO 驱动库实现批量查询的。默认情况下,MySQL 查询是 带缓存的, 这违背了使用游标(cursor)获取数据的目的, 因为它不阻止驱动程序将整个结果集加载到客户端的内存中。

注意: 当使用 libmysqlclient 时(PHP5 的标配),计算 PHP 的内存限制时,用于数据结果集的内存不会计算在内。 看上去批量查询是正确运行的,实际上整个数据集都被加载到了客户端的内存中, 而且这个使用量可能还会再增长。

要禁用缓存并减少客户端内存的需求量,PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 必须设置为 false。 这样,直到整个数据集被处理完毕前,通过此连接是无法创建其他查询的。 这样的操作可能会阻碍 ActiveRecord 执行表结构查询。 如果这不构成问题(表结构已被缓存过了), 我们可以通过切换原本的连接到非缓存模式,然后在批量查询完成后再切换回来。

1
2
3
4
5
Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
// 执行批量查询
Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

注意: 对于 MyISAM,在执行批量查询的过程中,表可能将被锁, 将延迟或拒绝其他连接的写入操作。 当使用非缓存查询时,尽量缩短游标打开的时间。
如果表结构没有被缓存,或在批量查询被处理过程中需要执行其他查询, 你可以创建一个单独的非缓存链接到数据库:

1
2
3
4
5
6
7
8
$unbufferedDb = new \yii\db\Connection([
'dsn' => Yii::$app->db->dsn,
'username' => Yii::$app->db->username,
'password' => Yii::$app->db->password,
'charset' => Yii::$app->db->charset,
]);
$unbufferedDb->open();
$unbufferedDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

如果你除了 PDO::MYSQL_ATTR_USE_BUFFERED_QUERYfalse 之外, 要确保 $unbufferedDb 拥有和原来缓存 $db 完全一样的属性, 请参阅实现 $db 的深度拷贝, 手动方法将它设置为 false 即可。

然后使用此连接正常创建查询,新连接用于运行批量查询, 逐条或批量进行结果处理:

1
2
3
4
5
6
7
8
9
10
// 获取 1000 为一组的批量数据
foreach ($query->batch(1000, $unbufferedDb) as $users) {
// ...
}
// 每次从服务端批量获取1000个数据,但是逐个遍历进行处理
foreach ($query->each(1000, $unbufferedDb) as $user) {
// ...
}

当结果集已处理完毕不再需要连接时,可以关闭它:

1
$unbufferedDb->close();

注意: 非缓存查询在 PHP 端使用更少的缓存,但会增加 MySQL 服务器端的负载。 建议您使用生产实践设计自己的代码以获取额外的海量数据,例如,将数字键分段,使用非缓存的查询遍历

AR模型实现乐观锁

乐观锁的原理比较简单。

  1. 在表中增加一个字段(如 ver )用来存锁值
  2. 取数据,主键 id 和 锁 ver 字段都要用到
  3. 更新的时候需要同时提交主键 id 和 锁 ver 进行更新,更新的时候会同时用上这两个条件,更新的同时 锁 ver 字段进行 +1

这就实现了可以多人同时读取,但是多人同时进行更新时由于 写入操作默认是加锁的所以同一时间只有一个可以写成功 ,而这个写入成功后由于 锁 ver 字段进行了 +1 ,所以更新条件也就变了,当时同时访问的其他人就无法进行更新操作了。

乐观锁实例

  1. 创建一个测试表
    下面给了一个简单的迁移操作
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    # 创建迁移
    yii migrate/create locking
    # 实现迁移
    public function safeUp()
    {
    // 创建表
    $this->createTable('clocking', [
    'id' => Schema::TYPE_PK,
    'title' => Schema::TYPE_STRING . ' NOT NULL',
    'ver' => Schema::TYPE_BIGINT . ' DEFAULT 0',
    ], ' ENGINE=InnoDB DEFAULT CHARSET=utf8');
    // 添加一条数据
    $this->insert('clocking', [
    'title' => 'test',
    ]);
    }
    public function safeDown()
    {
    return $this->dropTable('clocking');
    }
    # 执行迁移
    yii migrate

直接使用sql

1
2
3
4
5
6
7
8
CREATE TABLE `clocking` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`ver` bigint(20) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `clocking` VALUES ('1', 'test', '0');

  1. 创建 AR模型
    通过gii创建的,然后添加了返回锁字段的方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    class Clocking extends \yii\db\ActiveRecord
    {
    // 返回乐观锁字段
    public function optimisticLock()
    {
    return 'ver';
    }
    ...
    ...
    }
  2. 实现控制器逻辑和视图
    控制器动作逻辑

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    /**
    * 乐观锁测试
    * @return [type] [description]
    */
    public function actionLock($id)
    {
    // 指定布局文件
    $this->layout = '@app/views/layouts/main';
    $model = Clocking::findOne($id);
    try {
    if (Yii::$app->request->getIsPost() && $model->load(Yii::$app->request->post()) && $model->save()) {
    echo "更新成功";
    return;
    } else {
    return $this->render('lock', [
    'model' => $model,
    ]);
    }
    } catch (StaleObjectException $e) {
    echo "更新失败";
    // 解决冲突的代码
    }
    }

视图

要把锁值给视图,提交的时候进行提交

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
use yii\helpers\Html;
use yii\widgets\ActiveForm;
?>
<?php
$form = ActiveForm::begin([
'id' => 'login-form',
'options' => ['class' => 'form-horizontal'],
]) ?>
<?= $form->field($model, 'title') ?>
<!-- 存放隐藏的锁值 -->
<?=Html::activeHiddenInput($model, 'ver'); ?>
<div class="form-group">
<div class="col-lg-offset-1 col-lg-11">
<?= Html::submitButton('update', ['class' => 'btn btn-primary']) ?>
</div>
</div>
<?php ActiveForm::end() ?>

测试

  1. 先进行访问两次,不操作

  2. 更改其中一个进行提交,更新成功。锁值+1

  3. 更改另一个进行提交,更新失败。此时锁值不变

  4. 刷新、更改提交,更新成功。此时锁值+1

参考:
深入理解yii
官网文档

echo-ding wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!