MySQL的Transaction

在MySQL数据库中使用事务来处理数据操作，

需要注意的是MySQL对Transaction的支持需要表为INNODB所以我们以下面的例子来简单使用Transaction

 

 

首先

mysql> USE study;
Database changed
mysql>

新建数据库transaction;

 

 

mysql> CREATE TABLE transaction (id INT, name VARCHAR(20))ENGINE=INNODB;
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW CREATE TABLE transaction;
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                       |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| transaction | CREATE TABLE `transaction` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

 

MySQL中通过变量autocommit的值来判断时候开启事务，当autocommit为1时表示自动提交数据，所以这里是指autocommit=0

mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM transaction;
Empty set (0.00 sec)

 

下面插入数据

mysql> INSERT INTO transaction VALUES(1, 'adam');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM transaction;
+------+------+
| id   | name |
+------+------+
|    1 | adam |
+------+------+
1 row in set (0.00 sec)

 

使用ROLLBACK来取消上一步的操作。或者使用COMMIT;来提交操作。

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM transaction;
Empty set (0.00 sec)

mysql>

简单的事务应用。需要注意表必须为INNODB