PHP mysql and mysqli transaction instructions_PHP tutorial

First of all, mysqli connection is a permanent connection, while mysql is a non-permanent connection. What does it mean? Whenever the mysql connection is used for the second time, a new process will be reopened, while mysqli only uses the same process, which can greatly reduce the pressure on the server side.

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:

The code is as follows

Copy code

代码如下	复制代码
$mysqli = new mysqli('localhost','root','','DB_Lib2Test'); $mysqli->autocommit(false);//开始事物 $mysqli->query($sql1); $mysqli->query($sql2); if(!$mysqli->errno){   $mysqli->commit();   echo 'ok'; }else{  echo 'err';   $mysqli->rollback(); }

$mysqli = new mysqli('localhost','root','','DB_Lib2Test');
$mysqli->autocommit(false);//Start things

$mysqli->query($sql1);

代码如下

复制代码

$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); // 开始事务 $mysqli->query($sql1); $mysqli->query($sql2); if (!$mysqli->errno) {  $mysqli->commit();  echo 'ok'; } else {  echo 'err';  $mysqli->rollback(); }

$mysqli->query($sql2);

if(!$mysqli->errno){

$mysqli->commit();

代码如下

复制代码

$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);

echo 'ok'; }else{ echo 'err'; $mysqli->rollback(); }

In PHP, mysqli has well encapsulated the related operations of mysql transactions. Example below:

The code is as follows

Copy code

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

The code is as follows

Copy code

$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, used for read-only programs to improve performance
InnoDB: supports ACID transactions, row-level locks, and concurrency
Berkeley DB: supports transactions
One more thing to note: MySQL's default behavior is to execute a COMMIT statement after each SQL statement is executed, effectively treating each statement as a separate 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 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.

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

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);//开始事物 $mysqli->query($sql1); $mysqli->query($sql2); $mysqli->query($sql3); if (!$mysqli->errno) {  $mysqli->commit();  echo 'ok'; } else {  echo 'err';  $mysqli->rollback(); }

Copy code

$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

***: 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/630689.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630689.htmlTechArticleFirst of all, mysqli connection is a permanent connection, while mysql is a non-permanent connection. What does it mean? Whenever the mysql connection is used for the second time, a new process will be reopened, while mysqli...