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;
}
}
数据库编程需要注意的
优化可以参考
索引和性能分析–网上找了10点,作为优化的补充吧
尽量避免在列上做运算,这样会导致索引失败
使用join时,应该用小结果集驱动大结果集。
注意like模糊查询的使用,避免%%
仅列出需要查询的字段,这对速度不会有明显的影响,主要考虑节省内存。
使用批量插入语句节省交互。
limit的基数比较大的时候使用between。
不要使用rand()函数获取多条随机记录
避免使用null。
不要使用count(id),而应该是count(*)。
不要做无谓的排序操作,而尽可能在索引中完成排序。
服务器和配置优化
mysql瓶颈
如果mysql实在不满足您的要求,mongodb,redis,memcache可以走起了
数据库设计
范式的问题,还是可以参考上面的博客啦。
mysql的注入问题需要大家注意,注意转义输入输出。
第三方的mysql操作的库
抽象出数据库的一些常用的属性和方法,子类去实现具体的部分