YII2 database Read-write detach configuration example

Getting started with a database requires first configuring the database Connectivity component by adding a DB component to the application configuration implementation (the "base" Web app 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 ',//database user name      ' password ' + ',//Database Password ' charset ' + '      utf8 ',    ],< c17/>],  //...];

Please refer to PHP Manual for more information about DSN format. After you configure a connection component, you can access it using the following syntax:

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

Please refer to [[Yii\db\connection]] for a list of configurable properties. If you want to connect to the database through ODBC, you need to configure the [[yii\db\connection::d Rivername]] property, 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 ' and '      UTF8 ',    ],    ' seconddb ' = [      ' Class ' = ' yii\db\connection ',      ' dsn ' = ' sqlite:/path/to/database/file ',     ],  ],  //...];

Used in code in the following ways:

$primaryConnection = \yii:: $app->db; $secondaryConnection = \yii:: $app->seconddb;

If you do not want to define a database connection as a global application component, you can initialize it directly in your 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 a 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 () ;      }    ],  ],  // ...];

SQL Basic Query

Once you have a connection instance, you can execute the SQL query through [[Yii\db\command]].

SELECT Query

The query returns multiple rows:

$command = $connection->createcommand (' SELECT * from post '), $posts = $command->queryall ();

Return a single line:

$command = $connection->createcommand (' SELECT * from post WHERE id=1 '); $post = $command->queryone ();

Query multi-line single value:

$command = $connection->createcommand (' SELECT title from Post '); $titles = $command->querycolumn ();

Query scalar value/computed value:

$command = $connection->createcommand (' SELECT COUNT (*) from post '), $postCount = $command->queryscalar ();

UPDATE, INSERT, delete updates, insertions and deletions, etc.

If execute 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,delete method, which generates the appropriate SQL and executes according to the parameters.

Insert$connection->createcommand ()->insert (' User ', [  ' name ' = ' Sam ',  ' age ' = ', ']) Execute ();//Insert multiple lines $connection->createcommand ()->batchinsert (' User ', [' name ', ' age '], [  [' Tom ', 30] ,  [' Jane ', +],  [' Linda ', ' +],])->execute ();//Update$connection->createcommand ()->update (' user ' , [' Status ' = 1], ' Age > ')->execute ();//Delete$connection->createcommand ()->delete (' User ', ' status = 0 ')->execute ();

Referenced table and column names

The following syntax is used most of the time to safely reference table and column names:

$sql = "Select COUNT ([[$column]]) from {{table}}"; $rowCount = $connection->createcommand ($sql)->queryscalar ();

The above code [[$column]] is converted to a reference to the appropriate column name, and {{table}} is converted to a reference to the appropriate table name. The table name has a special variable {{%Y}}, and if you set the table prefix you can automatically prefix the table name with the variant:

$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 result in the Tbl_table table:

return [  //...  ] Components ' = + [    //...    ' db ' = = [      //...      ' Tableprefix ' = ' tbl_ ',],],  ;

Another option for manually referencing table and column names is to use [[Yii\db\connection::quotetablename ()]] and [[Yii\db\connection::quotecolumnname ()]]:

$column = $connection->quotecolumnname ($column), $table = $connection->quotetablename ($table); $sql = "Select COUNT ($column) from $table "; $rowCount = $connection->createcommand ($sql)->queryscalar ();

Preprocessing statements

To pass a query parameter for security, you can use a preprocessing statement, first using: Placeholder placeholder, and then binding the variable to the corresponding placeholder:

$command = $connection->createcommand (' SELECT * from post WHERE id=:id '); $command->bindvalue (': Id ', $_get[' id ']) ; $post = $command->query ();

Another use is to prepare a preprocessing statement 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 ();

Hint that binding a variable before execution and then changing the value of the variable in each execution (typically used in a loop) is more efficient.

Transaction

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

$transaction = $connection->begintransaction (); try {  $connection->createcommand ($sql 1)->execute ();   $connection->createcommand ($sql 2)->execute ();  // ... Execute other SQL statements  ... $transaction->commit ();} catch (Exception $e) {  $transaction->rollback ();}

We start a transaction by [[Yii\db\connection::begintransaction () |begintransaction ()]] and catch the exception through a try catch. When execution succeeds, by [[Yii\db\ Transaction::commit () |commit ()]] commits the transaction and ends, and the transaction is rolled back by [[Yii\db\transaction::rollback () |rollback ()]] When an exception failure occurs.

Multiple transactions can also be nested if required:

External transaction $transaction1 = $connection->begintransaction (); try {  $connection->createcommand ($sql 1) Execute ();  Internal transaction  $transaction 2 = $connection->begintransaction ();  try {    $connection->createcommand ($sql 2)->execute ();    $transaction 2->commit ();  } catch (Exception $e) {    $transaction 2->rollback ();  }  $transaction 1->commit ();} catch (Exception $e) {  $transaction 1->rollback ();}

Note that the database you are using must support savepoints to execute correctly, the above code can be executed in all relational data, but security is guaranteed only with support for savepoints.

YII also supports setting isolation levels for transactions isolation levels, which uses the default isolation level of the database when executing transactions, and you can also specify isolation levels for things. YII provides the following constants as a common isolation level

[[\yii\db\transaction::read_uncommitted]]-allows reading of changed uncommitted data, which may result in dirty reads, non-repeatable reads, and Phantom reads

[[\yii\db\transaction::read_committed]]-allows concurrent transactions to be read after committing, can avoid dirty reads, may lead to repeated reading and phantom reading.

[[\yii\db\transaction::repeatable_read]]-the same field is repeated for multiple reads, which can result in a phantom read.

[[\yii\db\transaction::serializable]]-the principle of full compliance with acid, to ensure that no dirty read, non-repeatable read and Phantom read.

You can use the constants above or use a string string command to execute the command in the corresponding database to set the isolation level, such as for Postgres valid command for serializable READ only deferrable.

Note: Some databases can only set the transaction isolation level for connections, so you must explicitly establish an isolation level for the connection. Currently affected database: MSSQL SQLite

Note: SQLite supports only two transaction isolation levels, so you can only set read UNCOMMITTED and SERIALIZABLE. Using other isolation levels throws an exception.

Note: PostgreSQL does not allow you to set the isolation level before the transaction starts, so you cannot specify the isolation level at the beginning of the transaction. You can call [[Yii\db\transaction::setisolationlevel ()]] to set after the transaction begins.

database replication and read-write separation

Many databases support databases to replicate database replication to improve availability and responsiveness. In database replication, data is always from the primary server to the slave server. All insert and update write operations are performed on the primary server, while read operations are performed from the server.

configuration [[Yii\db\connection]] enables database replication and read-write separation.

[  ' class ' = ' yii\db\connection ',  //Configure Master server  ' DSN ' = ' DSN for master server ',  ' username ' = ' Master ',  ' password ' = ' + ',  //config from server  ' slaveconfig ' and ' [    ' username ' = ' slave ',    ' Password ' = = ',    ' attributes ' = = [      //Use a smaller connection timeout      pdo::attr_timeout = 10,
  ],  ],  //config from server group  ' slaves ' = [    [' DSN ' = ' DSN for slave server 1 '],    [' DSN ' + ' DSN fo R slave server 2 '],    [' dsn ' = = ' DSN for slave server 3 '],    [' DSN ' + ' DSN for slave server 4 '],  ]

The above configuration realizes a main multi-slave structure, from the server to execute the read query, the master server performs the write query, the function of read and write separation is completed automatically by the background code. Callers need not care. For example:

Create a database connection object using the above configuration $db = Yii::createobject ($config);//By performing a query operation from the server $rows = $db->createcommand (' SELECT * from user LIMIT ')->queryall ()//The update operation is performed via the master server $db->createcommand ("Update user SET username= ' demo ' WHERE id=1 ') Execute ();

Note: queries executed through [[Yii\db\command::execute ()]] are considered to be write operations, and all other query methods executed using [[Yii\db\command]] are considered read operations. You can go through the $db-> Slave Gets the slave server that is currently being used.

The connection component supports load balancing and failover from the server, and when a read query is executed for the first time, a connection is selected from the server, and if the connection fails, the other is selected, and if all from the server is unavailable, the primary server is connected. You can configure [[yii\db\connection::serverstatuscache|server status Cache]] to remember those that cannot connect from the server, making yii over a period of time [[Yii\db\connection:: Serverretryinterval]. Attempts to connect to a slave server that are not available at all are not repeated inside.

Note: In the above configuration, each slave server connection timeout is specified as 10s. If the connection cannot be made within 10s, the server is considered dead. You can also customize the timeout parameter.
You can also configure a multi-master multi-slave structure, for example:

[  ' class ' = ' yii\db\connection ',  //Configure the master server  ' masterconfig ' = [    ' username ' + ' master ',    ' Password ' = ' and ',    ' attributes ' = = [      //Use a smaller connection timeout      pdo::attr_timeout = 10,
  
   ],  ],  //Configure the Primary server group  ' masters ' = [    [' DSN ' = ' DSN for master server 1 '],    [' DSN ' = ' DSN ' for Master server 2 '],  ],  //config from server  ' slaveconfig ' = [    ' username ' = ' slave ',    ' password ' =& Gt ',    ' attributes ' = = [      //Use a smaller connection timeout      pdo::attr_timeout =],  ],  //config from server group  ' slaves ' = [' dsn ' = ' DSN    for slave server 1 '],    [' DSN ' = ' DSN ' for slave serve R 2 '],    [' dsn ' = = ' DSN for slave server 3 '],    [' DSN ' + ' DSN for slave server 4 '],  ]
  

The above configuration developed 2 master servers and 4 slave servers. The connection component also supports load balancing and failover of the primary server, and, unlike from the server, throws an exception if all primary servers are unavailable.

Note: When you use [[yii\db\connection::masters|masters]] to configure one or more primary servers, other properties about database connections in Connection (for example: DSN, username, Password) will be ignored.

The transaction uses the primary server's connection by default, and all operations in the transaction will use the primary server's connection, for example:

Start transaction on primary server connection $transaction = $db->begintransaction (); try {  //All queries are executed on the primary server  $rows = $db CreateCommand (' SELECT * from User LIMIT ')->queryall ();  $db->createcommand ("UPDATE User SET username= ' demo ' WHERE id=1")->execute ();  $transaction->commit ();} catch (\exception $e) {  $transaction->rollback ();  Throw $e;}

If you want to perform transactional operations from the server, you must explicitly specify, for example:

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

Sometimes you want to force a master server to execute a read query, and you can call the Semaster () method.

$rows = $db->usemaster (function ($db) {  return $db->createcommand (' SELECT * from User LIMIT ')->queryall ( );});

You can also set $db->enableslaves to False to make all queries execute on the primary server.

Manipulating Database schemas

Obtaining schema information

You can get Schema information through [[Yii\db\schema]] instances:

$schema = $connection->getschema ();

This example includes a series of methods to retrieve information about the various aspects of the database:

$tables = $schema->gettablenames ();