mysql

浏览数：33 / 时间：2015年06月12日

mysql

数据库介绍

存储数据
事务

存储引擎

MyISAM: 无法处理事务
InnoDB:
        更新密集的表
        事务
        自动灾难恢复
show engines;

测试准备

create databases test;
use test;
create table student (id int auto_increment , name varchar(20), num int, primary key(id) ) ;
insert into student (name, num)  values (‘one‘, 111), (‘two‘, 222);

select name, num from student order by name asc;

connect

<?php
$link = new mysqli(‘localhost‘, ‘root‘, ‘YOUR_PASSWD‘, ‘shop‘);
if ($link->connect_errno) {
    echo ‘<font color="#c00"> fail:‘.$link->connect_error.‘</font>‘;
    exit();
} else {
    echo ‘<font color="#000"> succeed </font>‘;
}
?>

<?php
$link = new mysqli(‘localhost‘, ‘root‘, ‘YOUR_PASSWD‘);
if ($link->connect_errno) {
    echo ‘<font color="#c00"> fail:‘.$link->connect_error.‘</font>‘;
    exit();
} else {
    echo ‘<font color="#000"> succeed </font>‘;
}
$link->select_db(‘shop‘);
?>

query

query(..., MYSQLI_STORE_RESULT);
结果作为一个缓存集返回, 可以立即对整个结果集导航 (默认设置), 会增加内存, 可以很快知道返回了多少行

query(..., MYSQLI_USE_RESULT);
非缓存集, 根据需要从服务器获取结果集, 对于较大的结果集, 可以提高性能, 响应时间快

mysql_query() 不能传二进制 BLOB 字段，因为二进制信息中的\0 会被误判为语句结束，mysql_real_query() 则可以

<?php
$query = ‘show databases;‘;
$link->query($query);
if ($link->errno == 0) {
    echo ‘show databases succeed‘, ‘<br>‘;
} else {
    echo ‘show databases fail‘, $link->error, ‘<br>‘;
}
?>

<?php
$link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
if ($link->connect_errno) {
    echo ‘fail: ‘, $link->connect_error;
    exit();
}

$query = ‘select name, num from student order by name asc‘;
$result = $link->query($query);
if ($link->errno) {
    echo ‘query fail: ‘, $link->error, ‘<br>‘;
}

while (list($name, $num) = $result->fetch_row()) {
    echo ‘name = ‘, $name, ‘ num = ‘, $num, ‘<br>‘;
}

while ($row = $result->fetch_row()) {
    echo ‘name = ‘, $row[0], ‘ num = ‘, $row[1], ‘<br>‘;
}

while ($row = $result->fetch_object()) {
    echo ‘name = ‘, $row->name, ‘ num = ‘, $row->num, ‘<br>‘;
}

echo ‘there are ‘, $link->affected_rows, ‘ affected rows <br>‘;

$result->free();

$link->close();
?>

prepare

传统的 query() 使用循环机制, (重复解析)

<?php
$link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
if ($link->connect_errno) {
    echo ‘fail: ‘, $link->connect_error;
    exit();
}

$query = ‘select name, num from student order by name asc‘;

$stmt = $link->stmt_init();

$stmt->prepare($query);

....

$stmt->close();
$link->close();
?>

绑定参数

insert

i   所有整数类型
d   double 和 float 类型
s   其他类型  如字符串

<?php
$link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
if ($link->connect_errno) {
    echo ‘fail: ‘, $link->connect_error;
    exit();
}

$query = ‘insert into student (name, num) values (?, ?)‘;

$stmt = $link->stmt_init();

$stmt->prepare($query);

$stmt->bind_param(‘si‘, $name, $num);

$name_array = array(‘test1‘, ‘test2‘);
$num_array = array(001, 002);

$x = 0;

while ($x < sizeof($name_array)) {
    $name = $name_array[$x];
    $num  = $num_array[$x];

    $stmt->execute();
    $x++;
}

$stmt->close();
$link->close();
?>

绑定结果

select

<?php
$link = new mysqli("localhost", "root", "YOUR_PASSWD", "test");
if ($link->connect_errno) {
    echo ‘fail: ‘, $link->connect_error;
    exit();
}

$query = ‘select name, num from student‘;

$stmt = $link->stmt_init();

$stmt->prepare($query);

$stmt->execute();

$stmt->bind_result($name, $num);

echo ‘<table border=1> <th>name</th><th>num</th>‘;
while ($stmt->fetch()) {
    echo ‘<tr><td>‘, $name, ‘</td>‘, ‘<td>‘, $num, ‘</td></tr>‘;
}
echo ‘</table>‘;

$stmt->close();
$link->close();
?>