Mysql排序的特性详情

1銆侀棶棰樺満鏅?/h2>

鏂颁笂绾夸竴涓�浜ゆ槗璁板綍瀵煎嚭鍔熻兘锛岄�昏緫寰堢畝鍗曪細鏍规嵁鏌ヨ�㈡潯浠讹紝瀵煎嚭瀵瑰簲鐨勬暟鎹�銆傜敱浜庢暟鎹�閲忔瘮杈冨ぇ锛屽湪鏌ヨ�㈡暟鎹�搴撴椂閲囩敤浜嗗垎椤垫煡璇�锛屾瘡娆℃煡璇?000鏉℃暟鎹�銆?/p>

鑷�娴嬫�ｅ父锛屾祴璇曠幆澧冩�ｅ父锛屼笂绾夸箣鍚庤繍钀ュ弽棣堝�煎嚭鐨?strong>鏁版嵁鏈夐噸澶嶈�板�?/strong>銆?/p>

鍘熸湰鏄�浠ヤ负涓氬姟閫昏緫闂�棰橈紝閲嶆�?code>Review浜嗕竴閬嶄唬鐮侊紝渚濇棫鏈�鎵惧埌闂�棰樺師鍥犮�傛渶鍚庡彧濂芥妸SQL璇�鍙ユ嬁鍑烘潵鍗曠嫭鎵ц�岋紝瀵煎嚭鏁版嵁锛屽�规瘮鍙戠幇绔熺劧鏄�SQL璇�鍙ユ煡璇㈢粨鏋滀贡搴忓�艰嚧鐨勩�?/p>

2銆佸師鍥犲垎鏋?/h2>

鏌ヨ�㈣��鍙ヤ�?code>create_time杩涜�屽�掑簭鎺掑簭锛岄�氳繃limit杩涜�屽垎椤碉紝鍦ㄦ�ｅ父鎯呭喌涓嬩笉浼氬嚭鐜伴棶棰樸�備絾褰撲笟鍔″苟鍙戦噺姣旇緝澶э紝瀵艰嚧create_time瀛樺湪澶ч噺鐩稿悓鍊兼椂锛屽啀鍩轰簬limit杩涜�屽垎椤碉紝灏变細鍑虹幇涔卞簭闂�棰樸�?/p>

鍑虹幇鐨勫満鏅�鏄�锛?/strong>浠?code>create_time鎺掑簭锛屽綋create_time瀛樺湪鐩稿悓鍊硷紝閫氳繃limit鍒嗛〉锛屽�艰嚧鍒嗛〉鏁版嵁涔卞簭銆?/p>

姣斿�傦紝鏌ヨ�?code>1000鏉℃暟鎹�锛屽叾涓�鏈変竴鎵?code>create_time璁板綍鍊奸兘涓衡�?code>2021-10-28 12:12:12鈥滐紝褰撳垱寤烘椂闂寸浉鍚岀殑杩欎簺鏁版嵁锛屼竴閮ㄥ垎鍑虹幇鍦ㄧ��涓�椤碉紝涓�閮ㄥ垎鍑虹幇鍦ㄧ��浜岄〉锛屽湪鏌ヨ�㈢��浜岄〉鐨勬暟鎹�鏃讹紝鍙�鑳戒細鍑虹幇绗�涓�椤靛凡缁忔煡杩囩殑鏁版嵁銆?/p>

涔熷氨鏄�璇达紝鏁版嵁浼氭潵鍥炶烦鍔�锛屼竴浼氬効鍑虹幇鍦ㄧ��涓�椤碉紝涓�浼氬効鍑虹幇鍦ㄧ��浜岄〉锛岃繖灏卞�艰嚧瀵煎嚭鐨勬暟鎹�涓�閮ㄥ垎閲嶅�嶏紝涓�閮ㄥ垎缂哄け銆?/p>

鏌ョ湅浜哅ysql 5.7鍜?.0鐨勫畼鏂规枃妗ｏ紝鎻忚堪濡備笅锛?/strong>

“嘎子”直播卖手机称8999元只卖1999 商家：忽悠人 1880元就能买到	错过了房地产、比特币，2019年可以投资什么？
怎么制作移动网站	被误解到层层伪装的5G天线——心里有苦说不出

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

涓婅堪鍐呭�规�傝堪锛?/strong>鍦ㄤ娇鐢?code>ORDER BY瀵瑰垪杩涜�屾帓搴忔椂锛屽�傛灉瀵瑰簲锛?code>ORDER BY鐨勫垪锛夊垪瀛樺湪澶氳�岀浉鍚屾暟鎹�锛岋紙Mysql锛夋湇鍔″櫒浼氭寜鐓т换鎰忛『搴忚繑鍥炶繖浜涜�岋紝骞朵笖鍙�鑳戒細鏍规嵁鏁翠綋鎵ц�岃�″垝浠ヤ笉鍚岀殑鏂瑰紡杩斿洖銆?/p>

绠�鍗曟潵璇村氨鏄�锛�ORDER BY鏌ヨ�㈢殑鏁版嵁锛屽�傛灉ORDER BY鍒楀瓨鍦ㄥ�氳�岀浉鍚屾暟鎹�锛�Mysql浼氶殢鏈鸿繑鍥炪�傝繖灏变細瀵艰嚧铏界劧浣跨敤浜嗘帓搴忥紝浣嗕篃浼氬彂鐢熶贡搴忕殑鐘跺喌銆?/p>

3銆佽В鍐虫柟妗?/h2>

閽堝�逛笂杩伴棶棰橈紝鍩烘湰鐨勮В鍐虫�濊矾鏄�锛�閬垮厤ORDER BY鍒楃殑鍊煎嚭鐜伴噸澶嶃�傚洜姝わ紝鍙�浠ュ姞鍏ュ叾浠栫淮搴︼紝姣斿�侷D绛夊叾浠栨帓搴忓垪銆?/p>

select * from tb_order order by create_time ,id desc;

杩欐牱锛屽湪create_time鐩稿悓鏃讹紝浼氭牴鎹甶d杩涜�屾帓搴忥紝鑰宨d鑲�瀹氭槸涓嶅悓鐨勶紝灏卞啀涓嶄細鍑虹幇涓婅堪闂�棰樹簡銆?/p>

4銆佹嫇灞曠煡璇?/h2>

鍏跺疄锛屼笂杩板唴瀹瑰湪Mysql鐨勫畼缃戝凡缁忔湁鏄庣‘璇存槑锛岃�屼笖杩樹妇浜嗕緥瀛愩�備笅闈㈠�瑰畼缃戠殑鍐呭�瑰拰渚嬪瓙鍋氫竴涓�绠�鍗曠殑姹囨�绘�荤粨銆?br />

抖音快手挖不动淘宝墙角	华为南泥湾计划打响：上山挖矿下乡养猪 不靠手机也能活
光线就是创造力 不同天气下巧妙区别用光	外设小科普：键盘作为使用率超高的工具，该如何消毒？

4.1 limit鏌ヨ��浼樺寲

濡傛灉鎴戜滑鍙�鏄�鏌ヨ��涓�涓�缁撴灉闆嗙殑涓�閮ㄥ垎锛岄偅涔堜笉瑕佹煡璇㈡墍鏈夋暟鎹�锛岀劧鍚庡啀涓㈠純涓嶉渶瑕佺殑鏁版嵁锛岃�屾槸瑕侀�氳繃limit鏉′欢鏉ヨ繘琛岄檺鍒躲�?/p>

鍦ㄦ病浣跨敤having鏉′欢鏃讹紝Mysql鍙�鑳戒細瀵�limit鏉′欢浼樺寲锛?/strong>

• 濡傛灉鍙�鏌ヨ�㈠嚑鏉℃暟鎹�锛屽缓璁�浣跨敤limit锛岃繖鏍?code>Mysql鍙�鑳戒細鐢ㄥ埌绱㈠紩锛岃�岄�氬父鎯呭喌涓?code>Mysql鏄�鍏ㄨ〃鎵�鎻忥紱
• 濡傛灉灏?code>limit row_count鍜?code>order by缁撳悎浣跨敤锛孧ysql浼氬湪鎵惧埌绗�涓�涓?code>row_count缁撴灉闆嗗悗绔嬪埢鍋滄�㈡帓搴忥紝鑰屼笉鏄�瀵规暣涓�缁撴灉闆嗚繘琛屾帓搴忋�傚�傛灉姝ゆ椂鍩轰簬绱㈠紩杩涜�屾搷浣滐紝閫熷害浼氭洿蹇�銆傚�傛灉蹇呴』杩涜�屾枃浠舵帓搴忥紝鍦ㄦ壘鍒?code>row_count缁撴灉闆嗕箣鍓嶏紝浼氬�归儴鍒嗘垨鎵�鏈夌�﹀悎鏉′欢鐨勭粨鏋滆繘琛屾帓搴忋�備絾褰撴壘鍒?code>row_count缁撴灉涔嬪悗锛屼究涓嶄細瀵瑰墿浣欓儴鍒嗚繘琛屾帓搴忎簡銆傝繖绉嶇壒鎬х殑涓�涓�琛ㄧ幇灏辨槸鎴戜滑鍓嶉潰鎻愬埌鐨勫甫鏈塴imit鍜屼笉甯�limit杩涜�屾煡璇㈡椂锛岃繑鍥炵殑缁撴灉椤哄簭鍙�鑳戒笉鍚屻�?/li>
• 濡傛灉灏?code>limit row_count鍜宒istinct缁撳悎浣跨敤锛孧ysql浼氬湪鎵惧埌row_count缁撴灉闆嗗敮涓�琛屽悗绔嬮┈鍋滄��銆?/li>
• 鍦ㄦ煇浜涙儏鍐典笅锛屽彲浠ラ�氳繃鎸夌収椤哄簭璇诲彇绱㈠紩锛堟垨瀵圭储寮曡繘琛屾帓搴忥級锛岀劧鍚庤�＄畻鎽樿�佺洿鍒扮储寮曞彉鍖栨潵瀹炵幇group by銆傚湪杩欑�嶆儏鍐典笅锛�limit row_count涓嶄細璁＄畻浠讳綍涓嶅繀瑕佺殑group by鍊笺�?/li>
• 涓�鏃�MySQL鍚戝�㈡埛绔�鍙戦�佷簡鎵�闇�鏁伴噺鐨勮�岋紝灏变細涓�姝㈡煡璇�锛岄櫎闈炰娇鐢ㄤ�?code>SQL_CALC_FOUND_ROWS銆傚湪杩欑�嶆儏鍐典笅锛屽彲浠ヤ娇鐢� SELECT FOUND_ROWS() 妫�绱㈣�屾暟銆?/li>
• LIMIT 0浼氬揩閫熻繑鍥炰竴涓�绌洪泦鍚堬紝閫氬父鍙�鐢ㄤ簬妫�鏌�SQL鐨勬湁鏁堟�с�傝繕鍙�浠ョ敤浜庡湪搴旂敤绋嬪簭涓�鑾峰緱缁撴灉闆嗙殑绫诲瀷銆傚湪Mysql瀹㈡埛绔�涓�锛屽彲浠ヤ娇鐢?code>--column-type-info鏉ユ樉绀虹粨鏋滃垪绫诲瀷銆?/li>
• 濡傛灉浣跨敤涓存椂琛ㄦ潵瑙ｆ瀽鏌ヨ��锛?code>Mysql浼氫娇鐢?code> limit row_count鏉ヨ�＄畻闇�瑕佸�氬皯绌洪棿銆?/li>
• 濡傛灉order by鏈�浣跨敤绱㈠紩锛屼笖瀛樺湪limit鏉′欢锛屽垯浼樺寲鍣ㄥ彲鑳戒細閬垮厤浣跨敤鍚堝苟鏂囦欢锛岃�岄噰鐢ㄥ唴瀛?code>filesort鎿嶄綔瀵瑰唴瀛樹腑鐨勮�岃繘琛屾帓搴忋�?br />

浜嗚В浜?code>limit鐨勪竴浜涚壒鎬э紝涓嬮潰鍐嶅洖鍒版湰鏂囩殑閲嶇偣锛?code>limit row_count鍜?code>order by缁撳悎浣跨敤鐗规�с�?/p>

4.2 limit涓巓rder by缁撳悎浣跨敤

鍦ㄤ笂闈㈢��浜屾潯涓�宸茬粡鎻愬埌锛�limit row_count鍜?code>order by缁撳悎鍛堢幇鐨勭壒鎬т箣涓�灏辨槸缁撴灉杩斿洖鐨勯『搴忔槸涓嶇‘瀹氱殑銆傝�屽奖鍝嶆墽琛岃�″垝鐨勪竴涓�鍥犵礌灏辨�?code>limit锛屽洜姝ゅ甫鏈?code>limit涓庝笉甯︽湁limit鎵ц�屽悓鏍风殑鏌ヨ�㈣��鍙ワ紝杩斿洖缁撴灉鐨勯『搴忓彲鑳戒笉鍚屻�?/p>

涓嬮潰绀轰緥涓�锛屾牴鎹甤ategory鍒楄繘琛屾帓搴忔煡璇�锛岃�宨d鍜宺ating鏄�涓嶇‘瀹氱殑锛�

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

褰撴煡璇㈣��鍙ュ寘鍚玪imit鏃讹紝鍙�鑳戒細褰卞搷鍒癱ategory鍊肩浉鍚岀殑鏁版嵁锛?/strong>

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

鍏朵腑id涓?鍜?鐨勭粨鏋滀綅缃�鍙戠敓浜嗗彉鍖栥�?/p>

鍦ㄥ疄璺典腑锛屼繚鎸佹煡璇㈢粨鏋滅殑椤哄簭鎬у線寰�闈炲父閲嶈�侊紝姝ゆ椂灏遍渶瑕佸紩鍏ュ叾浠栧垪鏉ヤ繚璇佺粨鏋滅殑椤哄簭鎬т簡銆?/p>

褰撲笂杩板疄渚嬪紩鍏�id涔嬪悗锛屾煡璇㈣��鍙ュ強缁撴灉濡備笅锛?/strong>

苹果公司为何屡屡成功？以摄像头升级为例，看苹果独特的组织结构	win10系统告别电脑卡顿的几种方法
美团“卷不动”香港外卖市场	买冰箱空调洗衣机少踩雷！这些选购干货看下

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+
​
mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

鍙�浠ョ湅鍑猴紝褰撴坊鍔犱簡id鍒楃殑鎺掑簭锛屽嵆浣?code>category鐩稿悓锛屼篃涓嶄細鍑虹幇涔卞簭闂�棰樸�傝繖姝ｄ笌鎴戜滑鏈�鍒濈殑瑙ｅ喅鏂规�堜竴鑷淬�?/p>

5銆佸皬缁?/h2>

鏈�鏉ラ�氳繃瀹炶返涓�鍋跺彂鐨勪竴涓�鍧戯紝鑱婂埌浜�Mysql瀵?code>limit鏌ヨ�㈣��鍙ョ殑浼樺寲锛屽悓鏃舵彁渚涗簡瑙ｅ喅鏂规�堬紝鍗虫弧瓒充簡涓氬姟闇�姹傦紝鍙堥伩鍏嶄簡涓氬姟閫昏緫鐨勯敊璇�銆?/p>

寰堝�氭湅鍙嬮兘鍦ㄤ娇鐢�order by鍜?code>limit璇�鍙ヨ繘琛屾煡璇�锛屼絾濡傛灉涓嶇煡閬?code>Mysql鐨勮繖浜涗紭鍖栫壒鎬э紝寰堝彲鑳藉凡缁忓叆鍧戯紝鍙�涓嶈繃鏁版嵁閲忔病鏈夎Е鍙戝憟鐜拌�屽凡銆?/p>

鍒版�よ繖绡囧叧浜嶮ysql鎺掑簭鐨勭壒鎬ц�︽儏鐨勬枃绔犲氨浠嬬粛鍒拌繖浜�,鏇村�氱浉鍏矼ysql鎺掑簭鐗规�у唴瀹硅�锋悳绱㈣剼鏈�涔嬪�朵互鍓嶇殑鏂囩珷鎴栫户缁�娴忚�堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у�朵互鍚庡�氬�氭敮鎸佽剼鏈�涔嬪�讹紒