建立两个表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)