PHP mysql and mysqli transaction usage instructions Share_PHP Tutorial

Mysqli encapsulates some advanced operations such as transactions, and also encapsulates many available methods in the DB operation process.

Mysqli transactions are widely used.

For example the following example:

Copy code The 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. Example below:

Copy code The 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',60)";

$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 php again The mysql series of functions perform transactions.

Copy code The 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',60)";

$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 to improve performance of read-only programs
InnoDB: supports ACID transactions, row-level locks, and concurrency
Berkeley DB: supports transactions
One more thing to note: MySQL default The behavior is to execute a COMMIT statement after each SQL statement is executed, effectively separating each statement into a transaction.

But often, when we need to use transactions, we need to execute multiple sql statements. This requires us to manually set MySQL's autocommit attribute to 0, which defaults to 1.

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

What will be the consequences if you don’t do this?

We will remove the comments //mysql_query(‘SET autocommit=0′); and // mysql_query($sql3); in the second piece of code above, and then execute.

At this time, mysql_query($sql3) will not be inserted into the database when executed.

If we remove the comment from // mysql_query(‘SET autocommit=1′);, then mysql_query($sql3); will be executed successfully.

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

For example, the following statement

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 take another example.

Copy code The 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 $sql2 fails to execute, $sql1 will still be executed. Why?

Because when rename is executed, mysql will execute commit first and then rename by default.

Attention

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

***: Generally, the default engine of MYSQL database is MyISAM. This engine does not support transactions! If you want MYSQL to support transactions, you can modify it manually:

The method is as follows: 1. Modify the c:/appserv/mysql/my.ini file, find skip-InnoDB, add # in front, and save the file.

2. Enter: services.msc during operation to restart the mysql service.

3. Go to phpmyadmin, mysql->show engines; (or execute mysql->show variables like 'have_%';), check InnoDB for YES, which means the database supports InnoDB.
This means that transaction transactions are supported.

http://www.bkjia.com/PHPjc/328165.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/328165.htmlTechArticlemysqli encapsulates some advanced operations such as transactions, and also encapsulates many available methods in the DB operation process. The place where it is most used is mysqli transactions. For example, the following example: ...