MySQLÐÔÄÜÓÅ»¯µÄ×î¼Ñ20+Ìõ¾Ñé
½ñÌ죬Êý¾Ý¿âµÄ²Ù×÷Ô½À´Ô½³ÉΪÕû¸öÓ¦ÓõÄÐÔÄÜÆ¿¾±ÁË£¬Õâµã¶ÔÓÚWebÓ¦ÓÃÓÈÆäÃ÷ÏÔ¡£¹ØÓÚÊý¾Ý¿âµÄÐÔÄÜ£¬Õâ²¢²»Ö»ÊÇDBA²ÅÐèÒªµ£ÐĵÄÊ£¬¶øÕâ¸üÊÇÎÒÃdzÌÐòÔ±ÐèҪȥ¹Ø×¢µÄÊÂÇé¡£µ±ÎÒÃÇÈ¥Éè¼ÆÊý¾Ý¿â±í½á¹¹£¬¶Ô²Ù×÷Êý¾Ý¿âʱ£¨ÓÈÆäÊDzé±íʱµÄSQLÓï¾ä£©£¬ÎÒÃǶ¼ÐèҪעÒâÊý¾Ý²Ù×÷µÄÐÔÄÜ¡£ÕâÀÎÒÃDz»»á½²¹ý¶àµÄSQLÓï¾äµÄÓÅ»¯£¬¶øÖ»ÊÇÕë¶ÔMySQLÕâÒ»WebÓ¦ÓÃ×î¶àµÄÊý¾Ý¿â¡£Ï£ÍûÏÂÃæµÄÕâЩÓÅ»¯¼¼ÇɶÔÄãÓÐÓá£
1. Ϊ²éѯ»º´æÓÅ»¯ÄãµÄ²éѯ
´ó¶àÊýµÄMySQL·þÎñÆ÷¶¼¿ªÆôÁ˲éѯ»º´æ¡£ÕâÊÇÌá¸ßÐÔ×îÓÐЧµÄ·½·¨Ö®Ò»£¬¶øÇÒÕâÊDZ»MySQLµÄÊý¾Ý¿âÒýÇæ´¦ÀíµÄ¡£µ±ÓкܶàÏàͬµÄ²éѯ±»Ö´ÐÐÁ˶à´ÎµÄʱºò£¬ÕâЩ²éѯ½á¹û»á±»·Åµ½Ò»¸ö»º´æÖУ¬ÕâÑù£¬ºóÐøµÄÏàͬµÄ²éѯ¾Í²»ÓòÙ×÷±í¶øÖ±½Ó·ÃÎÊ»º´æ½á¹ûÁË¡£
ÕâÀï×îÖ÷ÒªµÄÎÊÌâÊÇ£¬¶ÔÓÚ³ÌÐòÔ±À´Ëµ£¬Õâ¸öÊÂÇéÊǺÜÈÝÒ×±»ºöÂԵġ£ÒòΪ£¬ÎÒÃÇijЩ²éѯÓï¾ä»áÈÃMySQL²»Ê¹Óûº´æ¡£Çë¿´ÏÂÃæµÄʾÀý£º
ÉÏÃæÁ½ÌõSQLÓï¾äµÄ²î±ð¾ÍÊÇ CURDATE() £¬MySQLµÄ²éѯ»º´æ¶ÔÕâ¸öº¯Êý²»Æð×÷Óá£ËùÒÔ£¬Ïñ NOW() ºÍ RAND() »òÊÇÆäËüµÄÖîÈç´ËÀàµÄSQLº¯Êý¶¼²»»á¿ªÆô²éѯ»º´æ£¬ÒòΪÕâЩº¯ÊýµÄ·µ»ØÊǻ᲻¶¨µÄÒ×±äµÄ¡£ËùÒÔ£¬ÄãËùÐèÒªµÄ¾ÍÊÇÓÃÒ»¸ö±äÁ¿À´´úÌæMySQLµÄº¯Êý£¬´Ó¶ø¿ªÆô»º´æ¡£
2. EXPLAIN ÄãµÄ SELECT ²éѯ
ʹÓà EXPLAIN ¹Ø¼ü×Ö¿ÉÒÔÈÃÄãÖªµÀMySQLÊÇÈçºÎ´¦ÀíÄãµÄSQLÓï¾äµÄ¡£Õâ¿ÉÒÔ°ïÄã·ÖÎöÄãµÄ²éѯÓï¾ä»òÊDZí½á¹¹µÄÐÔÄÜÆ¿¾±¡£
EXPLAIN µÄ²éѯ½á¹û»¹»á¸æËßÄãÄãµÄË÷ÒýÖ÷¼ü±»ÈçºÎÀûÓõģ¬ÄãµÄÊý¾Ý±íÊÇÈçºÎ±»ËÑË÷ºÍÅÅÐòµÄ……µÈµÈ£¬µÈµÈ¡£
ÌôÒ»¸öÄãµÄSELECTÓï¾ä£¨ÍƼöÌôÑ¡ÄǸö×Ôӵģ¬Óжà±íÁª½ÓµÄ£©£¬°Ñ¹Ø¼ü×ÖEXPLAIN¼Óµ½Ç°Ãæ¡£Äã¿ÉÒÔʹÓÃphpmyadminÀ´×öÕâ¸öÊ¡£È»ºó£¬Äã»á¿´µ½Ò»Õűí¸ñ¡£ÏÂÃæµÄÕâ¸öʾÀýÖУ¬ÎÒÃÇÍü¼Ç¼ÓÉÏÁËgroup_idË÷Òý£¬²¢ÇÒÓбíÁª½Ó£º
µ±ÎÒÃÇΪ group_id ×ֶμÓÉÏË÷Òýºó£º
ÎÒÃÇ¿ÉÒÔ¿´µ½£¬Ç°Ò»¸ö½á¹ûÏÔʾËÑË÷ÁË 7883 ÐУ¬¶øºóÒ»¸öÖ»ÊÇËÑË÷ÁËÁ½¸ö±íµÄ 9 ºÍ 16 ÐС£²é¿´rowsÁпÉÒÔÈÃÎÒÃÇÕÒµ½Ç±ÔÚµÄÐÔÄÜÎÊÌâ¡£
3. µ±Ö»ÒªÒ»ÐÐÊý¾ÝʱʹÓà LIMIT 1
µ±Äã²éѯ±íµÄÓÐЩʱºò£¬ÄãÒѾ֪µÀ½á¹ûÖ»»áÓÐÒ»Ìõ½á¹û£¬µ«ÒòΪÄã¿ÉÄÜÐèҪȥfetchÓα꣬»òÊÇÄãÒ²Ðí»áÈ¥¼ì²é·µ»ØµÄ¼Ç¼Êý¡£
ÔÚÕâÖÖÇé¿öÏ£¬¼ÓÉÏ LIMIT 1 ¿ÉÒÔÔö¼ÓÐÔÄÜ¡£ÕâÑùÒ»Ñù£¬MySQLÊý¾Ý¿âÒýÇæ»áÔÚÕÒµ½Ò»ÌõÊý¾ÝºóÍ£Ö¹ËÑË÷£¬¶ø²»ÊǼÌÐøÍùºó²éÉÙÏÂÒ»Ìõ·ûºÏ¼Ç¼µÄÊý¾Ý¡£
ÏÂÃæµÄʾÀý£¬Ö»ÊÇΪÁËÕÒÒ»ÏÂÊÇ·ñÓГÖйú”µÄÓû§£¬ºÜÃ÷ÏÔ£¬ºóÃæµÄ»á±ÈÇ°ÃæµÄ¸üÓÐЧÂÊ¡££¨Çë×¢Ò⣬µÚÒ»Ìõ
Ïà¹ØÎĵµ£º
MySQL °æ±¾£º5.0.45 phpMyAdmin°æ±¾£º2.11.3
Ê×ÏÈ¿´MySQL 5.0²Î¿¼ÊÖ²áÖйØÓÚ´´½¨´æ´¢¹ý³ÌµÄÓ﷨˵Ã÷£º
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] r ......
×÷ÕߣºDirk (dirk.ye AT gmail.com)
Url£ºhttp://dirk.pdx.cn
ÈÕÆÚ£º2004/12/08
Ê×ÏÈ£¬Ä¿Ç°ÔÚ²úÆ·»·¾³¿ÉÓõÄMySQL°æ±¾£¨Ö¸4.0.xºÍ4.1.x£©ÖУ¬Ö»ÓÐInnoDBÒýÇæ²ÅÔÊÐíʹÓÃÍâ¼ü£¬ËùÒÔ£¬ÎÒÃǵÄÊý¾Ý±í±ØÐëʹÓÃ
InnoDBÒýÇæ¡£
ÏÂÃæ£¬ÎÒÃÇÏÈ´´½¨ÒÔϲâÊÔÓÃÊý¾Ý¿â
񡜧
CREATE TABLE `roottb` (
`id` INT(11) UNSIGNED AU ......
ÔÚ´¦ÀíÒ»¸ö´óÊý¾ÝÁ¿Êý¾Ý¿âµÄʱºò
ͻȻ·¢ÏÖmysql¶ÔÓÚcount(*)µÄ²»Í¬´¦Àí»áÔì³É²»Í¬µÄ½á¹û
±ÈÈçÖ´ÐÐ
SELECT count(*) from tablename
¼´Ê¹¶ÔÓÚǧÍò¼¶±ðµÄÊý¾ÝmysqlÒ²Äܷdz£Ñ¸Ëٵķµ»Ø½á¹û
¶ø¶ÔÓÚ
SELECT count(*) from tablename WHERE…..
mysqlµÄ²éѯʱ¼ä¿ªÊ¼ÅÊÉý
×Ðϸ²éÔÄÀÛÏÂÊÖ²á,·¢ÏÖµ±Ã»ÓÐWHEREÓï¾ä¶ÔÓÚÕ ......
ÎÊÌâÊÇÕâÑùµÄ£º
Ò»ÕÅtestµÄ±í£¬×Ö·û¼¯²ÉÓõÄlatin1¡£
select to_id from test where to_id='cnÏó_Íõ';
+---------------+
| to_id |
+---------------+
| cnÌÕ_ÌÕ |
| cnÏó_Íõ |
+---------------+
2 rows in set (0.00 sec)
È¡cnÏó_ÍõµÄÊý¾Ý,¾ÓÈ»°ÑcnÌÕ_ÌÕµÄÊý¾ÝҲȡ»ØÀ´ÁË¡£
ÕâÏÔÈ»ÊDz»ÔÊÐíµÄ¡£
......