mysql函数和mysqli函数用法详解

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());
}

目录