作者 你好，世界！

Yii2文档–数据库访问

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

配置数据库信息

return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=example',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
            'tablePrefix'=>'tbl_', 
            'on afterOpen'=>function($event){
              $event->sender->createCommand("SET time_zone ='UTC'")->execute();
             }//执行设置时区为UTC时区在第一次建立DB连接时。
        ],
    ],
];

Yii2支持配置多个数据库。

SQL查询：

  1. 创建一个yii\db\Command普通的SQL查询；
  2. 绑定参数(可选择的);
  3. 执行yii\db\Command其中某一个SQL执行方法
  • Executing SQL Queries
// 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();

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

  • Binding Parameters
$post = Yii::$app->db
->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
->bindValue(':id', $_GET['id'])
->bindValue(':status', 1)
->queryOne();

$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();
$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id');
$post1 = $command->bindValue(':id', 1)->queryOne();
$post2 = $command->bindValue(':id', 2)->queryOne();

$command = Yii::$app->db
->createCommand('SELECT * FROM post WHERE id=:id')->bindParam(':id', $id);

$id = 1;
$post1 = $command->queryOne();

$id = 2;
$post2 = $command->queryOne();
  • Executing Non-SELECT Queries

之前上述所有的查询方法都是从数据库取数据。如果不从数据库取数据，应该使用yii\db\Command中的execute()方法。

// 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();

// table name, column names, column values
Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
])->execute();
  • Performing Transactions

当在一个序列中运行多个相关查询时，您可能需要将它们包在一个事务中，以确保您的数据库的完整性和一致性。如果任何查询失败，数据库将回滚到状态，就好像没有执行这些查询一样。

$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;
}
  • Specifying lsolation Levels

对于事务，我们可以指定事务的隔离级别。当我们开始一个新的事务时，会使用系统默认设置的隔离级别。也可以重写默认的隔离级别:

$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 – the weakest level, Dirty reads,non-repeatable reads and phantoms may occur.

\yii\db\Transaction::READ_COMMITTED – avoid dirty reads.

\yii\db\Transaction::REPEATABLE_READ – avoid dirty reads and non-repeatable reads.

\yii\db\Transaction::SERIALIZABLE – the strongest level, avoids all of the above named problems.

  • Nesting Transactions
$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;
}
  • Replication and Read-Write Splitting

利用数据库复制，实现读写分离，在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'],
    ],
]

// 使用上述实例创建一个连接实例
Yii::$app->db = Yii::createObject($config);
// 查询其中主服务器下的一个slaves
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
// 查询主服务器
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

也可以同时配置多个主服务器和多个slaves

[
    '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'],
    ],
]

默认情况下，事务使用主连接，在一个事务中，所有的数据库操作都将使用主连接：

$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;
}

如果想要使用一个slave连接：

$transaction = Yii::$app->db->slave->beginTransaction();

有时，可能希望强制使用主连接执行读取查询，可使用useMaster():

$rows = Yii::$app->db->useMaster(function ($db) {
    return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});
  • Working with Database Schema

Yii DAO提供了一整套的方法操作数据库，如创建新表，从表中删除列等等;

  • yii\db\Command::createTable(): creating a table
  • yii\db\Command::renameTable(): renaming a table
  • yii\db\Command::dropTable(): removing a table
  • yii\db\Command::truncateTable(): removing all rows in a table
  • yii\db\Command::addColumn(): adding a column
  • yii\db\Command::renameColumn(): renaming a column
  • yii\db\Command::dropColumn(): removing a column
  • yii\db\Command::alterColumn(): altering a column
  • yii\db\Command::addPrimaryKey(): adding a primary key
  • yii\db\Command::dropPrimaryKey(): removing a primary key
  • yii\db\Command::addForeignKey(): adding a foreign key
  • yii\db\Command::dropForeignKey(): removing a foreign key
  • yii\db\Command::createIndex(): creating an index
  • yii\db\Command::dropIndex(): removing an index

这些方法的使用方式:

// CREATE TABLE
Yii::$app->db->createCommand()->createTable('post', [
    'id' => 'pk',
    'title' => 'string',
    'text' => 'text',
]);

其他方法具体使用请参考API文档。

留下评论