mysql load data infile
ÈçºÎÌá¸ßmysql load
data
infile
µÄËÙ¶È
²âÊÔÊý¾Ý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
µÄʱºòÖ»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¡£
Ïà¹ØÎĵµ£º
1¡¢ÔÚlinuxÏ£º
²é¿´mysql ÊÇ·ñÔËÐУºps -ef | grep myslq
Èç¹û MySQL ÕýÔÚÔËÐУ¬Ê×ÏÈɱ֮: kill mysqlµÄ½ø³ÌºÅ¡£
¡¡¡¡Æô¶¯ MySQL :/usr/bin/safe_mysqld --skip-grant-tables &
¡¡¡¡¾Í¿ÉÒÔ²»ÐèÒªÃÜÂë¾Í½øÈë MySQL ÁË¡£
¡¡¡¡È»ºó¾ÍÊÇ
¡¡¡¡>use mysql
¡¡¡¡ ......
Ò»:mysqlÀïĿǰֻ֧³Ö4ÖÖË÷Òý·Ö±ðÊÇ:b-tree,full-text,hashÒÔ¼°r-treeË÷Òý b-treeË÷ÒýÓ¦¸ÃÊÇmysqlÀï×î¹ã·ºµÄË÷ÒýµÄÁË,³ýÁËarchive,»ù±¾ËùÓеĴ洢ÒýÇæ¶¼Ö§³ÖËü. 1.b-treeÔÚmyisamÀïµÄÐÎʽºÍinnodbÉÔÓв»Í¬ ÔÚinnodbÀïÃæÓÐÁ½ÖÖÐÎ̬:ÆäÒ»ÊÇprimary keyÐÎ̬Æäleaf nodeÀï´æ·ÅµÄÊÇÊý¾Ý.¶øÇÒ²»½ö´æ·ÅÁËË÷Òý¼üµÄÊý¾Ý,»¹´æ·ÅÁËÆ ......
[root@sql21 ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.26-rc-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database &nb ......
......