mysql api 原生用法示例

//初始化数据库连接，并保存到内存以重复利用
function getConnect($db = '') {
    static $connect;
    if (!empty($connect) && $connect->ping()) {
        return $connect;
    } else if (!empty($connect)) {
        $connect->close();
    }
    
    $connect = new mysqli(HOST, USER, PASSWORD, $db, PORT);
    if ($connect->connect_errno) {
        die('Failed to connect mysql.' . $connect->connect_error);
    }
    $connect->set_charset('utf8');
    return $connect;
}

function  selectDb($db) {
    return getConnect()->select_db($db);
}

function escapeString($str) {
    return getConnect()->real_escape_string($str);
}

$connect = getConnect();//常用属性：affected_rows / insert_id / error

//单语句查询
$result = $connect->query($sql = 'select 123');
//fetch_row返回的是索引型数组，fetch_array可返回关联/索引型数组
//fetch_assoc关联型数组， fetch_object返回的是对象
//注意fetch_all是一次性返回所有数据，比前面这些方法更耗内存。
//$result->data_seek($offset)可以跳到指定的行号数据。
while ($row = $result->fetch_row) { 
    echo $row[0], "\n";
}
$result->free(); //及时释放结果集是个良好的习惯

//多语句查询
$ret = $connect->multi_query($sql = 'use mydb;select 345;');
if ($ret) {
    //获取结果需要用到more_results / next_results / use_result
    do {
        if ($result = $connect->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n",  $row[0]);
            }
            $result->free();
            if ($connect->more_results()) {
                printf("The result of next sql:\n");
            }
        }
    } while ($connect->next_results());
}

//预处理
$stmt = $connect->prepare($sql = "select f1, f2 from tbl where f1=? and f2=?");
$stmt->bind_param('ss'/*占位符的类型*/, 'val1', 'val2');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_row()) {
    printf("%s, %s \n", $row[0], $row[1]);
}
$result->free();
$stmt->close();

//事务
$connect->autocommit(false);
$connect->query($sql1);
$result = $connect->query($sql2);
if (!$result) {
    $connect->rollback();
    $connect->autocommit(true);
} else {
    $connect->commit();
    $connect->autocommit(true);
}

//关闭数据库连接
$connect->close();