php的分表分库类
2024-08-24 20:40:12
<?php
include 'config.php';
class Model{
//用户名
protected $user;
//密码
protected $pwd;
//主机
protected $host;
//库名，是一个数组
protected $dbName=array();
//字符集
protected $charset='utf8';
//连接资源是一个数组
protected $_link=array();
//通用表名
protected $tabName;
//真实表名
protected $trueTabName;
//表前缀
protected $prefix;
//字段缓存
protected $fields;
//创建表的sql语句
protected $createSql='CREATE TABLE IF NOT EXISTS __TABLENAME__(
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`username` char(15) NOT NULL,
`password` char(32) NOT NULL,
`createtime` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;'; //1,通过ID取余，得到真实表名 mod
//2,用户名截取前几位 substr
//3,md5 md5
//4,不带分库分表 none protected $partition=array(
'type'=>'md5', 'rule'=>1, ); public function __construct($tabName=''){
$this->user=DB_USER;
$this->host=DB_HOST;
$this->dbName[0]=DB_NAME;
$this->charset=DB_CHARSET;
$this->prefix=DB_PREFIX;
$this->pwd=DB_PWD; if(empty($tabName)){
//userModel
//newModel
$this->tabName=$this->prefix.ucfirst(strtolower(substr(get_class($this),0,-5))); }else{
$this->tabName=$this->prefix.$tabName;
} $this->_link[0]=$this->connect($this->host,$this->user,$this->pwd,$this->dbName,$this->charset); } public function connect($host,$user,$pwd,$dbName,$charset,$linkId=0){
$conn=mysql_connect($host,$user,$pwd); if(mysql_errno()){
$this->error(-1,$conn);
return false;
} if(!$this->selectDb($dbName[$linkId],$conn)){
$this->error(-2,$conn);
return false;
} if(!$this->setCharset($charset,$conn)){
$this->error(-3,$conn);
return false;
} return $conn; } public function selectDb($dbName,$conn){
if(mysql_select_db($dbName,$conn)){ return true;
}else{
return false;
}
} public function setCharset($charset,$conn){
if(mysql_set_charset($charset,$conn)){
return true;
}else{
return false;
} } public function addServer($host,$user,$pwd,$dbName,$charset,$linkId){
$this->dbName[$linkId]=$dbName;
$this->_link[$linkId]=$this->connect($host,$user,$pwd,$dbName,$charset,$linkId); } public function getTrueTable($content,$linkId=0){
switch($this->partition['type']){
case 'mod':
if(!is_int($content)){
$this->error(-4);
return false;
}
$string=$content%$this->partition['rule'];
break;
case 'substr':
$string=substr($content,0,$this->partition['rule']);
break;
case 'md5':
$string=substr(md5($content),0,$this->partition['rule']);
break;
case 'none':
$string=null;
break;
} if(empty($string)){
$this->trueTableName=$this->tabName; }else{
$this->trueTableName=$this->tabName.'_'.$string;
} //第一，判断表是否存在，存在返回表字段缓存
//第二，不存在，则创建表，返回字段缓存 $this->existsTable($this->trueTableName,$linkId); }
//表是否存在
//是否缓存了字段 protected function existsTable($tableName,$linkId=0){
$database=$this->dbName[$linkId];
$sql='select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`=\''.$database.'\' and `TABLE_NAME`=\''.$tableName.'\''; if($this->execute($sql,$linkId)){
//表存在
if(file_exists('cache/'.md5($this->tabName).'.php')){
$this->fields=include 'cache/'.md5($this->tabName).'.php';
}else{
//暂时留着不写，待会来写
$this->fields=$this->getFieldCache($linkId);
} }else{
//表不存在
$this->createTable($this->trueTableName,$linkId);
$this->fields=$this->getFieldCache($linkId); } } protected function getFieldCache($linkId=0){
if(file_exists('cache/'.md5($this->tabName).'.php')){
$fields=include 'cache/'.md5($this->tabName).'.php';
return $fields;
}
$sql="desc $this->trueTableName";
$f=$this->query($sql,$linkId); $fields=$this->writeFields($f); return $fields; } protected function writeFields($f){
foreach($f as $key=>$value){
$fields[]=$value['Field']; if($value['Key']=='PRI'){
$fields['_pk']=$value['Field'];
}
if($value['Extra']=='auto_increment'){
$fields['_auto']=$value['Field'];
}
}
$string="<?php \n return ".var_export($fields,true)."\n?>"; file_put_contents('cache/'.md5($this->tabName).'.php',$string);
return $fields; } protected function createTable($tabName,$linkId=0){
$sql=str_replace('__TABLENAME__',$tabName,$this->createSql); $this->execute($sql,$linkId);
} //不需要返回结果集我用execute方法
public function execute($sql,$linkId=0){
$conn=$this->_link[$linkId]; $result=mysql_query($sql,$this->_link[$linkId]);
if($result&&mysql_affected_rows()){ return mysql_affected_rows();
}else{
return false;
} } //需要返回结果集我用query方法
public function query($sql,$linkId=0){
$result=mysql_query($sql,$this->_link[$linkId]); if($result&&mysql_affected_rows()){
while($row=mysql_fetch_assoc($result)){ $rows[]=$row;
}
}else{
return false;
}
return $rows;
} public function error($num,$conn){
switch($num){
case -1:
$string='连接数据库服务器失败'.mysql_error($conn);
break;
case -2:
$string='选择数据失败';
break;
case -3:
$string='设置字符集失败';
break;
case -4:
$string='数据库路由时选择的是取余，传入的不是整型';
break;
}
} //查最大值
public function max($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select max($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row; } //查最小值
public function min($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select min($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row; }
//求和
public function sum($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select sum($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row; }
//最平均数
public function avg($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select avg($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row; }
//求总数
public function count($field='',$linkId=0){
if(empty($field)){
$field=$this->fields['_pk'];
}
$sql="select count($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;
}
//
//删除
public function delete($data,$where='',$linkId=0,$order='',$limit=''){
//delete from 表 where 字段 order by 字段 limit if(is_array($data)){
$value=join(',',$data);
}else{
$value=(int)$data;
}
$fields=$this->fields['_pk']; if(empty($where)){ $sql="delete from $this->trueTableName where $fields in ($value)";
}else{
$where='where '.$where;
if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
} $sql="delete from $this->trueTableName $where $order $limit";
}
return $this->execute($sql,$linkId);
}
//
//修改
public function save($data,$where,$linkId=0,$order='',$limit=''){ //update 表 set 字段=值,字段=值 where 条件 order limit
$key=array_keys($data);
$newKey=array_intersect($key,$this->fields); foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$update.=$key.'="'.$value.'",'; }
$update=rtrim($update,','); if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
} if(!empty($where)){
$where='where '.$where;
} $sql="update $this->trueTableName set $update $where $order $limit"; echo $sql;
$result=$this->execute($sql,$linkId);
return $result; } //增加
public function add($data,$linkId=0){
//insert into 表(字段) values(值)
$key=array_keys($data);
$newKey=array_intersect($key,$this->fields);
foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$values.="'".$value."',";
}
$values=trim($values,',');
$fields=join(',',$newKey);
$sql="insert into $this->trueTableName($fields) values($values)";
echo $sql;
$result=$this->execute($sql,$linkId);
return $result;
} //单条查询
public function find($linkId=0,$where='',$order=''){
//select * from 表 where order limit 1
$field=join(',',$this->fields);
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
$sql="select $field from $this->trueTableName $where $order limit 1";
$result=$this->query($sql,$linkId);
return $result[0]; } //多条查询
public function select($field='',$linkId=0,$where='',$order='',$limit=''){
//select * from 表 where order limit
if(empty($field)){
$fields=join(',',$this->fields);
}else{
if(is_array($field)){
$newKey=array_intersect($field,$this->fields);
$fields=implode(',',$newKey);
}else{
$fields=$field;
}
}
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
}
$sql="select $fields from $this->trueTableName $where $order $limit";
$result=$this->query($sql,$linkId);
return $result; }
//按照字段来查询数据 function __call($name,$param){
$key=substr($name,0,5);
if(strtolower($key)=='getby'){
$field=strtolower(substr($name,5)); if(!in_array($field,$this->fields)){
return false;
}
$f=join(',',$this->fields);
$value=$param[0];
$sql="select $f from $this->trueTableName where $field='$value'"; $result=$this->query($sql);
return $result[0]; }
} } ?>
最新文章
- div内容溢出时显示滚动条
- 【Beta】Daily Scrum Meeting第四次
- iOS 设置导航栏之二（设置导航栏的颜色、文字的颜色、左边按钮的文字及颜色）
- Python 以指定概率获取元素
- Ehcache - hello world
- Python3.x和Python2.x的区别-转
- 连接Oracle的几种方式
- RDay1-Problem 1 A
- 手把手制作一个简单的IDEA插件(环境搭建Demo篇)
- GUI 设计
- Python Web开发问题收集（二）
- 32 C++常见错误集锦
- 解决Windows内存问题的两个小工具RamMap和VMMap
- SCU 4437 Carries（二分乱搞）题解
- 1080 MOOC期终成绩
- 【Android】Android 8种对话框(Dialog)
- Spacy 使用
- sql语句-8-sql学习流程
- java中的==、equals()、hashCode()
- 【HDOJ 2888】Check Corners（裸二维RMQ）