php的数据库编程

php的数据库编程

php的pdo编程

通过简单的例子看看pdo编程的增删改查

$pdo = new PDO("mysql:host=localhost;dbname=statis_data","root","123456");

//增加
$id = rand(1,10000);
$insert_sql = "insert into test(id)values(:id)";
$result = $pdo->prepare($insert_sql);
$result->execute(array(":id"=>$id));

$id2 = rand(1,10000);
$result->bindParam(":id", $id2);
$result->execute();

//删除
$delete_sql = "delete from test where id in($id,$id2)";
$pdo->exec($delete_sql);

//修改
$update_sql = "update test set id=111 where id=:id";
$result = $pdo->prepare($update_sql);
$id = 1;
$result->bindParam(":id", $id);
$result->execute();


//查找
$select_sql = "select * from test";
$query = $pdo->query($select_sql);
while($row=$query->fetch()){
    print_r($row);
}

pdo编程为数据库迁移做好准备。通常数据库确定后是很少变得，至少互联网开发是这样的

pdo编程读取缺点，相对于mysqli之类的扩展，会有一些性能损失，有些情况下这种损失
是无法接受的。

php的mysqli编程

php的mysqli编程的例子

class MysqlHandle
{
    private $host,$user,$password,$db;
    private $port = 3306;
    private $charset = 'utf8';
    private $timeout = 5;

    private $resource_arr = array();
    private $current_connect = null;
    private $db_config = null;

    //retry times
    private $retry_times = 3;
    private $sleep_time = 1;
    private $error ;

    private $slow_log = '/tmp/query_slow.log';

    public $num_rows = 0;

    public function __construct($db_config=array()){
        if(empty($db_config))
        {
            throw new Exception("please check the db config!");
        }
        if(!isset($db_config["host"]) || !isset($db_config["user"]) || !isset($db_config["password"]) || !isset($db_config["db"]))
        {
            throw new Exception("the config file like this array(host,user,password)");
        }

        $this->host = $db_config["host"];
        $this->user = $db_config["user"];
        $this->password = $db_config["password"];
        $this->db = $db_config["db"];
        if(isset($db_config["port"])) $this->port = $db_config["port"];
        if(isset($db_config["charsert"]))$this->charsert = $db_config["charsert"];
        if(isset($db_config["timeout"])) $this->timeout = $db_config["timeout"];
        $this->processConnect();
    }

    public  function processConnect(){
        $db_key = md5($this->host.$this->user.$this->password.$this->db);
        if(!is_array($this->resource_arr) || !array_key_exists($db_key, $this->resource_arr) || !is_resource($this->resource_arr[$db_key])){
            $this->getConnect();
            if(!$this->current_connect){
                throw new Exception("connect db faild ".mysql_error());
            }
            $this->resource_arr[$db_key] = $this->current_connect;
        }else{
            $this->current_connect = $this->resource_arr[$db_key];
        }
    }

    public  function getConnect()
    {
        $mysqli = mysqli_init();
        if(!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT,$this->timeout)){
            throw new Exception("set timeout faild!");
        }
        if(!$mysqli->real_connect($this->host,$this->user,$this->password,$this->db,$this->port)){
            throw new Exception(" connect db faild ".$mysqli->connect_error);
        }
        $mysqli->set_charset($this->charset);
        $this->current_connect = $mysqli;
    }

    public function reConnect(){
        if(!$this->current_connect->ping()){
            $this->current_connect->close();
            $try_time = $this->retry_times;
            do{
                try{
                    $this->getConnect();
                    if(!empty($this->current_connect) && $this->current_connect->ping()){
                        break;
                    }
                }catch(Exception $e){
                    --$try_time;
                    sleep(1);
                }
            }while($try_time  > 0);
        }
    }

    public function __destruct(){
        $this->current_connect->close();
        unset($this->current_connect);
        unset($this->resource_arr);
    }

    public function checkConnect(){
        if(!$this->current_connect->ping()){
            $this->reConnect();
        }
    }


    public function query($sql){
        $this->checkConnect();
        $ret = $this->current_connect->query($sql);
        if(!$ret){
            $this->error = $this->current_connect->error;
        }else{
            $this->num_rows = $ret->num_rows;
        }
        return $ret;
    }

    public function insert($sql){
        $this->checkConnect();
        $this->query($sql);
        return  $this->current_connect->insert_id;
    }

    public function fetchRow($sql){
        $this->checkConnect();
        $ret = array();
        $result = $this->current_connect->query($sql);
        if($result){
            $ret =$result->fetch_array();
            $result->free();
        }
        return $ret;
    }

    public function fetchRows($sql,$resultType = MYSQLI_ASSOC){
        $this->checkConnect();
        $ret = array();
        $result = $this->current_connect->query($sql);
        if($result){
            while($row = $result->fetch_array($resultType)){
                $ret[] = $row;
            }
            $result->free();
        }
        return $ret;
    }

    public function getQueryError(){
        return $this->error;
    }

    public function startTrans(){
        $this->checkConnect();
        $this->current_connect->query('SET AUTOCOMMIT=0');
        $this->current_connect->query('START TRANSACTION');
        return true;
    }


    public function transCommit(){
        $this->checkConnect();
        $this->current_connect->query('COMMIT');
        $this->current_connect->query('SET AUTOCOMMIT=1');
        return TRUE;
    }

    public function transRollback(){
        $this->checkConnect();
        $this->current_connect->query('ROLLBACK');
        $this->current_connect->query('SET AUTOCOMMIT=1');
        return TRUE;
    }
}

数据库编程需要注意的

优化可以参考

mysql优化1
mysql优化2

索引和性能分析–网上找了10点，作为优化的补充吧

尽量避免在列上做运算，这样会导致索引失败
使用join时，应该用小结果集驱动大结果集。
注意like模糊查询的使用，避免%%
仅列出需要查询的字段，这对速度不会有明显的影响，主要考虑节省内存。
使用批量插入语句节省交互。
limit的基数比较大的时候使用between。
不要使用rand()函数获取多条随机记录
避免使用null。
不要使用count(id)，而应该是count(*)。
不要做无谓的排序操作，而尽可能在索引中完成排序。        


服务器和配置优化

mysql瓶颈

如果mysql实在不满足您的要求，mongodb，redis，memcache可以走起了

数据库设计

范式的问题，还是可以参考上面的博客啦。

mysql的注入问题需要大家注意，注意转义输入输出。

第三方的mysql操作的库

抽象出数据库的一些常用的属性和方法，子类去实现具体的部分