Go语言视频零基础入门到精通

admin

  
  
最近在做自己的网站，有些地方要判断mysql语句是否执行成功了，或者是SELECT语句是否取到了结果，查了查资料，终于解决了。
先来看一段php代码，有点长，建议直接运行一遍：点击下载代码

2.    

复制代码

1. define("HOST","database ip");
2.         define("USER","your database account");
3.         define("PASS","your password");
4.         define("DB","database name");
5.         $con = mysql_connect(HOST,USER,PASS);
6.         if (!$con) die("Could not connect: " . mysql_error());
7.         mysql_select_db(DB, $con);
9. //sql 0
10. $sql = "DROP TABLE `test` ";
11. $result=mysql_query($sql);
12. if($result)echo "table `test` already exist, so I drop it to continue the test
13. ";
14. echo "
15. ";
17. //sql 1
18. $sql="CREATE TABLE `test` (
19.         `rid` int(11) NOT NULL AUTO_INCREMENT,
20.         PRIMARY KEY (`rid`),
21.         `uid` int(11) NOT NULL UNIQUE,
22.         `face` int NOT NULL,
23.         `nick` varchar(12) NOT NULL,
24.         `luck` int NOT NULL DEFAULT 10
26. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
28. $result=mysql_query($sql,$con);
29. if($result)echo "sql 1:",$sql," executed successfully
30. ";
31. else echo "sql 1:",$sql,"fail
32. ";
33. echo "
34. ";
36. //sql 2
37. $sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("1","1","newnius")";
38. $result=mysql_query($sql);
39. $count=mysql_affected_rows();
40. if($count>0)echo "sql 2:",$sql," executed successfully
41. ";
42. else echo "sql 2:",$sql,"fail
43. ";
44. echo "
45. ";
47. //list records
48. echo "list records
49. ";
50. $sql="SELECT * FROM `test` ";
51. $result = mysql_query($sql);
52. $count=mysql_num_rows($result);
53. echo $count," record(s) found:
54. ";
55. while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
56. ";
57. echo "
58. ";
60. //sql 3
61. $sql="INSERT INTO `test`(`uid`, `face`) VALUES ("1","1")";
62. $result=mysql_query($sql);
63. $count=mysql_affected_rows();
64. if($count>0)echo "sql 3:",$sql," executed successfully
65. ";
66. else echo "sql 3:",$sql,"fail,","because face can not be null
67. ";
68. echo "
69. ";
71. //list records
72. echo "list records
73. ";
74. $sql="SELECT * FROM `test` ";
75. $result = mysql_query($sql);
76. $count=mysql_num_rows($result);
77. echo $count," record(s) found:
78. ";
79. while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
80. ";
81. echo "
82. ";
84. //add 3 records
85. echo "add 3 records
86. ";
87. $sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("2","1","order")";
88. $result=mysql_query($sql);
90. $sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("3","2","order")";
91. $result=mysql_query($sql);
93. $sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("4","3","order")";
94. $result=mysql_query($sql);
95. echo "
96. ";
98. //list records
99. echo "list records
100. ";
101. $sql="SELECT * FROM `test` ";
102. $result = mysql_query($sql);
103. $count=mysql_num_rows($result);
104. echo $count," record(s) found:
105. ";
106. while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
107. ";
108. echo "
109. ";
111. //sql 4
112. $sql="SELECT * FROM `test` WHERE nick="newnius" LIMIT 1";
113. $result = mysql_query($sql);
115. $count=mysql_num_rows($result);
116. if($count){
117.         echo "sql 4:",$sql," executed successfully.
118. ";
119.         echo $count," record(s) found:
120. ";
121.         while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
122. ";
123. }
124. else echo "sql 4:",$sql,"fail
125. ";
126. echo "
127. ";
129. //sql 5
130. $sql="UPDATE `test` SET uid = "10086" WHERE nick="newnius"";
131. $result = mysql_query($sql);
132. $count=mysql_affected_rows();
133. if($count>0)
134. {
135.         echo "sql 5:",$sql," executed successfully
136. ";
137.         echo $count," record(s) is(are) affected
138. ";
139. }
140. else echo "sql 5:",$sql,"fail
141. ";
142. echo "
143. ";
145. //list records
146. echo "list records
147. ";
148. $sql="SELECT * FROM `test` ";
149. $result = mysql_query($sql);
150. $count=mysql_num_rows($result);
151. echo $count," record(s) found:
152. ";
153. while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
154. ";
155. echo "
156. ";
158. //sql 6
159. $sql="UPDATE `test` SET uid = "10086" WHERE nick="unknown"";
160. $result = mysql_query($sql);
161. $count=mysql_affected_rows();
162. if($count>0)echo "sql 6:",$sql," executed successfully
163. ";
164. else echo "sql 6:",$sql,"fail, because no record found
165. ";
166. echo "
167. ";
169. //list records
170. echo "list records
171. ";
172. $sql="SELECT * FROM `test` ";
173. $result = mysql_query($sql);
174. $count=mysql_num_rows($result);
175. echo $count," record(s) found:
176. ";
177. while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
178. ";
179. echo "
180. ";
182. //sql 7
183. $sql="DELETE FROM `test` WHERE nick ="order"";
184. $result = mysql_query($sql);
185. $count=mysql_affected_rows();
186. if($count>0)
187. {
188.         echo "sql 7:",$sql," executed successfully
189. ";
190.         echo $count," record(s) deleted
191. ";
192. }
193. else echo "sql 7:",$sql,"fail
194. ";
195. echo "
196. ";
198. //list records
199. echo "list records
200. ";
201. $sql="SELECT * FROM `test` ";
202. $result = mysql_query($sql);
203. $count=mysql_num_rows($result);
204. echo $count," record(s) found:
205. ";
206. while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
207. ";
208. echo "
209. ";
211. //sql 8
212. $sql="DELETE FROM `test` WHERE nick ="order"";
213. $result = mysql_query($sql);
214. $count=mysql_affected_rows();
215. if($count>0)echo "sql 8:",$sql," executed successfully
216. ";
217. else echo "sql 8:",$sql,"fail, because no record found
218. ";
219. echo "
220. ";
222. //list records
223. echo "list records
224. ";
225. $sql="SELECT * FROM `test` ";
226. $result = mysql_query($sql);
227. $count=mysql_num_rows($result);
228. echo $count," record(s) found:
229. ";
230. while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
231. ";
232. echo "
233. ";
235. //sql 9
236. $sql = "DROP TABLE `test` ";
237. $result=mysql_query($sql);
238. if($result)echo "sql 9:",$sql," executed successfully
239. ";
240. else echo "sql 9:",$sql,"fail
241. ";
242. echo "
243. ";
245. mysql_close($con);
246. echo "test finished, thank you!
247. ";
248. echo "
249. ";
250. echo "click here to visit my blog
251. ";
252. echo "
254. ";
255. ?>

复制代码

  
  
  
[code][/code]


从代码中可以看出，判断INSERT、UPDATE、DELETE 用的判断方法是mysql_affected_rows() ，该方法返回的是最近一次与 link_identifier 关联的 INSERT、UPDATE 或 DELETE 查询所影响的记录行数。关于mysql_affected_rows()的返回值，w3school是如此说明的：
1、执行成功，则返回受影响的行的数目，如果最近一次查询失败的话，函数返回 -1。
2、如果最近一次操作是没有任何条件（WHERE）的 DELETE 查询，在表中所有的记录都会被删除，但本函数返回值在 4.1.2 版之前都为 0。
3、当使用 UPDATE 查询，MySQL 不会将原值与新值一样的列更新。这样使得 mysql_affected_rows() 函数返回值不一定就是查询条件所符合的记录数，只有真正被修改的记录数才会被返回。
4、REPLACE 语句首先删除具有相同主键的记录，然后插入一个新记录。该函数返回的是被删除的记录数加上被插入的记录数。
显然，INSERT、UPDATE 或 DELETE都（可能，指UPDATE）会改变数据表，所以可以用该函数。然而SELECT语句不会对数据表进行改动，故该函数不能用来判断SELECT的执行情况。
不过既然SELECT语句是为了取出符合条件的结果，我们就可以对结果进行分析，用mysql_num_rows($result) 这个函数得到结果的记录数。
由此可见，两个函数就可以基本解决我们的问题啦。