[MySQLÓÅ»¯] ÈçºÎ¶¨Î»Ð§Âʽϵ͵ÄSQL
[MySQLÓÅ»¯] -- ÈçºÎ¶¨Î»Ð§Âʽϵ͵ÄSQL
ʱ¼ä:2010-2-28À´Ô´:HaCMS¿ªÔ´ÉçÇø ×÷Õß:zhenpao
Ò»°ãͨ¹ýÒÔÏÂÁ½ÖÖ·½Ê½¶¨Î»Ö´ÐÐЧÂÊ½ÏµÍµÄ SQL Óï¾ä¡£ Âý²éѯÈÕÖ¾ÔÚ²éѯ½áÊøÒÔºó²Å¼Í¼£¬ËùÒÔÔÚÓ¦Ó÷´Ó³Ö´ÐÐЧÂʳöÏÖÎÊÌâµÄʱºò²éѯÂý²éѯÈÕÖ¾²¢²»Äܶ¨Î»ÎÊÌ⣬¿ÉÒÔʹÓà show processlist ÃüÁî²é¿´µ±Ç° MySQL ÔÚ½øÐеÄỊ̈߳¬°üÀ¨Ï̵߳Ä״̬¡¢ÊÇ·ñËø±íµÈ£¬¿ÉÒÔʵʱµØ²é ...
Ò»°ãͨ¹ýÒÔÏÂÁ½ÖÖ·½Ê½¶¨Î»Ö´ÐÐЧÂÊ½ÏµÍµÄ SQL Óï¾ä¡£
Âý²éѯÈÕÖ¾ÔÚ²éѯ½áÊøÒÔºó²Å¼Í¼£¬ËùÒÔÔÚÓ¦Ó÷´Ó³Ö´ÐÐЧÂʳöÏÖÎÊÌâµÄʱºò²éѯÂý²éѯÈÕÖ¾²¢²»Äܶ¨Î»ÎÊÌ⣬¿ÉÒÔʹÓà show processlist ÃüÁî²é¿´µ±Ç° MySQL ÔÚ½øÐеÄỊ̈߳¬°üÀ¨Ï̵߳Ä״̬¡¢ÊÇ·ñËø±íµÈ£¬¿ÉÒÔʵʱµØ²é¿´ SQL µÄ Ö´ÐÐÇé¿ö£¬Í¬Ê±¶ÔÒ»Ð©Ëø±í²Ù×÷½øÐÐÓÅ»¯¡£
ÏÂÃæÎÒÃǾÙÀý˵Ã÷һϣ¬ÈçºÎͨ¹ýÂý²éѯÈÕÖ¾¶¨Î»Ö´ÐÐЧÂÊµ×µÄ SQL Óï¾ä£º
¿ªÆôÂý²éѯÈÕÖ¾ , ÅäÖÃÑùÀý£º
[mysqld]
log-slow-queries
ÔÚ my.cnf ÅäÖÃÎļþÖÐÔö¼ÓÉÏÊöÅäÖÃÏî²¢ÖØÆô mysql ·þÎñ£¬Õâʱ mysql Âý²éѯ¹¦ÄÜÉúЧ¡£Âý²éѯ ÈÕÖ¾½«Ð´Èë²ÎÊý DATADIR £¨Êý¾ÝĿ¼£©Ö¸¶¨µÄ·¾¶Ï£¬Ä¬ÈÏÎļþÃûÊÇ host_name-slow.log ¡£
ºÍ´íÎóÈÕÖ¾¡¢²éѯÈÕÖ¾Ò»Ñù£¬Âý²éѯÈÕÖ¾¼Ç¼µÄ¸ñʽҲÊÇ´¿Îı¾£¬¿ÉÒÔ±»Ö±½Ó¶ÁÈ¡¡£ÏÂÀýÖÐÑÝʾÁËÂý²éѯÈÕÖ¾µÄÉèÖúͶÁÈ¡¹ý³Ì¡£
£¨ 1 £©Ê×ÏȲéѯһÏ long_query_time µÄÖµ ¡£
mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
£¨ 2 £©ÎªÁË·½±ã²âÊÔ£¬½«ÐÞ¸ÄÂý²éѯʱ¼äΪ 5 Ãë¡£
mysql> set long_query_time=5;
Query OK, 0 rows affected (0.02 sec)
£¨ 3 £©ÒÀ´ÎÖ´ÐÐÏÂÃæÁ½¸ö²éѯÓï¾ä¡£
µÚÒ»¸ö²éѯÒòΪ²éѯʱ¼äµÍÓÚ 5 Ãë¶ø²»»á³öÏÖÔÚÂý²éѯÈÕÖ¾ÖУº
mysql> select count(*) from order2008;
+----------+
| count(*) |
+----------+
| 208 |
+----------+
1 row in set (0.00 sec)
µÚ¶þ¸ö²éѯÒòΪ²éѯʱ¼ä´óÓÚ 5 Ãë¶øÓ¦¸Ã³öÏÖÔÚÂý²éѯÈÕÖ¾ÖУº
mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
| 655296
Ïà¹ØÎĵµ£º
mysqlÖÐINSTRº¯ÊýµÄÓ÷¨
INSTR(×Ö¶ÎÃû, ×Ö·û´®)
Õâ¸öº¯Êý·µ»Ø×Ö·û´®ÔÚijһ¸ö×ֶεÄÄÚÈÝÖеÄλÖÃ, ûÓÐÕÒµ½×Ö·û´®·µ»Ø0£¬·ñÔò·µ»ØÎ»Ö㨴Ó1¿ªÊ¼£©
SELECT * from tblTopic ORDER BY INSTR( topicTitle, 'ha' ) > 0 DESC
SELECT INSTR( topicTitle, 'ha' ) from tblTopic
mysqlÖÐʹÓÃinstrÅäºÏINÅÅÐò
½«instr½á¹û×÷Î ......
MYSQL4.1¼°¸ü¸ß°æ±¾Ö§³Ö·þÎñÆ÷¶Ë×¼±¸Óï¾ä(Prepared Statements), ËüʹÓÃÔöÇ¿µÄ¶þ½øÖƿͻ§¶Ë/·þÎñÆ÷ÐÒéÔÚ¿Í»§¶ËºÍ·þÎñÆ÷Ö®¼ä¸ßЧµÄ·¢ËÍÊý¾Ý£¬¿ÉÒÔͨ¹ýÖ§³ÖÕâÖÖÐÐÐÒéµÄ±à³Ì¿âÀ´·ÃÎÊ×¼±¸Óï¾ä£¬ÁÐÈëMYSQL CAPI,MYSQL Connector/JºÍMYSQL Connector/NET ΪJAVAºÍ.NETÌṩÁËͬÑùµÄ·ÃÎʽӿڡ£ËüÒ²ÓÐSQLÓïÑԵķÃÎʽӿڡ£
´´½¨×¼ ......
¸ù¾ÝÄãµÄʹÓÃÄ¿µÄÎÒ¾õµÃÕâ¸öº¯ÊýÓÐÁ½·½ÃæµÄÓÃ;£º
·ÀÖ¹SQL Injection¹¥»÷£¬Ò²¾ÍÊÇÄã±ØÐëÑéÖ¤Óû§µÄÊäÈë
²Ù×÷Êý¾ÝµÄʱºò±ÜÃâ²»±ØÒªµÄ×Ö·ûµ¼Ö´íÎó
mysql_real_escape_string() º¯ÊýתÒå SQL Óï¾äÖÐʹÓõÄ×Ö·û´®ÖеÄÌØÊâ×Ö·û¡£
ÏÂÁÐ×Ö·ûÊÜÓ°Ï죺
\x00
\n
\r
\
'
"
\x1a
Èç¹û³É¹¦£¬Ôò¸Ãº¯Êý·µ»Ø±»×ªÒåµÄ×Ö·û´ ......
»·¾³£º
²Ù×÷ϵͳ£ºWIN2003
MySql£ºmysql Ver 12.22 Distrib 4.0.18, for Win95/Win98 (i32)
˼Ï룺
ΪÁ˰²È«¼ÓÃÜÐèÒªÉèÖÃmysqlÊý¾Ý¿âÏà¹ØÓû§µÄÃÜÂ룻
MySqlÊý¾Ý¿âµÄÃÜÂë¶¼ÊDZ£´æÔÚmysqlÊý¾Ý¿âʵÀýµÄuser±íÖеÄpassword×Ö¶ÎÀͨ¹ýPASSWORD("")µÄº¯Êý¶ÔÃÜÂë¼ÓÃܺ󱣴æÔÚ±íÖУ»
ËùÒÔÒªÐÞ¸ÄÓû§µÄÃÜÂëÖ ......
¡¾×ª¡¿http://www.linuxsir.org/main/?q=node/241
1.°²×°»·¾³
²Ù×÷ϵͳ£ºRed Hat Linux Enterprise AS 4.0
Êý¾Ý¿â£ºMySQL 5.0.24
Web·þÎñÆ÷£ºApache 2.2.3
½Å±¾ÓïÑÔ£ºPHP 5.1.6
2.°²×°MySQL 5.0.24
-------------ÏÂÔØÈí¼þ°ümysql-5.0.24.tar.gz£¬µØÖ·http://www.mysql.com-------------
# tar zvxf mysql-5.0.24.ta ......