判断MYSQL语句执行结果



最近在做自己的网站，有些地方要判断mysql语句是否执行成功了，或者是SELECT语句是否取到了结果，查了查资料，终于解决了。

先来看一段php代码，有点长，建议直接运行一遍：点击下载代码

 

define("HOST","database ip");
	define("USER","your database account");
	define("PASS","your password");
	define("DB","database name");
	$con = mysql_connect(HOST,USER,PASS);
	if (!$con) die("Could not connect: " . mysql_error());
	mysql_select_db(DB, $con);

//sql 0
 $sql = "DROP TABLE `test` ";
 $result=mysql_query($sql);
 if($result)echo "table `test` already exist, so I drop it to continue the test
";
 echo "
";

//sql 1
 $sql="CREATE TABLE `test` (
	`rid` int(11) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`rid`),
	`uid` int(11) NOT NULL UNIQUE,
	`face` int NOT NULL,
	`nick` varchar(12) NOT NULL,
	`luck` int NOT NULL DEFAULT 10

) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

$result=mysql_query($sql,$con);
 if($result)echo "sql 1:",$sql," executed successfully
";
 else echo "sql 1:",$sql,"fail
";
 echo "
";

//sql 2
 $sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("1","1","newnius")";
 $result=mysql_query($sql);
 $count=mysql_affected_rows();
 if($count>0)echo "sql 2:",$sql," executed successfully
";
 else echo "sql 2:",$sql,"fail
";
 echo "
";

//list records
 echo "list records
";
 $sql="SELECT * FROM `test` ";
 $result = mysql_query($sql);
 $count=mysql_num_rows($result);
 echo $count," record(s) found:
";
 while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 echo "
";

//sql 3
 $sql="INSERT INTO `test`(`uid`, `face`) VALUES ("1","1")";
 $result=mysql_query($sql);
 $count=mysql_affected_rows();
 if($count>0)echo "sql 3:",$sql," executed successfully
";
 else echo "sql 3:",$sql,"fail,","because face can not be null
";
 echo "
";

//list records
 echo "list records
";
 $sql="SELECT * FROM `test` ";
 $result = mysql_query($sql);
 $count=mysql_num_rows($result);
 echo $count," record(s) found:
";
 while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 echo "
";

//add 3 records
 echo "add 3 records
";
 $sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("2","1","order")";
 $result=mysql_query($sql);

$sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("3","2","order")";
 $result=mysql_query($sql);

$sql="INSERT INTO `test`(`uid`, `face`, `nick`) VALUES ("4","3","order")";
 $result=mysql_query($sql);
 echo "
";

//list records
 echo "list records
";
 $sql="SELECT * FROM `test` ";
 $result = mysql_query($sql);
 $count=mysql_num_rows($result);
 echo $count," record(s) found:
";
 while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 echo "
";

//sql 4
 $sql="SELECT * FROM `test` WHERE nick="newnius" LIMIT 1";
 $result = mysql_query($sql);

$count=mysql_num_rows($result);
 if($count){
	echo "sql 4:",$sql," executed successfully.
";
	echo $count," record(s) found:
";
	while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 }
 else echo "sql 4:",$sql,"fail
";
 echo "
";

//sql 5
 $sql="UPDATE `test` SET uid = "10086" WHERE nick="newnius"";
 $result = mysql_query($sql);
 $count=mysql_affected_rows();
 if($count>0)
 {
	echo "sql 5:",$sql," executed successfully
";
	echo $count," record(s) is(are) affected
";
 }
 else echo "sql 5:",$sql,"fail
";
 echo "
";

//list records
 echo "list records
";
 $sql="SELECT * FROM `test` ";
 $result = mysql_query($sql);
 $count=mysql_num_rows($result);
 echo $count," record(s) found:
";
 while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 echo "
";

//sql 6
 $sql="UPDATE `test` SET uid = "10086" WHERE nick="unknown"";
 $result = mysql_query($sql);
 $count=mysql_affected_rows();
 if($count>0)echo "sql 6:",$sql," executed successfully
";
 else echo "sql 6:",$sql,"fail, because no record found
";
 echo "
";

//list records
 echo "list records
";
 $sql="SELECT * FROM `test` ";
 $result = mysql_query($sql);
 $count=mysql_num_rows($result);
 echo $count," record(s) found:
";
 while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 echo "
";

//sql 7
 $sql="DELETE FROM `test` WHERE nick ="order"";
 $result = mysql_query($sql);
 $count=mysql_affected_rows();
 if($count>0)
 {
	echo "sql 7:",$sql," executed successfully
";
	echo $count," record(s) deleted
";
 }
 else echo "sql 7:",$sql,"fail
";
 echo "
";

//list records
 echo "list records
";
 $sql="SELECT * FROM `test` ";
 $result = mysql_query($sql);
 $count=mysql_num_rows($result);
 echo $count," record(s) found:
";
 while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 echo "
";

//sql 8
 $sql="DELETE FROM `test` WHERE nick ="order"";
 $result = mysql_query($sql);
 $count=mysql_affected_rows();
 if($count>0)echo "sql 8:",$sql," executed successfully
";
 else echo "sql 8:",$sql,"fail, because no record found
";
 echo "
";

//list records
 echo "list records
";
 $sql="SELECT * FROM `test` ";
 $result = mysql_query($sql);
 $count=mysql_num_rows($result);
 echo $count," record(s) found:
";
 while($row = mysql_fetch_array($result))echo "rid:",$row["rid"]," uid:",$row["uid"]," nick:",$row["nick"],"
";
 echo "
";

//sql 9
 $sql = "DROP TABLE `test` ";
 $result=mysql_query($sql);
 if($result)echo "sql 9:",$sql," executed successfully
";
 else echo "sql 9:",$sql,"fail
";
 echo "
";

mysql_close($con);
 echo "test finished, thank you!
";
 echo "
";
 echo "click here to visit my blog
";
 echo "

";
 ?>

从代码中可以看出，判断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) 这个函数得到结果的记录数。

由此可见，两个函数就可以基本解决我们的问题啦。