Using database configuration and SQL operation example tutorials in PHP's Yii framework, yii example tutorials

Database access (DAO)
Yii includes a data access layer (DAO) built on PHP PDO. DAO provides a unified API for different databases. ActiveRecord provides database and model (M, Model in MVC) Interaction, QueryBuilder is used to create dynamic query statements. DAO provides simple and efficient SQL queries, which can be used in various places to interact with the database.

Yii supports the following databases (DBMS) by default:

• MySQL
• MariaDB
• SQLite
• PostgreSQL
• CUBRID: Version >= 9.3 . (Due to a bug in the PHP PDO extension, the reference value will be invalid, so you need to use 9.3 on both the client and server side of CUBRID)
• Oracle
• MSSQL: Version>=2005.

Configuration

To start using the database, you first need to configure the database connection component. This is achieved by adding the db component to the application configuration (the "basic" Web application is config/web.php). DSN (Data Source Name) is the data source name, used to specify Database information. As shown below:

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', //數(shù)據(jù)庫(kù)用戶名
      'password' => '', //數(shù)據(jù)庫(kù)密碼
      'charset' => 'utf8',
    ],
  ],
  // ...
];

Please refer to the PHP manual for more information about the DSN format. After configuring the connection component, it can be accessed using the following syntax:

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

Please refer to yiidbConnection for a list of configurable properties. If you want to connect to the database through ODBC, you need to configure the yiidbConnection::driverName attribute, for example:

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

Note: If you need to use multiple databases at the same time, you can define multiple connection components:

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', 
    ],
  ],
  // ...
];

is used in code via:

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

If you don’t want to define the database connection as a global application component, you can initialize it directly in the code:

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

Tip: If you need to perform additional SQL queries after creating the connection, you can add the following code to the application configuration file:

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

If executing SQL does not return any data, you can use the execute method in the command:

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

You can use the insert, update, and delete methods, which will generate appropriate SQL based on the parameters and execute them.

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

Referenced table name and column name

Most of the time use the following syntax to safely reference table and column names:

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

The above code $column will be converted to reference the appropriate column name, and {{table}} will be converted to reference the appropriate table name. The table name has a special variable {{%Y}}. If a table prefix is ??set, use this variant to automatically add a prefix before the table name:

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

If the table prefix is ??set as follows in the configuration file, the above code will query the results in the tbl_table table:

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

Another option for manually quoting table and column names is to use yiidbConnection::quoteTableName() and yiidbConnection::quoteColumnName():

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

Prepared statements

To safely pass query parameters, you can use preprocessing statements. First, you should use: placeholder, and then bind the variable to the corresponding placeholder:

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

Another usage is to prepare a prepared statement once and execute multiple queries:

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

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

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

Tip, it is more efficient to bind variables before execution and then change the value of the variable in each execution (generally used in loops).
Transactions

When you need to execute multiple related queries sequentially, you can encapsulate them into a transaction to protect data consistency. Yii provides a simple interface to implement transaction operations. Execute the SQL transaction query statement as follows:

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

We start a transaction through yiidbConnection::beginTransaction() and catch the exception through try catch. When the execution is successful, the transaction is submitted and ended through yiidbTransaction::commit(). When an exception occurs and the transaction fails, the transaction is performed through yiidbTransaction::rollBack(). Rollback.

You can also nest multiple transactions if necessary: ??

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

  // 內(nèi)部事務(wù)
  $transaction2 = $connection->beginTransaction();
  try {
    $connection->createCommand($sql2)->execute();
    $transaction2->commit();
  } catch (Exception $e) {
    $transaction2->rollBack();
  }

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

Note that the database you use must support Savepoints to execute correctly. The above code can be executed in all relational data, but security can only be guaranteed by supporting Savepoints.
Yii also supports setting isolation levels for transactions. When executing a transaction, the default isolation level of the database will be used. You can also specify the isolation level for things. Yii provides the following constants as commonly used isolation levels

• yiidbTransaction::READ_UNCOMMITTED - allows reading changed uncommitted data, which may lead to dirty reads, non-repeatable reads and phantom reads
• yiidbTransaction::READ_COMMITTED - allows concurrent transactions to be read after they are committed, which can avoid dirty reads, which may lead to repeated reads and phantom reads.
• yiidbTransaction::REPEATABLE_READ - Multiple reads of the same field have consistent results, which can lead to phantom reads.
• yiidbTransaction::SERIALIZABLE - Fully obeys the ACID principle to ensure that dirty reads, non-repeatable reads and phantom reads do not occur.

You can use the above constants or use a string command and execute the command in the corresponding database to set the isolation level. For example, the effective command for postgres is SERIALIZABLE READ ONLY DEFERRABLE.

注意:某些數(shù)據(jù)庫(kù)只能針對(duì)連接來設(shè)置事務(wù)隔離級(jí)別，所以你必須要為連接明確制定隔離級(jí)別.目前受影響的數(shù)據(jù)庫(kù):MSSQL SQLite

注意:SQLite 只支持兩種事務(wù)隔離級(jí)別，所以你只能設(shè)置READ UNCOMMITTED 和 SERIALIZABLE.使用其他隔離級(jí)別會(huì)拋出異常.

注意:PostgreSQL 不允許在事務(wù)開始前設(shè)置隔離級(jí)別，所以你不能在事務(wù)開始時(shí)指定隔離級(jí)別.你可以在事務(wù)開始之后調(diào)用yii\db\Transaction::setIsolationLevel() 來設(shè)置.
關(guān)于隔離級(jí)別[isolation levels]: http://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels

數(shù)據(jù)庫(kù)復(fù)制和讀寫分離

很多數(shù)據(jù)庫(kù)支持?jǐn)?shù)據(jù)庫(kù)復(fù)制 http://en.wikipedia.org/wiki/Replication_(computing)#Database_replication">database replication來提高可用性和響應(yīng)速度. 在數(shù)據(jù)庫(kù)復(fù)制中，數(shù)據(jù)總是從主服務(wù)器 到 從服務(wù)器. 所有的插入和更新等寫操作在主服務(wù)器執(zhí)行，而讀操作在從服務(wù)器執(zhí)行.

通過配置yii\db\Connection可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)復(fù)制和讀寫分離.

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

  // 配置主服務(wù)器
  'dsn' => 'dsn for master server',
  'username' => 'master',
  'password' => '',

  // 配置從服務(wù)器
  'slaveConfig' => [
    'username' => 'slave',
    'password' => '',
    'attributes' => [
      // use a smaller connection timeout
      PDO::ATTR_TIMEOUT => 10,
    ],
  ],

  // 配置從服務(wù)器組
  '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'],
  ],
]

以上的配置實(shí)現(xiàn)了一主多從的結(jié)構(gòu)，從服務(wù)器用以執(zhí)行讀查詢，主服務(wù)器執(zhí)行寫入查詢，讀寫分離的功能由后臺(tái)代碼自動(dòng)完成.調(diào)用者無須關(guān)心.例如：

// 使用以上配置創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象
$db = Yii::createObject($config);

// 通過從服務(wù)器執(zhí)行查詢操作
$rows = $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();

// 通過主服務(wù)器執(zhí)行更新操作
$db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

注意:通過yii\db\Command::execute() 執(zhí)行的查詢被認(rèn)為是寫操作，所有使用yii\db\Command來執(zhí)行的其他查詢方法被認(rèn)為是讀操作.你可以通過$db->slave得到當(dāng)前正在使用能夠的從服務(wù)器.
Connection組件支持從服務(wù)器的負(fù)載均衡和故障轉(zhuǎn)移，當(dāng)?shù)谝淮螆?zhí)行讀查詢時(shí)，會(huì)隨即選擇一個(gè)從服務(wù)器進(jìn)行連接，如果連接失敗則又選擇另一個(gè)，如果所有從服務(wù)器都不可用，則會(huì)連接主服務(wù)器。你可以配置yii\db\Connection::serverStatusCache來記住那些不能連接的從服務(wù)器，使Yii 在一段時(shí)間[[yii\db\Connection::serverRetryInterval].內(nèi)不會(huì)重復(fù)嘗試連接那些根本不可用的從服務(wù)器.

注意:在上述配置中，每個(gè)從服務(wù)器連接超時(shí)時(shí)間被指定為10s. 如果在10s內(nèi)不能連接，則被認(rèn)為該服務(wù)器已經(jīng)掛掉.你也可以自定義超時(shí)參數(shù).
你也可以配置多主多從的結(jié)構(gòu)，例如:

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

  // 配置主服務(wù)器
  'masterConfig' => [
    'username' => 'master',
    'password' => '',
    'attributes' => [
      // use a smaller connection timeout
      PDO::ATTR_TIMEOUT => 10,
    ],
  ],

  // 配置主服務(wù)器組
  'masters' => [
    ['dsn' => 'dsn for master server 1'],
    ['dsn' => 'dsn for master server 2'],
  ],

  // 配置從服務(wù)器
  'slaveConfig' => [
    'username' => 'slave',
    'password' => '',
    'attributes' => [
      // use a smaller connection timeout
      PDO::ATTR_TIMEOUT => 10,
    ],
  ],

  // 配置從服務(wù)器組
  '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'],
  ],
]

上述配置制定了2個(gè)主服務(wù)器和4個(gè)從服務(wù)器.Connection組件也支持主服務(wù)器的負(fù)載均衡和故障轉(zhuǎn)移，與從服務(wù)器不同的是，如果所有主服務(wù)器都不可用，則會(huì)拋出異常.

注意:當(dāng)你使用yii\db\Connection::masters來配置一個(gè)或多個(gè)主服務(wù)器時(shí)，Connection中關(guān)于數(shù)據(jù)庫(kù)連接的其他屬性（例如：dsn,username, password）都會(huì)被忽略.
事務(wù)默認(rèn)使用主服務(wù)器的連接，并且在事務(wù)執(zhí)行中的所有操作都會(huì)使用主服務(wù)器的連接，例如:

// 在主服務(wù)器連接上開始事務(wù)
$transaction = $db->beginTransaction();

try {
  // 所有的查詢都在主服務(wù)器上執(zhí)行
  $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;
}

如果你想在從服務(wù)器上執(zhí)行事務(wù)操作則必須要明確地指定，比如:

$transaction = $db->slave->beginTransaction();

有時(shí)你想強(qiáng)制使用主服務(wù)器來執(zhí)行讀查詢，你可以調(diào)用seMaster()方法.

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

你也可以設(shè)置$db->enableSlaves 為false來使所有查詢都在主服務(wù)器上執(zhí)行.

• 操作數(shù)據(jù)庫(kù)模式
• 獲得模式信息

你可以通過 yii\db\Schema實(shí)例來獲取Schema信息:

$schema = $connection->getSchema();

該實(shí)例包括一系列方法來檢索數(shù)據(jù)庫(kù)多方面的信息：

$tables = $schema->getTableNames();

更多信息請(qǐng)參考yii\db\Schema

修改模式

除了基礎(chǔ)的 SQL 查詢，yii\db\Command還包括一系列方法來修改數(shù)據(jù)庫(kù)模式：

• 創(chuàng)建/重命名/刪除/清空表
• 增加/重命名/刪除/修改字段
• 增加/刪除主鍵
• 增加/刪除外鍵
• 創(chuàng)建/刪除索引

使用示例:

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

完整參考請(qǐng)查看yii\db\Command.

SQL查詢示例：

// find the customers whose primary key value is 10
$customers = Customer::findAll(10);
$customer = Customer::findOne(10);

// the above code is equivalent to:
$customers = Customer::find()->where(['id' => 10])->all();

// find the customers whose primary key value is 10, 11 or 12.
$customers = Customer::findAll([10, 11, 12]);
$customers = Customer::find()->where(['IN','id',[10,11,12]])->all();

// the above code is equivalent to:
$customers = Customer::find()->where(['id' => [10, 11, 12]])->all();

// find customers whose age is 30 and whose status is 1
$customers = Customer::findAll(['age' => 30, 'status' => 1]);

// the above code is equivalent to:
$customers = Customer::find()->where(['age' => 30, 'status' => 1])->all();

// use params binding
$customers = Customer::find()->where('age=:age AND status=:status')->addParams([':age'=>30,':status'=>1])->all();

// use index
$customers = Customer::find()->indexBy('id')->where(['age' => 30, 'status' => 1])->all();

// get customers count
$count = Customer::find()->where(['age' => 30, 'status' => 1])->count();

// add addition condition
$customers = Customer::find()->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();

// find by sql
$customers = Customer::findBySql('SELECT * FROM customer WHERE age=30 AND status=1 AND score>100 ORDER BY id DESC LIMIT 5,10')->all();

修改：

// update status for customer-10
$customer = Customer::findOne(10);
$customer->status = 1;
$customer->update();

// the above code is equivalent to:
Customer::updateAll(['status' => 1], 'id = :id',[':id'=>10]);

刪除：

// delete customer-10
Customer::findOne(10)->delete();

// the above code is equivalent to:
Customer::deleteAll(['status' => 1], 'id = :id',[':id'=>10]);

--------------------------------使用子查詢------------------------------------------

$subQuery = (new Query())->select('COUNT(*)')->from('customer');

// SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('customer');

--------------------------------手寫SQL-------------------------------------------

// select
$customers = Yii::$app->db->createCommand('SELECT * FROM customer')->queryAll();

// update
Yii::$app->db->createCommand()->update('customer',['status'=>1],'id=10')->execute();

// delete
Yii::$app->db->createCommand()->delete('customer','id=10')->execute();

//transaction
// outer 
$transaction1 = $connection->beginTransaction();
try {
  $connection->createCommand($sql1)->execute();

  // internal
  $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',

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

  // slaves
  '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'],
  ],
]

您可能感興趣的文章:

• PHP的Yii框架中移除組件所綁定的行為的方法
• PHP的Yii框架中行為的定義與綁定方法講解
• 詳解在PHP的Yii框架中使用行為Behaviors的方法
• 深入講解PHP的Yii框架中的屬性（Property）
• 深入解析PHP的Yii框架中的event事件機(jī)制
• 全面解讀PHP的Yii框架中的日志功能
• Yii使用find findAll查找出指定字段的實(shí)現(xiàn)方法
• 解析yii數(shù)據(jù)庫(kù)的增刪查改
• Yii PHP Framework實(shí)用入門教程(詳細(xì)介紹)
• 詳解PHP的Yii框架中組件行為的屬性注入和方法注入