MySQLʹÓü¼ÇÉÐĵÃ
2009Äê10ÔÂ30ÈÕ ÐÇÆÚÎå 22:53
ÓÃmysql
ÄÚÖú¯Êýת»»ipµØÖ·ºÍÊý×Ö
ÀûÓÃÁ½¸öÄÚÖú¯Êý
inet_aton:½«ipµØַת»»³ÉÊý×ÖÐÍ
inet_ntoa:½«Êý×ÖÐÍת»»³ÉipµØÖ·
³ä·ÖÀûÓÃmysqlÄÚÖõÄ
format
º¯Êý
ÓÈÆäÊÇÔÚ´¦Àí×Ö·û¸ñʽµÄʱºò,ÀýÈ罫12345ת»»³É12,345ÕâÑùµÄ,Ö»ÒªÓÃ:format(12345,0)¼´¿É,Èç¹ûÓÃformat(12345,2)ÔòÏÔʾµÄÊÇ12,345.00ÁË...
ÀûÓÃmysqlµÄÄÚÖú¯Êý´¦Àíʱ¼ä´ÁÎÊÌâ
eg : select from_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');
½á¹û: 2004 3rd August 03:35:48 2004
ÀûÓÃmysql_convert_table_formatת»»±íÀàÐÍ
ÐèÒªDBIºÍDBDµÄmysqlÏà¹ØÄ£¿éÖ§³Ö²ÅÄÜÓÃ,Àý×Ó:
mysql_convert_table_format --user=root --
password
='xx' --type=myisam test yejr
ÐÞ¸Ämysql±íÖеÄ×Ö¶ÎÃû
alter
table
tb_name change old_col new_col definition...
ÀûÓÃÁÙʱ±äÁ¿
select @var1:=a1+a2 as a_sum,@var2:=b1+b2 as b_sum,@var1+@var2 as total_sum from test_table xxx;
ÓÃ
int
ÀàÐÍ´æ´¢ipµØÖ·
ÔÏÈ´íÎóµÄÈÏΪ±ØÐëÓÃbigint²Å¹»£¬ºóÀ´·¢ÏÖʹÓÃint unsignedÀàÐ;Í×ã¹»ÁË¡£ :)
ÀûÓÃIFº¯Êý¿ìËÙÐÞ¸ÄENUM×Ö¶ÎÖµ
Ò»¸öÀý×Ó£º
update rule
set
enable = if('0' = enable,'1','0') where xxx;
enable ÀàÐÍ£ºenum('0','1') not null default '0'
ÊÂÎñÎÞ·¨Ç¶Ì×
±ÜÃⳤʱ¼äµÄsleepÁ¬½ÓÔì³ÉµÄÁ¬½ÓÊý³¬³öÎÊÌâ
É趨ȫ¾Ö±äÁ¿ wait_timeout ºÍ interactive_timeout Ϊ±È½ÏСµÄÖµ£¬ÀýÈç
10(s)£¬¾ÍÄÜʹÿ¸ösleepÁ¬½ÓÔÚ10sÖ®ºóÈç¹û»¹Ã»Óвéѯ
µÄ»°×Ô¶¯¶Ï¿ª¡£
É趨mysql¿Í»§¶ËµÄÌáʾ·û(prompt)
export MYSQL_PS1="(\\u:\\h:)\\d> "
ÔòÓà mysql -hlocalhost -uroot -pxx db_name µÇ¼ºó£¬Ìáʾ·û±ä³É£º
(root:localhost:)db_name>
ºÃÓÃ°É :), ʱʱ¿Ì¿ÌÌáÐÑÄãÔÚÄĸö·þÎñÆ÷ÉÏ£¬¾¡Á¿±ÜÃâÎó²Ù×÷µÄ·¢Éú
¸üÏêϸµÄÇë²é¿´MySQLÊÖ²á
ÕûÀíMyISAMËéƬ
1.) ¶¨ÆÚÔËÐÐ OPTIMIZE TABLE ÃüÁî¼´¿É
2.) ÓÃmysqldump³öÊý¾Ý£¬È»ºóÖØÐÂ
import
»ØÈ¥£¬Õâ¶Ô´ó±íÀ´ËµÏÔÈ»µÚÒ»ÖÖ·½·¨±È½Ï·½±ã
ÕûÀíInnodbËéƬ
1.) ÔËÐÐNULLÃüÁ ALTER TABLE XXX ENGINE=Innodb;
2.) ͬÉϵÄmysqldump·½·¨
MySQLÈç¹ûÈÏΪ¼ìË÷µÄ¼Ç¼ÊýÁ¿³¬¹ý×ܼǼÊýµÄ30%£¬ÔòÑ¡ÔñÈ«±íɨÃ裬¶ø·ÇʹÓÃË÷Òý
MySQL 5.0.3Ö®ºó,VARCHAR×ֶκóÃæµÄ¿Õ¸ñ¾Í²»ÔÙɾ³ý
Éý¼¶µ½4.1.1»ò¸ü¸ßºó£¬¾ÍºÜÄѽµ¼¶»Øµ½4.0»ò4.1ÁË£¬ÒòΪ InnoDB ʹÓÃÁ˶à¸ö±í¿Õ¼äµÄÔµ¹Ê
MySQL 4.1Ö®ºó£¬MySQL°Ñ
Ïà¹ØÎĵµ£º
µÚÒ»¡¢mysql·þÎñµÄÆô¶¯ºÍÍ£Ö¹
net stop mysql
net start mysql
µÚ¶þ¡¢µÇ½mysql
Óï·¨ÈçÏ£º mysql -uÓû§Ãû -pÓû§ÃÜÂë
¼üÈëÃüÁîmysql -uroot -p£¬ »Ø³µºóÌáʾÄãÊäÈëÃÜÂ룬ÊäÈë12345£¬È»ºó»Ø³µ¼´¿É½øÈëµ½mysqlÖÐÁË£¬mysqlµÄÌáʾ·ûÊÇ£º
mysql>
×¢Ò⣬Èç¹ûÊÇÁ¬½Óµ½ÁíÍâµÄ»úÆ÷ÉÏ£¬ÔòÐèÒª¼ÓÈëÒ»¸ö²ÎÊý-h»úÆ÷IP
µÚÈý¡ ......
GRANT ALL PRIVILEGES
1¡£ ¸Ä±í·¨¡£¿ÉÄÜÊÇÄãµÄÕʺŲ»ÔÊÐí´ÓÔ¶³ÌµÇ½£¬Ö»ÄÜÔÚlocalhost¡£Õâ¸öʱºòÖ»ÒªÔÚlocalhostµÄÄÇ̨µçÄÔ£¬µÇÈëmysqlºó£¬¸ü¸Ä "mysql" Êý¾Ý¿âÀïµÄ "user" ±íÀïµÄ "host" Ï´Ó"localhost"¸Ä³Æ"%"
Sql´úÂë ¸´ÖÆ´úÂë
1. mysql -u root -pvmwaremysql>use mysql;
  ......
ÓÉÓÚÏîÄ¿ÐèÒª,¶Ômysql·Ö²¼ÊÂÎñÓÐÒ»¶¨ÒªÇó.ÕâÀïÏȼòµ¥ËµÃ÷±ØÒª.
¼ÙÉè,ÈÕ³£²ÆÎñÖÐ,ÒªÍê³ÉÒ»¸öÓµ»¤¶©¹º,±ØÐ뽫¶¨µ¥²Ù×÷ºÍ²ÆÎñ²Ù×÷·âÔÚÒ»¸öÊÂÎñÖвÅÄܱ£Ö¤½»Ò×ÍêÕûÐÔ.
Èç¹û¶¨µ¥ºÍ²ÆÎñÊý¾Ý¶¼·Ö²¼ÔÚ¸÷×ÔµÄʵÀýÖÐ,Ôò±ØÐëÓ÷ֲ¼ÊÂÎñ²ÅÄÜÂú×ã.
·Ï»°²»¶à£¬½øÈë·Ö²¼ÊÂÎñ̽Ë÷¡£
(±¾ÎÄÀý×ÓÀ´×ÔÍøÂ磬±¾ÎÄÖ»×¢ÖØ̽Ë÷£©
1.ÔÚ±¾»ú loca ......
mysql ½Øȡijһ¸öʱ¼ä(datetimeÀàÐÍ)µÄÈÕÆÚ£º
·½·¨1£ºselect date(row_name) from table_name where row = row1;
·½·¨2£ºselect left(row_name, 10) from table_name where row = row1;
·½·¨3£ºselect cast(row_name as char[10]) from table_name where row = row1;
È¡µÃij¸öÈÕÆÚµÄtime_tÊýÖµ£ºselect unix_times ......