PageRenderTime 48ms CodeModel.GetById 20ms RepoModel.GetById 0ms app.codeStats 0ms

/Upload/documents/handbook/database/mysql-storage-process-detailed/mysql-storage-process-detailed-error-handling.html

http://dyhb-frame.googlecode.com/
HTML | 197 lines | 191 code | 6 blank | 0 comment | 0 complexity | ca20e8c8d23b9cf9659a98e48e7a01ed MD5 | raw file
    

  1. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

    2. 

  2. <html>

    3. 

  3.  <head>

    4. 

  4.   <title>The DoYouHaoBaby PHP Framework For PHP5 | ????????</title>

    5. 

  5.   <meta http-equiv="content-type" content="text/html; charset=gbk" />

    6. 

  6.   <meta name="Author" content="???Dyhb">

    7. 

  7.   <meta name="Keywords" content="DoYouHaoBaby,Framework">

    8. 

  8.   <meta name="Description" content="The DoYouHaoBaby PHP Framework | ?????????????">

    9. 

  9.   <link rel="stylesheet" type="text/css" href="./../../media/css/style.css" />

    10. 

  10.   <link rel="stylesheet" type="text/css" href="./../../media/css/highlight.css" />

    11. 

  11.  </head>

    12. 

  12.  <body style="margin:0">

    13. 

  13.   <div id="header"><img src="./../../media/images/logo.png"  border="0" title="The DoYouHaoBaby PHP Framework Logo" ></div>

    14. 

  14.   <div id="content"> 

    15. 

  15.      <div id="version">

    16. 

  16. 	    The DoYouHaoBaby PHP Framework????????<br>

    17. 

  17.         Copyright(c) <a href="http://doyouhaobaby.net">DoYouHaoBaby.NET</a>

    18. 

  18.         All Rights Reserved.

    19. 

  19.      </div>

    20. 

  20. 

    21. 

  21. 

    22. 

  22.  <div class="menu">

    23. 

  23. 	    <table cellspacing=0 cellpadding=0 width="95%"  border=0>

    24. 

  24.  <tbody>

    25. 

  25.  <tr>

    26. 

  26.  <th align=middle colspan=3>The DoYouHaoBaby PHP Framework????????</th>

    27. 

  27. </tr>

    28. 

  28.  <tr>

    29. 

  29.  <td valign=bottom align=left width="25%">

    30. 

  30. <a accesskey="p" href="../../index.html">??</a>?

    31. 

  31. </td>

    32. 

  32.  <td valign=bottom align=middle width="50%">

    33. 

  33. </td>

    34. 

  34.  <td valign=bottom align=right width="25%">

    35. 

  35. <a accesskey="n" href="./index.html">??</a>

    36. 

  36. </td>

    37. 

  37. </tr>

    38. 

  38. </tbody>

    39. 

  39. </table>

    40. 

  40.     </div>

    41. 

  41. 

    42. 

  42.      <div id="box">

    43. 

  43. 	     <div class="title">[ ???? ]</div>

    44. 

  44. 		 <div class="detail"> 

    45. 

  45. 		<p>

    46. 

  46. 			?????????</p>

    47. 

  47. 		<ul>

    48. 

  48. 			<li>

    49. 

  49. 				????</li>

    50. 

  50. 			<li>

    51. 

  51. 				?????</li>

    52. 

  52. 			<li>

    53. 

  53. 				??</li>

    54. 

  54. 		</ul>

    55. 

  55. 		<p>

    56. 

  56. 			???????????????<br />

    57. 

  57. 			<br />

    58. 

  58. 			<div class="title2"><strong>?????????</strong></div><br />

    59. 

  59. 			?INSERT???????????????????????????????????????????????????INSERT?????????????????????????????????????????????????????????????<br />

    60. 

  60. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    61. 

  61. <OL class=dp-c>

    62. 

  62. <LI class=alt><SPAN><SPAN>mysql&gt;&nbsp;CREATE&nbsp;TABLE&nbsp;t2 &nbsp;&nbsp;</SPAN></SPAN></LI>

    63. 

  63. <LI><SPAN>s1&nbsp;INT,&nbsp;PRIMARY&nbsp;KEY&nbsp;(s1)) &nbsp;&nbsp;</SPAN></LI>

    64. 

  64. <LI class=alt><SPAN>engine=innodb;</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    65. 

  65. <LI><SPAN>mysql&gt;&nbsp;CREATE&nbsp;TABLE&nbsp;t3&nbsp;(s1&nbsp;INT,&nbsp;KEY&nbsp;(s1),&nbsp;FOREIGN&nbsp;KEY&nbsp;(s1)&nbsp;REFERENCES&nbsp;t2&nbsp;(s1))&nbsp;engine=innodb;</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    66. 

  66. <LI class=alt><SPAN>mysql&gt;&nbsp;INSERT&nbsp;INTO&nbsp;t3&nbsp;VALUES&nbsp;(5);</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    67. 

  67. <LI><SPAN>... &nbsp;&nbsp;</SPAN></LI>

    68. 

  68. <LI class=alt><SPAN>ERROR&nbsp;1216&nbsp;(23000):&nbsp;Cannot&nbsp;add&nbsp;</SPAN><SPAN class=keyword>or</SPAN><SPAN>&nbsp;update&nbsp;a&nbsp;child&nbsp;row:&nbsp;a&nbsp;foreign&nbsp;key&nbsp;constraint&nbsp;fails&nbsp;???????????????&nbsp;&nbsp;</SPAN></SPAN></LI></OL></DIV>

    69. 

  69. 			<br />

    70. 

  70. 			??????????????????????????InnoDB?????????????????????????????????????????????????????????1216?<br />

    71. 

  71. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    72. 

  72. <OL class=dp-c>

    73. 

  73. <LI class=alt><SPAN><SPAN>CREATE&nbsp;TABLE&nbsp;</SPAN><SPAN class=func>error_log</SPAN><SPAN>&nbsp;(error_message &nbsp;&nbsp;</SPAN></SPAN></LI>

    74. 

  74. <LI><SPAN>CHAR(80))</SPAN><SPAN class=comment>//</SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI></OL></DIV><br />

    75. 

  75. 			?????????????????????????<br />

    76. 

  76. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    77. 

  77. <OL class=dp-c>

    78. 

  78. <LI class=alt><SPAN><SPAN>CREATE&nbsp;PROCEDURE&nbsp;p22&nbsp;(parameter1&nbsp;INT)&nbsp;BEGIN &nbsp;&nbsp;</SPAN></SPAN></LI>

    79. 

  79. <LI><SPAN>DECLARE&nbsp;</SPAN><SPAN class=func>EXIT</SPAN><SPAN>&nbsp;HANDLER&nbsp;FOR&nbsp;1216 &nbsp;&nbsp;</SPAN></SPAN></LI>

    80. 

  80. <LI class=alt><SPAN>INSERT&nbsp;INTO&nbsp;</SPAN><SPAN class=func>error_log</SPAN><SPAN>&nbsp;VALUES&nbsp;(CONCAT('Time:&nbsp;',current_date, &nbsp;&nbsp;</SPAN></SPAN></LI>

    81. 

  81. <LI><SPAN>'.&nbsp;Foreign&nbsp;Key&nbsp;Reference&nbsp;Failure&nbsp;For &nbsp;&nbsp;</SPAN></LI>

    82. 

  82. <LI class=alt><SPAN>Value&nbsp;=&nbsp;',parameter1)); &nbsp;&nbsp;</SPAN></LI>

    83. 

  83. <LI><SPAN>INSERT&nbsp;INTO&nbsp;t3&nbsp;VALUES&nbsp;(parameter1);&nbsp; &nbsp;&nbsp;</SPAN></LI>

    84. 

  84. <LI class=alt><SPAN></SPAN><SPAN class=func>END</SPAN><SPAN>;</SPAN><SPAN class=comment>//</SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI></OL></DIV>

    85. 

  85. 			<br />

    86. 

  86. 			??????????????????DECLARE EXIT HANDLER????????????????1215??????????????????????EXIT????????????????????<br />

    87. 

  87. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    88. 

  88. <OL class=dp-c>

    89. 

  89. <LI class=alt><SPAN><SPAN>CALL&nbsp;p22&nbsp;(5)&nbsp;</SPAN><SPAN class=comment>//</SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI></OL></DIV>

    90. 

  90. 			<br />

    91. 

  91. 			???????????????????5??????????????????????????????????????t3?????????????error_log???? ????????????? INSERT into table t3 ?????<br />

    92. 

  92. 			<br />

    93. 

  93. 			<div class="title2"><strong>?????????</strong></div><br />

    94. 

  94. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    95. 

  95. <OL class=dp-c>

    96. 

  96. <LI class=alt><SPAN><SPAN>DECLARE &nbsp;&nbsp;</SPAN></SPAN></LI>

    97. 

  97. <LI><SPAN>{&nbsp;</SPAN><SPAN class=func>EXIT</SPAN><SPAN>&nbsp;|&nbsp;CONTINUE&nbsp;}&nbsp;HANDLER&nbsp;FOR &nbsp;&nbsp;</SPAN></SPAN></LI>

    98. 

  98. <LI class=alt><SPAN>{&nbsp;error-number&nbsp;|&nbsp;{&nbsp;SQLSTATE&nbsp;error-string&nbsp;}&nbsp;|&nbsp;condition&nbsp;}&nbsp;SQL&nbsp;statement&nbsp;&nbsp;</SPAN></LI></OL></DIV>

    99. 

  99. 			<br />

    100. 

  100. 			???????????????????????????????MySQL???????????EXIT????????????????????????????CONTINUE?????EXIT????????????????????????????????????????<br />

    101. 

  101. 			<br />

    102. 

  102. 			<strong>1.CONTINUE????</strong><br />

    103. 

  103. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    104. 

  104. <OL class=dp-c>

    105. 

  105. <LI class=alt><SPAN><SPAN>CREATE&nbsp;TABLE&nbsp;t4&nbsp;(s1&nbsp;int,primary&nbsp;key(s1));</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    106. 

  106. <LI><SPAN>CREATE&nbsp;PROCEDURE&nbsp;p23&nbsp;() &nbsp;&nbsp;</SPAN></LI>

    107. 

  107. <LI class=alt><SPAN>BEGIN &nbsp;&nbsp;</SPAN></LI>

    108. 

  108. <LI><SPAN>DECLARE&nbsp;CONTINUE&nbsp;HANDLER &nbsp;&nbsp;</SPAN></LI>

    109. 

  109. <LI class=alt><SPAN>FOR&nbsp;&nbsp;&nbsp;SQLSTATE&nbsp;'23000'&nbsp;SET&nbsp;@x2&nbsp;=&nbsp;1;&nbsp;SET&nbsp;@x&nbsp;=&nbsp;1; &nbsp;&nbsp;</SPAN></LI>

    110. 

  110. <LI><SPAN>INSERT&nbsp;INTO&nbsp;t4&nbsp;VALUES&nbsp;(1);&nbsp;SET&nbsp;@x&nbsp;=&nbsp;2; &nbsp;&nbsp;</SPAN></LI>

    111. 

  111. <LI class=alt><SPAN>INSERT&nbsp;INTO&nbsp;t4&nbsp;VALUES&nbsp;(1);&nbsp;SET&nbsp;@x&nbsp;=&nbsp;3; &nbsp;&nbsp;</SPAN></LI>

    112. 

  112. <LI><SPAN></SPAN><SPAN class=func>END</SPAN><SPAN>;</SPAN><SPAN class=comment>//</SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI></OL></DIV>

    113. 

  113. 			<br />

    114. 

  114. 			??MySQL??????CONTINUE?????????????????????????????????????CONTINUE?????????<br />

    115. 

  115. 			<br />

    116. 

  116. 			<strong>2. ??CONTINUE????</strong><br />

    117. 

  117. 			?????SQLSTATE????????????????????MySQL????1216????????23000SQLSTATE??????????????????????????????????????????&quot;SET @x = 1&quot;?????1???????????@x????2??????????????????????????????????????????????????????????????????????????????@x2???2??????????????CONTINUE??????????????????????<br />

    118. 

  118. 			?????@x???3???<br />

    119. 

  119. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    120. 

  120. <OL class=dp-c>

    121. 

  121. <LI class=alt><SPAN><SPAN>mysql&gt;&nbsp;CALL&nbsp;p23()</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    122. 

  122. <LI><SPAN>Query&nbsp;OK,&nbsp;0&nbsp;rows&nbsp;affected&nbsp;(0.00&nbsp;sec) &nbsp;&nbsp;</SPAN></LI>

    123. 

  123. <LI class=alt><SPAN>mysql&gt;&nbsp;SELECT&nbsp;@x,&nbsp;@x2</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    124. 

  124. <LI><SPAN>+------+------+ &nbsp;&nbsp;</SPAN></LI>

    125. 

  125. <LI class=alt><SPAN>|&nbsp;@x&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;@x2&nbsp;&nbsp;&nbsp;| &nbsp;&nbsp;</SPAN></LI>

    126. 

  126. <LI><SPAN>+------+------+ &nbsp;&nbsp;</SPAN></LI>

    127. 

  127. <LI class=alt><SPAN>|&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;| &nbsp;&nbsp;</SPAN></LI>

    128. 

  128. <LI><SPAN>+------+------+ &nbsp;&nbsp;</SPAN></LI>

    129. 

  129. <LI class=alt><SPAN>1&nbsp;row&nbsp;in&nbsp;set&nbsp;(0.00&nbsp;sec)&nbsp;&nbsp;</SPAN></LI></OL></DIV>

    130. 

  130. 			<br />

    131. 

  131. 			?????????@x????????????3???@x2????1?????? ?????????????????????????????????????????????????????????????????????????????????????????????????????</p>

    132. 

  132. 		<p>

    133. 

  133. 			<strong>1???CONTINUE</strong><br />

    134. 

  134. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    135. 

  135. <OL class=dp-c>

    136. 

  136. <LI class=alt><SPAN><SPAN>CREATE&nbsp;PROCEDURE&nbsp;p24&nbsp;() &nbsp;&nbsp;</SPAN></SPAN></LI>

    137. 

  137. <LI><SPAN>BEGIN &nbsp;&nbsp;</SPAN></LI>

    138. 

  138. <LI class=alt><SPAN>DECLARE&nbsp;`Constraint&nbsp;Violation`&nbsp;CONDITION&nbsp;FOR&nbsp;SQLSTATE&nbsp;'23000'; &nbsp;&nbsp;</SPAN></LI>

    139. 

  139. <LI><SPAN>DECLARE&nbsp;</SPAN><SPAN class=func>EXIT</SPAN><SPAN>&nbsp;HANDLER&nbsp;FOR &nbsp;&nbsp;</SPAN></SPAN></LI>

    140. 

  140. <LI class=alt><SPAN>`Constraint&nbsp;Violation`&nbsp;ROLLBACK; &nbsp;&nbsp;</SPAN></LI>

    141. 

  141. <LI><SPAN>&nbsp;START&nbsp;TRANSACTION; &nbsp;&nbsp;</SPAN></LI>

    142. 

  142. <LI class=alt><SPAN>INSERT&nbsp;INTO&nbsp;t2&nbsp;VALUES&nbsp;(1); &nbsp;&nbsp;</SPAN></LI>

    143. 

  143. <LI><SPAN>INSERT&nbsp;INTO&nbsp;t2&nbsp;VALUES&nbsp;(1); &nbsp;&nbsp;</SPAN></LI>

    144. 

  144. <LI class=alt><SPAN>COMMIT; &nbsp;&nbsp;</SPAN></LI>

    145. 

  145. <LI><SPAN></SPAN><SPAN class=func>END</SPAN><SPAN>;&nbsp;</SPAN><SPAN class=comment>//</SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI></OL></DIV>

    146. 

  146. 			<br />

    147. 

  147. 			???????????????????????????????SQLSTATE??????????????????????????????????????????????t2???InnoDB???????????????ROLLBACK?????ROLLBACK???????????????????????????????SQLSTATE 23000???????SQLSTATE 23000??????<br />

    148. 

  148. 			<br />

    149. 

  149. 			<strong>2. ????</strong><br />

    150. 

  150. 			?????????ROLLBACK???????SQLSTATE 23000?????<br />

    151. 

  151. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    152. 

  152. <OL class=dp-c>

    153. 

  153. <LI class=alt><SPAN><SPAN>mysql&gt;&nbsp;CALL&nbsp;p24()</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    154. 

  154. <LI><SPAN>Query&nbsp;OK,&nbsp;0&nbsp;rows&nbsp;affected&nbsp;(0.28&nbsp;sec) &nbsp;&nbsp;</SPAN></LI>

    155. 

  155. <LI class=alt><SPAN>&nbsp;&nbsp;</SPAN></LI>

    156. 

  156. <LI><SPAN>&nbsp;&nbsp;</SPAN></LI>

    157. 

  157. <LI class=alt><SPAN>mysql&gt;&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;t2</SPAN><SPAN class=comment>//&nbsp;Empty&nbsp;set&nbsp;(0.00&nbsp;sec)</SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI></OL></DIV>

    158. 

  158. 			<br />

    159. 

  159. 			????????????????????????????t2???????????????????????????<br />

    160. 

  160. 			<DIV class=dp-highlighter><DIV class=bar></DIV>

    161. 

  161. <OL class=dp-c>

    162. 

  162. <LI class=alt><SPAN><SPAN>mysql&gt;&nbsp;CREATE&nbsp;PROCEDURE&nbsp;p9&nbsp;() &nbsp;&nbsp;</SPAN></SPAN></LI>

    163. 

  163. <LI><SPAN>-&gt;&nbsp;BEGIN &nbsp;&nbsp;</SPAN></LI>

    164. 

  164. <LI class=alt><SPAN>-&gt;&nbsp;&nbsp;&nbsp;&nbsp;DECLARE&nbsp;</SPAN><SPAN class=func>EXIT</SPAN><SPAN>&nbsp;HANDLER&nbsp;FOR&nbsp;NOT&nbsp;FOUND&nbsp;BEGIN&nbsp;</SPAN><SPAN class=func>END</SPAN><SPAN>; &nbsp;&nbsp;</SPAN></SPAN></LI>

    165. 

  165. <LI><SPAN>-&gt;&nbsp;&nbsp;&nbsp;&nbsp;DECLARE&nbsp;</SPAN><SPAN class=func>EXIT</SPAN><SPAN>&nbsp;HANDLER&nbsp;FOR&nbsp;SQLEXCEPTION&nbsp;BEGIN&nbsp;</SPAN><SPAN class=func>END</SPAN><SPAN>; &nbsp;&nbsp;</SPAN></SPAN></LI>

    166. 

  166. <LI class=alt><SPAN>-&gt;&nbsp;&nbsp;&nbsp;&nbsp;DECLARE&nbsp;</SPAN><SPAN class=func>EXIT</SPAN><SPAN>&nbsp;HANDLER&nbsp;FOR&nbsp;SQLWARNING&nbsp;BEGIN&nbsp;</SPAN><SPAN class=func>END</SPAN><SPAN>; &nbsp;&nbsp;</SPAN></SPAN></LI>

    167. 

  167. <LI><SPAN>-&gt;&nbsp;</SPAN><SPAN class=func>END</SPAN><SPAN>;</SPAN><SPAN class=comment>// </SPAN><SPAN>&nbsp;&nbsp;</SPAN></SPAN></LI>

    168. 

  168. <LI class=alt><SPAN>Query&nbsp;OK,&nbsp;0&nbsp;rows&nbsp;affected&nbsp;(0.00&nbsp;sec)&nbsp;&nbsp;</SPAN></LI></OL></DIV>

    169. 

  169. 			<br />

    170. 

  170. 			????????????NOT FOUND (????), SQLEXCEPTION (??),SQLWARNING (?????)???????????????????????????????????????&quot;DECLARE SQLEXCEPTION CONDITION ...&quot;?????????????<br />

    171. 

  171. 			<br />

    172. 

  172. 			<span style="color: rgb(0, 128, 0);"><u><em><strong>To be continue!</strong></em></u></span></p>

    173. 

  173. 

    174. 

  174. 		 </div>

    175. 

  175.      </div>

    176. 

  176. 

    177. 

  177. <div class="menu">

    178. 

  178.  <table cellspacing=0 cellpadding=0 width="95%"  border=0>

    179. 

  179.  <tbody>

    180. 

  180.  <tr>

    181. 

  181.  <td valign=top align=middle width="34%">

    182. 

  182. <a accesskey="h" href="../../index.html">??</a>

    183. 

  183. </td>

    184. 

  184. </tr>

    185. 

  185.  <tr>

    186. 

  186.  <td valign=top align=middle width="34%">

    187. 

  187. <a accesskey="u" href="./index.html">??</a>

    188. 

  188. </td>

    189. 

  189. </tr>

    190. 

  190. </tbody>

    191. 

  191. </table>

    192. 

  192. </div>

    193. 

  193. 

    194. 

  194. </div>

    195. 

  195.   <div id="footer">Copyright (c) <a href="http://doyouhaobaby.net" title="The DoYouHaoBaby Framework" >DoYouHaoBaby</a> All rights reserved.?</div>

    196. 

  196.  </body>

    197. 

  197. </html>
    198.