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ù)庫用戶名
      'password' => '', //數(shù)據(jù)庫密碼
      '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: ??

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

  // 內(nèi)部事務
  $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ù)庫只能針對連接來設(shè)置事務隔離級別，所以你必須要為連接明確制定隔離級別.目前受影響的數(shù)據(jù)庫:MSSQL SQLite

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

try {
  // 所有的查詢都在主服務器上執(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;
}

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

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

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

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

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

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

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

$schema = $connection->getSchema();

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

$tables = $schema->getTableNames();

更多信息請參考yii\db\Schema

修改模式

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

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

使用示例:

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

完整參考請查看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事件機制
• 全面解讀PHP的Yii框架中的日志功能
• Yii使用find findAll查找出指定字段的實現(xiàn)方法
• 解析yii數(shù)據(jù)庫的增刪查改
• Yii PHP Framework實用入門教程(詳細介紹)
• 詳解PHP的Yii框架中組件行為的屬性注入和方法注入