Exemple de configuration de séparation en lecture-écriture de la base de données Yii2

return [
  // ...
  'components' => [
    // ...
    'db' => [
      'class' => 'yii\db\Connection',
      'dsn' => 'mysql:host=localhost;dbname=mydatabase', // MySQL, MariaDB
      //'dsn' => 'sqlite:/path/to/database/file', // SQLite
      //'dsn' => 'pgsql:host=localhost;port=5432;dbname=mydatabase', // PostgreSQL
      //'dsn' => 'cubrid:dbname=demodb;host=localhost;port=33000', // CUBRID
      //'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase', // MS SQL Server, sqlsrv driver
      //'dsn' => 'dblib:host=localhost;dbname=mydatabase', // MS SQL Server, dblib driver
      //'dsn' => 'mssql:host=localhost;dbname=mydatabase', // MS SQL Server, mssql driver
      //'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle
      'username' => 'root', //数据库用户名
      'password' => '', //数据库密码
      'charset' => 'utf8',
    ],
  ],
  // ...
];

$connection = \Yii::$app->db;

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

return [
  // ...
  'components' => [
    // ...
    'db' => [
      'class' => 'yii\db\Connection',
      'dsn' => 'mysql:host=localhost;dbname=mydatabase', 
      'username' => 'root',
      'password' => '',
      'charset' => 'utf8',
    ],
    'secondDb' => [
      'class' => 'yii\db\Connection',
      'dsn' => 'sqlite:/path/to/database/file', 
    ],
  ],
  // ...
];

$primaryConnection = \Yii::$app->db;
$secondaryConnection = \Yii::$app->secondDb;

$connection = new \yii\db\Connection([
  'dsn' => $dsn,
   'username' => $username,
   'password' => $password,
]);
$connection->open();

return [
  // ...
  'components' => [
    // ...
    'db' => [
      'class' => 'yii\db\Connection',
      // ...
      'on afterOpen' => function($event) {
        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
      }
    ],
  ],
  // ...
];

$command = $connection->createCommand('SELECT * FROM post');
$posts = $command->queryAll();

$command = $connection->createCommand('SELECT * FROM post WHERE id=1');
$post = $command->queryOne();

$command = $connection->createCommand('SELECT title FROM post');
$titles = $command->queryColumn();

$command = $connection->createCommand('SELECT COUNT(*) FROM post');
$postCount = $command->queryScalar();

$command = $connection->createCommand('UPDATE post SET status=1 WHERE id=1');
$command->execute();

// INSERT
$connection->createCommand()->insert('user', [
  'name' => 'Sam',
  'age' => 30,
])->execute();

// INSERT 一次插入多行
$connection->createCommand()->batchInsert('user', ['name', 'age'], [
  ['Tom', 30],
  ['Jane', 20],
  ['Linda', 25],
])->execute();

// UPDATE
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE
$connection->createCommand()->delete('user', 'status = 0')->execute();

$sql = "SELECT COUNT([[$column]]) FROM {{table}}";
$rowCount = $connection->createCommand($sql)->queryScalar();

$sql = "SELECT COUNT([[$column]]) FROM {{%$table}}";
$rowCount = $connection->createCommand($sql)->queryScalar();

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

$column = $connection->quoteColumnName($column);
$table = $connection->quoteTableName($table);
$sql = "SELECT COUNT($column) FROM $table";
$rowCount = $connection->createCommand($sql)->queryScalar();

$command = $connection->createCommand('SELECT * FROM post WHERE id=:id');
$command->bindValue(':id', $_GET['id']);
$post = $command->query();

$command = $connection->createCommand('DELETE FROM post WHERE id=:id');
$command->bindParam(':id', $id);

$id = 1;
$command->execute();

$id = 2;
$command->execute();

$transaction = $connection->beginTransaction();
try {
  $connection->createCommand($sql1)->execute();
   $connection->createCommand($sql2)->execute();
  // ... 执行其他 SQL 语句 ...
  $transaction->commit();
} catch(Exception $e) {
  $transaction->rollBack();
}

// 外部事务
$transaction1 = $connection->beginTransaction();
try {
  $connection->createCommand($sql1)->execute();

  // 内部事务
  $transaction2 = $connection->beginTransaction();
  try {
    $connection->createCommand($sql2)->execute();
    $transaction2->commit();
  } catch (Exception $e) {
    $transaction2->rollBack();
  }

  $transaction1->commit();
} catch (Exception $e) {
  $transaction1->rollBack();
}

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

  // 配置主服务器
  'dsn' => 'dsn for master server',
  'username' => 'master',
  'password' => '',

  // 配置从服务器
  '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'],
  ],
]

// 使用以上配置创建数据库连接对象
$db = Yii::createObject($config);

// 通过从服务器执行查询操作
$rows = $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();

// 通过主服务器执行更新操作
$db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

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

// 在主服务器连接上开始事务
$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 = $db->slave->beginTransaction();

$rows = $db->useMaster(function ($db) {
  return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});

$schema = $connection->getSchema();

$tables = $schema->getTableNames();

// 创建表
$connection->createCommand()->createTable('post', [
  'id' => 'pk',
  'title' => 'string',
  'text' => 'text',
]);