Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

[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º¯ÊýµÄÓ÷¨

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½á¹û×÷Î ......

¸ßÐÔÄÜMYSQL¶ÁÊé±Ê¼Ç£ºMYSQL×¼±¸Óï¾ä

MYSQL4.1¼°¸ü¸ß°æ±¾Ö§³Ö·þÎñÆ÷¶Ë×¼±¸Óï¾ä(Prepared Statements), ËüʹÓÃÔöÇ¿µÄ¶þ½øÖƿͻ§¶Ë/·þÎñÆ÷ЭÒéÔÚ¿Í»§¶ËºÍ·þÎñÆ÷Ö®¼ä¸ßЧµÄ·¢ËÍÊý¾Ý£¬¿ÉÒÔͨ¹ýÖ§³ÖÕâÖÖÐÐЭÒéµÄ±à³Ì¿âÀ´·ÃÎÊ×¼±¸Óï¾ä£¬ÁÐÈëMYSQL CAPI,MYSQL Connector/JºÍMYSQL Connector/NET ΪJAVAºÍ.NETÌṩÁËͬÑùµÄ·ÃÎʽӿڡ£ËüÒ²ÓÐSQLÓïÑԵķÃÎʽӿڡ£
´´½¨×¼ ......

PHPÖеÄmysql_real_escape_stringº¯Êý

¸ù¾ÝÄãµÄʹÓÃÄ¿µÄÎÒ¾õµÃÕâ¸öº¯ÊýÓÐÁ½·½ÃæµÄÓÃ;£º
·ÀÖ¹SQL Injection¹¥»÷£¬Ò²¾ÍÊÇÄã±ØÐëÑéÖ¤Óû§µÄÊäÈë
²Ù×÷Êý¾ÝµÄʱºò±ÜÃâ²»±ØÒªµÄ×Ö·ûµ¼Ö´íÎó
mysql_real_escape_string() º¯ÊýתÒå SQL Óï¾äÖÐʹÓõÄ×Ö·û´®ÖеÄÌØÊâ×Ö·û¡£
ÏÂÁÐ×Ö·ûÊÜÓ°Ï죺
\x00
\n
\r
\
'
"
\x1a
Èç¹û³É¹¦£¬Ôò¸Ãº¯Êý·µ»Ø±»×ªÒåµÄ×Ö·û´ ......

MySqlÊý¾Ý¿âÓû§ÃÜÂëµÄÐÞ¸Ä

»·¾³£º
²Ù×÷ϵͳ£ºWIN2003
MySql£ºmysql  Ver 12.22 Distrib 4.0.18, for Win95/Win98 (i32)
 
˼Ï룺
ΪÁ˰²È«¼ÓÃÜÐèÒªÉèÖÃmysqlÊý¾Ý¿âÏà¹ØÓû§µÄÃÜÂ룻
MySqlÊý¾Ý¿âµÄÃÜÂë¶¼ÊDZ£´æÔÚmysqlÊý¾Ý¿âʵÀýµÄuser±íÖеÄpassword×Ö¶ÎÀͨ¹ýPASSWORD("")µÄº¯Êý¶ÔÃÜÂë¼ÓÃܺ󱣴æÔÚ±íÖУ»
ËùÒÔÒªÐÞ¸ÄÓû§µÄÃÜÂëÖ ......

LinuxÏÂApache 2.2, MySQL 5.0ºÍPHP 5.0µÄ°²×°ÓëÅäÖÃ

¡¾×ª¡¿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 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ