PHP MySQL and mysqli transaction usage Instructions _php Tutorial

First, the MYSQLI connection is a permanent connection, and MySQL is a non-permanent connection. What do you mean? The MySQL connection will reopen a new process whenever it is used for the second time, while Mysqli will only use the same process, which can greatly reduce the server-side pressure.

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

The most widely used place is the mysqli transaction.

such as the following example:

The code is as follows	Copy Code
$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 already encapsulated the operation of MySQL transactions well. The following example:

copy code

$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 ',"); $mysqli = new mysqli (' localhost ', ' root ', ' ', ' db_lib2test '); $mysqli->autocommit (FALSE);//Start 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 perform the transaction.

The code is as follows

Copy Code

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

It is important to note that

MyISAM: Transactions are not supported for read-only programs to improve performance
InnoDB: Supports acid transactions, row-level locks, concurrency
Berkeley DB: Support transactions
It is also important to note that the default behavior of MySQL is to execute a COMMIT statement after each SQL statement executes, effectively independently of each statement as a single transaction.

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

Also, open a transaction explicitly by 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 code above; and//mysql_query ($sql 3); The comment is removed and then executed.

At this point, mysql_query ($sql 3) execution is 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.

Typically a commit or ROLLBACK statement executes when a transaction is completed, but some DDL statements, and so on, 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

$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 above example, if the $SQL2 execution error occurs, $sql 1 will still be executed. Why is it?

Because rename executes, MySQL defaults to commit, and then executes rename.

Attention

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

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

The method is as follows: 1. Modify the C:/appserv/mysql/my.ini file, find the Skip-innodb, add # to the front, save the file.

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

3. To phpMyAdmin, mysql->show engines; (or execute mysql->show variables like ' have_% '; ), viewing InnoDB as yes means that the database supports InnoDB.
It also indicates that the support transaction is transaction.

http://www.bkjia.com/PHPjc/630689.html www.bkjia.com true http://www.bkjia.com/PHPjc/630689.html techarticle First, the MYSQLI connection is a permanent connection, and MySQL is a non-permanent connection. What do you mean? The MySQL connection will reopen a new process whenever it is used for the second time, and Mysqli ...

