mysql预处理事务处理实例讲解

6.9 mysql 预处理

实验一：传入一个值

    mysql> prepare stu_pre from 'select * from student where id>?';
    Query OK, 0 rows affected (0.00 sec)
    Statement prepared

    mysql> set @i=3;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select @i;
    +------+
    | @i   |
    +------+
    |    3 |
    +------+
    1 row in set (0.00 sec)

    mysql> execute stu_pre using @i;
    +------+------+------+-------+
    | id   | name | sex  | score |
    +------+------+------+-------+
    |    4 | dd   | male |   3.5 |
    |    5 | ee   | male |   3.6 |
    |    6 | ff   | male |   3.9 |
    +------+------+------+-------+
    3 rows in set (0.00 sec)

实验二：传入多个值

    mysql> select * from student;
    +------+------+--------+-------+
    | id   | name | sex    | score |
    +------+------+--------+-------+
    |    1 | aa   | female |  NULL |
    |    2 | bb   | male   |  NULL |
    |    3 | cc   | male   |   3.1 |
    |    4 | dd   | male   |   3.5 |
    |    5 | ee   | male   |   3.6 |
    |    6 | ff   | male   |   3.9 |
    +------+------+--------+-------+

    mysql> set @id=3;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set @sex='male';
    Query OK, 0 rows affected (0.00 sec)

    mysql> prepare stu_pre from 'select * from student where id execute stu_pre using @id,@sex;
    +------+------+------+-------+
    | id   | name | sex  | score |
    +------+------+------+-------+
    |    2 | bb   | male |  NULL |
    +------+------+------+-------+
    1 row in set (0.00 sec)

实验三：删除预处理

    mysql> execute stu_pre using @id,@name;
    +------+------+------+-------+
    | id   | name | sex  | score |
    +------+------+------+-------+
    |    2 | bb   | male |  NULL |
    +------+------+------+-------+
    1 row in set (0.00 sec)

    mysql> drop prepare stu_pre;
    Query OK, 0 rows affected (0.00 sec)

    mysql> execute stu_pre using @id,@name;
    ERROR 1243 (HY000): Unknown prepared statement handler (stu_pre) given to EXECUTE

6.10 mysql 事务处理

MyISAM引擎不支持事务，innodb支持事务
修改表引擎：

mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                   |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `score` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table student engine=innodb;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                   |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `score` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6.10.1 关闭自动提交功能

    set autocommit=0;

6.10.2 保存还原点

    savepoint pointName;

6.10.3 还原至某个点

    rollback to pointName;

6.10.4 还原到原始点

    rollback

实验一：

#设置事务不自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+------+--------+-------+
| id   | name | sex    | score |
+------+------+--------+-------+
|    1 | aa   | female |  NULL |
|    2 | bb   | male   |  NULL |
|    3 | cc   | male   |   3.1 |
|    6 | ff   | male   |   3.9 |
+------+------+--------+-------+
4 rows in set (0.00 sec)

#删除id=1的数据

mysql> delete from student where id=1;
Query OK, 1 row affected (0.00 sec)

#设置还原点
mysql> savepoint p1;
Query OK, 0 rows affected (0.00 sec)

#可以看到id=1的数据已经消失
mysql> select * from student;
+------+------+------+-------+
| id   | name | sex  | score |
+------+------+------+-------+
|    2 | bb   | male |  NULL |
|    3 | cc   | male |   3.1 |
|    6 | ff   | male |   3.9 |
+------+------+------+-------+
3 rows in set (0.00 sec)

#再删除id=2的数据
mysql> delete from student where id=2;
Query OK, 1 row affected (0.00 sec)

#设置还原点
mysql> savepoint p2;
Query OK, 0 rows affected (0.00 sec)


#可以看到id=5的数据已经消失
mysql> select * from student;
+------+------+------+-------+
| id   | name | sex  | score |
+------+------+------+-------+
|    3 | cc   | male |   3.1 |
|    6 | ff   | male |   3.9 |
+------+------+------+-------+
2 rows in set (0.00 sec)

#回滚至第一个还原点
mysql> rollback to p1;
Query OK, 0 rows affected (0.00 sec)

#可以看到id=2的数据恢复了
mysql> select * from student;
+------+------+------+-------+
| id   | name | sex  | score |
+------+------+------+-------+
|    2 | bb   | male |  NULL |
|    3 | cc   | male |   3.1 |
|    6 | ff   | male |   3.9 |
+------+------+------+-------+
3 rows in set (0.00 sec)

#回滚到初始点
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

#可见数据全部恢复
mysql> select * from student;
+------+------+--------+-------+
| id   | name | sex    | score |
+------+------+--------+-------+
|    1 | aa   | female |  NULL |
|    2 | bb   | male   |  NULL |
|    3 | cc   | male   |   3.1 |
|    6 | ff   | male   |   3.9 |
+------+------+--------+-------+
4 rows in set (0.00 sec)

#提交事务
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+------+--------+-------+
| id   | name | sex    | score |
+------+------+--------+-------+
|    1 | aa   | female |  NULL |
|    2 | bb   | male   |  NULL |
|    3 | cc   | male   |   3.1 |
|    6 | ff   | male   |   3.9 |
+------+------+--------+-------+
4 rows in set (0.00 sec)

注意：再进行实验时需要将事务的自动提交关闭，否则不能进行该实验。