PHP mysql and mysqli transaction usage and operation comparison

First of all, mysqli connection is a permanent connection, while mysql is a non-permanent connection. What does that 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 transactions, etc. Some advanced operations also encapsulate many available methods in the DB operation process.

The most common application is mysqli transactions.

For example, the following example:

The code is as follows:

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

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

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); // 开始事务
$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:

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

It should be noted here that

MyISAM: does not support transactions and is used for read-only programs to improve performance
InnoDB: supports ACID Transactions, row-level locks, concurrency
Berkeley DB: Support transactions
One more thing to note: MySQL's default behavior is to execute a COMMIT statement after each SQL statement is executed, thereby effectively making each statement independent 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 we 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.

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();
}

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.

Note

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 manually modify it yourself:

The method is as follows: 1. Modify the c:/appserv/mysql/my.ini file, find skip-InnoDB, add # in front, and then 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.

The above is the detailed content of PHP mysql and mysqli transaction usage and operation comparison. For more information, please follow other related articles on the PHP Chinese website!