yii2.0--执行非查询语句

转载于 2019-08-06 18:43:03 发布 · 614 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://www.yiichina.com/doc/guide/2.0/db-dao

Yii2.0 专栏收录该内容

63 篇文章

订阅专栏

本文详细介绍了如何在Yii框架中进行数据库操作，包括创建数据库连接、执行SQL查询、使用预处理语句防止SQL注入，以及如何高效执行批量插入和更新操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

对于那些不取回数据的语句，你应该调用的是 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();

数据库访问对象（Database Access Objects）

Yii 包含了一个建立在 PHP PDO 之上的数据访问层 (DAO)。DAO为不同的数据库提供了一套统一的API。其中 ActiveRecord 提供了数据库与模型(MVC 中的 M,Model) 的交互，QueryBuilder 用于创建动态的查询语句。 DAO提供了简单高效的SQL查询，可以用在与数据库交互的各个地方.

使用 Yii DAO 时，你主要需要处理纯 SQL 语句和 PHP 数组。因此，这是访问数据库最高效的方法。然而，因为不同数据库之间的 SQL 语法往往是不同的，使用 Yii DAO 也意味着你必须花些额外的工夫来创建一个”数据库无关“的应用。

Yii DAO 支持下列现成的数据库：

MySQL
MariaDB
SQLite
PostgreSQL：版本 8.4 或更高
CUBRID：版本 9.3 或更高。
Oracle
MSSQL：版本 2008 或更高。

信息： 在Yii 2.1及更高版本中，DAO 支持 CUBRID，Oracle 和 MSSQL 不再作为框架的内置核心组件提供。它们必须作为独离的扩展安装。 yiisoft/yii2-oracle 和 yiisoft/yii2-mssql 都属于官方扩展。

注意： 供 PHP 7 使用的新版 pdo_oci 扩展目前仅发布了源代码，如果你想编译使用请参照社区用户提供的编译安装指引。或者你也可以在你的应用中使用 PDO模拟层。

创建数据库连接（Creating DB Connections）

想要访问数据库，你首先需要通过创建一个 yii\db\Connection 实例来与之建立连接。

$db = new yii\db\Connection([
    'dsn' => 'mysql:host=localhost;dbname=example',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
]);

因为数据库连接经常需要在多个地方使用到，一个常见的做法是以应用组件的方式来配置它，如下:

return [
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=example',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ],
    ],
    // ...
];

之后你就可以通过语句 Yii::$app->db 来使用数据库连接了。

提示： 如果你的应用需要访问多个数据库，你可以配置多个 DB 应用组件。

配置数据库连接时，你应该总是通过 dsn 属性来指明它的数据源名称 (DSN) 。不同的数据库有着不同的 DSN 格式。请参考 PHP manual 来获得更多细节。下面是一些例子：

MySQL, MariaDB: mysql:host=localhost;dbname=mydatabase
SQLite: sqlite:/path/to/database/file
PostgreSQL: pgsql:host=localhost;port=5432;dbname=mydatabase
CUBRID: cubrid:dbname=demodb;host=localhost;port=33000
MS SQL Server (via sqlsrv driver): sqlsrv:Server=localhost;Database=mydatabase
MS SQL Server (via dblib driver): dblib:host=localhost;dbname=mydatabase
MS SQL Server (via mssql driver): mssql:host=localhost;dbname=mydatabase
Oracle: oci:dbname=//localhost:1521/mydatabase

请注意，如果你是通过 ODBC 来连接数据库，你应该配置 yii\db\Connection::$driverName 属性，以便 Yii 能够知道实际的数据库种类。例如：

'db' => [
    'class' => 'yii\db\Connection',
    'driverName' => 'mysql',
    'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
    'username' => 'root',
    'password' => '',
],

除了 dsn 属性，你常常需要配置 username 和 password。请参考 yii\db\Connection 来获取完整的可配置属性列表。

信息： 当你实例化一个 DB Connection 时，直到你第一次执行 SQL 或者你明确地调用 open() 方法时，才建立起实际的数据库连接。

提示： 有时你可能想要在建立起数据库连接时立即执行一些语句来初始化一些环境变量 (比如设置时区或者字符集), 你可以通过为数据库连接的 afterOpen 事件注册一个事件处理器来达到目的。你可以像这样直接在应用配置中注册处理器：
'db' => [
    // ...
    'on afterOpen' => function($event) {
        // $event->sender refers to the DB connection
        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
    }
],

执行 SQL 查询（Executing SQL Queries）

一旦你拥有了 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();

注意： 为了保持精度，即使对应的数据库列类型为数值型，所有从数据库取得的数据都被表现为字符串。

绑定参数（Binding Parameters）

当使用带参数的 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 变量，然后在之后的每次执行前改变变量的值（这通常是用循环来完成的）。以这种方式执行查询比为每个不同的参数值执行一次新的查询要高效得多得多。

信息： 参数绑定仅用于需要将值插入到包含普通SQL的字符串中的地方。在 Query Builder 和 Active Record 等更高抽象层的许多地方，您经常会指定一组将被转换为 SQL 的值。在这些地方，参数绑定是由 Yii 内部完成的，因此不需要手动指定参数。

执行非查询语句（Executing Non-SELECT Queries）

上面部分中介绍的 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();

另一个有用的方法是 upsert()。Upsert 是一种原子操作，如果它们不存在（匹配唯一约束），则将行插入到数据库表中，或者在它们执行时更新它们：

Yii::$app->db->createCommand()->upsert('pages', [
    'name' => 'Front page',
    'url' => 'http://example.com/', // url is unique
    'visits' => 0,
], [
    'visits' => new \yii\db\Expression('visits + 1'),
], $params)->execute();

上面的代码将插入一个新的页面记录或自动增加访问计数器。

请注意，上述的方法只是构建出语句，你总是需要调用 execute() 来真正地执行它们。