mysqlʹÓÃrandËæ»ú²éѯ¼Ç¼ЧÂʲâÊÔ
Ò»Ö±ÒÔΪmysqlËæ»ú²éѯ¼¸ÌõÊý¾Ý£¬¾ÍÓÃ
SELECT * from `table` ORDER BY RAND() LIMIT 5
¾Í¿ÉÒÔÁË¡£
µ«ÊÇÕæÕý²âÊÔһϲŷ¢ÏÖÕâÑùЧÂʷdz£µÍ¡£Ò»¸ö15ÍòÓàÌõµÄ¿â£¬²éѯ5ÌõÊý¾Ý£¬¾ÓȻҪ8ÃëÒÔÉÏ
²é¿´¹Ù·½ÊֲᣬҲ˵rand()·ÅÔÚORDER BY ×Ó¾äÖлᱻִÐжà´Î£¬×ÔȻЧÂʼ°ºÜµÍ¡£
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
ËÑË÷Google£¬ÍøÉÏ»ù±¾É϶¼ÊDzéѯmax(id) * rand()À´Ëæ»ú»ñÈ¡Êý¾Ý¡£
SELECT *
from `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) from `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;
µ«ÊÇÕâÑù»á²úÉúÁ¬ÐøµÄ5Ìõ¼Ç¼¡£½â¾ö°ì·¨Ö»ÄÜÊÇÿ´Î²éѯһÌõ£¬²éѯ5´Î¡£¼´±ãÈç´ËÒ²ÖµµÃ£¬ÒòΪ15ÍòÌõµÄ±í£¬²éѯֻÐèÒª0.01Ãë²»µ½¡£
ÉÏÃæµÄÓï¾ä²ÉÓõÄÊÇJOIN£¬mysqlµÄÂÛ̳ÉÏÓÐÈËʹÓÃ
SELECT *
from `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) from `table` )
ORDER BY id LIMIT 1;
ÎÒ²âÊÔÁËһϣ¬ÐèÒª0.5Ã룬ËÙ¶ÈÒ²²»´í£¬µ«ÊǸúÉÏÃæµÄÓï¾ä»¹ÊÇÓкܴó²î¾à¡£×ܾõÓÐʲôµØ·½²»Õý³£¡£
ÓÚÊÇÎÒ°ÑÓï¾ä¸ÄдÁËһϡ£
SELECT * from `table`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) from `table`)))
ORDER BY id LIMIT 1;
ÕâÏ£¬Ð§ÂÊÓÖÌá¸ßÁË£¬²éѯʱ¼äÖ»ÓÐ0.01Ãë
×îºó£¬ÔÙ°ÑÓï¾äÍêÉÆһϣ¬¼ÓÉÏMIN(id)µÄÅжϡ£ÎÒÔÚ×ʼ²âÊÔµÄʱºò£¬¾ÍÊÇÒòΪûÓмÓÉÏMIN(id)µÄÅжϣ¬½á¹ûÓÐÒ»°ëµÄʱ¼ä×ÜÊDzéѯµ½±íÖеÄÇ°Ã漸ÐС£
ÍêÕû²éѯÓï¾äÊÇ£º
SELECT * from `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) from
`table`)-(SELECT MIN(id) from `table`)) + (SELECT MIN(id) from
`table`)))
ORDER BY id LIMIT 1;
SELECT *
from `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) from
`table`)-(SELECT MIN(id) from `table`))+(SELECT MIN(id) from `table`))
AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
×îºóÔÚphpÖжÔÕâÁ½¸öÓï¾ä½øÐзֱð²éѯ10´Î£¬
Ç°Õß»¨·Ñʱ¼ä 0.147433 Ãë
ºóÕß»¨·Ñʱ¼ä 0.015130 Ãë
¿´À´²ÉÓÃJOINµÄÓï·¨±ÈÖ±½ÓÔÚWHEREÖÐʹÓú¯ÊýЧÂÊ»¹Òª¸ßºÜ¶à¡£
Ïà¹ØÎĵµ£º
MySQL³£ÓòÙ×÷»ù±¾²Ù×÷£¬ÒÔ϶¼ÊÇMySQL5.0ϲâÊÔͨ¹ýÊ×ÏÈ˵Ã÷Ï£¬¼ÇסÔÚÿ¸öÃüÁî½áÊøʱ¼ÓÉÏ£»£¨·ÖºÅ£©
1.µ¼³öÕû¸öÊý¾Ý¿â
mysqldump -u Óû§Ãû -p --default-character-set=latin1 Êý¾Ý¿âÃû > µ¼³öµÄÎļþÃû(Êý¾Ý¿âĬÈϱàÂëÊÇlatin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.µ¼³öÒ»¸ö±í
mysqldump -u Ó ......
¼ÙÉèÄãÊÇÒ»¸öAccess¿ª·¢ÈËÔ±£¬¶øÄãÏëÒÆÖ²Ò»¸öAccessºǫ́µ½Ò»¸ö±¾µØµÄMySQL ·þÎñÆ÷ÓÃÓÚ¿ª·¢ºÍ²âÊÔ¡£Ä㽫ά»¤Äã×Ô¼ºµÄ½â¾ö·½°¸²¢ÀÖÓÚʹÓÃCommunity Server
¡¡¡¡µÚÒ»²½——°²×°MySQL
¡¡¡¡µ½http://dev.mysql.com/downloads/ ÏÂÔØÕâ¸ö·þÎñÆ÷¡¢MySQL GUI¹¤¾ßºÍMySQL ODBCÇý¶¯¡£Äã¿ÉÒÔÑ¡ÔñµÄ·þÎñÆ÷ÓУº5.0, 5.1»ò6.0 ......
ÎÒÒ»Ö±ÊÇʹÓÃmysqlÕâ¸öÊý¾Ý¿âÈí¼þ£¬Ëü¹¤×÷±È½ÏÎȶ¨£¬Ð§ÂÊÒ²ºÜ¸ß¡£ÔÚÓöµ½ÑÏÖØÐÔÄÜÎÊÌâʱ£¬Ò»°ã¶¼ÓÐÕâô¼¸ÖÖ¿ÉÄÜ£º
1¡¢Ë÷ÒýûÓн¨ºÃ£»
2¡¢sqlд·¨¹ýÓÚ¸´ÔÓ£»
3¡¢ÅäÖôíÎó£»
4¡¢»úÆ÷ʵÔÚ¸ººÉ²»ÁË£»
1¡¢Ë÷ÒýûÓн¨ºÃ
Èç¹û¿´µ½mysqlÏûºÄµÄcpuºÜ´ó£¬¿ÉÒÔÓÃmysqlµÄclient¹¤¾ßÀ´¼ì²é¡£
ÔÚlinuxÏÂÖ´ÐÐ
/usr/local/mysql/bin/ ......
³õ´Î°²×°·½·¨£º
1¡¢È¥¹Ù·½ÏÂÔØMYSQL 5.0¡£
¡¡¡¡2¡¢Ð޸ķÀ»ðǽµÄÉèÖã¬ÔÚÔÊÐíÀýÍâÀïÌí¼ÓеĶ˿ڣ¬Ãû×ÖΪmysql£¬¿ª·ÅµÄ¶Ë¿ÚΪTCP 3306¡£
¡¡¡¡Õâ¸ö´ó¼ÒÓ¦¸Ã¶¼»á£¬ÔÚVista¿ØÖÆÃæ°åµÄWindows·À»ðǽÀïÃ棬µã»÷“¸ü¸ÄÉèÖÔ-“ÀýÍ┣¬µã»÷“Ìí¼Ó¶Ë¿Ú”¾Í¿ÉÒÔ¡£¡¡¡¡
......
MySQLÊÇÎÒÃDZȽϳ£ÓõÄÒ»ÖÖÊý¾Ý
¿âÈí¼þ¡£ËüÓÐ×ÅÖî¶àµÄÓŵ㣬È翪ԴµÄ£¬Ãâ·ÑµÄµÈµÈ¡£ÆäʵËü»¹ÓÐÒ»¸öºÜºÃµÄÌص㣬ÄǾÍÊÇÓжàÖÖÒýÇæ¿ÉÒÔ¹©ÄãÑ¡Ôñ¡£Èç¹ûÈü
³µÊÖÄܸù¾Ý²»Í¬µÄ·¿ö£¬µØÐÎËæÊÖ¸ü»»ÓëÖ®×îÊÊÒ˵ÄÒýÇ棬ÄÇôËûÃǽ«´´ÔìÆæ¼£¡£È»¶øÄ¿Ç°ËûÃÇ»¹×ö²»µ½ÄÇÑù±ã½ÝµÄ¸ü»»ÒýÇ棬µ«ÊÇÎÒÃÇÈ´¿ÉÒÔ£¡
Ëù ......