場景描述
最近工作中遇到需要導(dǎo)一批數(shù)據(jù)到mysql中欣范。數(shù)據(jù)以txt文件形式給出，大概有80G大小拍柒，統(tǒng)計后有三千多萬行心傀，也即會往表里導(dǎo)入三千多萬條數(shù)據(jù)。具體該怎么辦呢拆讯？寫程序一行一行讀文本脂男，然后拼接SQL語句往數(shù)據(jù)庫里插入（批量插入），太耗時了也太麻煩了种呐，否定了這種想法宰翅。有沒有工具能夠直接從文本到數(shù)據(jù)到mysql呢？經(jīng)查詢爽室，mysql客戶端自帶了導(dǎo)入工具mysqlimport同時mysql本身就支持LOAD DATA SQL語句汁讼。最終我選用了mysql的LOAD DATA命令來導(dǎo)入這些數(shù)據(jù)。下面我們就通過這個場景，一起了解一下mysql文本導(dǎo)入的使用方法嘿架。
文本預(yù)處理
數(shù)據(jù)文本是由以'\01'分隔的一行行字符串構(gòu)成的卜录。我們必須把這里的'\01'替換成mysql文本導(dǎo)入中支持的分隔符，我們這里選擇替換成'\t\t',在文本目錄下執(zhí)行命令：
sed -i 's/\x01/\t\t/g' ./*
sed的執(zhí)行速度還是很快的眶明，一千六百萬行文本五分鐘左右就替換好了。替換后的文本如下所示：
現(xiàn)在我們的數(shù)據(jù)經(jīng)過預(yù)處理筐高，已經(jīng)可以用來導(dǎo)入了搜囱。
數(shù)據(jù)導(dǎo)入
前面也說過，我們選擇mysql的命令來導(dǎo)入文本數(shù)據(jù)柑土。所以我們先一起來看一下mysql的數(shù)據(jù)導(dǎo)入語法：
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
我們一個個參數(shù)具體解釋一下：
- file_name
file_name必須是本地的要導(dǎo)入的文本數(shù)據(jù) - CHARACTER SET
CHARACTER SET指定導(dǎo)入的文件的字符格式 - FIELDS | COLUMNS
如果需要在導(dǎo)入數(shù)據(jù)時對導(dǎo)入的文本進行分割蜀肘，我們需要添加FIELDS TERMINATED BY ','語法，LINES TERMINATED BY 語句是以指定字符分隔文本為行稽屏，默認是以換行符進行行分隔扮宠。 - IGNORE number [LINES | ROWS]
IGNORE 后面加數(shù)字表示導(dǎo)入數(shù)據(jù)時忽略文本的前多少行. - (col_name1,col_name2...)字段列表
每一行文本被分隔為一個個字段，但是我們導(dǎo)入這些數(shù)據(jù)的時候狐榔，每個字段對應(yīng)到表中哪一個字段坛增，是在此處指定的。數(shù)據(jù)文本中的字段按順序?qū)氲皆撟侄瘟斜碇付ǖ淖侄沃斜∧濉Ｈ绻霈F(xiàn)文本中的字段比我們mysql表中的字段要多收捣，或者是我們只希望導(dǎo)入每一行數(shù)據(jù)的某一些字段，我們可以在字段列表中按照導(dǎo)入文本的字段順序加入一些用戶變量庵楷，如下所示：
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);
這樣做就相當于我們直接丟棄了導(dǎo)入文本中的某些字段值罢艾。
- SET col_name = expr
如果表中某些字段我們希望是一個特定值，我們可以在這里直接設(shè)置尽纽。
我們的具體導(dǎo)入sql語句如下：
LOAD DATA INFILE '/opt/data/backup/xx_01_xx
INTO TABLE db_abc.t_info_xxx CHARACTER SET utf8 FIELDS TERMINATED BY '\t\t' IGNORE 1 LINES
(@dummycola,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,
@dummycolb,@dummycolc,col12,col13,@dummycold,col14,col15,
@dummycole,col16,col17,col18,col19,@dummycolf,col19,col20,
col21,col22,col23,col24,col25,col26,col27);
先拿一部分數(shù)據(jù)測試咐蚯，一百六十萬行數(shù)據(jù)，導(dǎo)入大概花了2min左右弄贿，速度還是可以接受的春锋。
執(zhí)行結(jié)束后，我們可以通過sql命令SHOW WARNINGS查看數(shù)據(jù)導(dǎo)入中的一些告警挎春，我的導(dǎo)入中就發(fā)現(xiàn)了一些字段的寬度不夠的情況看疙，通過調(diào)整、測試直奋，保證表結(jié)構(gòu)無誤能庆，就可以正式來導(dǎo)入這些數(shù)據(jù)了。
由于我們的這些數(shù)據(jù)是由十多個文本組成的脚线，一條一條語句的在mysql客戶端命令行去執(zhí)行每個文件的導(dǎo)入太麻煩了搁胆，寫個腳本來執(zhí)行方便多了，我們的腳本如下：
#!/bin/bash
mysql=/usr/bin/mysql
host="192.168.0.1"
port=1234
username=xxx
passwd="xxx"
dbname=db_abc
tbname=t_info_xxx
time0=`date +%s`
echo "------------------start to import data into mysql...-----------------------"
echo -e "\n"
for i in `seq 1 12`
do
num=`printf "%.2d" $i`
filename="xxx_${num}_xxx"
echo "import file:${filename}..."
time1=`date +%s`
mysql -h${host} -P${port} -u${username} -p${passwd} -e "
use ${dbname};
LOAD DATA LOCAL INFILE '/opt/data/backup/${filename}' INTO TABLE ${dbname}.${tbname} CHARACTER SET utf8
FIELDS TERMINATED BY '\t\t' IGNORE 1 LINES
(@dummycola,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,@dummycolb,
@dummycolc,col12,col13,@dummycold,col14,col15,@dummycole,col16,col17,col18,col19,@dummycolf,col19,col20,col21,col22,col23,col24,col25,col26,col27);
quit
"
echo "import file:${filename} success!"
time2=`date +%s`
comp1=`expr $time2 - $time1`
echo "import file:${filename} has cost ${comp1} seconds!"
echo -e "\n"
done
timen=`date +%s`
comp2=`expr $timen - $time0`
echo "The whole data import cost ${comp2} seconds!"
echo -e "\n"
echo "-----------------import data into mysql end!-----------------------"
注：
- 因為是公司內(nèi)部數(shù)據(jù)，所以具體的字段名稱渠旁、數(shù)據(jù)庫名稱等都做了處理攀例，但是這個腳本的整體結(jié)構(gòu)是一樣的。
- 最后sql語句中的字段列表中有一些@dummycolx列顾腊，這些用戶變量是為了解決文本中多出來的一些字段粤铭，而我們數(shù)據(jù)庫表中沒有對應(yīng)字段，或者我們不需要一些文本中的字段杂靶，把這些@dummycolx列放在這里就相當于丟棄了這些字段梆惯，只導(dǎo)入我們需要的那些字段。
- 最終我們一共導(dǎo)入了2000萬條數(shù)據(jù)吗垮，總共耗時10多分鐘垛吗。
另外
mysqlimport工具使用起來也是十分方便，實際上mysqlimport工具內(nèi)部就是包裝了LOAD DATA INFILE語句烁登。如果使用的話怯屉，具體可以參考這里：mysqlimport
