PHP MySQL and mysqli transaction usage instructions sharing _php tips

Mysqli encapsulates some advanced operations, such as transactions, while encapsulating many of the methods available during DB operations.

The most widely used place is the mysqli business.

Like the following example:

Copy Code code as follows:

$mysqli = new mysqli (' localhost ', ' root ', ' ', ' db_lib2test ');
$mysqli->autocommit (false);/Start Things
$mysqli->query ($sql 1);
$mysqli->query ($sql 2);
if (! $mysqli->errno) {
$mysqli->commit ();
echo ' OK ';
}else{
echo ' err ';
$mysqli->rollback ();
}

In PHP, Mysqli has been very good at encapsulating MySQL transaction related operations. The following example:

Copy Code code as follows:

$sql 1 = "Update User set scorecount = Scorecount +10 where id= ' 123456 '";
$sql 2 = "Update scoredetail set fscore = where id= ' 123456 '";
$sql 3 = "INSERT into Scoredetail id,score) VALUES (' 123456 ', 60)";

$mysqli = new mysqli (' localhost ', ' root ', ' ', ' db_lib2test ');
$mysqli->autocommit (FALSE); Start a transaction
$mysqli->query ($sql 1);
$mysqli->query ($sql 2);
if (! $mysqli->errno) {
$mysqli->commit ();
echo ' OK ';
} else {
echo ' err ';
$mysqli->rollback ();
}

Here, we use the PHP MySQL series function to execute the transaction.

Copy Code code as follows:

$sql 1 = "Update User set scorecount = Scorecount +10 where id= ' 123456 '";
$sql 2 = "Update scoredetail set fscore = where id= ' 123456 '";
$sql 3 = "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 ($sql 1);
mysql_query ($sql 2);
if (Mysql_errno ()) {
mysql_query (' rollback ');
echo ' err ';
} else {
mysql_query (' commit ');
echo ' OK ';
}

mysql_query (' SET autocommit=1 ');
mysql_query ($sql 3);

To be aware here,

MyISAM: Transactions are not supported for read-only programs to improve performance
InnoDB: Supports acid transactions, row-level locks, concurrency
Berkeley DB: Supporting Transactions
One more thing to note: MySQL's default behavior is to execute a COMMIT statement after each SQL statement executes, effectively making each statement independent of a transaction.

But often, we need to execute more than one SQL statement when using a transaction. This requires us to manually set the MySQL Autocommit property to 0, the default is 1.

Also, explicitly open a transaction using the START TRANSACTION statement. As in the example above.

If not, what will be the result?

We will//mysql_query (' SET autocommit=0′ ') in the second paragraph of the above Code; and//mysql_query ($sql 3); Comments are removed and then executed.

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

If we will//mysql_query (' SET autocommit=1′ '); The comment of this sentence is removed, then mysql_query ($sql 3); Will execute successfully.

A transaction is usually completed when a commit or ROLLBACK statement is executed, but some DDL statements implicitly trigger a commit.

such as 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 to see.

Copy Code code as follows:

$sql 1 = ' CREATE TABLE scoredetail_new (id int) ';
$sql 2 = ' Rename table Scoredetail to Scoredetail_bak ';
$sql 3 = ' Rename table scoredetail_new to Scoredetail ';

$mysqli = new mysqli (' localhost ', ' root ', ' ', ' db_lib2test ');
$mysqli->autocommit (false);/Start Things
$mysqli->query ($sql 1);
$mysqli->query ($sql 2);
$mysqli->query ($sql 3);
if (! $mysqli->errno) {
$mysqli->commit ();
echo ' OK ';
} else {
echo ' err ';
$mysqli->rollback ();
}

In the example above, if $SQL2 execution fails, $sql 1 will do the same. Why, then?

Because rename is executing, MySQL defaults to execute a commit first, then executes rename.

Attention

Only InnoDB and BDB types of data tables in MySQL can support transaction processing! Other types are not supported!

: General MySQL database default engine is MyISAM, this engine does not support transactions! If you want MySQL to support transactions, you can modify them manually:

The method is as follows: 1. Modify the C:/appserv/mysql/my.ini file, find the Skip-innodb, precede it with #, and save the file.

2. In operation input: services.msc, restart the MySQL service.

3. In phpMyAdmin, mysql->show engines; (or perform mysql->show variables like ' have_% '; To see InnoDB as yes, which means that the database supports InnoDB.
It also means that support services are transaction.