3 关注者

数据库访问对象 ¶

创建数据库连接
执行 SQL 查询
引用表和列名
执行事务
复制和读写分离
使用数据库模式

Yii DAO（数据库访问对象）构建于 PDO 之上，提供了一个面向对象的 API 用于访问关系型数据库。它是其他更高级数据库访问方法的基础，包括查询构建器和活动记录。

使用 Yii DAO 时，您主要需要处理纯 SQL 和 PHP 数组。因此，它是访问数据库最有效的方式。但是，由于不同数据库的 SQL 语法可能有所不同，使用 Yii DAO 也意味着您需要付出额外的努力来创建与数据库无关的应用程序。

在 Yii 2.0 中，DAO 原生支持以下数据库

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

注意：PHP 7 的新版 pdo_oci 目前仅以源代码形式存在。请按照社区提供的说明进行编译，或者使用 PDO 模拟层。

创建数据库连接 ¶

要访问数据库，您首先需要通过创建 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 访问数据库连接。

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

在配置数据库连接时，您应该始终通过 dsn 属性指定其数据源名称 (DSN)。不同数据库的 DSN 格式各不相同。请参阅 PHP 手册以了解更多详细信息。以下是一些示例

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（通过 sqlsrv 驱动程序）：sqlsrv:Server=localhost;Database=mydatabase
MS SQL Server（通过 dblib 驱动程序）：dblib:host=localhost;dbname=mydatabase
MS SQL Server（通过 mssql 驱动程序）：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 连接实例时，直到您执行第一个 SQL 语句或显式调用 open() 方法，才会建立与数据库的实际连接。

提示：有时您可能希望在建立数据库连接后立即执行一些查询来初始化一些环境变量（例如，设置时区或字符集）。您可以通过为数据库连接的 afterOpen 事件注册一个事件处理程序来做到这一点。您可以在应用程序配置中直接注册处理程序，如下所示：
'db' => [
    // ...
    'on afterOpen' => function($event) {
        // $event->sender refers to the DB connection
        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
    }
],

对于 MS SQL Server，需要额外的连接选项才能正确处理二进制数据。

'db' => [
 'class' => 'yii\db\Connection',
    'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase',
    'attributes' => [
        \PDO::SQLSRV_ATTR_ENCODING => \PDO::SQLSRV_ENCODING_SYSTEM
    ]
],

执行 SQL 查询 ¶

获得数据库连接实例后，您可以通过以下步骤执行 SQL 查询：

使用普通 SQL 查询创建一个 yii\db\Command；
绑定参数（可选）；
在 yii\db\Command 中调用其中一个 SQL 执行方法。

以下示例演示了从数据库中获取数据的各种方法：

// return a set of rows. each row is an associative array of column names and values.
// an empty array is returned if the query returned no results
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
            ->queryAll();

// return a single row (the first row)
// false is returned if the query has no result
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
           ->queryOne();

// return a single column (the first column)
// an empty array is returned if the query returned no results
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
             ->queryColumn();

// return a scalar value
// false is returned if the query has no result
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post')
             ->queryScalar();

注意：为了保持精度，从数据库中获取的数据都表示为字符串，即使相应的数据库列类型为数字。

绑定参数 ¶

从包含参数的 SQL 创建 DB 命令时，您几乎总是应该使用绑定参数的方法来防止 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 的字符串的位置。在查询构建器和活动记录等更高抽象层中的许多地方，您通常指定一个值数组，这些值将被转换为 SQL。在这些地方，参数绑定由 Yii 内部完成，因此无需手动指定参数。

执行非 SELECT 查询 ¶

上一节中介绍的 queryXyz() 方法都处理从数据库中获取数据的 SELECT 查询。对于不返回数据的查询，您应该改为调用 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 查询，您可以分别调用 insert()、update()、delete() 来构建相应的 SQL，而不是编写普通的 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' => 'https://example.com/', // url is unique
    'visits' => 0,
], [
    'visits' => new \yii\db\Expression('visits + 1'),
], $params)->execute();

上面的代码将以原子方式插入新的页面记录或递增其访问计数器。

请注意，上述方法仅创建查询，您始终必须调用 execute() 才能实际运行它们。

引用表和列名称 ¶

编写数据库无关代码时，正确引用表和列名称通常很麻烦，因为不同的数据库有不同的名称引用规则。为了克服这个问题，您可以使用 Yii 引入的以下引用语法：

[[column name]]：将要引用的列名括在双方括号中；
{{table name}}：将要引用的表名括在双花括号中。

Yii DAO 会自动将这些结构转换为使用 DBMS 特定语法的相应引用的列或表名。例如：

// executes this SQL for MySQL: SELECT COUNT(`id`) FROM `employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{employee}}")
            ->queryScalar();

使用表前缀 ¶

如果您的大多数 DB 表名称都共享一个公共前缀，您可以使用 Yii DAO 提供的表前缀功能。

首先，通过应用程序配置中的 yii\db\Connection::$tablePrefix 属性指定表前缀。

return [
    // ...
    'components' => [
        // ...
        'db' => [
            // ...
            'tablePrefix' => 'tbl_',
        ],
    ],
];

然后在您的代码中，每当您需要引用名称包含此类前缀的表时，请使用 {{%table_name}} 语法。百分号将自动替换为您在配置 DB 连接时指定的表前缀。例如：

// executes this SQL for MySQL: 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;
} catch(\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}

通过调用 beginTransaction() 方法，将启动一个新的事务。事务表示为存储在 $transaction 变量中的 yii\db\Transaction 对象。然后，正在执行的查询包含在一个 try...catch... 块中。如果所有查询都成功执行，则调用 commit() 方法提交事务。否则，如果触发并捕获异常，则调用 rollBack() 方法回滚在该失败查询之前的事务中查询所做的更改。throw $e 然后会重新抛出异常，就像我们没有捕获它一样，因此正常的错误处理过程将负责处理它。

注意：在上面的代码中，我们有两个 catch 块，以兼容 PHP 5.x 和 PHP 7.x。\Exception 自 PHP 7.0 起实现了 \Throwable 接口，因此如果您的应用程序仅使用 PHP 7.0 及更高版本，则可以跳过 \Exception 部分。

指定隔离级别 ¶

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 - 最强的级别，避免上述所有问题。

除了使用上述常量来指定隔离级别外，您还可以使用您正在使用的 DBMS 支持的有效语法的字符串。例如，在 PostgreSQL 中，您可以使用 "SERIALIZABLE READ ONLY DEFERRABLE"。

请注意，某些 DBMS 仅允许为整个连接设置隔离级别。即使您没有指定任何内容，任何后续事务也将获得相同的隔离级别。使用此功能时，您可能需要为所有事务显式设置隔离级别，以避免设置冲突。在撰写本文时，只有 MSSQL 和 SQLite 受此限制的影响。

注意：SQLite 仅支持两个隔离级别，因此您只能使用 READ UNCOMMITTED 和 SERIALIZABLE。使用其他级别会导致抛出异常。

注意：PostgreSQL 不允许在事务开始之前设置隔离级别，因此您不能在启动事务时直接指定隔离级别。在这种情况下，您必须在事务启动后调用 yii\db\Transaction::setIsolationLevel()。

嵌套事务 ¶

如果您的 DBMS 支持保存点，您可以像以下示例一样嵌套多个事务：

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;
    } catch (\Throwable $e) {
        $innerTransaction->rollBack();
        throw $e;
    }

    $outerTransaction->commit();
} catch (\Exception $e) {
    $outerTransaction->rollBack();
    throw $e;
} catch (\Throwable $e) {
    $outerTransaction->rollBack();
    throw $e;
}

复制和读写分离 ¶

许多 DBMS 支持数据库复制以获得更好的数据库可用性和更快的服务器响应时间。通过数据库复制，数据将从所谓的主服务器复制到从服务器。所有写入和更新都必须在主服务器上进行，而读取也可以在从服务器上进行。

要利用数据库复制并实现读写分离，您可以像以下示例一样配置 yii\db\Connection 组件：

[
    'class' => 'yii\db\Connection',

    // configuration for the master
    'dsn' => 'dsn for master server',
    'username' => 'master',
    'password' => '',

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    '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'],
    ],
]

上述配置指定了一个具有单个主服务器和多个从服务器的设置。将连接其中一个从服务器并用于执行读取查询，而主服务器将用于执行写入查询。此配置将自动完成此类读写分离。例如：

// create a Connection instance using the above configuration
Yii::$app->db = Yii::createObject($config);

// query against one of the slaves
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();

// query against the master
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

信息：通过调用 yii\db\Command::execute() 执行的查询被视为写入查询，而通过 yii\db\Command 的其中一个“查询”方法执行的所有其他查询都是读取查询。您可以通过 Yii::$app->db->slave 获取当前活动的从属连接。

Connection 组件支持从属服务器之间的负载平衡和故障转移。首次执行读取查询时，Connection 组件将随机选择一个从属服务器并尝试连接到它。如果发现从属服务器“已死”，它将尝试另一个从属服务器。如果所有从属服务器都不可用，它将连接到主服务器。通过配置服务器状态缓存，“已死”服务器可以被记住，以便在一段时间内不再尝试连接它。

信息： 在上述配置中，为每个从服务器指定了 10 秒的连接超时时间。这意味着如果在 10 秒内无法连接到某个从服务器，则将其视为“已死”。您可以根据实际环境调整此参数。

您还可以配置多个主服务器和多个从服务器。例如，

[
    'class' => 'yii\db\Connection',

    // common configuration for masters
    'masterConfig' => [
        'username' => 'master',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of master configurations
    'masters' => [
        ['dsn' => 'dsn for master server 1'],
        ['dsn' => 'dsn for master server 2'],
    ],

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    '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 属性配置一个或多个主服务器时，Connection 对象本身用于指定数据库连接的所有其他属性（例如 dsn、username、password）都将被忽略。

默认情况下，事务使用主服务器连接。并且在事务中，所有数据库操作都将使用主服务器连接。例如，

$db = Yii::$app->db;
// the transaction is started on the master connection
$transaction = $db->beginTransaction();

try {
    // both queries are performed against the master
    $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;
} catch(\Throwable $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 提供了一组抽象数据类型，允许您定义与数据库无关的模式。这些类型会根据数据库转换为特定于 DBMS 的类型定义，表将在其中创建。有关更多信息，请参阅 createTable() 方法的 API 文档。

除了更改数据库模式外，您还可以通过 DB 连接的 getTableSchema() 方法检索有关表的定义信息。例如，

$table = Yii::$app->db->getTableSchema('post');

该方法返回一个 yii\db\TableSchema 对象，其中包含有关表的列、主键、外键等的信息。所有这些信息主要由查询构建器和活动记录利用，以帮助您编写与数据库无关的代码。

关键概念：依赖注入容器

返回顶部

查询构建器

发现错别字或您认为此页面需要改进？
在 github 上编辑它 !

用户贡献笔记 2

#21225

0 0

有趣的是，有时引用会返回 SQL 错误

// MyQuery.php:
[$tableName, $alias] = $this->getTableNameAndAlias();

// This works:
$this->andWhere([$alias . '.id_user' => $idUser];
$this->andWhere([$alias . '.[[id_user]]' => $idUser];
$this->andWhere(["{$alias}" . '.[[id_user]]' => $idUser];

// 1064 You have an error in your SQL syntax:
$this->andWhere(["{{$alias}}" . '.[[id_user]]' => $idUser];
$this->andWhere(["{{$tableName}}" . '.[[id_user]]' => $idUser];

// 1054 Unknown column '{xxx}.id_user'
$this->andWhere(["{{$alias}}" . '.id_user' => $idUser];
$this->andWhere(["{{$tableName}}" . '.id_user' => $idUser];

rackycz 于 2024年7月25日上午6:23:48

#21256

如果主数据库用于初始连接，所有后续连接是否默认为主数据库，或者是否需要显式强制它们到主数据库？这很重要，因为从主数据库到从数据库的复制可能会有延迟。如果写入数据并在之后立即尝试读取它，则数据可能尚未在从服务器上可用。这种情况是否自动处理，或者我们是否需要在每次写入操作后手动将 Yii::$app->db->enableSlaves 设置为 false 以确保一致性？

mgs 于 2024年9月12日上午10:44:47

发表评论

为了发表评论，请注册或登录。