mysql-Advanced Things

Things, plainly, is to bind multiple SQL statements together, if one of them can not be executed on all of these SQL termination, the typical use of it is involved in Money transfer operations;

INODB,BDB and NDB to support things

MyISAM not supported

View the MySQL support database commands used

Show engines

Modify the Table engine

ALTER TABLE XXX Engine=innodb;

Turn off automatic submission of MySQL when you start things

Start transaction; or begin;

Turn off Auto-commit: Set autocommit=0;

Turn on auto-commit: Set autocommit=1;

Ps:

with start transaction; and set autocommit=0; The difference is that the former is automatically committed after the commit command is sent and the latter needs to be manually opened

Set autocommit, Commit, begin, alter, create, rollback, and so on will automatically commit the transaction

Here's how to do it:

First create a bank table

CREATE TABLE Bank (
Bid int unsigned primary key auto_increment,
Name Char (a) NOT null default ' ',
Money Decimal (6,2) unsigned NOT NULL default 0
);

Insert two messages

INSERT into bank (Name,money) VALUES (' Mr. A ', +), (' Mr. B ', 0);

Open a transaction (complete, successful)
Begin
Turn off auto-commit
Set autocommit=0;
Minus 500 first.
Update bank set money=money-500 where bid=1;
Plus 500
Update bank set money=money+500 where bid=2;
Commit rollback
Commit;/rollback;

If not successful, the above commit; Replace with rollback; Rollback operation

Working with PDO in PHP demo

1<?PHP2 $dsn= "mysql:host=127.0.0.1;dbname= Test database name";3 Try{4     //Connect PDO5     $pdo=NewPdo ($dsn, ' Root ', ');6     //Set exception error7     $pdo->setattribute (pdo::attr_errmode,pdo::errmode_exception);8     //Set character sets9     $pdo->query (' SET NAMES UTF8 ');Ten     //Open Transaction One     $pdo->query ("BEGIN"); A     //Turn off auto-commit -     $pdo->query ("SET autocommit=0"); -     //1. Deduction of Money the     $pdo-exec("UPDATE Bank SET money=money-500 WHERE bid=1"); -     //2. Add Money -     $pdo-exec("Upd1ate Bank SET money=money+500 WHERE bid=2"); -     //Submit +     $pdo->query ("COMMIT"); -  +}Catch(pdoexception$e){ A     //Rollback If there is an error, undo the previous action at     $pdo->query (' ROLLBACK '); -     Echo' <span style= ' color:red ' > '.$e->getmessage (). ' </span> '; - } -  -  -?>

In this case, when the SQL command in the try brackets goes wrong, the following rollback operation is made to ensure that all two SQL will succeed

mysql-Advanced Things