search

Home >Backend Development >PHP Tutorial >mysql operation class, v0.2, adding some solutions to deal with large data volumes

mysql operation class, v0.2, adding some solutions to deal with large data volumes

WBOY
WBOYOriginal
2016-07-25 09:06:191040browse
/*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

*/
  1. /*mysql simple class by joffe q89949401 bib@Crazycode poet;
  2. 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;
  3. There is currently no method related to stored procedures. Of course, stored procedures are generally done when creating a database.
  4. The config file needs to be configured The following constant information:
  5. LIB: Class storage location
  6. DEBUG: Whether to enable debug (if it is enabled, error message tracking will be output)
  7. TB_EX: Database table prefix;
  8. */
  9. defined('LIB') or die('Missing config!! ');
  10. final class mysql {
  11. /**
  12. * Total number of queries
  13. *
  14. * @var int
  15. */
  16. public static $querynum = 0;
  17. /**
  18. * connection handle
  19. *
  20. * @var object
  21. */
  22. public static $link;
  23. /*
  24. table prefix
  25. @var string The following method requires TB_EX to be configured in the configuration file as the prefix of the table
  26. */
  28. static function add_ex($tb){
  29. return TB_EX.$tb_ex.$tb;
  30. }
  31. /*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
  33. */
  34. public static $is_type_tight=false;
  36. /**
  37. * Constructor
  38. *
  39. * @param string $dbhost host name
  40. * @param string $dbuser user
  41. * @param string $dbpw password
  42. * @param string $dbname database name
  43. * @param int $pconnect whether to persist the connection
  44. */
  45. static function connent($dbhost, $dbuser, $dbpw, $dbname = "",$dbcharset, $pconnect = 0) {
  46. if($ pconnect) {
  47. if(!self::$link = @mysql_pconnect($dbhost, $dbuser, $dbpw)) {
  48. self::halt("Can not connect to MySQL server");
  49. }
  50. } else {
  51. if(!self::$link = @mysql_connect($dbhost, $dbuser, $dbpw)) {
  52. self::halt("Can not connect to MySQL server");
  53. }
  54. }
  55. if(self::version () > "4.1") {
  56. if($dbcharset) {
  57. mysql_query("SET character_set_connection={$dbcharset}, character_set_results=$dbcharset, character_set_client=binary", self::$link);
  58. }
  59. if( self::version() > "5.0.1") {
  60. mysql_query("SET sql_mode=''", self::$link);
  61. }
  62. }
  63. if($dbname) {
  64. mysql_select_db($dbname, self::$link);
  65. }
  66. }
  67. /**
  68. * Select database
  69. *
  70. * @param string $dbname
  71. * @return
  72. */
  73. static function select_db($dbname) {
  74. return mysql_select_db($dbname, self::$link);
  75. }
  76. /**
  77. * Get a record from the result set
  78. *
  79. * @param object $query
  80. * @param int $result_type
  81. * @return array
  82. */
  83. static function fetch_array($query, $result_type = MYSQL_ASSOC) { //By default, only associative arrays are fetched, not numeric arrays.
  84. return mysql_fetch_array($query, $result_type);
  85. }
  87. /**
  88. * 查询SQL
  89. *
  90. * @param string $sql
  91. * @param string $type
  92. * @return object
  93. */
  94. static function query($sql, $type = "") {
  95. $func = $type == "UNBUFFERED" && @function_exists("mysql_unbuffered_query") ?
  96. "mysql_unbuffered_query" : "mysql_query";
  97. if( !($query = $func($sql, self::$link)) && $type != "SILENT") {
  98. self::halt("MySQL Query Error", $sql);
  99. }
  100. self:: $querynum++;
  101. return $query;
  102. }
  103. /**
  104. * Get the number of affected items
  105. *
  106. * @return int
  107. */
  108. static function affected_rows() {
  109. return mysql_affected_rows(self::$link);
  110. }
  111. /**
  112. * Return error message
  113. *
  114. * @return array
  115. */
  116. static function error() {
  117. return ((self::$link) ? mysql_error(self::$link) : mysql_error());
  118. }
  119. /**
  120. * Return error code
  121. *
  122. * @return int
  123. */
  124. static function errno() {
  125. return intval((self::$link) ? mysql_errno(self::$link) : mysql_errno());
  126. }
  127. /**
  128. * Return query results
  129. *
  130. * @param object $query
  131. * @param string $row
  132. * @return mixed
  133. */
  134. static function result($query, $row,$flname=0 ) {
  135. $query = @mysql_result($query, $row,$flname);
  136. return $query;
  137. }
  138. /**
  139. * Number of results
  140. *
  141. * @param object $query
  142. * @return int
  143. */
  144. static function num_rows($query) {
  145. $query = mysql_num_rows( $query);
  146. return $query;
  147. }
  148. /**
  149. * Get the total number of fields
  150. *
  151. * @param object $query
  152. * @return int
  153. */
  154. static function num_fields($query) {
  155. return mysql_num_fields($query);
  156. }
  157. /**
  158. * Release the result set
  159. *
  160. * @param object $query
  161. * @return bool
  162. */
  163. static function free_result($query) {
  164. return @mysql_free_result($query);
  165. }
  166. /**
  167. * Return auto-increment ID
  168. *
  169. * @return int
  170. */
  171. static function insert_id() {
  172. return ($id = mysql_insert_id(self::$link)) >= 0 ? $id : self::$result(self::$query("SELECT last_insert_id()"), 0);
  173. }
  174. /**
  175. * Get a row from the result set as an enumeration array
  176. *
  177. * @param object $query
  178. * @return array
  179. */
  180. static function fetch_row($query) {
  181. $query = mysql_fetch_row($query);
  182. return $query;
  183. }
  184. /**
  185. * Get column information from the result set and return it as an object
  186. *
  187. * @param object $query
  188. * @return object
  189. */
  190. static function fetch_fields($query) {
  191. return mysql_fetch_field($query);
  192. }
  194. static function select_affectedt_rows($rs){
  195. return mysql_affected_rows($rs,self::$link);
  197. }
  198. /**
  199. * Return mysql version
  200. *
  201. * @return string
  202. */
  203. static function version() {
  204. return mysql_get_server_info(self::$link);
  205. }
  206. /**
  207. * Close connection
  208. *
  209. * @return bool
  210. */
  211. static function close() {
  212. return mysql_close(self::$link);
  213. }
  214. /**
  215. * Output error message
  216. *
  217. * @param string $message
  218. * @param string $sql
  219. */
  220. static function halt($message = "", $sql = "") {
  221. @header("Content-type: text/html; charset=utf-8");
  223. if (DEBUG==1){
  224. $debug = debug_backtrace();
  225. echo $message . "rn
    SQL--> " . $sql."rn
    ERROR_MESSAGE-->".self::error().
  226. "rn
    --------------debug--------------rn
    ";
  227. self::echoarray($debug);
  228. echo "rn
    -------------debug end----------------";
  230. }else{
  231. echo 'SQL Error';
  232. }
  233. @self::rollback();
  234. exit;
  235. }
  236. /////////////////////////////以下是扩展的sql方法.//////
  237. /* 把数组按照 key value value 的对应关系插入数据表table中
  238. table 要插入的数据表
  239. 要注意 这些扩展方法是没自己给表有加前缀的.
  240. */
  241. static function insert($table,$array){
  242. $temp="";$temp2='';
  243. foreach($array as $key=>$value){
  244. if(self::$is_type_tight){
  245. if(is_string($value)){
  246. $temp .="$key,";$temp2 .="'$value',";
  247. }elseif(is_int($value||is_null($value)||is_float($value))){
  248. $value+=0;
  249. $temp .="$key,";$temp2 .="'$value',";
  250. }
  251. }else{
  252. $temp .="$key,";$temp2 .="'$value',";
  253. }
  254. }
  255. $temp = substr($temp,0,strlen($temp)-1);
  256. $temp2 = substr($temp2,0,strlen($temp2)-1);
  258. $sql = "INSERT INTO $table ($temp) VALUE($temp2)";
  259. return self::query($sql);
  260. }
  263. static function del($table,$where){
  264. $sql = "DELETE FROM {$table} where {$where}";
  265. return self::query($sql);
  266. }
  268. static function update($table,$array,$where){
  269. foreach ($array as $key=>$value){
  270. $temp .= "$key='$value',";
  271. }
  272. $temp = substr($temp,0,strlen($temp)-1);
  273. $sql = "update {$table} set ($temp) where {$where} ";
  274. return self::query($sql);
  275. }
  277. /*进行数据库查询select 参数不定
  278. 参数说明:所有参数必须是string
  279. 第一个参数必须是表名;
  280. 从第二个参数起,
  281. 如果是写上 "where:XXX" 则认为是where条件;
  282. 如果写上 "xxx" 则认为是键值
  283. 如果写上 "by:XXX" 则认为是排序
  284. 如果写上 "limit:xxx,xxx" 则认为是分页
  286. #参数不正确则返回false; 成功查询返回查询后的数组;
  287. */
  288. static function select(){
  289. $numargs = func_num_args();//获取参数个数;
  290. $where = "";$key="";$limit="";$by="";
  291. if($numargs==0){return false;}
  292. //echo $numargs;
  293. if($numargs>=2){
  294. $arg_list = func_get_args();
  295. $table = $arg_list[0];
  296. unset($arg_list[0]);
  297. // print_r($arg_list);
  298. foreach($arg_list as $k=>$value){
  299. if(preg_match("#^(where:)w#",$value)){
  300. $temp = explode(":",$value);
  301. $where = "WHERE {$temp[1]} " ;
  302. }elseif(preg_match("#^by:w#",$value)){
  303. $temp = explode(":",$value);
  304. $by = "order by {$temp[1]}" ;
  305. }elseif(preg_match("#^limit:w#",$value)){
  306. $temp = explode(":",$value);
  307. $limit = "limit {$temp[1]}";
  308. }else{
  309. $key .= "$value,";
  310. }
  311. }
  313. if($key==""){
  314. $key = "*";
  315. }else{
  316. $key =substr($key,0,strlen($key)-1);
  317. }
  319. $sql_base="SELECT $key FROM $table";
  320. }
  321. if(!empty($where)){
  322. $sql_base .= " $where";
  323. }
  324. if(!empty($by)){
  325. $sql_base .= " $by";
  326. }
  327. if(!empty($limit)){
  328. $sql_base .= " $limit";
  329. }
  330. //echo $sql_base;
  331. //echo $by ;
  332. $rs = self::query($sql_base);
  333. $re=array();
  334. if(self::num_rows($rs)>=1){
  335. while($info = self::fetch_array($rs)){
  336. $re[]=$info;
  337. }
  338. }
  339. self::free_result($rs);
  340. return $re;
  341. }
  344. /*回滚事务*/
  345. static function rollback(){
  346. self::query('rollback');
  347. }
  348. /*开始事务*/
  349. static function begin(){
  350. self::query('SET AUTOCOMMIT =0'); //停用自动提交;
  351. self::query('BEGIN') ;//开始一个事务;
  352. }
  354. /*提交事务*/
  355. static function commit(){
  356. self::query('commit');
  357. }
  360. static function echoarray($array){
  361. foreach($array as $k=>$v ){
  362. if(is_array($v)){
  363. if(is_array($v)){
  364. echo "
    --------------------------------
    ";
  365. self::echoarray($v);
  366. }
  367. }else{
  368. if($k==='line')
  369. echo "$k -> " .$v." ";
  370. else
  371. echo "$k -> " .$v." ";
  372. }
  373. }
  374. }
  376. function get_server_info(){
  377. return mysql_get_server_info();
  378. }
  381. //下面是应付大数据的表的优化查询
  383. /*
  384. big_select 适合用于大规模的查询,利用覆盖索引实现大幅度的偏移活动窗口,令查询在覆盖索引上偏移而不是在所有数据上,减少msql在数据上检查,再把其他数据join进来这样更效率.但对于小规模数据,这种查询反而增加复杂度,增加优化器压力.说个例子,如果你是limit 10000,20;mysql会先查出10020条数据 再丢弃10000 这种操作代价非常大,利用这个函数可以有效提升效率,但如果是 limit 20,那就会稍微比直接select 慢了一些
  385. @table string要查询的表 如"table1"
  386. @keys string 要查询的键值,多个键值用","分割 如"key1,key2,key3"结束不含","尽量少用"*"且一些关键词请加上`;
  387. @Index string主索引键或者唯一索引键名,只需要一个 如"id";
  388. @pagesize int 分页大小,必须,你不会想这么多数据全部出来吧
  389. @pageNo 页码,从0开始
  390. @orderby string 排序 如"id desc";可空,但不建议空
  391. @where string 条件 如 "date>122424533"可空
  392. #返回数组
  393. */
  394. static function big_select($table,$keys,$index,$pagesize,$pageNo,$orderby=NULL,$where=NULL){
  395. $start=$pageNo*$pagesize;
  396. if($where){
  397. $sqlIndex="SELEECT {$index} from {$table} where {$where}";
  398. }else{
  399. $sqlIndex="SELEECT {$index} from {$table}";
  400. }
  401. if($orderby){
  402. $sqlIndex .=" ORDER BY {$orderby} Limit $start,$pagesize";
  403. }else{
  404. $sqlIndex .=" ORDER BY Limit $start,$pagesize";
  405. }
  406. $sql = "SELECT $keys FROM {$table} INNER JOIN({$sqlIndex}) AS lim USING({$index})";
  408. $rs = self::query($sql);
  410. $re=array();
  411. if(self::num_rows($rs)>=1){
  412. while($info = self::fetch_array($rs)){
  413. $re[]=$info;
  414. }
  415. }
  416. self::free_result($rs);
  417. return $re;
  419. }
  420. /* 如果一个很大的数据(预计大于万行)删除它的工作周期会比较长,会长时间锁住不应该锁住的表或行,令一些不该打断的数据被打断 以下方法是把一个大的sql任务分小(分为一次5000行)但次操作可能会造成删除空隙期间插入了新的数据,而新的数据可能因满足条件而被删除.本方法容易因为超时而失败.
  421. @table string 要删除数据的表名
  422. @where string 条件 可省略
  423. #int 删除掉的行数
  424. */
  425. static function big_del($table,$where){
  426. set_time_limit(0);
  427. $sql="delete from {$table} where {$where} Limit 5000";
  428. $rows = 0;
  429. $eff=0;
  430. do{
  431. self::query($sql);
  432. $rows=self::affected_rows();
  433. $eff += $rows;
  434. }while($rows>0);
  435. return $eff;
  436. }
  437. }
  440. ?>
复制代码


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Calculate age based on date of birth (taking into account leap years)Next article：Calculate age based on date of birth (taking into account leap years)

Related articles

See more