linux centos系统
mysql版本5.1.41use test;
drop table if exists test.a ;
CREATE TABLE `test`.`a` (
`c2` INT NOT NULL ,
PRIMARY KEY (`c2`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
insert into test.a select 1;
select * from test.a;
rollback;
select * from test.a;执行结果,为什么rollback;之后,test.a表还有1条记录呢,不是应该回滚后,表里面没有记录吗?
mysql版本5.1.41use test;
drop table if exists test.a ;
CREATE TABLE `test`.`a` (
`c2` INT NOT NULL ,
PRIMARY KEY (`c2`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
insert into test.a select 1;
select * from test.a;
rollback;
select * from test.a;执行结果,为什么rollback;之后,test.a表还有1条记录呢,不是应该回滚后,表里面没有记录吗?
解决方案 »
- 编译安装MSYQL-PROXY的时候出错
- 能不能在索引上添加字段?
- 求一条sql语句,感激不尽
- 求教,如果表中没有某字段,就增加这个段的sql该怎么写?
- 关于论坛的列表sql
- perl(Class::MethodMaker) is needed by 怎么处理
- 在linux下C语言调用的问题
- mysql 错误,错误号(2013):ERROR 2013: Lost connection to MySQL server during query
- mysql中的replace语句到底该怎么写?
- default一条语句怎么默认约束两个列
- linux下postgresql-8.3.8-1-linux-x64.bin安装求教
- 为什么count(*)就是统计记录数呢?
是1啊,如果一个mysql库里面既有myisam表,又有innodb表的话,这个autocommit应该设置成多少呢?
1:自动提交,你insert 的进去的时候,mysql马上就提交了,还没等你rollback.
所以楼主想要rollback的话 需要 手动开启事务且不提交,方法:
start transaction/begin/set autocommit=0
设置成0之后,楼主就可以自己insert 之后,手动的提交或则回滚;(commit\rollback)
楼主做下实验就知道了;
select @autocommit;贴出来以供分析。
Query OK, 0 rows affected (0.01 sec)mysql> insert into test values(3, 4);
Query OK, 1 row affected (0.05 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from test;
+------+------+
| id | col2 |
+------+------+
| 1 | 2 |
| 2 | 1 |
| 1 | 3 |
| 3 | 4 |
+------+------+
4 rows in set (0.00 sec)mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)mysql> insert into test values(4, 4);
Query OK, 1 row affected (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.03 sec)mysql> select * from test;
+------+------+
| id | col2 |
+------+------+
| 1 | 2 |
| 2 | 1 |
| 1 | 3 |
| 3 | 4 |
+------+------+
4 rows in set (0.00 sec)mysql>当autocommit=0时,需要显式的commit来提交事务。
mysql> create table auto(id int);
Query OK, 0 rows affected (0.39 sec)mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)mysql> insert into auto values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from auto;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.05 sec)mysql> select * from auto;
Empty set (0.00 sec)要是rollback 改成commit 提交事务的话,则会写到表里面去了;--------
要是set autocommit =0 不执行的话,则rollback无效;楼主可以自己测试;
那为什么,我没有commit,也没有rollback,我只有commit,过一会后,也能select 出来呢?