本文共 4174 字，大约阅读时间需要 13 分钟。
错误控制 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 后不需要 ; 前提 - 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 ;
转载地址：http://sqnni.baihongyu.com/