Yii2 database read/write splitting configuration example, yii2 example

Yii2 database read/write splitting configuration example, yii2 example

To start using a database, you must first configure the database connection component and add the db component to the application configuration. (the "Basic" Web application is config/web. PHP), DSN (Data Source Name) is the Name of the Data Source, used to specify the database information. as follows:

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 = 100 ', // 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 '=> 'oss: dbname = // localhost: 1521/mydatabase ', // Oracle 'username' => 'root', // database username 'Password' => '', // Database Password 'charset' => 'utf8',],], //...];

For more information about the DSN format, see PHP manual. After configuring the connection component, you can use the following syntax to access it:

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

See [[yii \ db \ Connection] to obtain a list of configurable properties. If you want to connect to the database through ODBC, You need to configure the [[yii \ db \ Connection: driverName] attribute, for example:

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

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

Use the following method in the Code:

$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 in the Code:

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

TIPS: If you need to execute an additional SQL query 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();      }    ],  ],  // ...];

Basic SQL query

Once an instance is connected, you can use [yii \ db \ Command] to execute an SQL query.

SELECT query

Multiple rows returned by the query:

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

Return row:

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

Query the single value of multiple rows:

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

Query scalar value/calculated value:

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

UPDATE, INSERT, delete update, INSERT, and DELETE

If the SQL statement does not return any data, 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 to generate appropriate SQL statements based on the parameters and execute them.

// INSERT $ connection-> createCommand ()-> insert ('user', ['name' => 'Sam ', 'age' => 30,]) -> execute (); // INSERT multiple rows at a time $ 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, the following syntax is used to safely reference the table name and column Name:

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

The Code [[$ column] above is changed to reference an appropriate column name, and {table} is changed to reference an appropriate table name. The table name has a special variable {% Y}. If you set the table prefix, you can use this variant to automatically add the 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 result in the tbl_table table:

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

Another option for manually referencing table names 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 statement

To pass query parameters safely, you can use a preprocessing statement. You should first use the 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 method 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();

Note: it is more efficient to bind a variable before execution, and then change the value of the variable in each execution (usually used in a loop.

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 for transaction operations. execute the SQL transaction query statement as follows:

$ Transaction = $ connection-> beginTransaction (); try {$ connection-> createCommand ($ sql1)-> execute (); $ connection-> createCommand ($ sql2) -> execute ();//... execute other SQL statements... $ transaction-> commit ();} catch (Exception $ e) {$ transaction-> rollBack ();}

We start a transaction through [[yii \ db \ Connection: beginTransaction () | beginTransaction ()], and catch exceptions through try catch. when the execution is successful, submit the Transaction through [[yii \ db \ Transaction: commit () | commit ()] and end, when an exception fails, use [[yii \ db \ Transaction: rollBack () | rollBack ()] to roll back the Transaction.

You can also nest multiple transactions as needed:

// External transaction $ transaction1 = $ connection-> beginTransaction (); try {$ connection-> createCommand ($ sql1)-> execute (); // internal transaction $ 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 are using must support Savepoints for proper execution. The above code can be executed in all relational data, but only Savepoints can ensure security.

Yii also supports setting the isolation level isolation levels for the transaction. When executing the transaction, the default isolation level of the database will be used. You can also specify the isolation level for the transaction. yii provides the following constants as common isolation levels

[[\ Yii \ db \ Transaction: READ_UNCOMMITTED]-allows reading changed uncommitted data, which may lead to dirty reads, non-repeated reads, and Phantom reads.

[[\ Yii \ db \ Transaction: READ_COMMITTED]-allows concurrent transactions to be read after being committed to avoid dirty reads, which may lead to repeated reads and Phantom reads.

[[\ Yii \ db \ Transaction: REPEATABLE_READ]-multiple reads of the same field are consistent, which can lead to phantom read.

[[\ Yii \ db \ Transaction: SERIALIZABLE]-fully complies with ACID principles to ensure that dirty reads, non-repeated reads, and Phantom reads are not performed.

You can use the above constants or a string command to execute this command in the corresponding database to set the isolation level. For example, the effective command for ipvs is serializable read only deferrable.

Note: Some databases can only set transaction isolation levels for connections, so you must specify the isolation level for the connections. Currently, the affected database: MSSQL SQLite

Note: SQLite only supports two transaction isolation levels. Therefore, you can only set read uncommitted and SERIALIZABLE. Exceptions are thrown when other isolation levels are used.

Note: PostgreSQL does not allow you to set the isolation level before the transaction starts. Therefore, you cannot specify the isolation level when the transaction starts. you can call [[yii \ db \ Transaction: setIsolationLevel ()] after the Transaction starts.

Database Replication and read/write splitting

Many databases support database replication to improve availability and response speed. in database replication, data is always from the master server to the slave server. all insert and update write operations are performed on the master server, while read operations are performed on the slave server.

You can configure [[yii \ db \ Connection] to implement database replication and read/write splitting.

['Class' => 'yii \ db \ connection', // configure the master server 'dsn '=> 'dsn for master Server', 'username' => 'master ', 'Password' => '', // configure the slave server 'slveconfig' => ['username' => 'slave ', 'Password' => '', 'bubuckets' => [// use a smaller connection timeout PDO: ATTR_TIMEOUT => 10,],], // configure the slave server group 'slafs' => [['dsn '=> 'dsn for slave server 1'], ['dsn '=> 'dsn for slave server 2'], ['dsn' => 'dsn for slave server 3'], ['dsn '=> 'dsn for slave server 4'],],]

The preceding configuration implements a master-slave structure. The slave server executes read queries, and the master server executes write queries. The read/write splitting function is automatically completed by the background code. the caller does not need to care about it. for example:

// Use the preceding configuration to create a database connection object $ db = Yii: createObject ($ config ); // query FROM the server $ rows = $ db-> createCommand ('select * FROM user LIMIT 10')-> queryAll (); // perform the UPDATE operation on the master server $ db-> createCommand ("UPDATE user SET username = 'Demo' WHERE id = 1")-> execute ();

Note: The query executed through [[yii \ db \ Command: execute ()] is considered as a write operation, all other query methods that use [[yii \ db \ Command] are considered read operations. you can use $ db-> slave to obtain the slave server currently in use.

The Connection component supports Server Load balancer and failover. When you perform a read query for the first time, a slave server is selected for Connection. If the Connection fails, another slave server is selected, if all slave servers are unavailable, the master server is connected. You can configure [[yii \ db \ Connection: serverStatusCache | server status cache] to remember slave servers that cannot be connected, enable Yii to [[yii \ db \ Connection: serverRetryInterval]. will not try to connect to those slave servers that are basically unavailable.

Note: In the preceding configuration, each slave server connection timeout value is set to 10 s. if the connection fails within 10 seconds, the server is considered to have crashed. 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' => '', 'bubuckets' => [// use a smaller connection timeout PDO: ATTR_TIMEOUT => 10,],], // configure the master server group 'masters '=> [['dsn' => 'dsn for master server 1'], ['dsn '=> 'dsn for master server 2'],], // configure the slave server 'slaveconfig' => ['username' => 'slave ', 'Password' => '', 'bubuckets' => [// use a smaller connection timeout PDO: ATTR_TIMEOUT => 10,],], // configure the slave server group 'slafs' => [['dsn '=> 'dsn for slave server 1'], ['dsn '=> 'dsn for slave server 2'], ['dsn' => 'dsn for slave server 3'], ['dsn '=> 'dsn for slave server 4'],],]

The preceding configuration sets two master servers and four slave servers. the Connection component also supports load balancing and Failover of the master server. Unlike the slave server, if all the master servers are unavailable, an exception is thrown.

Note: When you use [[yii \ db \ Connection: masters | masters] to configure one or more Master servers, other database Connection attributes in Connection (for example: dsn, username, password) will be ignored.

Transactions use the connection of the master server by default, and all operations performed by the transaction use the connection of the master server. For example:

// Start the transaction on the master server connection $ transaction = $ db-> beginTransaction (); try {// All queries are executed on the master server $ 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 ;}

If you want to execute transaction operations on the slave server, you must specify them explicitly, for example:

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

Sometimes you want to force the master server to execute read queries, you can call the seMaster () method.

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

You can also set $ db-> enableSlaves to false to execute all queries on the master server.

Database Operation Mode

Obtain mode information

You can use the [[yii \ db \ Schema] instance to obtain the Schema information:

$schema = $connection->getSchema();

This instance includes a series of methods to retrieve information from multiple aspects of the database:

$tables = $schema->getTableNames();

For more information, refer to [yii \ db \ Schema].

Modify Mode

In addition to basic SQL queries, [[yii \ db \ Command] also includes a series of methods to modify the database mode:

• Create/rename/delete/clear a table
• Add/rename/delete/modify Fields
• Add/delete primary keys
• Add/delete Foreign keys
• Create/delete an index
  

Example:

// Create a table $ connection-> createCommand ()-> createTable ('post', ['id' => 'pk', 'title' => 'string ', 'text' => 'text',]);

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.