ÈçºÎÌá¸ßmysql load data infileµÄËÙ¶È
°æÈ¨ÉùÃ÷
£º×ªÔØÊ±ÇëÒÔ³¬Á´½ÓÐÎʽ±êÃ÷ÎÄÕÂÔʼ³ö´¦ºÍ×÷ÕßÐÅÏ¢¼°±¾ÉùÃ÷
http://bbayou.blogbus.com/logs/37045617.html
²âÊÔÊý¾Ý2.5G£¬¹²ÓÐÊý¾Ý9427567Ìõ¡£ÓõÄmysqlµÄlarge·þÎñÆ÷µÄÅäÖá£
load
Ò»´ÎÐèÒª´ó¸Å10·ÖÖÓ×óÓÒ¡£
½¨µÄ±íÓõÄÊÇMYISAM£¬µ÷ÕûÁ˼¸¸ösessionµÄ²ÎÊýÖµ
SET
SESSION
BULK_INSERT_BUFFER_SIZE
=
256217728
;
SET
SESSION
MYISAM_SORT_BUFFER_SIZE
=
256217728
;
ÔËÐнá¹ûÈçÏÂ
Query OK, 9427567 rows affected, 1558 warnings (3 min 55.21 sec)
Records: 9427567 Deleted: 0 Skipped: 0 Warnings: 0
googleµ½µÄ»¹¿ÉÒÔ
set
global
KEY_BUFFER_SIZE
=
256217728
;
alter
table
tablename
disable
keys
;
ÈçºÎload
Êý¾ÝÀïÃæ´ø·´Ð±¸Ü(backslash)”\” µÄÊý¾Ý
ÓÉÓÚÈç¹ûÄãûÓÐÖ¸¶¨FIELDS×Ӿ䣬ÔòĬÈÏֵΪ¼ÙÉèÄúдÏÂÈçÏÂÓï¾äʱµÄÖµ£º
FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ‘\\’
ËùÒÔ£¬Èç¹ûÄãµÄÊý¾ÝÀïÃæÓз´Ð±¸Ü(backslash)”\”µÄʱºò£¬Êý¾Ý»á³öÏÖ±»½Ø¶ÏµÄÎÊÌâ¡£³öÏÖÕâÖÖÎÊÌ⣬ֻҪдÉÏÈçϵÄfields×Ӿ伴¿É
FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ”
ÈçºÎload
²»Í¬±àÂëµÄÊý¾Ý
ÔÀ´ÓõÄ4.XµÄmysql£¬ÎÒÊÇselect INTO OUTFILE £¬Ö»ºóÓÃiconv,»òÕ߯äËûÈí¼þÀ´×ö¡£¿ÉÒԲο¼ÕâÀï
£¬µ«ÊÇÓÉÓÚÕâ´ÎÊý¾Ý´ó£¬ÓÃultraeditµÈÈí¼þ´ò¿ª¶¼Òª°ëÌì¡£ºÃÔÚаæµÄmysql¿ÉÒÔÔö¼ÓÒ»¸öеIJÎÊý
CHARACTER SET gbk
ÎÒµÄÎı¾Êý¾ÝÊÇGBKµÄ±àÂ룬Êý¾Ý±íÊÇutf8µÄ£¬ÓÃÕâÖÖ·½·¨²âÊԳɹ¦¡£
ÈçºÎload
µÄʱºòÖ»insertÌØ¶¨µÄÁÐ
±ÈÈç±íÀïÃæÓбÈÔªÊý¾Ý¶àµÄÁС£¿ÉÒÔÔÚload
µÄʱºòÖ¸¶¨Òª²åÈëµÄ×Ö¶ÎÃû×Ö¡£
ʾÀýµÄ´úÂëÈçÏ£º
LOAD
DATA
INFILE
'
~/data.txt
'
INTO
TABLE
fb0505
CHARACTER
SET
gbk
FIELDS
TERMINATED
BY
'
\t
'
ENCLOSED
BY
''
ESCAPED
BY
''
LINES
TERMINATED
BY
'
\n
'
STARTING
BY
''
(
seller_id
,
fb_type
,
fb_date
,
item_url
)
;
ÆäÖбífb0505ÀïÃæ»¹ÓÐÒ»ÁÐÊÇid¡£
Ïà¹ØÎĵµ£º
MySQLµÄÊý¾Ý±íÀàÐͺܶ࣬MySQLÒ»¹²ÏòÓû§ÌṩÁ˰üÀ¨DBD¡¢HEAP¡¢ISAM¡¢MERGE¡¢MyIAS¡¢InnoDBÒÔ¼°GemeniÕâ7ÖÖ±í¡£ÆäÖÐDBD¡¢InnoDBÊôÓÚÊÂÎñ°²È«Àà±í£¬¶øÆäËûÊôÓÚÊÂÎñ·Ç°²È«Àà±í¡£
DBD
Berkeley DB(DBD)±íÊÇÖ§³ÖÊÂÎñ´¦ÀíµÄ±í£¬ÓÉSleepycatÈí¼þ¹«Ë¾¿ª·¢¡£ËüÌṩMySQLÓû§ÆÚ´ýÒѾõŦÄÜ--ÊÂÎñ¿ØÖÆ¡£ÊÂÎñ¿ØÖÆÔÚÈκÎÊý¾Ý¿âϵͳÖÐ ......
ÔÚphpmyadminµÄ¸ùĿ¼Ï½¨Á¢config.inc.php
дÈëÈçÏ´úÂ룺
<?php
//phpmyadmin ÅäÖÃÎļþ
//±¾µØMySQLÊý¾Ý¿â
$i=1;
$cfg['Servers'][$i]['host'] =
'localhost';
$cfg['Servers'][$i]['user'] ......
cmdÏÂÇл»µ½ mysql °²×°Ä¿Â¼
Àý
d:/mysql/bin
(Èç¹ûÓÃWAMP°²×°ÔòÊÇD:\wamp\bin\mysql\mysql5.1.30\bin)
ǰÌ᣺mysqlÓû§rootÃÜÂëΪ¿Õ.
Êä
Èë mysql -u root mysql
mysql> ״̬ÏÂÊäÈë
update user set password=password('ÐÂÃÜÂë') where user='root';
ȯ
ÏÔ
Quer ......
´ËÎÄÕÂÖ÷ÒªÃèÊöµÄÊÇMySQLÊý¾ÝÀàÐÍÓ뽨¿â²ßÂÔµÄÏêϸ½âÎö£¬ÎÒÃÇ´ó¼Ò¶¼ÖªµÀÎÞÂÛÊÇÔÚ²»ÊշѵÄÊý¾Ý¿â¿Õ¼ä»òÕßÊÇ´óÐ͵ĵç×ÓÉÌÎñÍøÕ¾£¬ÆäºÏÀíµÄÉè¼Æ±í½á¹¹¡¢³ä·ÖÀûÓÿռäÊÇÊ®·Ö±ØÒªµÄ¡£
¡¡¡¡Õâ¾ÍÒªÇóÎÒÃǶÔÊý¾Ý¿âϵͳµÄ³£ÓÃMySQLÊý¾ÝÀàÐÍÓгä·ÖµÄÈÏʶ¡£ÏÂÃæÎҾͽ«ÎÒµÄÒ»µãÐĵÃд³öÀ´¸ú´ó¼Ò·ÖÏí¡£
¡¡¡¡Ò»¡¢MySQLÊý¾ÝÀàÐÍÖ®Êý×ÖÀàÐ ......
ÔÚZend FrameWorkÖе÷ÓÃmysqlµÄ´æ´¢¹ý³Ì·½·¨ÈçÏ£º
$db = Zend_Db::factory($sql_DbType,$sql_Config);
$connection=$db->getConnection();
Zend_Registry::set("db",$db);
Zend_Registry::set("connect",$connection);
µ÷Óô洢¹ý³ÌÈçÏ´úÂëËùʾ£º
$this->_db = Zend_Registry::get('db');
$connection=Zend_ ......