432      阿裏雲  技術社區[雲棲]

mysql error handler 例

前提

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 ;

最後更新：2017-04-02 22:16:20

  上一篇： Javascript中的prototype

  下一篇： Java注解