| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> mysql >> 带你轻松接触MySQL数据库的异常处理 dai ni qing song jie chu MySQL shu ju ku de yi chang chu li
 

带你轻松接触MySQL数据库的异常处理

2008-06-01 03:23:18 编辑來源:互联网 繁體版 评论
 
 
本文为【带你轻松接触MySQL数据库的异常处理】的汉字拼音对照版显示拼音
  duiyuMySQLdeyichangchulibenrenbuchangyongbuguowojiaodehuanshiyouxiexialaidebiyao

  biaozhungeshi

  DECLARE handler_type HANDLER FOR condition_value[,...] statement

  handler_type:

  CONTINUE

  | EXIT

  | UNDO --zanshibuzhichi

  condition_value:

  SQLSTATE [VALUE] sqlstate_value

  | condition_name

  | SQLWARNING

  | NOT FOUND

  | SQLEXCEPTION

  | mysql_error_code

  condition_valuexijie

  1MySQL ERROR CODE liebiao

  ruguoxuyaochakangengduodecuowuliebiaokeyizhijiedaoMySQLanzhuanglujingxia

  biruwode/usr/local/mysql/share/mysql/errmsg.txt

  shuomingSQLSTATE [VALUE] sqlstate_valuezhezhonggeshishizhuanmenweiANSI SQL he ODBCyijiqitadebiaozhun.

  bingbushisuoyoudeMySQL ERROR CODE douyangshedaoSQLSTATE

  2jiarubuxuyaocharuERROR CODEkeyiyongsujitiaojianlaidaiti

  SQLWARNING daibiaosuoyouyi01kaitoudecuowudaima

  NOT FOUND daibiaosuoyouyi02kaitoudecuowudaimadangranyekeyidaibiaoyigeyoubiaodaodashujujidemoyi

  SQLEXCEPTION daibiaochuleSQLWARNINGheNOT FOUND desuoyoucuowudaima

  3jutishili

  CREATE TABLE t (s1 int,primary key (s1));

  mysql> use t_girl

  Database changed

  mysql> CREATE TABLE t (s1 int,primary key (s1));

  Query OK, 0 rows affected (0.00 sec)

  mysql>

  mysql>

  mysql> DELIMITER ||

  mysql> CREATE PROCEDURE handlerdemo ()

  -> BEGIN

  -> DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN END; -- yudaochongfujianzhijiu退tuichu

  -> SET @x = 1;

  -> INSERT INTO t VALUES (1);

  -> SET @x = 2;

  -> INSERT INTO t VALUES (1);

  -> SET @x = 3;

  -> END||

  Query OK, 0 rows affected (0.00 sec)

  mysql> DELIMITER ;

  mysql> call handlerdemo();

  Query OK, 0 rows affected (0.00 sec)

  mysql> select @x;

  +------+

  | @x |

  +------+

  | 2 |

  +------+

  1 row in set (0.00 sec)

  mysql> call handlerdemo();

  Query OK, 0 rows affected (0.00 sec)

  mysql> select @x;

  +------+

  | @x |

  +------+

  | 1 |

  +------+

  1 row in set (0.00 sec)

  mysql>

  yudaocuowujixudeqingkuang

  mysql> truncate table t;

  Query OK, 0 rows affected (0.01 sec)

  mysql> DELIMITER $$

  mysql> DROP PROCEDURE IF EXISTS `t_girl`.`handlerdemo`$$

  Query OK, 0 rows affected (0.00 sec)

  mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `handlerdemo`()

  -> BEGIN

  -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END;

  -> SET @x = 1;

  -> INSERT INTO t VALUES (1);

  -> SET @x = 2;

  -> INSERT INTO t VALUES (1);

  -> SET @x = 3;

  -> END$$

  Query OK, 0 rows affected (0.01 sec)

  mysql> DELIMITER ;

  mysql> call handlerdemo();

  Query OK, 0 rows affected (0.00 sec)

  mysql> select @x;

  +------+

  | @x |

  +------+

  | 3 |

  +------+

  1 row in set (0.00 sec)

  mysql> call handlerdemo();

  Query OK, 0 rows affected (0.00 sec)

  mysql> select @x;

  +------+

  | @x |

  +------+

  | 3 |

  +------+

  1 row in set (0.00 sec)

  mysql>

  womenkeyikandaoshizhongzhixingdaozuihou

  dangranshangmiandeSQLSTATE '23000'keyitihuanwei1062

  jinggao

  mysql> alter table t add s2 int not null;

  Query OK, 0 rows affected (0.01 sec)

  Records: 0 Duplicates: 0 Warnings: 0

  zhegeliemeiyoumorenzhicharudeshihouhuichuxianjinggaohuozhe1364cuowutishi

  mysql> DELIMITER $$

  mysql> DROP PROCEDURE IF EXISTS `t_girl`.`handlerdemo`$$

  Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `handlerdemo`()

  -> BEGIN

  -> DECLARE CONTINUE HANDLER FOR 1062 BEGIN END;

  -> DECLARE CONTINUE HANDLER FOR SQLWARNING

  -> BEGIN

  -> update t set s2 = 2;

  -> END;

  -> DECLARE CONTINUE HANDLER FOR 1364

  -> BEGIN

  -> INSERT INTO t(s1,s2) VALUES (1,3);

  -> END;

  -> SET @x = 1;

  -> INSERT INTO t(s1) VALUES (1);

  -> SET @x = 2;

  -> INSERT INTO t(s1) VALUES (1);

  -> SET @x = 3;

  -> END$$

  Query OK, 0 rows affected (0.00 sec)

  mysql> DELIMITER ;

  mysql> call handlerdemo();

  Query OK, 0 rows affected (0.00 sec)

  mysql> select * from t;

  +----+----+

  | s1 | s2 |

  +----+----+

  | 1 | 3 |

  +----+----+

  1 row in set (0.00 sec)

  yudaocuowushicharudexinjilu

  mysql> select @x;

  +------+

  | @x |

  +------+

  | 3 |

  +------+

  1 row in set (0.00 sec)原文】【拼音
 
 
 
上一篇《使用实例管理器轻松管理多个MySQL实例》
下一篇《循序渐进讲解MySQL数据库的性能调整》
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
对于MySQL的异常处理,本人不常用。不过我觉得还是有写下来的必要。 标准格式 DECLARE handler_type HANDLER FOR condition_value[,...] statement handler_type: CONTINUE | EXIT | UNDO --暂时不支持 condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code condition_value细节 1、MySQL ERROR CODE 列表 如果需要查看更多的错误列表可以直接到MySQL安装路径下。 比如我的/usr/local/mysql/share/mysql/errmsg.txt 说明:SQLSTATE [VALUE] sqlstate_value这种格式是专门为ANSI SQL 和 ODBC以及其他的标准. 并不是所有的MySQL ERROR CODE 都映射到SQLSTATE。 2、假如不需要插入ERROR CODE,可以用速记条件来代替 SQLWARNING 代表所有以01开头的错误代码 NOT FOUND 代表所有以02开头的错误代码,当然也可以代表一个游标到达数据集的末尾。 SQLEXCEPTION 代表除了SQLWARNING和NOT FOUND 的所有错误代码。 3、具体示例: CREATE TABLE t (s1 int,primary key (s1)); mysql> use t_girl Database changed mysql> CREATE TABLE t (s1 int,primary key (s1)); Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> DELIMITER || mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN END; -- 遇到重复键值就退出 -> SET @x = 1; -> INSERT INTO t VALUES (1); -> SET @x = 2; -> INSERT INTO t VALUES (1); -> SET @x = 3; -> END|| Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> call handlerdemo(); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +------+ | @x | +------+ | 2 | +------+ 1 row in set (0.00 sec) mysql> call handlerdemo(); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +------+ | @x | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> 遇到错误继续的情况 mysql> truncate table t; Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER $$ mysql> DROP PROCEDURE IF EXISTS `t_girl`.`handlerdemo`$$ Query OK, 0 rows affected (0.00 sec) mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `handlerdemo`() -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END; -> SET @x = 1; -> INSERT INTO t VALUES (1); -> SET @x = 2; -> INSERT INTO t VALUES (1); -> SET @x = 3; -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; mysql> call handlerdemo(); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec) mysql> call handlerdemo(); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec) mysql> 我们可以看到,始终执行到最后。 当然,上面的SQLSTATE '23000'可以替换为1062。 警告: mysql> alter table t add s2 int not null; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 这个列没有默认值,插入的时候会出现警告或者1364错误提示。 mysql> DELIMITER $$ mysql> DROP PROCEDURE IF EXISTS `t_girl`.`handlerdemo`$$ Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `handlerdemo`() -> BEGIN -> DECLARE CONTINUE HANDLER FOR 1062 BEGIN END; -> DECLARE CONTINUE HANDLER FOR SQLWARNING -> BEGIN -> update t set s2 = 2; -> END; -> DECLARE CONTINUE HANDLER FOR 1364 -> BEGIN -> INSERT INTO t(s1,s2) VALUES (1,3); -> END; -> SET @x = 1; -> INSERT INTO t(s1) VALUES (1); -> SET @x = 2; -> INSERT INTO t(s1) VALUES (1); -> SET @x = 3; -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> call handlerdemo(); Query OK, 0 rows affected (0.00 sec) mysql> select * from t; +----+----+ | s1 | s2 | +----+----+ | 1 | 3 | +----+----+ 1 row in set (0.00 sec) 遇到错误时,插入的新记录。 mysql> select @x; +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec)
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
赏心悦目的模特儿(9)
赏心悦目的模特儿(8)
赏心悦目的模特儿(7)
赏心悦目的模特儿(6)
周六一日游--绿野翠蜂场(一)
骆驼峰
下一站上环
杂乱的几张Danang
 
>>返回首页<<
 
 
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有