PHP_MYSQL的事物处理机制-极品分享

PHP_MYSQL的事物处理机制

一、如果想使用事物处理机制必须在见表时使用InnoDB ENGINE

在见表的时候要加上ENGINE=InnoDB

例如：

create table account(
id int primary key,
balance float
) ENGINE=InnoDB DEFAULT CHARSET=UTF8
insert into account values (1,34);
insert into account values (2,20);

二、但，你的mysql服务器必须要支持InnoDB 引擎，默认是不支持的。只有默认的MyISAM引擎，安装InnoDB 引擎方法：

解决方法：

首先看一下MySQL安装了什么引擎

[root@Bugsong ~]# mysql -u root -pmysql> show plugin;+------------+--------+----------------+--------------+---------+| Name       | Status | Type           | Library      | License |+------------+--------+----------------+--------------+---------+| binlog     | ACTIVE | STORAGE ENGINE | NULL         | GPL     || CSV        | ACTIVE | STORAGE ENGINE | NULL         | GPL     || MEMORY     | ACTIVE | STORAGE ENGINE | NULL         | GPL     || MyISAM     | ACTIVE | STORAGE ENGINE | NULL         | GPL     || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL         | GPL     |+------------+--------+----------------+--------------+---------+5 rows in set, 1 warning (0.06 sec)

[root@Bugsong ~]# mysql -u root -p

mysql> show plugin;

+------------+--------+----------------+--------------+---------+

+------------+--------+----------------+--------------+---------+

+------------+--------+----------------+--------------+---------+

5 rows in set, 1 warning (0.06 sec)

这里我们可以看到只有MyISAM引擎。

接下来进行安装：

mysql> install plugin innodb soname 'ha_innodb.so';Query OK, 0 rows affected (0.36 sec)+------------+--------+----------------+--------------+---------+| Name       | Status | Type           | Library      | License |+------------+--------+----------------+--------------+---------+| binlog     | ACTIVE | STORAGE ENGINE | NULL         | GPL     || CSV        | ACTIVE | STORAGE ENGINE | NULL         | GPL     || MEMORY     | ACTIVE | STORAGE ENGINE | NULL         | GPL     || MyISAM     | ACTIVE | STORAGE ENGINE | NULL         | GPL     || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL         | GPL     || InnoDB     | ACTIVE | STORAGE ENGINE | ha_innodb.so | GPL     |+------------+--------+----------------+--------------+---------+6 rows in set, 1 warning (0.06 sec)

mysql> install plugin innodb soname 'ha_innodb.so';

Query OK, 0 rows affected (0.36 sec)

+------------+--------+----------------+--------------+---------+

+------------+--------+----------------+--------------+---------+

+------------+--------+----------------+--------------+---------+

6 rows in set, 1 warning (0.06 sec)

安装完毕。

到这里就可以继续安装Mediawiki了，在数据库设置的界面就可以选择数据库引擎了。

如果你需要设置成默认引擎，请继续往下看：

设置默认数据库引擎

首先查看一下引擎：

mysql> show engines;+------------+---------+------------------------------------------------------------+--------------+------+------------+| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |+------------+---------+------------------------------------------------------------+--------------+------+------------+| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         || InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        || MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         || MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         || MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |+------------+---------+------------------------------------------------------------+--------------+------+------------+5 rows in set (0.00 sec)

mysql> show engines;

+------------+---------+------------------------------------------------------------+--------------+------+------------+

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| CSV | YES | CSV storage engine | NO | NO | NO |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

5 rows in set (0.00 sec)

数据库引擎里已经有InnoDB了。

mysql>alter table tb_name engine=innodb;

1	mysql>alter table tb_name engine=innodb;

输入上面的命令就可以设置InnoDB为默认的数据库引擎了。

三、事务处理简单案例一：

<?php
//把账户1的2元钱转账到账号2上
$mysqli=new MySQLi("127.0.0.1","root","zkeys","sqlphp");
if($mysqli->connent_error){
   die($mysqli->connent_error);
}
//★将提交设置为false
$mysqli->autocommit(false);
$sql1="update account set balance=balance-2 where id=1";
$sql2="update account set balance=balance+2 where id=2";

$b1=$mysqli->query($sql1);
$b2=$mysqli->query($sql2);
if(!$b1||!$b2){
   echo "失败".$mysqli->error;
   //★失败则回滚
   $mysqli->rollback();
}else{
   echo "成功";
   //★成功则提交
   $mysqli->commit();
}
$mysqli->close();
?>

四、事务处理简单案例二：

<?php
/*======================================事务处理======================================*/
//建表时要使用InnoDB(type=InnoDB)
    $mysqli=new MySQLi("localhost","root","","xiaoqiangdb");

    //$mysqli->set_charset("utf8");    //设置字符集

    $mysqli->autocommit(0); //关闭自动提交

    $error=true;
    $price=50;
    $sql="UPDATE zhanghaodb set yue=yue-{$price} where name='zhangsan'";
    $result=$mysqli->query($sql);

    if(!$result){
        $error=false;
        echo "从张三转出失败<br>";
    }else{
        if($mysqli->affected_rows==0){
            $error=false;
            echo "张三的钱没有变化";
        }else{
            echo "张三的钱转出成功<br>";
        }
    }



    $sql="UPDATE zhanghaodb set yue=yue+{$price} where name='lisi'";
    $result=$mysqli->query($sql);

    if(!$result){
        $error=false;
        echo "从李四转入失败<br>";
    }else{
        if($mysqli->affected_rows==0){
            $error=false;
            echo "李四的钱没有变化";
        }else{
            echo "李四的钱转入成功<br>";
        }
    }

    if($error){
        echo "转账成功!";
    }else{
        echo "转账失败!";
        $mysqli->rollback();    //回滚
    }

    $mysqli->autocommit(1);    //开启自动提交
    $mysqli->close();

?>

极品分享