mysql与mysqli 区别
首先, mysqli 连接是永久连接,而mysql是非永久连接。什么意思呢? mysql连接每当第二次使用的时候,都会重新打开一个新的进程,而mysqli则只使用同一个进程,这样可以很大程度的减轻服务器端压力。
其次,mysqli封装了诸如事务等一些高级操作,同时封装了DB操作过程中的很多可用的方法。具体查看 http://cn.php.net/mysqli
mysqli类是php自带的,不是默认开启的.

 
//1. 连接 详情见 http://cn.php.net/manual/en/mysqli.quickstart.connections.php
//最后一个参数是端口,如果不指定会默认为3306
$mysqli = new mysqli("127.0.0.1", "user", "password", "database", 3306);
//可以判断是否连接成功
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
} 
//可以输出连接信息
//echo $mysqli->host_info . "\n"; 
/2. /执行语句 详情见 http://cn.php.net/manual/en/mysqli.quickstart.statements.php
//或者mysqli_query($mysqli, 'select * from data_base' )
$result = $mysqli->query( 'select * from data_base' ); 
//可以通过data_seek来反方向的读取数据,其实这样还不如在sql语句里直接排序
//echo "Reverse order...\n";
for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
    $res->data_seek($row_no);
    $row = $res->fetch_assoc();
    echo " id = " . $row['id'] . "\n";
}

//或者按顺序读出
fetch_assoc() 函数从结果集中取得一行作为关联数组,用关键字索引取值
或者$row = mysqli_fetch_assoc($result)

while ($row = $res->fetch_assoc()) {
    echo " id = " . $row['id'] . "\n";
}

fetch_row() 函数从结果集中取得一行作为关联数组,用数字索引取值
或者$row = mysqli_fetch_row($result)

while ($row = $res->fetch_row()) {
    echo " id = " . $row['id'] . "\n";
}

fetch_object() 将一行取回到一个对象中,然后通过类的方式取值
或者$row = mysqli_fetch_object($result)

while ($row = $res->fetch_object()) {
    echo " id = " . $row->id. "\n";
}

fetch_array 可以指定返回方式
或者 mysqli_fetch_array

/* numeric array */
$row = $result->fetch_array(MYSQLI_NUM);
printf ("%s (%s)\n", $row[0], $row[1]);

/* associative array */
$row = $result->fetch_array(MYSQLI_ASSOC);
printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);

/* associative and numeric array */
$row = $result->fetch_array(MYSQLI_BOTH);
printf ("%s (%s)\n", $row[0], $row["CountryCode"]);

另外fetch_all 返回所有结果,可以指定索引类型
mixed mysqli_result::fetch_all ([ int $resulttype = MYSQLI_NUM ] )
mixed mysqli_fetch_all ( mysqli_result $result [, int $resulttype = MYSQLI_NUM ] )

为了防止sql注入,使用Prepared statement

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
     echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

/* Prepared statement: repeated execution, only data transferred from client to server */
for ($id = 2; $id < 5; $id++) {
    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}
//如果是查询语句,就用下面获取结果 
//$res = $stmt->get_result();
/* explicit close recommended */
$stmt->close();

一些option设置还可以参见
http://php.net/manual/en/mysqli.options.php
mysqli_options ( mysqli $link , int $option , mixed $value )
比如设置连接时间限制, $option = MYSQLI_OPT_CONNECT_TIMEOUT $value就是秒数

这里够用了,详情见 http://cn.php.net/mysqli

标签: mysqli

评论已关闭