mysql operation class, v0.2, adding some solutions to deal with large data volumes-PHP Tutorial-php.cn

/*mysql simple class by joffe q89949401 bib@Crazycode poet; This class is completely static. When using it, include it directly and use the mysql::method() name. Since the class is globally visible in php5, there is no need to worry about the variable scope. If you have any comments, please send a private message ||qq mail; Currently there is no method related to stored procedures. Of course, stored procedures are usually done when creating a database. The config file needs to configure the following constant information: LIB: class storage location DEBUG: Whether to enable debug (if enabled, error information tracking will be output) TB_EX: database table prefix;

Added select del update insert big_select big_del function

*/

/*mysql simple class by joffe q89949401 bib@Crazycode poet;
When this class is used completely statically, include it directly and use the mysql::method() name because the class is global in php5 Visible, so you don’t have to worry about variable scope. If you have any comments, please send a private message ||qq mail;
There is currently no method related to stored procedures. Of course, stored procedures are generally done when creating a database.
The config file needs to be configured The following constant information:
LIB: Class storage location
DEBUG: Whether to enable debug (if it is enabled, error message tracking will be output)
TB_EX: Database table prefix;
*/
defined('LIB') or die('Missing config!! ');
final class mysql {
/**
* Total number of queries
*
* @var int
*/
public static $querynum = 0;
/**
* connection handle
*
* @var object
*/
public static $link;
/*
table prefix
@var string The following method requires TB_EX to be configured in the configuration file as the prefix of the table
*/
static function add_ex($tb){
return TB_EX.$tb_ex.$tb;
}
/*Whether the mysql database uses strict types (mysql The type is not automatically converted) The default is false, which means that mysql has type conversion turned on. This variable is currently used as a single quote for the insert function in mysql without automatic conversion. Related functions may be added in the future
*/
public static $is_type_tight=false;
/**
* Constructor
*
* @param string $dbhost host name
* @param string $dbuser user
* @param string $dbpw password
* @param string $dbname database name
* @param int $pconnect whether to persist the connection
*/
static function connent($dbhost, $dbuser, $dbpw, $dbname = "",$dbcharset, $pconnect = 0) {
if($ pconnect) {
if(!self::$link = @mysql_pconnect($dbhost, $dbuser, $dbpw)) {
self::halt("Can not connect to MySQL server");
}
} else {
if(!self::$link = @mysql_connect($dbhost, $dbuser, $dbpw)) {
self::halt("Can not connect to MySQL server");
}
}
if(self::version () > "4.1") {
if($dbcharset) {
mysql_query("SET character_set_connection={$dbcharset}, character_set_results=$dbcharset, character_set_client=binary", self::$link);
}
if( self::version() > "5.0.1") {
mysql_query("SET sql_mode=''", self::$link);
}
}
if($dbname) {
mysql_select_db($dbname, self::$link);
}
}
/**
* Select database
*
* @param string $dbname
* @return
*/
static function select_db($dbname) {
return mysql_select_db($dbname, self::$link);
}
/**
* Get a record from the result set
*
* @param object $query
* @param int $result_type
* @return array
*/
static function fetch_array($query, $result_type = MYSQL_ASSOC) { //By default, only associative arrays are fetched, not numeric arrays.
return mysql_fetch_array($query, $result_type);
}
/**
* 查询SQL
*
* @param string $sql
* @param string $type
* @return object
*/
static function query($sql, $type = "") {
$func = $type == "UNBUFFERED" && @function_exists("mysql_unbuffered_query") ?
"mysql_unbuffered_query" : "mysql_query";
if( !($query = $func($sql, self::$link)) && $type != "SILENT") {
self::halt("MySQL Query Error", $sql);
}
self:: $querynum++;
return $query;
}
/**
* Get the number of affected items
*
* @return int
*/
static function affected_rows() {
return mysql_affected_rows(self::$link);
}
/**
* Return error message
*
* @return array
*/
static function error() {
return ((self::$link) ? mysql_error(self::$link) : mysql_error());
}
/**
* Return error code
*
* @return int
*/
static function errno() {
return intval((self::$link) ? mysql_errno(self::$link) : mysql_errno());
}
/**
* Return query results
*
* @param object $query
* @param string $row
* @return mixed
*/
static function result($query, $row,$flname=0 ) {
$query = @mysql_result($query, $row,$flname);
return $query;
}
/**
* Number of results
*
* @param object $query
* @return int
*/
static function num_rows($query) {
$query = mysql_num_rows( $query);
return $query;
}
/**
* Get the total number of fields
*
* @param object $query
* @return int
*/
static function num_fields($query) {
return mysql_num_fields($query);
}
/**
* Release the result set
*
* @param object $query
* @return bool
*/
static function free_result($query) {
return @mysql_free_result($query);
}
/**
* Return auto-increment ID
*
* @return int
*/
static function insert_id() {
return ($id = mysql_insert_id(self::$link)) >= 0 ? $id : self::$result(self::$query("SELECT last_insert_id()"), 0);
}
/**
* Get a row from the result set as an enumeration array
*
* @param object $query
* @return array
*/
static function fetch_row($query) {
$query = mysql_fetch_row($query);
return $query;
}
/**
* Get column information from the result set and return it as an object
*
* @param object $query
* @return object
*/
static function fetch_fields($query) {
return mysql_fetch_field($query);
}
static function select_affectedt_rows($rs){
return mysql_affected_rows($rs,self::$link);
}
/**
* Return mysql version
*
* @return string
*/
static function version() {
return mysql_get_server_info(self::$link);
}
/**
* Close connection
*
* @return bool
*/
static function close() {
return mysql_close(self::$link);
}
/**
* Output error message
*
* @param string $message
* @param string $sql
*/
static function halt($message = "", $sql = "") {
@header("Content-type: text/html; charset=utf-8");
if (DEBUG==1){
$debug = debug_backtrace();
echo $message . "rn
SQL--> " . $sql."rn
ERROR_MESSAGE-->".self::error().
"rn
--------------debug--------------rn
";
self::echoarray($debug);
echo "rn
-------------debug end----------------";
}else{
echo 'SQL Error';
}
@self::rollback();
exit;
}
/////////////////////////////以下是扩展的sql方法.//////
/* 把数组按照 key value value 的对应关系插入数据表table中
table 要插入的数据表
要注意这些扩展方法是没自己给表有加前缀的.
*/
static function insert($table,$array){
$temp="";$temp2='';
foreach($array as $key=>$value){
if(self::$is_type_tight){
if(is_string($value)){
$temp .="$key,";$temp2 .="'$value',";
}elseif(is_int($value||is_null($value)||is_float($value))){
$value+=0;
$temp .="$key,";$temp2 .="'$value',";
}
}else{
$temp .="$key,";$temp2 .="'$value',";
}
}
$temp = substr($temp,0,strlen($temp)-1);
$temp2 = substr($temp2,0,strlen($temp2)-1);
$sql = "INSERT INTO $table ($temp) VALUE($temp2)";
return self::query($sql);
}
static function del($table,$where){
$sql = "DELETE FROM {$table} where {$where}";
return self::query($sql);
}
static function update($table,$array,$where){
foreach ($array as $key=>$value){
$temp .= "$key='$value',";
}
$temp = substr($temp,0,strlen($temp)-1);
$sql = "update {$table} set ($temp) where {$where} ";
return self::query($sql);
}
/*进行数据库查询select 参数不定
参数说明:所有参数必须是string
第一个参数必须是表名;
从第二个参数起,
如果是写上 "where:XXX" 则认为是where条件;
如果写上 "xxx" 则认为是键值
如果写上 "by:XXX" 则认为是排序
如果写上 "limit:xxx,xxx" 则认为是分页
#参数不正确则返回false; 成功查询返回查询后的数组;
*/
static function select(){
$numargs = func_num_args();//获取参数个数;
$where = "";$key="";$limit="";$by="";
if($numargs==0){return false;}
//echo $numargs;
if($numargs>=2){
$arg_list = func_get_args();
$table = $arg_list[0];
unset($arg_list[0]);
// print_r($arg_list);
foreach($arg_list as $k=>$value){
if(preg_match("#^(where:)w#",$value)){
$temp = explode(":",$value);
$where = "WHERE {$temp[1]} " ;
}elseif(preg_match("#^by:w#",$value)){
$temp = explode(":",$value);
$by = "order by {$temp[1]}" ;
}elseif(preg_match("#^limit:w#",$value)){
$temp = explode(":",$value);
$limit = "limit {$temp[1]}";
}else{
$key .= "$value,";
}
}
if($key==""){
$key = "*";
}else{
$key =substr($key,0,strlen($key)-1);
}
$sql_base="SELECT $key FROM $table";
}
if(!empty($where)){
$sql_base .= " $where";
}
if(!empty($by)){
$sql_base .= " $by";
}
if(!empty($limit)){
$sql_base .= " $limit";
}
//echo $sql_base;
//echo $by ;
$rs = self::query($sql_base);
$re=array();
if(self::num_rows($rs)>=1){
while($info = self::fetch_array($rs)){
$re[]=$info;
}
}
self::free_result($rs);
return $re;
}
/*回滚事务*/
static function rollback(){
self::query('rollback');
}
/*开始事务*/
static function begin(){
self::query('SET AUTOCOMMIT =0'); //停用自动提交;
self::query('BEGIN') ;//开始一个事务;
}
/*提交事务*/
static function commit(){
self::query('commit');
}
static function echoarray($array){
foreach($array as $k=>$v ){
if(is_array($v)){
if(is_array($v)){
echo "
--------------------------------
";
self::echoarray($v);
}
}else{
if($k==='line')
echo "$k -> " .$v." ";
else
echo "$k -> " .$v." ";
}
}
}
function get_server_info(){
return mysql_get_server_info();
}
//下面是应付大数据的表的优化查询
/*
big_select 适合用于大规模的查询,利用覆盖索引实现大幅度的偏移活动窗口,令查询在覆盖索引上偏移而不是在所有数据上,减少msql在数据上检查,再把其他数据join进来这样更效率.但对于小规模数据,这种查询反而增加复杂度,增加优化器压力.说个例子,如果你是limit 10000,20;mysql会先查出10020条数据再丢弃10000 这种操作代价非常大,利用这个函数可以有效提升效率,但如果是 limit 20,那就会稍微比直接select 慢了一些
@table string要查询的表如"table1"
@keys string 要查询的键值,多个键值用","分割如"key1,key2,key3"结束不含","尽量少用"*"且一些关键词请加上`;
@Index string主索引键或者唯一索引键名,只需要一个如"id";
@pagesize int 分页大小,必须,你不会想这么多数据全部出来吧
@pageNo 页码,从0开始
@orderby string 排序如"id desc";可空,但不建议空
@where string 条件如 "date>122424533"可空
#返回数组
*/
static function big_select($table,$keys,$index,$pagesize,$pageNo,$orderby=NULL,$where=NULL){
$start=$pageNo*$pagesize;
if($where){
$sqlIndex="SELEECT {$index} from {$table} where {$where}";
}else{
$sqlIndex="SELEECT {$index} from {$table}";
}
if($orderby){
$sqlIndex .=" ORDER BY {$orderby} Limit $start,$pagesize";
}else{
$sqlIndex .=" ORDER BY Limit $start,$pagesize";
}
$sql = "SELECT $keys FROM {$table} INNER JOIN({$sqlIndex}) AS lim USING({$index})";
$rs = self::query($sql);
$re=array();
if(self::num_rows($rs)>=1){
while($info = self::fetch_array($rs)){
$re[]=$info;
}
}
self::free_result($rs);
return $re;
}
/* 如果一个很大的数据(预计大于万行)删除它的工作周期会比较长,会长时间锁住不应该锁住的表或行,令一些不该打断的数据被打断以下方法是把一个大的sql任务分小(分为一次5000行)但次操作可能会造成删除空隙期间插入了新的数据,而新的数据可能因满足条件而被删除.本方法容易因为超时而失败.
@table string 要删除数据的表名
@where string 条件可省略
#int 删除掉的行数
*/
static function big_del($table,$where){
set_time_limit(0);
$sql="delete from {$table} where {$where} Limit 5000";
$rows = 0;
$eff=0;
do{
self::query($sql);
$rows=self::affected_rows();
$eff += $rows;
}while($rows>0);
return $eff;
}
}
?>

复制代码