´ÓMysqlijһ±íÖÐËæ»ú¶ÁÈ¡nÌõÊý¾ÝµÄSQL²éѯÓï¾äµÄÓÐЧ·½·¨

¸üÐÂʱ¼ä£º2015-03-14 13:11:33 À´Ô´£º ×÷Õß: ä¯ÀÀ2272´Î ÎÄ×Ö´óС£º´óÖÐС

ÈôÒªÔÚi¡Ü R¡Ü jÕâ¸ö·¶Î§µÃµ½Ò»¸öËæ»úÕûÊýR£¬ÐèÒªÓõ½±í´ïʽ FLOOR(i + RAND() * (j¨C i + 1))¡£ÀýÈ磬 ÈôÒªÔÚ7 µ½ 12 µÄ·¶Î§£¨°üÀ¨7ºÍ12£©Äڵõ½Ò»¸öËæ»úÕûÊý, ¿ÉʹÓÃÒÔÏÂÓï¾ä£º

SELECT FLOOR(7 + (RAND() * 6));

ÒÔÉÏÕª³­×ÔMySQLÊÖ²á

´Ó Mysql ±íÖÐËæ»ú¶ÁÈ¡Êý¾Ý²»ÄÑ£¬·½·¨»¹Í¦¶àµÄ£¬µ«ÊÇÈç¹ûÒª¿¼ÂÇЧÂÊ£¬µÃµ½Ò»¸ö¿ìËٵĸßЧÂʵķ½·¨£¬ÄǾͲ»ÊÇÒ»¼þ¼òµ¥µÄÊÂÇéÁË£¨ÖÁÉÙ¶ÔÎÒÀ´Ëµ²»¼òµ¥£©¡£

Ëæ»ú»ñµÃMysqlÊý¾Ý±íµÄÒ»Ìõ»ò¶àÌõ¼Ç¼Óкܶ෽·¨£¬ÏÂÃæÎÒ¾ÍÒÔusers£¨userId£¬userName£¬password¡­¡­£©±í£¨ÓÐÒ»°Ù¶àÍòÌõ¼Ç¼£©ÎªÀý£¬¶Ô±È½²½âϼ¸¸ö·½·¨Ð§ÂÊÎÊÌ⣺


  1. select * from      users order by rand() LIMIT 1
    Ö´ ÐиÃsqlÓï¾ä£¬ÀÏ°ëÌìûÓз´Ó¦£¬×îºó±»ÆÈÊÖ¶¯Í£Ö¹Ö´ÐУ¬Ôõ¸öÉËÈËÁ˵𡣡ºóÀ´ÎÒ²éÁËÒ»ÏÂMYSQLÊֲᣬÀïÃæÕë¶ÔRAND()µÄÌáʾ´ó¸ÅÒâ˼¾ÍÊÇ£¬ÔÚ ORDER BY´Ó¾äÀïÃæ²»ÄÜʹÓÃRAND()º¯Êý£¬ÒòΪÕâÑù»áµ¼ÖÂÊý¾ÝÁỶà´ÎɨÃ裬µ¼ÖÂЧÂÊÏ൱Ï൱µÄµÍ£¡Ð§Âʲ»ÐУ¬ÇмÉʹÓã¡

  2. SELECT * FROM      users  AS t1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(userId)      FROM `users`)-(SELECT MIN(userId) FROM users))+(SELECT MIN(userId) FROM      users)) AS userId) AS t2 WHERE t1.userId >= t2.userId ORDER BY      t1.userId LIMIT 1
    Ö´ÐиÃsqlÓï¾ä£¬ÓÃʱ0.031s£¬Ð§ÂÊû˵µÄ£¬Ï൱µÄ¸øÁ¦£¡ÐÄÀïÄǸöˬ°¡£¬½ô½Ó×Å£¬ÎÒ°Ñ¡±LIMIT      1¡°¸ÄΪÁË¡±LIMIT 100¡° Ëæ»úÈ¡Ò»°ÙÌõ¼Ç¼£¬ÓÃʱ0.048£¬¸øÁ¦°É¡£¿ÉÊǾÍÔÚ´ËʱÎÊÌâ³öÏÖÁË£¬·¢ÏÖ½á¹ûºÃÏñ²»ÊÇËæ»úµÄ£¿ÎªÁËÑéÖ¤½á¹û£¬ÓÖÖ´ÐÐÁËN´Î£¬Õæ²»ÊÇËæ»úµÄ£¬ ÎÊÌâ³öÏÖÔÚ¡±ORDER BY t1.userId¡°ÕâÀ°´userIdÅÅÐòÁË¡£Ëæ»úÈ¡Ò»Ìõ¼Ç¼»¹ÊDz»´íµÄÑ¡Ôñ£¬¶àÌõ¾Í²»ÐÐÁË°¡£¡

  3. SELECT * FROM      users WHERE userId >= ((SELECT MAX(userId) FROM users)-(SELECT      MIN(userId) FROM users)) * RAND() + (SELECT MIN(userId) FROM users)       LIMIT 1
    Ö´ÐиÃsqlÓï¾ä£¬ÓÃʱ0.039s£¬Ð§ÂÊÌ«¸øÁ¦ÁË£¡½Ó×ÅÎҾͰѡ±LIMIT 1¡°¸ÄΪÁË¡±LIMIT 10000¡°£¬ÓÃʱ0.063s¡£¾­¹ý¶à´ÎÑéÖ¤£¬¸ç¶ÔµÆ·¢ÊÄ£¬½á¹û¿Ï¶¨ÊÇËæ»úµÄ£¡
    ½áÂÛ£ºËæ»úÈ¡Ò»Ìõ»ò¶àÌõ¼Ç¼£¬·½·¨¶¼²»´í£¡

  4. ͨ¹ýsql»ñµÃ×î´óÖµºÍ×îСֵ£¬È»ºóͨ¹ýphpµÄrandÉú³ÉÒ»¸öËæ»úÊýrandnum£¬ÔÙͨ¹ýSELECT * FROM users WHERE userId >= randnum LIMIT 1£¬»ñµÃÒ»Ìõ¼Ç¼ЧÂÊÓ¦¸Ã»¹¿ÉÒÔ£¬¶àÌõÓ¦¸Ã¾Í²»ÐÐÁË¡£

    ½áÂÛ£º·½·¨1ЧÂʲ»ÐУ¬ÇмÉʹÓã»Ëæ»ú»ñµÃÒ»Ìõ¼Ç¼£¬·½·¨2ÊÇÏ൱²»´íµÄÑ¡Ôñ£¬²ÉÓÃJOINµÄÓï·¨±ÈÖ±½ÓÔÚWHEREÖÐʹÓú¯ÊýЧÂÊ»¹ÊÇÒª¸ßһЩµÄ£¬²»¹ý·½·¨3Ò²²»´í£»Ëæ»ú»ñµÃ¶àÌõ¼Ç¼£¬·½·¨3û˵µÄ£¡

¡¾´ó ÖРС¡¿¡¾´òÓ¡¡¿ ¡¾·±Ìå¡¿¡¾Í¶¸å¡¿¡¾Êղء¿ ¡¾ÍƼö¡¿¡¾¾Ù±¨¡¿¡¾ÆÀÂÛ¡¿ ¡¾¹Ø±Õ¡¿ ¡¾·µ»Ø¶¥²¿¡¿
    ÎÄÕÂÆÀÂÛ

    ÆÀÂÛ

    ÕÊ¡¡¡¡ºÅ: ÃÜÂë: (ÐÂÓû§×¢²á)
    Ñé Ö¤ Âë:
    ±í¡¡¡¡Çé:
    ÄÚ¡¡¡¡ÈÝ:

    ÈÈÃÅÎÄÕÂ