- 积分
- 44
- UID
- 15508
- 阅读权限
- 10
- 注册时间
- 2014-4-23
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
建立两个表test1,test2,表test1的userid字段长度是8,表test2的userid字段长度是6
create table test1
(
idx int AUTO_INCREMENT,
userid varchar(8) not null,
primary key (idx)
)
ENGINE = InnoDB AUTO_INCREMENT=1;
create table test2
(
idx int AUTO_INCREMENT,
userid varchar(6) not null,
primary key (idx)
)
ENGINE = InnoDB AUTO_INCREMENT=1;
建立一个存储过程proc_test,原意是向两个表test1,test2分别插入记录,如果其中任何一个插入失败就全部回滚。
DELIMITER //
CREATE PROCEDURE proc_test
(IN i_userid VARCHAR(32))
begin
loop_label: loop
start transaction;
insert into test1(userid) values(i_userid);
if row_count() < 1 then
set @ret = -1;
rollback;
leave loop_label;
end if;
insert into test2(userid) values(i_userid);
if row_count() < 1 then
set @ret = -1;
rollback;
leave loop_label;
else
set @ret = 0;
commit;
leave loop_label;
end if;
end loop;
select @ret;
end
//
DELIMITER ;
使用存储过程,插入“test”
mysql> call proc_test('test');
+------+
| @ret |
+------+
| 0 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1;
+-----+--------+
| idx | userid |
+-----+--------+
| 1 | test |
+-----+--------+
1 row in set (0.01 sec)
mysql> select * from test2;
+-----+--------+
| idx | userid |
+-----+--------+
| 1 | test |
+-----+--------+
1 row in set (0.00 sec)
查询两个表都是正常的,没有问题
使用存储过程,插入“test1234”
mysql> call proc_test('test1234');
ERROR 1265 (01000): Data truncated for column 'userid' at row 1
“test12345” 的长度超过了表test2中字段userid定义的长度,插入失败,查询结果如下
可是这样就没有达到我预想的结果,插入任何一个表失败后,所有的操作都要回滚
请各位帮忙看看哪里的问题,谢谢了。
mysql> select * from test1;
+-----+----------+
| idx | userid |
+-----+----------+
| 1 | test |
| 2 | test1234 |
+-----+----------+
2 rows in set (0.00 sec)
mysql> select * from test2;
+-----+--------+
| idx | userid |
+-----+--------+
| 1 | test |
+-----+--------+
1 row in set (0.00 sec)
|
|