¶ÔmysqlµÄorder by¼°limitÓÅ»¯ÒªÊÊÊý¾ÝÇé¿ö¶ø¶¨
ÒÔÏÂÕâÆªÎÄÕÂÂÛÊöÉÏÔÚʲôÇé¿öÏÂ×Ó²éѯ±ÈÒ»°ãµÄÄÚÁ¬½Ó²éѯ¸ü¼Ó¿ìËÙ
http://www.mysqlperformanceblog.com/2010/03/18/when-the-subselect-runs-faster/
ÒÔÏÂÊÇÎÒ¶ÔmysqlµÄorder byºÍlimitÓÅ»¯µÄ½¨Ò飬ҪÊʱíµÄÊý¾ÝÁ¿ºÍÓÃÀ´¹ýÂËÁеÄΨһֵµÄ¸öÊý¶ø¶¨
£¨1£©where×Ó¾äÖÐÓÃÀ´¹ýÂ˽á¹ûµÄÁÐËùÔÚË÷ÒýµÄcadinalityºÜС£¬¼´ÓÃÏà¹ØµÄÁÐÌõ¼þ¹ýÂËËùµÃµ½µÄ½á¹û¼¯ºÜ´óʱ£¬ÓÃ×Ó²éѯ¸ü¿ì
ÐÎʽÈ磺select * from table where column in (select column from table where column=?) order by column_id limit m,n
£¨2£©µ±¹ýÂËËùµÃµ½µÄ½á¹û¼¯ºÜСʱ£¬²»ÓÃ×Ó²éѯ¸ü¿ì(ÒòΪÈç¹ûÓÃ×Ó²éѯ£¬ÏȽøÐÐÅÅÐò£¬¿ÉÄÜÅÅÔÚÇ°ÃæµÄnÌõ¼Ç¼ȫ²»ÊÇÂú×ã×Ó²éѯÌõ¼þµÄ£¬Òò´Ë¿ÉÄÜɨÃè¸ü¶àµÄÐУ»µ«µ±¹ýÂ˵Ľá¹û¼¯ºÜ´óʱ£¬ÅÅÔÚÇ°ÃæµÄÂú×ãÌõ¼þµÄÐгöÏֵĻúÂÊÒ²´ó£¬Òò´Ë¿ÉÄÜɨÃè¸üÉÙµÄÐУ©
ÐÎʽÈ磺select * from table where column=? order by column_id limit m,n
Ïà¹ØÎĵµ£º
Ò»¡¢µ¼ÈëÊý¾Ý
1¡¢È·¶¨
Êý¾Ý¿âĬÈϱàÂ룬±ÈÈç±àÂë
Ϊgbk,½«¶ÁÈë;¾¶±àÂëͬÑùÉèΪgbk£¬ÃüÁîΪ£º
set names gbk;
2¡¢source
d:/20080613.sql µ¼ÈëÊý¾Ý¡£ÑéÖ¤
Êý¾Ý¿â
ÖеÄÊý¾ÝÊÇ·ñ´æÔÚÂÒÂë¡£
3¡¢Èç¹ûÈÔÈ»´æÔÚÂÒÂëÎÊÌ⣬Õâʱºò¾ÍÒª¿¼ÂǸı䵼ÈëÎļþµÄ±à ......
À´Ô´ http://e-xia.com/2009/06/rownum-in-mysql/
ÔÚ¹¤×÷ÖÐÅöµ½ÕâÑùµÄÎÊÌ⣬ÔÚÉú³É±¨±íʱµÚÒ»ÁÐÒªÊä³ötop 1, top 2, ... , top 10¡£¶ømysql²¢²»×Ô´øÕâÑùµÄ¹¦ÄÜ¡£¼ÙÉèÎÒÃÇÓÐÕâÑùµÄÒ»¸ö±í£º
mysql> create table tbl (
-> id int primary key,
-> col int
-> );
Query OK, 0 ......
mysql version 5.1
ÔÚmysql´´½¨±íµÄʱºò¾³£»áÓöµ½´´½¨ÈÕÆÚ×Ö¶ÎÐèÒªÉèÖõ±Ç°Ê±¼äΪĬÈÏÖµµÄʱºò£¬¾ÍÈçsqlserver2000Ò»Ñù£¬°ÑĬÈÏÖµÉèΪgetdate()¼´¿É£¬ÎÒÔÚÍøÉϲéÁËN¾Ã¶¼Ã»Óв鵽ʹÓÃÓÐЧµÄ·½·¨£¬×îºó×Ô¼ºÑо¿ÁËһЩ·½·¨Óë´ó¼Ò·ÖÏí£º
Êý¾Ý¿â£ºtest_db1
´´½¨±í£ºtest_ta1
Á½¸ö×Ö¶ ......
mysql¿ÉÒÔÔËÐÐÔÚ²»Í¬sql modeģʽÏÂÃæ£¬sql modeģʽ¶¨ÒåÁËmysqlÓ¦¸ÃÖ§³ÖµÄsqlÓï·¨£¬Êý¾ÝУÑéµÈ£¡
²é¿´Ä¬ÈϵÄsql modeģʽ£º
select @@sql_mode;
ÎÒµÄÊý¾Ý¿âÊÇ£º
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ÔÚ´ËģʽÏÂÃæ£¬Èç¹û²åÈëµÄÊý¾ÝµÄ³¤¶È´óÓÚ¶¨ÒåµÄ³¤¶È£¬ÄÇô¾Í»á±¨´í£¡
......
ÒýÑÔ
¡¡¡¡Êý¾Ý¿âµÄÉè¼Æ·¶Ê½ÊÇÊý¾Ý¿âÉè¼ÆËùÐèÒªÂú×ãµÄ¹æ·¶£¬Âú×ãÕâЩ¹æ·¶µÄÊý¾Ý¿âÊǼò½àµÄ¡¢½á¹¹Ã÷ÎúµÄ£¬Í¬Ê±£¬²»»á·¢Éú²åÈ루insert£©¡¢É¾³ý£¨delete£©ºÍ¸üУ¨update£©²Ù×÷Òì³£¡£·´Ö®ÔòÊÇÂÒÆß°ËÔ㣬²»½ö¸øÊý¾Ý¿âµÄ±à³ÌÈËÔ±ÖÆÔìÂé·³£¬¶øÇÒÃæÄ¿¿ÉÔ÷£¬¿ÉÄÜ´æ´¢ÁË´óÁ¿²»ÐèÒªµÄÈßÓàÐÅÏ¢¡£
¡¡¡¡Éè¼Æ·¶Ê½ÊDz»ÊǺÜÄѶ®ÄØ£¿·ÇÒ² ......