MySQL函数和MySQLi函数用法详解

MySQL函数和MySQLi 函数允许您访问 MySQL 数据库服务器。以下介绍几个关于MySQL和MySQLi的相关概念：

• 在php5版本前，一般用php的mysql函数去驱动mysql数据库，如mysql_query()的函数，属于面向过程；php5版本以后，增加了mysqli，它是mysql系统函数的增强版，属于面向对象，用对象的方式操作驱动mysql数据库
• mysql是非持继连接函数，mysql每次链接都会打开一个连接的进程，而mysqli是永远连接函数，mysqli多次运行时使用同一连接进程,减少了服务器的开销。

---

MySQL和MySQLi函数

MySQL：参见http://www.w3school.com.cn/php/php_ref_mysql.asp
MySQLi：参见http://www.w3school.com.cn/php/php_ref_mysqli.asp

用法

//mysql$conn = mysql_connect('localhost', 'user', 'password');　//连接mysql数据库mysql_select_db('data_base'); //选择数据库　　$result = mysql_query('select * from data_base');//第二个可选参数，指定打开的连接　　$row = mysql_fetch_row( $result ) ) //取一行数据　　echo $row[0]; //输出第一个字段的值　　//mysqli$conn = new mysqli('localhost', 'user', 'password','data_base');//构造时候不指定数据库，下一句需要$conn -> select_db('data_base')实现$result = $conn -> query( 'select * from data_base' );　　$row = $result -> fetch_row(); echo row[0]; //以下介绍MySQLi常用的函数用法：//连接数据库$conn=new mysqli("localhost","root","root","db_database09");if (mysqli_connect_errno( )) {printf("Connect failed: %s\n", mysqli_connect_error( ));exit ( );} else {printf("Connect succeeded\n");}//设置字符编码$conn->query("set names gb2312");//错误检查if ($conn->query($sql) <> TRUE) {printf("Statement failed %d: (%s) %s\n" ,$conn->errno,$conn->sqlstate,$conn->error);}//简单无返回查询$conn->query("CREATE TABLE guy_1 (guys_integers INT)");if ($conn->errno <> 0 ) {printf("Statement failed %d: (%s) %s\n" ,$conn->errno,$conn->sqlstate,$conn->error);}//返回结果集fetch_object（关联数组）$sql="SELECT employee_id, surname, salary FROM employees WHERE salary>95000 AND department_id=1 AND status='G'";$results=$conn->query($sql);if ($conn->errno) { die ($conn->errno." ".$conn->error); }while($row=$results->fetch_object( )) {printf("%d\t%s\t%d\n",$row->employee_id,$row->surname,$row->salary);}//使用fetch_row返回结果集（数字数组）$sql="SELECT employee_id, surname, salary FROM employees WHERE salary>95000 AND department_id=1 AND status='G'";$results=$conn->query($sql);if ($conn->errno) { die ($conn->errno." ".$conn->error); }while($row=$results->fetch_row( )) {printf("%d\t%s\t%d\n",$row[0],$row[1],$row[2]);}//事务管理$conn->autocommit(FALSE);$conn->query("UPDATE account_balance SET balance=balance-$tfer_amount WHERE account_id=$from_account");if ($conn->errno) {printf("transaction aborted: %s\n",$conn->error);$conn->rollback( );}else {$conn->query("UPDATE account_balance SET balance=balance+$tfer_amount WHERE account_id=$to_account");if ($conn->errno) {printf("transaction aborted: %s\n",$conn->error);$conn->rollback( );}else {printf("transaction succeeded\n");$conn->commit( );}}//prepare语句$insert_stmt=$conn->prepare("INSERT INTO x VALUES(?,?)") or die($mysqli->error);$insert_stmt->bind_param("is", $my_number,$my_string); for ($my_number = 1; $my_number <= 10; $my_number++) {$my_string="row ".$my_number;$insert_stmt->execute( ) or die ($insert_stmt->error);}$insert_stmt->close( );//从prepared语句中返回结果集$sql="SELECT employee_id,surname,firstname FROM employees WHERE department_id=? AND status=? IMIT 5";$stmt = $conn->prepare($sql);if ($mysqli->errno<>0) {die($mysqli->errno.": ".$mysqli->error);}$stmt->bind_param("is",$input_department_id,$input_status) or die($stmt-error);$stmt->bind_result( $employee_id,$surname,$firstname) or die($stmt->error);$input_department_id=1;$input_status='G';$stmt->execute( );if ($mysqli->errno<>0) {die($stmt.errno.": ".$stmt->error) ;}while ($stmt->fetch( )) {printf("%s %s %s\n", $employee_id,$surname,$firstname);}//获得 Metadata结果集$metadata = $stmt->result_metadata( );$field_cnt = $metadata->field_count;while ($colinfo = $metadata->fetch_field( )) {printf("Column: %s\n", $colinfo->name);printf("max. Len: %d\n", $colinfo->max_length);printf("Type: %d\n\n", $colinfo->type);}//调用无结果集的存储过程$sql = 'call simple_stored_proc( )';$conn->query($sql);if ($conn->errno) {die("Execution failed: ".$conn->errno.": ".$conn->error);}else {printf("Stored procedure execution succeeded\n");}//返回单个结果集的存储过程CREATE PROCEDURE department_list( ) READS SQL DATA SELECT department_name,location from departments;$sql = "call department_list( )";$results = $conn->query($sql);if ($conn->errno) {die("Execution failed: ".$conn->errno.": ".$conn->error);}while ($row = $results->fetch_object( )) {printf("%s\t%s\n", $row->department_name, $row->location);}//有输入参数和返回结果集的存储过程CREATE PROCEDURE customers_for_rep(in_sales_rep_id INT) READS SQL DATA SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=in_sales_rep_id;$sql = "CALL customers_for_rep(?)";$stmt = $conn->prepare($sql);if ($conn->errno) {die($conn->errno.":: ".$conn->error);}$stmt->bind_param("i", $in_sales_rep_id);$in_sales_rep_id = 1;$stmt->execute( );if ($conn->errno) {die($conn->errno.": ".$conn->error);}$stmt->bind_result($customer_id,$customer_name);while ($stmt->fetch( )) {printf("%d %s \n", $customer_id,$customer_name);}//输出参数的处理CREATE PROCEDURE sp_rep_customer_count(in_emp_id DECIMAL(8,0), OUT out_cust_count INT) NOT DETERMINISTIC READS SQL DATA BEGIN SELECT count(*) INTO out_cust_count FROM customers WHERE sales_rep_id=in_emp_id; END;$sql="CALL sp_rep_customer_count(1,@customer_count)";$stmt = $conn->prepare($sql);if ($conn->errno) {die($conn->errno.": ".$conn->error);}$stmt->execute( );if ($conn->errno) {die($conn->errno.": ".$conn->error);}$stmt->close( );$results = $conn->query("SELECT @customer_count AS customer_count");$row = $results->fetch_object( );printf("Customer count=%d\n",$row->customer_count);//多结果集处理CREATE PROCEDURE stored_proc_with_2_results(in_sales_rep_id INT) DETERMINISTIC READS SQL DATA BEGIN SELECT employee_id,surname,firstname FROM employees WHERE employee_id=in_sales_rep_id; SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=in_sales_rep_id; END;$query = "call stored_proc_with_2_results( $employee_id )";if ($conn->multi_query($query)) {$result = $conn->store_result( );while ($row = $result->fetch_object( )) {printf("%d %s %s\n",$row->employee_id,$row->surname,$row->firstname);}$conn->next_result( );$result = $conn->store_result( );while ($row = $result->fetch_object( )) {printf("%d %s \n",$row->customer_id,$row->customer_name);}}//不确定结果集数的处理$query = "call stored_proc_with_2_results( $employee_id )";if ($conn->multi_query($query)) {do {if ($result = $conn->store_result( )) {while ($finfo = $result->fetch_field( )) {printf("%s\t", $finfo->name);}printf("\n");while ($row = $result->fetch_row( )) {for ($i=0;$i<$result->field_count;$i++) {printf("%s\t", $row[$i]);}printf("\n");}$result->close( );}} while ($conn->next_result());}

目录