MySQL鍒嗛〉鏂規
銆MySQL鐨勫欏典技涔鐩存槸涓棶棰橈紝鏈
粈涔堜鍖
ysql鍒嗛〉鏂規鍚涓嬮潰鎬葷粨浜嗕浜汳ysql鍒嗛〉鏂規錛
ぇ瀹朵璧鋒
鐪嬬湅銆���
鏂規1錛氱洿ヤ嬌ㄦ暟鎻緵鐨
QL璇
銆璇鏍峰紡錛歁ySQL涓紝鍙敤濡備笅鏂規錛歋ELECT * FROM 琛ㄥ悕縐LIMIT M錛孨銆���
銆閫傚鍦烘櫙錛氶ㄤ簬版嵁緝灝戠殑鎯呭喌(鍏冪粍鐧鍗冪駭)銆���
銆鍘熷/緙虹偣錛氬琛ㄦ壂鎻忥紝閫熷害浼氬鎱涓鏈夌殑版嵁搴撶粨鏋滈榪斿洖涓嶇ǔ瀹濡傛煇嬈繑鍥錛錛錛
澶栫殑涓榪斿洖2錛錛)銆侺imit愬埗鐨
槸浠庣粨鏋滈鐨
浣嶇疆澶勫鍑篘鍑猴紝鍏朵綑涘純銆���
鏂規2錛氬緩富閿鍞鞝紩錛
埄ㄧ儲寮鍋囪姣忛〉10
銆璇鏍峰紡錛歁ySQL涓紝鍙敤濡備笅鏂規錛���
銆浠ｇ濡備笅錛���
銆SELECT * FROM 琛ㄥ悕縐WHERE id_pk > (pageNum*10) LIMIT M銆���
銆閫傚鍦烘櫙錛氶ㄤ簬版嵁鐨
鍐鍏冪粍頒涓銆���
銆鍘熷錛氱儲寮壂鎻忥紝閫熷害浼氬蹇鏈
湅鍙嬫鍑哄涓烘暟煡璇嚭ュ苟涓嶆槸鎸夌
pk_id掑簭鐨
紝鎵互浼
湁婕忔版嵁鐨
鍐碉紝鍙
鏂規3銆���
鏂規3錛氬熀浜庣儲寮曞啀掑簭
銆璇鏍峰紡錛歁ySQL涓紝鍙敤濡備笅鏂規錛歋ELECT * FROM 琛ㄥ悕縐WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M銆���
銆閫傚鍦烘櫙錛氶ㄤ簬版嵁鐨
鍐鍏冪粍頒涓. 鏈ソORDER BY鍚庣殑鍒楀璞℃槸涓婚敭鎴
涓浠ワ紝浣垮緱ORDERBY鎿嶄綔鑳藉埄ㄧ儲寮
娑堥
浣嗙粨鏋滈鏄ǔ瀹氱殑(紼衝畾鐨勫惈涔
紝鍙傝鏂規1)銆���
銆鍘熷錛氱儲寮壂鎻忥紝閫熷害浼氬蹇 浣哅ySQL鐨
搴忔搷浣滐紝鍙湁ASC娌℃湁DESC(DESC鏄亣鐨
紝鏈
浼氬鐪熸鐨凞ESC錛屾湡寰銆���
鏂規4錛氬熀浜庣儲寮曚嬌╬repare(絎涓棶鍙瘋〃紺簆ageNum錛浜屼釜琛ㄧず姣忛〉鍏冪粍
銆璇鏍峰紡錛歁ySQL涓紝鍙敤濡備笅鏂規錛���
銆浠ｇ濡備笅錛���
銆PREPARE stmt_name FROM SELECT * FROM 琛ㄥ悕縐WHERE id_pk > (?* ?) ORDER BY id_pk
銆ASC LIMIT M銆���
銆閫傚鍦烘櫙錛氬ぇ版嵁
銆鍘熷錛氱儲寮壂鎻忥紝閫熷害浼氬蹇 prepare璇
鍙
涓埇鐨
煡璇鍙ュ揩涓偣銆���
鏂規5錛氬埄／ySQL鏀寔ORDER鎿嶄綔鍙互鍒┼敤鞝紩蹇瀹氫綅閮ㄥ鍏冪粍錛伩鍏嶅
琛ㄦ壂鎻
銆姣斿錛氳絎000鍒019琛厓緇pk鏄富閿鍞閿銆���
銆浠ｇ濡備笅錛���
銆SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0錛0銆���
鏂規6錛氬埄quot;瀛煡璇榪炴帴+鞝紩"蹇瀹氫綅鍏冪粍鐨
綅緗紝鐒跺悗鍐嶈鍙
厓緇 閬撶悊鍚屾柟娉
銆濡id鏄富閿鍞閿紝钃濊瀛椾綋鏃跺彉錛���
銆鍒┼敤瀛煡璇ず渚
細
銆浠ｇ濡備笅錛���
銆SELECT* FROMyour_table WHEREid <=
銆(SELECTid FROMyour_table ORDER
銆BYid descLIMIT ($page-1)*$pagesize ORDERBYid desc
銆LIMIT $pagesize
銆鍒┼敤榪炴帴紺轟緥錛���
銆浠ｇ濡備笅錛���
銆SELECT* FROMyour_table ASt1
銆JOIN(SELECTid FROMyour_table ORDERBY
銆id descLIMIT ($page-1)*$pagesize ASt2
銆WHERE
銆t1.id <= t2.id ORDERBYt1.id descLIMIT $pagesize;
鏂規7錛氬瓨鍌ㄨ繃紼嬬被(鏈ソ鋙嶅悎涓婅堪鏂規5/6)
銆璇鏍峰紡錛氫笉鍐嶇鍑恆
銆閫傚鍦烘櫙錛氬ぇ版嵁忥紝ㄨ崘鐨柟娉曘
銆鍘熷錛妸鎿嶄綔灝佽鍦ㄦ湇鍔〞
錛
瀵規洿蹇浜涖
鏂規8錛氬闈柟娉���
銆緗鏈
漢鍐
嬌SQL_CALC_FOUND_ROWS銆傛病鏈
紝鍕挎ā浠銆���
銆鍩烘涓
紝鍙互ㄥ箍鍒版鏈
暟錛
嗘槸涓
鐨勩浣嗘柟娉鏈繀鑳芥
騫垮埌鍏朵粬版嵁搴擄紝ㄥ箍鐨勫墠鎻
槸錛
叾浠栨暟鏀寔ORDER BY鎿嶄綔鍙互鍒┼敤鞝紩鐩存帴瀹屾垚掑簭銆���
緗
弸璇
鍐欒璁�澊