错误控制
DECLARE { exit|continue } HANDLER FOR
{SQLSTATE [VALUE] | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code}
SQLWARNING 是对所有以01开头的SQLSTATE代码的速记。
NOT FOUND 是对所有以02开头的SQLSTATE代码的速记。
SQLEXCEPTION 是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记
example:
DECLARE EXIT HANDLER FOR 1452
begin
....
end;
切记, declare 后不需要 ;
{SQLSTATE [VALUE] | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code}
SQLWARNING 是对所有以01开头的SQLSTATE代码的速记。
NOT FOUND 是对所有以02开头的SQLSTATE代码的速记。
SQLEXCEPTION 是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记
example:
DECLARE EXIT HANDLER FOR 1452
begin
....
end;
切记, declare 后不需要 ;
- 前提
-
create table t1 ( id int not null auto_increment primary key ) engine=innodb;
create table t2 ( num int, key(num), foreign key(num) REFERENCES t1(id) ) engine=innodb;
错误日志记录
create table error_log (id int not null auto_increment primary key,
time datetime, code int, msg varchar(80) );
create table t3 ( id int not null );
create table t4 ( id int check id > 10 );
create table t5 ( id int not null auto_increment primary key, sex char(1), check sex in ('m','f'));
- ex1
-
ex: 希望记录 insert 失败的记录 (失败原因, 外键)
插入错误信息具有错误代码 ERROR 1452 (23000)
delimiter //
create procedure test12 ( in a int)
begin
DECLARE EXIT HANDLER FOR 1452
begin
insert into error_log (time,code, msg) values(now(), 1452, concat("t2 Foreign key reference failure for value=",a));
end;
insert into t2 values (a);
end;
//
delimiter ; - ex2
-
更新 t1 表主键中涉及 t2 外键报错
ERROR 1451 (23000):
delimiter //
create procedure test18 ( in a int, in b int )
begin
DECLARE EXIT HANDLER FOR 1451
begin
insert into error_log (time,code, msg) values(now(), 1451, concat("t1 update faile, reference t2 for value=",b));
end;
update t1 set id=b where id=a;
end;
//
delimiter ;
call test18(1,10); - ex3
-
t1 插入重复值 错误代码 1062
delimiter //
create procedure test13( in a int)
begin
declare exit handler for 1062
begin
insert into error_log (time,code, msg) values (now(), 1062, concat('t1 Duplicate entry=',a));
end;
insert into t1 values (a);
end;
//
delimiter ;
- ex4
-
t3 表插入空值
假如直接插入, 则只出现警告, 默认使用 0 值取代
令无法插入 null 值
delimiter //
create procedure test15( in a int )
begin
declare exit handler for 1048
begin
insert into error_log (time,code, msg) values (now(), 1048, 'table t3 empty values');
end;
insert into t3 values (a);
end;
//
delimiter ;
测试:
set @a=1;
call test15(@a); <- 插入t3;
call test15(@b); <- 插入error_log - ex5
-
参考官方 13.1.2. ALTER TABLE语法 描述:
所有的存储引擎均对CHECK子句进行分析，但是忽略CHECK子句 (参考 show create table t4, 没有check 语法)
只能够利用存储过程应用对应检查 令 check id > 10 生效
delimiter //
create procedure test16( in a int )
begin
if a <= 10
then
insert into error_log (time,code, msg) values (now(), 9901, concat('table t4 values less then 10, values=',a));
else
insert into t4 values (a);
end if;
end;
//
delimiter ; - ex6
-
令 check sex in ('m','f') 生效
delimiter //
create procedure test17( in a char(1) )
begin
if a <> 'm' and a <> 'f'
then
insert into error_log (time,code, msg) values (now(), 9901, concat('table t5 sex values not in, values=',a));
else
insert into t5 (sex) values (a);
end if;
end;
//
delimiter ; - ex7
-
t3 表插入空值 (使用 sqlstate 控制码)
假如直接插入, 则只出现警告, 默认使用 0 值取代,
令插入时具有默认值
tt@localhost[new]>insert into t3 values (@c);
ERROR 1048 (23000): Column 'id' cannot be null [23000 <- 控制码]
delimiter //
create procedure test19( in a int )
begin
declare exit handler for SQLSTATE '23000'
begin
insert into error_log (time,code, msg) values (now(), 23000, 'table t3 empty values');
insert into t3 values (1);
end;
insert into t3 values (a);
end;
//
delimiter ;
测试:
set @a=1;
call test15(@a); <- 插入t3;
call test15(@b); <- 插入error_log 与 t3 表 - ex8
-
错重错误定义
create unique index id_idx on t3(id);
1. 重复键 [ERROR 1062 (23000)] 2. 插入字符, 第一次默认插入 0 3. 空值 [ERROR 1048 (23000):]
注意: in a varchar(20) 才能够判断是否字符 int 则全自动转换成为数字
delimiter //
create procedure test21( in a varchar(20))
begin
declare exit handler for 1062
begin
insert into error_log (time,code, msg) values (now(), 23000, 'table t3 duplicate key');
end;
declare exit handler for 1048
begin
insert into error_log (time,code, msg) values (now(), 23000, 'table t3 null values');
end;
if ( a regexp '^[[:digit:]]*$' ) = 1
then
insert into t3 values (a);
else
insert into error_log (time,code, msg) values (now(), 23000, 'table t3 not number');
end if;
end;
//
delimiter ; - 判断纯字符函数
-
- isnum() 函数
-
delimiter //
create function isnum( a char(20))
returns int
begin
declare r int;
select a regexp '^[[:digit:]]*$' into r;
if r = 1
then
return 1;
else
return 0;
end if;
end;
//
delimiter ;
