Shulou(Shulou.com)06/03 Report--

This article mainly explains the "instructions for the use of mysql and mysqli transactions in PHP". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the instructions for the use of mysql and mysqli transactions in PHP".

Mysqli encapsulates some advanced operations, such as transactions, as well as many methods available during DB operations.

The most common application is the transaction of mysqli.

For example, the following example:

The copy code is as follows:

$mysqli = new mysqli ('localhost','root','','DB_Lib2Test')

$mysqli- > autocommit (false); / / start things

$mysqli- > query ($sql1)

$mysqli- > query ($sql2)

If (! $mysqli- > errno) {

$mysqli- > commit ()

Echo 'ok'

} else {

Echo 'err'

$mysqli- > rollback ()

}

In PHP, mysqli has well encapsulated the related operations of mysql transactions. The following is an example:

The copy code is as follows:

$sql1 = "update User set ScoreCount = ScoreCount + 10 where ID= '123456'"

$sql2 = "update ScoreDetail set FScore = 300 where ID= '123456'"

$sql3 = "insert into ScoreDetail ID,Score) values ('123456)

$mysqli = new mysqli ('localhost','root','','DB_Lib2Test')

$mysqli- > autocommit (false); / / start transaction

$mysqli- > query ($sql1)

$mysqli- > query ($sql2)

If (! $mysqli- > errno) {

$mysqli- > commit ()

Echo 'ok'

} else {

Echo 'err'

$mysqli- > rollback ()

}

Here, we use the php mysql series of functions to perform the transaction.

The copy code is as follows:

$sql1 = "update User set ScoreCount = ScoreCount + 10 where ID= '123456'"

$sql2 = "update ScoreDetail set FScore = 300 where ID= '123456'"

$sql3 = "insert into ScoreDetail ID,Score) values ('123456)

$conn = mysql_connect ('localhost','root','')

Mysql_select_db ('DB_Lib2Test')

Mysql_query ('start transaction')

/ / mysql_query ('SET autocommit=0')

Mysql_query ($sql1)

Mysql_query ($sql2)

If (mysql_errno ()) {

Mysql_query ('rollback')

Echo 'err'

} else {

Mysql_query ('commit')

Echo 'ok'

}

/ / mysql_query ('SET autocommit=1')

/ / mysql_query ($sql3)

Be careful here.

MyISAM: does not support transactions and is used by read-only programs to improve performance

InnoDB: supports ACID transactions, row-level locks, and concurrency

Berkeley DB: supporting transactions

It is also important to note that the default behavior of MySQL is to execute an SQL statement after each COMMIT statement is executed, effectively separating each statement into a transaction.

But often, we need to execute multiple sql statements when using transactions. This requires us to manually set the autocommit property of MySQL to 0, which defaults to 1.

At the same time, use the START TRANSACTION statement to explicitly open a transaction. Such as the example above.

What will be the result if we do not do so?

We uncomment / / mysql_query ('SET autocommit=0'); and / / mysql_query ($sql3) in the second piece of code above, and then execute it.

At this point, mysql_query ($sql3) execution does not insert into the database.

If we uncomment / / mysql_query ('SET autocommit=1'); on this sentence, then mysql_query ($sql3); will execute successfully.

Usually a transaction is completed when a COMMIT or ROLLBACK statement is executed, but some DDL statements and so on implicitly trigger COMMIT.

For example, the following statements

ALTER FUNCTION

ALTER PROCEDURE

ALTER TABLE

BEGIN

CREATE DATABASE

CREATE FUNCTION

CREATE INDEX

CREATE PROCEDURE

CREATE TABLE

DROP DATABASE

DROP FUNCTION

DROP INDEX

DROP PROCEDURE

DROP TABLE

UNLOCK TABLES

LOAD MASTER DATA

LOCK TABLES

RENAME TABLE

TRUNCATE TABLE

SET AUTOCOMMIT=1

START TRANSACTION

Let's give another example.

The copy code is as follows:

$sql1 = 'create table ScoreDetail_new (id int)'

$sql2 = 'rename table ScoreDetail to ScoreDetail_bak'

$sql3 = 'rename table ScoreDetail_new to ScoreDetail'

$mysqli = new mysqli ('localhost','root','','DB_Lib2Test')

$mysqli- > autocommit (false); / / start things

$mysqli- > query ($sql1)

$mysqli- > query ($sql2)

$mysqli- > query ($sql3)

If (! $mysqli- > errno) {

$mysqli- > commit ()

Echo 'ok'

} else {

Echo 'err'

$mysqli- > rollback ()

}

In the above example, if there is an error in the execution of $sql2, $sql1 will still execute. Why?

Because when rename is executed, mysql will execute commit before rename by default.

Be careful

Only data tables of type INNODB and BDB in MYSQL can support transactions! Other types are not supported!

*: the default engine for MYSQL databases is MyISAM, which does not support transactions! If you want MYSQL to support transactions, you can manually modify it yourself:

The methods are as follows: 1. Modify the c:/appserv/mysql/my.ini file, find the skip-InnoDB, add # before it, and save the file.

two。 Type: services.msc during the run to restart the mysql service.

3. In phpmyadmin, mysql- > show engines; (or execute mysql- > show variables like 'have_%';), and check that InnoDB is YES, which means that the database supports InnoDB.

This means that transaction transaction is supported.

Thank you for your reading. The above is the content of "instructions for the use of mysql and mysqli transactions in PHP". After the study of this article, I believe you have a deeper understanding of the use of mysql and mysqli transactions in PHP, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.