Mysql operations, v0.2, and some solutions to deal with large data volumes

Mysql operations, v0.2, and some solutions to deal with large data volumes

/* Mysql simple class by joffe q89949401 bib @ MAD code poet; this class is completely static when used directly include and use mysql: Method () the class is globally visible in php5, so you don't have to worry about the variable range. if you have any comments, please use a private email | qq mail. Currently, there is no method related to the stored procedure. of course, the stored procedure is generally made when the database is created. in the config file, you need to configure the following constant information: LIB: Class storage location DEBUG: whether to enable debug (if it is enabled, error information tracing will be output) TB_EX: database table prefix; Added the select del update insert big_select big_del function. */ /* Mysql simple class by joffe q89949401 bib @ MAD code poet; When using this class in full static mode, directly include the class and use mysql: Method () name. because the class is globally visible in php5, you don't have to worry about the variable range. if you have any comments, please use a private email | qq mail; Currently, there are no methods related to stored procedures. of course, stored procedures are generally used to create databases. The following constant information must be configured in the config file: LIB: class storage location DEBUG: whether to enable debug (if it is enabled, error 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 needs to configure TB_EX as the table prefix in the configuration file */ Static function add_ex ($ tb ){ Return TB_EX. $ tb_ex. $ tb; } /* Whether the mysql database uses a strict type (automatic conversion is not enabled for the mysql type). The default value is false, indicating that type conversion is enabled for mysql, currently, as long as the single quotes used for the insert function are not automatically converted to mysql, related functions may be added in the future. */ Public static $ is_type_tight = false; /** * Constructor * * @ Param string $ dbhost host name * @ Param string $ dbuser * @ Param string $ dbpw password * @ Param string $ dbname database name * @ Param int $ whether pconnect is continuously connected */ 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 a database * * @ Param string $ dbname * @ Return */ Static function select_db ($ dbname ){ Return mysql_select_db ($ dbname, self: $ link ); } /** * Retrieve 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 the associated array is used and no numeric array is used. Return mysql_fetch_array ($ query, $ result_type ); } /** * Querying 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; } /** * Number of affected items * * @ Return int */ Static function affected_rows (){ Return mysql_affected_rows (self: $ link ); } /** * Error message returned * * @ 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; } /** * Total number of fields * * @ Param object $ query * @ Return int */ Static function num_fields ($ query ){ Return mysql_num_fields ($ query ); } /** * Release result set * * @ Param object $ query * @ Return bool */ Static function free_result ($ query ){ Return @ mysql_free_result ($ query ); } /** * The Auto-increment ID is returned. * * @ Return int */ Static function insert_id (){ Return ($ id = mysql_insert_id (self: $ link)> = 0? $ Id: self ::$ result (self ::$ query ("SELECT last_insert_id ()"), 0 ); } /** * Retrieve a row from the result set as an enumerated array * * @ Param object $ query * @ Return array */ Static function fetch_row ($ query ){ $ Query = mysql_fetch_row ($ query ); Return $ query; } /** * Retrieve 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 ); } /** * Returns the mysql version. * * @ Return string */ Static function version (){ Return mysql_get_server_info (self: $ link ); } /** * Close the 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. "\ r \ n SQL --> ". $ SQL." \ r \ n ERROR_MESSAGE --> ". self: error (). "\ R \ n -------------- Debug -------------- \ r \ n "; Self: echoarray ($ debug ); Echo "\ r \ n ------------- Debug end ----------------"; } Else { Echo 'SQL error '; } @ Self: rollback (); Exit; } /////////////////////////// The following is an extended SQL method. ////// /* Insert the array into the table of the data table according to the corresponding relationship of the key value. Table Note that these extension methods do not add prefixes to tables by themselves. */ 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 ); } /* The select parameter for database query is invalid. Parameter description: all parameters must be string The first parameter must be the table name; Starting from the second parameter, If "where: XXX" is written, it is considered as the where condition; If "xxx" is written, it is considered as a key value. If "by: XXX" is written, it is regarded as sorting. If "limit: xxx, xxx" is written, it is regarded as paging. # If the parameter is incorrect, false is returned. if the query is successful, the queried array is returned; */ Static function select (){ $ Numargs = func_num_args (); // Obtain the number of parameters; $ 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 ($ )){ $ SQL _base. = "$ "; } If (! Empty ($ limit )){ $ SQL _base. = "$ limit "; } // Echo $ SQL _base; // Echo $; $ 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; } /* Roll back the transaction */ Static function rollback (){ Self: query ('rollback '); } /* Start transaction */ Static function begin (){ Self: query ('set AUTOCOMMIT = 0'); // disable automatic submission; Self: query ('begin'); // starts a transaction; } /* Submit the transaction */ 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 (); } // The following is an optimized query for tables dealing with big data. /* Big_select is suitable for large-scale queries. The Coverage Index is used to implement a large offset activity window, so that the query is offset on the covered index rather than on all data, reducing the data check of msql, it is more efficient to join other data. however, for small-scale data, such queries increase complexity and increase the optimizer pressure. for example, if you use limit, 20; mysql will first find 10020 pieces of data and then discard 10000, which is very costly. This function can effectively improve efficiency, but if it is limit 20, it will be a little slower than the direct select statement. @ Table string the table to be queried, such as "table1" @ Keys string the key value to be queried. Use "," to separate multiple key values such as "key1, key2, key3" end does not include ", "Try to use less" * "and add some keywords '; @ Index string primary Index key or unique Index key name, only one such as "id "; @ Pagesize int page size, required. you don't want to get all the data @ PageNo page number, starting from 0 @ Orderby string sorting, such as "id desc"; can be empty, but it is not recommended to be empty @ Where string condition, such as "date> 122424533", can be empty # Returned array */ 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; } /* If a large volume of data (more than ten thousand rows are expected) is deleted for a long time, the tables or rows that should not be locked will be locked for a long time, to interrupt data that should not be interrupted, the following method is to small a large SQL task (divided into one 5000 rows), but the next operation may cause new data to be inserted during the deletion gap, new data may be deleted because the conditions are met. this method may fail due to timeout. @ Table string name of the table to be deleted @ Where string condition can be omitted # Number of lines deleted by 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; } } ?>